Triggers in SQL Server

What is a Trigger?

A trigger is a special kind of stored procedure or an object which is executed automatically along with actions of a table like insert, update and delete. They are also used for Select queries. Triggers cannot be executed explicitly.

Types of Triggers

There are two types of triggers that can be executed along with Insert, Update, and Delete

1. After Triggers

2. Instead Of Triggers

After Triggers:

After Triggers are not supported by views.

After Trigger (using FOR/AFTER CLAUSE)

This trigger fires only after SQL Server completes the execution of the action successfully.

AFTER TRIGGERS can be classified further into three types as:

1. AFTER INSERT Trigger.
2. AFTER UPDATE Trigger.
3. AFTER DELETE Trigger.

Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger. 

CREATE TABLE [dbo].[Employee](
[id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Username] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[DepartID] [int] NULL
)


SET IDENTITY_INSERT [dbo].[Employee] ON 

   INSERT [dbo].[Employee] VALUES (1, N'john1', N'John', N'Varghese', 1)

   INSERT [dbo].[Employee] VALUES (2, N'joe1', N'Joe', N'Joseph', 1)

   INSERT [dbo].[Employee] VALUES (3, N'jack1', N'Jack', N'Titanic', 2)

   INSERT [dbo].[Employee] VALUES (4, N'james1', N'James', N'Mathew', 2)

   INSERT [dbo].[Employee] VALUES (5, N'jenny1', N'Jenny', N'Thomas', 3)

   INSERT [dbo].[Employee] VALUES (6, N'jerk1', N'Jerk', N'In', 3)

   INSERT [dbo].[Employee] VALUES (7, N'tom1', N'Tom', N'Cruze', 3)

   INSERT [dbo].[Employee] VALUES (8, N'christo1', N'Christo', N'Tin', NULL)

SET IDENTITY_INSERT [dbo].[Employee] OFF

SELECT * FROM EMPLOYEE

CREATE TRIGGER TRG_INSERTBLOCKER ON EMPLOYEE
AFTER INSERT
AS
BEGIN
   PRINT 'CANNOT INSERT RECORDS'
   ROLLBACK TRANSACTION

END

INSERT INTO EMPLOYEE VALUES ('TRIGGER1', 'TRIGGER', 'JOHN', 1)

To create audit for a table:

 CREATE TABLE EMPLOYEE_AUDIT(   ID INT,   USERNAME VARCHAR(55),   FIRSTNAME DECIMAL(10,2),   AUDITACTION VARCHAR(100),   AUDITTIMESTAMP DATETIME) 

--Create a trigger with "for insert" which is "after insert"

CREATE TRIGGER TRG_AUDIT_INSERT ON EMPLOYEE
FOR INSERT -- or AFTER INSERT
AS DECLARE @ID INT, @USERNAME VARCHAR(55), @FIRSTNAME VARCHAR(55), @AUDITACTION VARCHAR(100), @AUDITTIMESTAMP DATETIME
BEGIN

SELECT @ID = i.ID from inserted i
SELECT @USERNAME = i.Username from inserted i
SELECT @FIRSTNAME = i.FirstName from inserted i
SELECT @AUDITACTION = 'Insert Statement Executed'
SELECT @AUDITTIMESTAMP = GETDATE()

INSERT INTO Employee_Audit VALUES (@ID, @USERNAME, @FIRSTNAME, @AUDITACTION, @AUDITTIMESTAMP)
PRINT 'AFTER INSERT TRIGGER FIRED'
END

-- select to check whats the table status

SELECT * FROM EMPLOYEE_AUDIT

-- insert a record to check if the trigger works

INSERT INTO EMPLOYEE VALUES ('TRIGGER1', 'TRIGGER', 'JOHN', 1)

Difference between a FOR and AFTER trigger

There is no difference, they do the same thing.

CREATE TRIGGER trgTable on dbo.Table FOR INSERT,UPDATE,DELETE

Is the same as

CREATE TRIGGER trgTable on dbo.Table AFTER INSERT,UPDATE,DELETE

An INSTEAD OF trigger is different, and fires before or instead of the insert and can be used on views, in order to insert the appropriate values into the underlying tables.