SQL Server: Coding the After Delete Trigger in SQL Server

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

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

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.