如何在 T-SQL 2005 中删除之前触发触发器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/516119/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I fire a trigger BEFORE a delete in T-SQL 2005?
提问by Fet
How can I fire a trigger BEFORE a delete in T-SQL 2005? The FOR actually fires AFTER an event and they seems no BEFORE argument in the TRIGGER function. The INSTEAD OF is not what I want. I need to fire before I delete a record. Any ideas?
如何在 T-SQL 2005 中删除之前触发触发器？FOR 实际上在事件之后触发，并且它们在 TRIGGER 函数中似乎没有 BEFORE 参数。INSTEAD OF 不是我想要的。我需要在删除记录之前触发。有任何想法吗？
回答by Tom H
You can use the INSTEAD OF option, just explicitly delete the rows at the end. For example:
您可以使用 INSTEAD OF 选项，只需显式删除最后的行。例如：
CREATE TRIGGER dbo.My_Table_Delete_Instead_Of_Trigger
ON dbo.My_Table
INSTEAD OF DELETE
AS
BEGIN
-- Do some stuff here
DELETE T
FROM DELETED D
INNER JOIN dbo.My_Table T ON T.PK_1 = D.PK_1 AND T.PK_2 = D.PK_2
END
This assumed a primary key made up of columns PK_1 and PK_2.
这假设主键由 PK_1 和 PK_2 列组成。
回答by Mitch Wheat
You can't. But you can perform a rollback in an AFTER DELETE trigger.
你不能。但是您可以在 AFTER DELETE 触发器中执行回滚。
回答by Stephen Wrighton
You can't. What you can do is check the DELETE table and undo the delete if you need to do so.
你不能。您可以做的是检查 DELETE 表并在需要时撤消删除。
Here's the Sequence of events:
这是事件的顺序：
- Send DELETE Command to SQL Server
- Item is removed from the table
- OnDelete Trigger Fires Perform your
- Logic If Logic fails/passes/whatever, undo delete
- 向 SQL Server 发送 DELETE 命令
- 从表中删除项目
- OnDelete 触发器触发执行您的
- 逻辑如果逻辑失败/通过/无论如何，撤消删除
