How to emulate a BEFORE DELETE trigger in SQL Server 2005

Question

Let's say I have three tables, [ONE], [ONE_TWO], and [TWO]. [ONE_TWO] is a many-to-many join table with only [ONE_ID and [TWO_ID] columns. There are foreign keys set up to link [ONE] to [ONE_TWO] and [TWO] to [ONE_TWO]. The FKs use the ON DELETE CASCADE option so that if either a [ONE] or [TWO] record is deleted, the associated [ONE_TWO] records will be automatically deleted as well.

I want to have a trigger on the [TWO] table such that when a [TWO] record is deleted, it executes a stored procedure that takes a [ONE_ID] as a parameter, passing the [ONE_ID] values that were linked to the [TWO_ID] before the delete occurred:

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)

Clearly, I need a BEFORE DELETE trigger, but there is no such thing in SQL Server 2005. I can't use an INSTEAD OF trigger because of the cascading FK.

I get the impression that if I use a FOR DELETE trigger, when I join [deleted] to [ONE_TWO] to find the list of [ONE_ID] values, the FK cascade will have already deleted the associated [ONE_TWO] records so I will never find any [ONE_ID] values. Is this true? If so, how can I achieve my objective?

I'm thinking that I'd need to change the FK joining [TWO] to [ONE_TWO] to not use cascades and to do the delete from [ONE_TWO] manually in the trigger just before I manually delete the [TWO] records. But I'd rather not go through all that if there is a simpler way.

Answer 1

You can use an INSTEAD OF trigger. It fires before (replaces) the actual delete, therefore the linked record in [one_two] must 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