||SQL Server Tips by Gama and Naughter
The Set NULL action is implemented by triggers that will examine the
data and then set to NULL the rows from table [Tbl B] related to
changed or deleted records from table [Tbl A].
Delete Set NULL
Delete set NULL requires one trigger for table [Tbl A] that will set
to NULL all the values of the related columns from table [Tbl B].
CREATE TRIGGER "[Tbl A DNTrig]" ON [Tbl A] FOR DELETE AS
SET NOCOUNT ON
/* * DELETE SET NULL TO '[Tbl B]' */
IF (SELECT COUNT(*) FROM deleted, [Tbl B] WHERE (deleted. [Col A]= [Tbl
B]. [Col B])) > 0
UPDATE [Tbl B]
SET [Tbl B]. [Col B]=NULL
FROM [Tbl B], deleted
WHERE deleted. [Col A]= [Tbl B].[Col B]
Let us test the trigger:
DELETE [Tbl A] WHERE [col A]='value2'
The above book excerpt is from:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter