如何在 SQL Server 中创建删除前触发器?

2022-01-01 00:00:00 sql-server triggers

我想创建一个删除前触发器.当我从表中删除一条记录时，该记录必须插入到历史表中.如何在 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?

推荐答案

在这种情况下，您可能最好执行常规的after"触发器.这是处理此类情况的最常见方法.

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.

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

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.

相关文章