sql-servertriggers

How to create a before delete trigger in 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?

Solution

  • 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.