SQL Server: Coding the After Insert Trigger in SQL Server

A trigger is a 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 INSERT trigger whenever an insert statement event occurs.

Download the AFTER INSERT TRIGGER script used here with examples so that you can execute the script on your SQL Server machine while following the problem below.

Our goal is that when a record is inserted in employee table, we also want this record to be inserted in employee_backup table. Our example will have two tables: employee and employee_backup. We will create these tables and populate them with some data.

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

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 and sync employee_backup table with records from employee table so that both 
  tables will have the same records */

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

SELECT * 
INTO  EMPLOYEE_BACKUP
FROM  EMPLOYEE
GO

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

SELECT * from EMPLOYEE
SELECT * from EMPLOYEE_BACKUP
GO

Now, we need to keep both tables in sync so that when a record is inserted into EMPLOYEE it should automatically be inserted into EMPLOYEE_BACKUP. In such a case, we need an AFTER INSERT trigger.

We will create an AFTER INSERT trigger on employee table.

/* After Insert trigger on employee table */

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

CREATE TRIGGER TRG_InsertSyncEmp 
ON dbo.EMPLOYEE
AFTER INSERT AS
BEGIN
INSERT INTO EMPLOYEE_BACKUP
SELECT * FROM INSERTED
END
GO


Our trigger has been successfully created. Now, we will insert a record in employee table, and we will have the same record in employee_backup table because we have created an AFTER INSERT trigger on employee table.

The INSERTEDtable is a special table which keeps only the rows which are inserted; therefore, we are using this INSERTED table to keep both tables in sync. We are inserting the rows from INSERTED table which is populated with insert statements made on employee table into employee_backup table.

/* Insert a record in employee table. An Insert trigger will be executed here, 
   and the same record will be inserted into employee_backup table */

INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME) VALUES (504, 'Vish', 'Dalvi') 
/* See both tables are in sync with same number of records */

SELECT * from EMPLOYEE
SELECT * from EMPLOYEE_BACKUP 
GO

The AFTER INSERT trigger on EMPLOYEE table, as soon as it encounters an insert statement, immediately invokes another insert statement to insert the same row into EMPLOYEE_HISTORY table.

 

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.

Leave a Reply