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) Employee

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] [nvarchar](50) NULL,
    [Salary] [nvarchar](50) NULL,
GO

2) EmployeeTriggerAction

CREATE TABLE [dbo].[EmployeeTriggerAction]
(
    [InsertedId] [int] NULL,
    [ActionFired] [nvarchar](50) NULL
)
Go

Now, let us create Insert trigger for Employee Master table.

CREATE TRIGGER trg_InsertEmployee
   ON  [dbo].[Employee]
   AFTER INSERT
AS
DECLARE @EmpId int
BEGIN
    SELECT @EmpId=Id FROM INSERTED

    -- HERE WE CHECK FOR ID NOT NULL
    IF(@EmpId IS NOT NULL)
    BEGIN
    -- IF @EmpId IS NOT NULL THEN WE WILL INSERT THAT ID WITH ACTION NAME IN A NEW TABLE
        PRINT 'AFTER INSERT TRIGGER FIRED'
        INSERT INTO EmployeeTriggerAction(InsertedId,ActionFired) VALUES (@EmpId,'INSERT')
    END
END
GO

Now, let us insert new record in Employee Master table.

INSERT INTO Employee(Name,Salary) VALUES ('Ravi','20000')

 

SELECT * FROM Employee
SELECT * FROM EmployeeTriggerAction

 

 

Hope it will be helpful for the beginners.