Trigger In SQL Server - Insert Update Delete In Single Trigger With Example

Introduction
In this article we will learn what is trigger . How to manage Insert, Update and Delete operation in trigger. Insert Update and Delete with in Trigger.

Previous Updates
In previous articles we have learnt  Transaction Commit and Rollback in sql server with example.What is Lock and how to achieve lock on sql table. What is Blocking and Deadlock In SQL. Encrypt ConnectionString in Web.config.

What is Trigger
As per Microsoft official defination A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. 

Table Structure

Create Table TestEmployee ( ID INT IDENTITY(1,1) PRIMARY KEY , Name VARCHAR(50), Department VARCHAR(50)) Create Table TestEmployee_History (EmpHistID INT IDENTITY(1,1) PRIMARY KEY, EmpID INT , Name VARCHAR(50), Department VARCHAR(50), ActionType VARCHAR(10))

Trigger For Insert Update Delete

CREATE TRIGGER trg_TestEmployee_IUD ON TestEmployee AFTER INSERT, UPDATE, DELETE AS BEGIN DECLARE  @ID INT,                @Name VARCHAR(50),                @Department VARCHAR(50) ---- Get data from inserted/ updated SELECT @ID= ID,           @Name = Name,           @Department = Department            FROM inserted  ---- Get data from deleted               SELECT @ID= ID,                      @Name = Name,                      @Department = Department                                    FROM deleted   -- Insert Case    IF EXISTS( SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)      BEGIN              INSERT INTO TestEmployee_History(EmpID, Name, Department, ActionType)               Values( @ID, @Name, @Department, 'Insert')     END          -- Update Case    IF EXISTS( SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)     BEGIN             INSERT INTO TestEmployee_History(EmpID, Name, Department, ActionType)               Values( @ID, @Name, @Department, 'Update')     END               -- Delete Case        IF EXISTS( SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)        BEGIN                       INSERT INTO TestEmployee_History(EmpID, Name, Department, ActionType)            Values( @ID, @Name, @Department, 'Delete')        END END

In this trigger all update insert delete operations of table captured separately . 

Now Insert a Record in TestEmployee table- 

 When you insert a record on TestEmployee table then in message window you will see 1 Row(s) affected two times. This is because of trigger which inserts same record in History table.

 Update a Record in TestEmployee table-

Now Delete a Record in TestEmployee table-

In these images you can easily see the trigger works. On actionType column all three operations recorded.
Hope this article will helpful for you. If any issue then please let us know in comment section.