Triggers in SQL Server

A trigger is a special kind of stored procedure, which executes automatically, when an Insert, update and delete event fires on the table. When a user tries to modify the data through DML(Data Manipulation language), statement at that point of time it makes trigger fire.

Let us see it with an example,

First, we can create two new tables, the first one is 'Employee', that is master table and another one is the trigger reflaction table, that is 'EmployeeTriggerAction'. We will see, when a user enters a new record in Employee Master table, at the same time trigger will be fired and we will insert that table's primary ID and Action name, which is fired. 
  1. 1) Employee  
  3. CREATE TABLE [dbo].[Employee](  
  4.     [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,  
  5.     [Name] [nvarchar](50) NULL,  
  6.     [Salary] [nvarchar](50) NULL,  
  7. GO  
  1. 2) EmployeeTriggerAction  
  3. CREATE TABLE [dbo].[EmployeeTriggerAction]  
  4. (  
  5.     [InsertedId] [int] NULL,  
  6.     [ActionFired] [nvarchar](50) NULL  
  7. )  
  8. Go  
Now, let us create Insert trigger for Employee Master table.
  1. CREATE TRIGGER trg_InsertEmployee   
  2.    ON  [dbo].[Employee]  
  3.    AFTER INSERT  
  4. AS   
  5. DECLARE @EmpId int  
  6. BEGIN  
  10.     IF(@EmpId IS NOT NULL)  
  11.     BEGIN  
  14.         INSERT INTO EmployeeTriggerAction(InsertedId,ActionFired) VALUES (@EmpId,'INSERT')  
  15.     END  
  16. END  
  17. GO  
Now, let us insert new record in Employee Master table.
  1. INSERT INTO Employee(Name,Salary) VALUES ('Ravi','20000' 
  1. SELECT * FROM Employee  
  2. SELECT * FROM EmployeeTriggerAction  
Hope it will be helpful for the beginners.

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now