When implementing delete primary table data, determine if the foreign key table associated with it has a data reference, a flag, and no delete

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 bit, if not used to delete directly, how to achieve this process 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 from SQL Server 2005 and use error handling to determine whether an update is required.

-for example, below.

Use tempdb

Go

CREATE TABLE m (

ID int PRIMARY KEY,

BZ bit)

INSERT m SELECT 1, 0

UNION 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, 1

Go

--Removing processing stored procedures

CREATE PROC Dbo.p_delete

@id int

As

SET NOCOUNT on

BEGIN TRY

BEGIN TRAN

DELETE from m WHERE id = @id

COMMIT TRAN

End TRY

BEGIN CATCH

ROLLBACK TRAN

If error_number () = 547--If a FOREIGN KEY constraint error

BEGIN

BEGIN TRY

BEGIN TRAN--Update flag

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 CATCH

Go

--Call

EXEC Dbo.p_delete 1

EXEC Dbo.p_delete 2

SELECT * from M

SELECT * from C

Go

DROP