如何在SQL Server中创建一个删除前触发器？

Question

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

- user1374263

Answer 1

在这种情况下，您最好使用普通的“after”触发器。这是处理这种情况最常见的方法。

例如：

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表是一个虚拟表，其中包含在删除之前记录的内容。

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

- Code Magician

---

需要注意的是，为了使回滚在出现错误时起作用，触发器内可能需要使用try/catch块（例如），具体取决于错误的原因。 - crokusek

好的观点，但请记住，这仅适用于RAISEERROR。普通的语句终止错误（例如PK违规等）将导致在触发器外部开始的隐式事务回滚。 - Code Magician

在调用 RAISEERROR 后，您还可以调用 ROLLBACK TRANSACTION。 - Scott Software

Answer 2

你也可以使用 INSTEAD OF DELETE。

CREATE TRIGGER dbo.SomeTableYouWhatToDeleteFrom
ON dbo.YourTable
INSTEAD OF DELETE
AS
BEGIN

     -- Some code you want to do before delete

     DELETE YourTable
     FROM DELETED D
     INNER JOIN dbo.YourTable T ON T.PK_1 = D.PK_1
END

- Arion

---

由于表格启用了级联删除功能，无法使用"INSTEAD OF DELETE"。 - user1374263

顺便说一句，您可以将所有级联删除外键更改为普通外键，并从此触发器内处理删除操作，这样可以在一个地方清楚地了解由于删除而发生的所有事情。 - Tony

Answer 3

以下是以客户表为例完成的步骤：

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   LAST_UPDATED DATETIME,
   PRIMARY KEY (ID)
);

1. Create History:

   CREATE TABLE CUSTOMERS_HIST( 
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   LAST_UPDATED DATETIME,
   PRIMARY KEY (ID)
   );
   

2. Trigger on source table like below on delete event:

   CREATE TRIGGER TRG_CUSTOMERS_DEL 
   ON CUSTOMERS
   FOR DELETE
   AS
        INSERT INTO CUSTOMERS_HIST (ID, NAME, AGE, ADDRESS, LAST_UPDATED)
        SELECT ID, NAME, AGE, ADDRESS, LAST_UPDATED
        FROM DELETED
   

- Deepesh Tiwari

Answer 4

尝试使用在删除之前执行并在条件不满足时抛出错误的触发器。

CREATE TRIGGER [dbo].[TableName_PreventDeleteAndUpdate]
ON dbo.TableName
FOR DELETE, UPDATE -- runs before deletes and updates
AS
BEGIN
    IF (APP_NAME() <> 'SomeApp')
    BEGIN
        RAISERROR ('Only delete/update with SomeApp', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END

- MovGP0