||SQL Server Tips by Gama and Naughter
This cascade action is implemented by triggers that will abort the
operation if related data is found.
Update No Action
Update 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 Utrig]" ON [Tbl A] FOR UPDATE AS
SET NOCOUNT ON
/* * PREVENT UPDATES IF DEPENDENT RECORDS IN [Tbl B]*/
IF UPDATE([Col A])
IF (SELECT COUNT(*) FROM deleted, [Tbl B] WHERE (deleted. [Col A]= [Tbl
B]. [Col B])) > 0
RAISERROR 44445 'The record can''t be changed. Since related
records exist in table ''[Tbl B]'', referential integrity rules
would be violated.'
The code will first check if column [Col A] was updated, in this
case there is only one column being updated but there are other
cases when several columns are updated and it might be useful to
know which. Next it will check if there are related columns in table
[Tbl B], if so, it will return an error message and abort the
operation by rolling back the transaction. Otherwise, nothing will
Testing the trigger:
UPDATE [Tbl A] SET [col A]='2222' WHERE [col A]='value2'
By checking the contents of table [Tbl A] we can verify that the
column was updated properly.
The above book excerpt is from:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter