SQL Server: Coding the After Delete Trigger in SQL Server

Posted October 19, 2013 by Vishwanath Dalvi in Database, SQL Server

feature sql delete

A trigger is special type of stored procedure that is executed when an INSERT, DELETE or UPDATE statement modifies data in a table.

SQL Server initiates an AFTER DELETE trigger whenever a delete statement event occurs.

You can download the AFTER DELETE Trigger script used here so that you can run this script on your local SQL Server, while following the problem below.

We want to keep track of records deleted from employee table into employee_history table.

If any employee leaves the company, his record will be deleted from employee table. However, it will be inserted into employee_history table using the AFTER DELETE trigger.

Our example will have two tables: employee table and employee_history table.

We will create these tables and populate them with some data.

/* Check whether employee table already exists or not*/

IF OBJECT_ID('EMPLOYEE') IS NOT NULL
DROP TABLE EMPLOYEE
GO

/* Create employee table if it does not exist*/

CREATE TABLE EMPLOYEE
(
 EMPID INT PRIMARY KEY,
 FNAME VARCHAR(25),
 LNAME VARCHAR(25),
)
GO

/*Populate employee table with sample rows*/

INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (500, 'John','Smith'),
      (501, 'Alex','Admas'),
      (502, 'Eric','James'),
      (503, 'Shaun','Marsh')
GO

/*Create employee_history table with the same structure as
employee table with no rows*/

IF OBJECT_ID('EMPLOYEE_HISTORY') IS NOT NULL
DROP TABLE EMPLOYEE_HISTORY
GO

SELECT *
INTO  EMPLOYEE_HISTORY
FROM  EMPLOYEE
WHERE 1 = 0 -- This will populate Employee_history table with 0 rows
GO

/*See the table records from both tables. We have four rows in both tables*/

SELECT * from EMPLOYEE e -- 4 rows
SELECT * from EMPLOYEE_HISTORY eh -- 0 rows
GO

sql_server_after_delete_trigger


Now, if any record deleted from employee table is deleted, we need to insert it into employee_history table for future reference. Therefore, in this case, we will create an AFTER DELETE trigger.

/*After Delete trigger on employee table*/

IF OBJECT_ID('TRG_EmployeeHistory') IS NOT NULL
DROP TRIGGER TRG_EmployeeHistory
GO

CREATE TRIGGER TRG_EmployeeHistory
ON dbo.EMPLOYEE
AFTER DELETE AS
BEGIN
INSERT INTO EMPLOYEE_HISTORY
SELECT * FROM DELETED
END
GO


Now that we have an AFTER DELETE trigger on employee table, we will delete a row from employee table to see the trigger at work.

The deleted row will be inserted into employee_history table.

/*Delete a record from employee table*/

DELETE FROM EMPLOYEE
WHERE EMPID = 501
GO

/* Notice both the tables. The deleted record from employee is inserted into 
  EMPLOYEE_HISTORY */

SELECT * from EMPLOYEE e -- 3 rows
SELECT * from EMPLOYEE_HISTORY eh -- 1 row
GO

Coding-after-delete-trigger-2

The DELETED table is a magical table that keeps the rows which are deleted. In the AFTER DELETE trigger, we used the DELETED table to keep track of deleted rows. Those rows are inserted into EMPLOYEE_HISTORY table.

You can also go through SQL Server: Coding the After Insert Trigger.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

  • Marc Jellinek

    Only one problem here.

    If the structure of either EMPLOYEE or EMPLOYEE_HISTORY changes, or any error occurs within the trigger, the transaction will be rolled back… in other words, all work will be lost and the user will likely get an error they don’t understand.

    A better way to do this is to enable CDC (or Change Tracking) and check the CDC tables for deleted rows, then copy those rows to your history table. This can generally be done within a SQL Agent job that is scheduled to run periodically. It will be an asynchronous process, so don’t expect deleted rows to show up in the history table immediately… you’ll have to manage expectations.

    But this will be a more robust solution and will allow the user to continue working in case of an un-trapped error.