How to emulate a BEFORE DELETE trigger in SQL Server 2005

Question

Say I have three tables: [ONE], [ONE_TWO] and [TWO]. [ONE_TWO] - many-to-many join table with only columns [ONE_ID and [TWO_ID]. There are foreign keys configured to link [ONE] to [ONE_TWO] and [TWO] to [ONE_TWO]. FKs use the ON DELETE CASCADE option, so if the [ONE] or [TWO] record is deleted, the corresponding [ONE_TWO] records will be automatically deleted.

I want to have a trigger in the [TWO] table so that when the [TWO] record is deleted, it executes a stored procedure that takes the [ONE_ID] parameter as a parameter, passing the [ONE_ID] values ​​that were associated with [TWO_ID] before deleting:

DECLARE @Statement NVARCHAR(max) SET @Statement = '' SELECT @Statement = @Statement + N'EXEC [MyProc] ''' + CAST([one_two].[one_id] AS VARCHAR(36)) + '''; ' FROM deleted JOIN [one_two] ON deleted.[two_id] = [one_two].[two_id] EXEC (@Statement) 

It’s clear that I need a BEFORE DELETE trigger, but there is no such situation in SQL Server 2005. I cannot use the INSTEAD OF trigger due to cascading FK.

I get the impression that if I use the FOR DELETE trigger, when I join [deleted] to [ONE_TWO] to find the list of values ​​[ONE_ID], the FK cascade will already delete the related records [ONE_TWO] so that I will never find any values [ONE_ID]. It's true? If so, how can I achieve my goal?

I think that I will need to change the FK connecting [TWO] to [ONE_TWO] so as not to use cascades and do manual deletion of [ONE_TWO] in the trigger just before manually deleting [TWO] records. But I would not go through all this if there is an easier way.

Answer 1

You can use the INSTEAD OF trigger. It starts before (replaces) the actual deletion, so the related entry in [one_two] should still exist.

 create table [one] (one_id int not null primary key) create table [two] (two_id int not null primary key) create table [one_two] (one_id int, two_id int references two(two_id) on delete cascade) GO CREATE trigger t_del_two on two instead of delete as begin SET NOCOUNT ON DECLARE @Statement NVARCHAR(max) SET @Statement = '' SELECT @Statement = @Statement + N'EXEC [MyProc] ''' + CAST([one_two].[one_id] AS VARCHAR(36)) + '''; ' FROM deleted JOIN [one_two] ON deleted.[two_id] = [one_two].[two_id] PRINT (@Statement) --EXEC (@Statement) -- carry out the actual delete DELETE TWO WHERE two_id in (SELECT two_id from deleted) end GO 

Some sample values

 insert into one select 1 insert into one select 2 insert into one select 3 insert into two select 11 insert into two select 12 insert into two select 13 insert into one_two select 1,11 insert into one_two select 1,13 insert into one_two select 2,13 

Now test it

 delete two where two_id=13