How to create a before delete trigger in SQL Server?

Question

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?

Solution 1

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

Something like

CREATE TRIGGER TRG_AUD_DEL
ON yourTable
FOR DELETE
AS
     INSERT INTO my_audit_table  (col1, col2, ...)
     SELECT col1, col2...
     FROM 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.

Solution 2

You could also use 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

Solution 3

It could be done in following steps for let’s say in this example I am using customer table:

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