Problem Description: A basic information table, with more than 30 tables in the system, there is a foreign key relationship, when the basic data to delete, you need to determine whether has been used, if used to change the flag, if not used to delete directly, how to achieve this deal well? It's best to automatically adapt to changes in the table
Problem Resolution (SQL Server
????)--SQL Server 2005 error handling is easy to control, so you can delete it directly in SQL Server 2005 and use error handling to determine if it needs to be updated. --for example below. Use Tempdbgo create TABLE m ( ID int PRIMARY KEY, bz bit) INSERT m SELECT 1, 0UNIO N all SELECT 2, 0 create TABLE C ( ID int primary KEY, a_id int references m (ID) on delete NO ACTION) INSERT C SELECT 1, 1go --Delete processing stored procedure create PROC Dbo.p_d elete @id intasset NOCOUNT onbegin trybegin tran DELETE from m WHERE id = @idCOMMIT TR Anend trybegin CATCH ROLLBACK tran if error_number () = 547--If a FOREIGN KEY constraint error & nbsp begin begin try Begin TRAN --Update logo UPDATE m SET BZ = 1 WHERE id = @id COMMIT tran End try BEGIN catch SELECT error_number (), Error_message () End catch end else SELECT error_number (), error_message () end catchgo --invokes exec dbo.p_delete 1EXEC dbo.p_delete 2SELECT * from Mselect * From Cgo drop TABLE C, Mdrop PROC Dbo.p_delete
Problem Resolution (SQL Server
) --SQL Server 2000 is not good for error handling, it's generally recommended--you can get all the foreign key tables associated with a table --sample stored procedure create PROC dbo.p_delete by querying system tables from the system table. @tbname sysname, --Basic data table name @PkFieldName sysname, -Basic Data Table key field name @PkValue int -The underlying data table key value to delete asset NOCOUNT ondeclare @bz bit, @s nvarchar (4000) DECLARE TB CURSOR localforselect N ' SET @bz = case when EXISTS ( SELECT * from ' + QuoteName (@tbname) & nbsp; + n ' A, ' + QuoteName (object_name (B.fkeyid)) + n ' b& nbsp; where A. ' + QuoteName ((SELECT name from syscolumns where colid = B.rkey and id = B.rkeyid)) + N ' = B. ' + QuoteName (SELECT name from syscolumns WHERE colid = b. Fkey and id = b.fkeyid)) + N ' and A. ' + QuoteName ((SELECT name from syscolumns WHERE colid = b.rkey and id = b.rkeyid))   &N bsp; + N ' = @id) THEN 1 ELSE 0 end ' from sysobjects a JOIN Sysforeignkeys b on a.id= b.constid JOIN sysobjects C on a.parent_obj = C.idwhere A.xtype = ' F ' and C.xtype = ' U ' and object_name (B.rkeyid) = @tbnameOPEN Tbfetch TB into @sWHILE @ @FETCH_STATUS = 0begin EXEC sp_executes QL @s, N ' @tbname sysname, @id int, @bz bit out ', @tbname, @PkValue, @bz out IF @bz = 1 begin SET @s = N ' UPDATE ' + QuoteName (@tbname) + N ' SET BZ = 1 WHERE ' + QuoteName (@PkFieldName) & nbsp; +n ' = @id ' EXEC sp_executesql @s, n ' @id int ', @PkValue return end FETCH TB into @sENDCLOSE Tbdeallocate Tb set @s = n ' DELETE from ' + QuoteName (@tbname) + n ' WHERE ' + QuoteName (@PkFieldNam E + n ' = @id ' EXEC sp_executesql @s, n ' @id int ', @PkValueGO
Attention MattersDo not set cascading deletions when setting primary/foreign key relationships for a table (on delete CASCADE)
