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.
Follow the reactions below and share your own thoughts.