||SQL Server Tips by Gama and Naughter
Delete No Action
Delete No Action requires one trigger for table [Tbl A] that will
abort the operation if there are related columns in table [Tbl B].
CREATE TRIGGER "[Tbl A Dtrig]" ON [Tbl A] FOR DELETE AS
SET NOCOUNT ON
/* * PREVENT DELETES IF DEPENDENT RECORDS IN [Tbl B]*/
IF (SELECT COUNT(*) FROM deleted, [Tbl B] WHERE (deleted. [Col A]= [Tbl
B]. [Col B])) > 0
RAISERROR 44445 'The record can''t be deleted. Since related
records exist in table ''[Tbl B]'', referential integrity rules
would be violated.'
Testing 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