What is Trigger in Sql Server

What is Trigger 

Trigger is database object that call or run automatically even any action like insert, update, delete is performed into table. Each table has its own trigger that is associated with the table.

Type of Trigger:- there is two type of trigger

1. After trigger
2. Instead of trigger

Now, i am going both type of trigger in details with examples

1:- After Trigger

Whenever any action like insert, update or delete is perform on table after that these triggers are executed or we can say that, this Trigger executed after an action such as Insert, Update or Delete is performed on table.

I am creating Employee table where i will perform actions (like Insert, delete, update) and creating EmployeeActionLogs table to logs the trigger on the basis of action.

Following are the code to create the Employee table

CREATE TABLE [dbo].[Employee](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](100) NULL,
 [Age] [int] NULL,
 [Address] [nvarchar](100) NULL,
 [Department] [varchar](500) NULL,
 [Gender] [varchar](20) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Following are the code to create the EmployeeActionLogs table

CREATE TABLE [dbo].[EmployeeActionLogs](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Action] [varchar](50) NULL,
 [Message] [varchar](500) NULL,
 CONSTRAINT [PK_EmployeeActionLogs] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

After INSERT Trigger

Following is an example of an After Insert Trigger. Whenever a row is inserted in the Employee Table, the following trigger will be executed and trigger will logs in EmployeeActoinLos table

Create TRIGGER [dbo].[Employee_INSERT]   ON [dbo].[Employee]
AFTER INSERT
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @EmployeeId INT
 
       SELECT @EmployeeId = INSERTED.Id FROM INSERTED
 
       INSERT INTO [dbo].[EmployeeActionLogs]
       VALUES('Insert', 'New Employee - EmployeeId is '+CAST(@EmployeeId as varchar(10))+' Inserted')
END

Below code that inserts a row in Employee table.

INSERT INTO [dbo].[Employee]
           ([Name]
           ,[Age]
           ,[Address]
           ,[Department]
           ,[Gender])
     VALUES
           (
                   'John Joes'
     ,30
     ,'England'
     ,'IT'
     ,'Male'
   )
GO

Insert action performed on employee table the associated After INSERT trigger fired and maintained the EmployeeActionLogs table that is below

Id Action Message
2 Insert New Employee - EmployeeId is 19 Inserted
9 Insert New Employee - EmployeeId is 26 Inserted

After UPDATE Trigger

Following is an example of an After UPDATE Trigger. Whenever a row is Updated  in the Employee Table, the following trigger will be executed and trigger will logs in EmployeeActoinLos table.

CREATE TRIGGER [dbo].[Employee_UPDATE]   ON [dbo].[Employee]
AFTER UPDATE
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @EmployeeId INT
    DECLARE @EmployeeName INT
 
       SELECT @EmployeeId = INSERTED.Id,@EmployeeName=INSERTED.Name FROM INSERTED
 
       INSERT INTO [dbo].[EmployeeActionLogs]
       VALUES('Update', 'Existing EmployeeId '+CAST(@EmployeeId as varchar(10))+' is updated with name - '+@EmployeeName)
END

Below code that Update a row in Employee table.

USE [SQLCLR]
GO

DECLARE @EmployeeId INT=19

UPDATE [dbo].[Employee]
   SET [Name] = 'New John Joes' WHERE Id=@EmployeeId
GO

UPDATE action performed on employee table the associated After UPDATE  trigger fired and maintained the EmployeeActionLogs table that is below.

Id Action Message
2 Insert New Employee - EmployeeId is 19 Inserted
9 Insert New Employee - EmployeeId is 26 Inserted
10 Update Existing EmployeeId 19 is updated with name - New John Joes

After DELETE Trigger

Following is an example of an After DELETE Trigger. Whenever a row is Deleted from the Employee Table, the following trigger will be executed and trigger will logs in EmployeeActoinLos table.

Create TRIGGER [dbo].[Employee_DELETE]   ON [dbo].[Employee]
AFTER DELETE
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @EmployeeId INT
   
 
       SELECT @EmployeeId = INSERTED.Id FROM INSERTED
 
       INSERT INTO [dbo].[EmployeeActionLogs]
       VALUES('Delete', 'Existing EmployeeId '+CAST(@EmployeeId as varchar(10))+' is Deleted')
END

Below code that Delete a row in Employee table. 

DECLARE @EmployeeId INT=19

DELETE FROM [dbo].[Employee]
      WHERE id=@EmployeeId
GO

DELETE action performed on employee table the associated After DELETE  trigger fired and maintained the EmployeeActionLogs table that is below.

Id Action Message
2 Insert New Employee - EmployeeId is 19 Inserted
9 Insert New Employee - EmployeeId is 26 Inserted
10 Update Existing EmployeeId 19 is updated with name - New John Joes
11 Delete Existing EmployeeId 19 is Deleted
12 Delete Existing EmployeeId 26 is Deleted

2:- Instead of trigger

Instead of trigger executed before the action (like insert, update or delete) is performed. It is fully different from after trigger. suppose you want to insert record in a table then the trigger that are associated with the insert action on the table will fire before the row follow all the checks, like key, rules, and constraints. If it is fails to insert record, SQL Server will fire the Instead of Trigger that associated with table.

Following is an example of an INSTEAD OF DELETE. this trigger will fire before  a row is Deleted from the Employee Table, and  logs in EmployeeActoinLos table

Create TRIGGER [dbo].[Employee_InsteadOFDelete]   ON [dbo].[Employee]
INSTEAD OF DELETE
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @EmployeeId INT
    SELECT @EmployeeId = DELETED.Id FROM DELETED
   
   IF(@EmployeeId=1)
   BEGIN
    RAISERROR('admin cannot be deleted',16 ,1)
    ROLLBACK
    INSERT INTO EmployeeActionLogs
    VALUES('INSTEADOFDELETE', 'admin cannot be deleted.')

   END
   ELSE
    BEGIN
       INSERT INTO [dbo].[EmployeeActionLogs]
       VALUES('Delete', 'Existing Employee is Deleted')
    END      
END

Below code that Delete a row in Employee table.

Logs in EmployeeActoinLos table

Id Action Message
2 Insert New Employee - EmployeeId is 19 Inserted
9 Insert New Employee - EmployeeId is 26 Inserted
10 Update Existing EmployeeId 19 is updated with name - New John Joes
11 Delete Existing EmployeeId 19 is Deleted
12 Delete Existing EmployeeId 26 is Deleted
13 INSTEADOFDELETE admin cannot be deleted.