||SQL Server Tips by Gama and Naughter
Update Set NULL
Set NULL requires one trigger for table [Tbl A] that will set to
NULL values in the related columns from table [Tbl B].
CREATE TRIGGER "[Tbl A UNTrig]" ON [Tbl A] FOR UPDATE AS
SET NOCOUNT ON
/* * UPDATE 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, inserted
WHERE deleted. [Col A]= [Tbl B].[Col B]
Let us test the trigger:
UPDATE [Tbl A] SET [col A]='2222' WHERE [col A]='1111'
It is easy to verify that the trigger worked by examining the
contents of the table.
The above book excerpt is from:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter