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
GOOur 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.
