>ALTER TRIGGER [my_parent_delete] > ON [dbo].[my_parent_table] > INSTEAD OF DELETE >AS >BEGIN > SET NOCOUNT ON; > > DECLARE @C_KEY_VALUE CHAR(10) > > -- Get the value of the key/unique field that user wants to delete. > SELECT @C_KEY_VALUE FROM DELETED > > -- Update Child 1 > UPDATE my_child1_table SET foreign_key_fld = '' WHERE foreign_key_fld = @C_KEY_VALUE > -- Update Child 2 > UPDATE my_child2_table SET foreign_key_fld = '' WHERE foreign_key_fld = @C_KEY_VALUE > -- Update Child 3,4, etc. > > -- Now delete this row from the parent table > delete from my_parent_table WHERE EXISTS > ( SELECT * FROM DELETED WHERE my_parent_table.my_unique_fld = DELETED.my_unique_fld ) > >END >>
if (select count(*) from Deleted) >1 begin raiserror ('This trigger can only work with single record deletion!',16,1) rollback return end