Latest Post

Tuesday, 6 December 2016

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.

Government Jobs