如何在SQL Server中创建删除前触发器？ [英] How to create a before delete trigger in SQL Server?

问题描述

我想创建一个删除前触发器。当我从表中删除一条记录时，该记录必须插入到历史表中。如何在SQL Server中执行此操作？

I want to create a before delete trigger. When I delete a record from a table that record has to be inserted into a history table. How can I do this in SQL Server?

推荐答案

在这种情况下，最好执行常规的之后操作触发。这是处理这种情况的最常用方法。

In this situation, you're probably better off doing a regular "after" trigger. This is the most common approach to this type of situation.

类似

CREATE TRIGGER TRG_AUD_DEL
ON yourTable
FOR DELETE
AS
     INSERT INTO my_audit_table  (col1, col2, ...)
     SELECT col1, col2...
     FROM DELETED 

会发生什么，当从表中删除一条记录时，删除的行将插入到 my_audit_table DELETED 表是一个虚拟表，其中包含刚删除前的记录。

What will happen is, when a record (or records!) are deleted from your table, the deleted row will be inserted into my_audit_table The DELETED table is a virtual table that contains the record(s) as they were immediately prior to the delete.

此外，请注意，触发器作为Delete语句上隐式事务的一部分运行，因此，如果删除失败并回滚，则触发器也会回滚。

Also, note that the trigger runs as part of the implicit transaction on the delete statement, so if your delete fails and rolls back, the trigger will also rollback.

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