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