如何在SQL Server 2005中模拟删除前触发器 [英] How to emulate a BEFORE DELETE trigger in SQL Server 2005

问题描述

假设我有三个表，[ONE]，[ONE_TWO]和[TWO]。 [ONE_TWO]是仅具有[ONE_ID和[TWO_ID]列的多对多联接表。设置了将[ONE]链接到[ONE_TWO]和将[TWO]链接到[ONE_TWO]的外键。 FK使用ON DELETE CASCADE选项，因此，如果删除[ONE]或[TWO]记录，关联的[ONE_TWO]记录也将被自动删除。

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.

我想在[TWO]表上有一个触发器，以便在删除[TWO]记录时，它执行一个存储过程，该存储过程将[ONE_ID]作为一个参数，传递在删除发生之前链接到[TWO_ID]的[ONE_ID]值：

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)

很明显，我需要一个DELETE触发器，但SQL Server 2005中没有这样的触发器。我不能使用INSTEAD OF触发器由于FK级联。

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.

给人的印象是，如果使用FOR DELETE触发器，当我将[deleted]加入[ONE_TWO]来查找[ONE_ID]值列表时，FK级联将已经删除关联的[ONE_TWO]记录，因此我将永远找不到任何[ONE_ID]值。这是真的？如果是这样，我如何实现我的目标？

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?

我想我需要将加入[TWO]的FK更改为[ONE_TWO]以不使用级联，并手动从[ONE_TWO]中删除在触发器中，就在我手动删除[TWO]记录之前。但是，如果有更简单的方法，我宁愿不做所有这些事情。

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.

推荐答案

您可以使用INSTEAD OF触发器。它在（代替）实际的删除之前触发，因此[one_two]中的链接记录必须仍然存在。

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

一些样本值

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

现在进行测试

delete two where two_id=13

这篇关于如何在SQL Server 2005中模拟删除前触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！