Trigger in SQL Server

  • A trigger is a special kind of stored procedures that automatically executes when an event occurs in the database server.
  • Triggers executes when a user tries to modify data through a data manipulation language (DML) event, such as Insert, Delete, Update.
  • These triggers fire when any valid event is fired.
  • A Trigger that contains statement which causes the other triggers are known as Cascading Triggers.
Difference between Trigger and Stored Procedure

TriggerStored Procedure
Trigger is an act which is performed automatically before or after an event occurs.Stored procedure is a set of functionality which is executed when it is explicitly invoked.
It cannot accept parameters.It can accept parameters.
A trigger cannot return any value.A stored procedure can return a value.
It is executed automatically on some event.It needs to be explicitly called.
Triggers are used for insertion, update and deletion.Stored procedures are often using independently in the database.

Usages of Triggers

Triggers are used to:
1. Generate virtual column values automatically.
2. Log events.
3. Prevent invalid transactions.
4. Enforce complex business or referential integrity rules that user cannot able to define with constraints.

Types of DML Triggers

1. After Trigger (using FOR/AFTER CLAUSE)

  • Once the actions are performed by the INSERT, UPDATE, or DELETE statements, AFTER trigger gets executed.
  • In earlier versions of Microsoft SQL Server, the only available option was FOR trigger. So, the AFTER trigger can also be specified in exactly the same way as that of FOR trigger.
  • AFTER triggers can be specified only on tables.

2. Instead of Trigger (using INSTEAD OF CLAUSE)

  • INSTEAD OF triggers are generally used in place of the usual triggering action.
  • INSTEAD OF triggers can be specified on the basis of one or more base tables. However, it is possible for these triggers to extend and support the types of updates.
Let us create a table and try to perform various operations on it to see the output in each case.

Example: Create table Employee_Demo

CREATE TABLE Employee_Demo
(
Emp_ID int identity,
Emp_Name varchar(55),
Emp_Sal decimal (10,2)
)


Now Insert records

Insert into Employee_Demo values ('Jay',1000);
Insert into Employee_Demo values ('Albert',1200);
Insert into Employee_Demo values ('Mark',1100);
Insert into Employee_Demo values ('Jason',1300);
Insert into Employee_Demo values ('Ryan',1400);


Now create table Employee_Demo_Audit for logging/backup purpose of table Employee_Demo

create table Employee_Demo_Audit
(
Emp_ID int,
Emp_Name varchar(55),
Emp_Sal decimal(10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)


1. After Trigger

i) After Insert Trigger

Create trigger on table Employee_Demo for Insert statement

CREATE TRIGGER trgAfterInsert on Employee_Demo
FOR INSERT
AS declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.'; insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER INSERT trigger fired.'


After execution of the above code

Now try to insert data in Employee_Demo table.

insert into Employee_Demo(Emp_Name,Emp_Sal)values ('Jayesh',1000);

Now select data from both the tables to see trigger action.

select * from Employee_Demo
select * from Employee_Demo_Audit

Result:

insert trigger

ii) After Update Trigger

Create trigger on table Employee_Demo for Update statement

CREATE TRIGGER trgAfterUpdate ON dbo.Employee_Demo
FOR UPDATE
AS
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
if update(Emp_Name)
set @audit_action='Update Record --- After Update Trigger.';
if update (Emp_Sal)
set @audit_action='Update Record --- After Update Trigger.';
insert intoEmployee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER UPDATE trigger fired.'


Now try to update data in Employee_Demo table

update Employee_Demo set Emp_Name='Sapna' Where Emp_ID =6;

Result:

after update trigger

iii) After Delete Trigger

Create trigger on table Employee_Demo for Delete statement

CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
FOR DELETE
AS
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;
select @empname=d.Emp_Name from deleted d;
select @empsal=d.Emp_Sal from deleted d;
select @audit_action='Deleted -- After Delete Trigger.';
insert into Employee_Demo_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER DELETE TRIGGER fired.'


Now try to delete data in Employee_Demo table

DELETE FROM Employee_Demo where emp_id = 5

Result:

delete trigger

2. Instead of Triggers

i) Instead of Insert Trigger

Create trigger on table Employee_Demo for Insert statement

CREATE TRIGGER trgInsteadOfInsert ON dbo.Employee_Demo
CREATE TRIGGER
trgInsteadOfInsert ON dbo.Employee_Demo
INSTEAD OF Insert
AS
declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action
varchar(100);
select @emp_id=i.Emp_ID from inserted i;
select @emp_name=i.Emp_Name from inserted i;
select @emp_sal=i.Emp_Sal from inserted i;
SET @audit_action='Inserted Record -- Instead Of Insert Trigger.';
BEGIN
BEGIN TRAN
SET NOCOUNT ON
if(@emp_sal>=1000)
begin
RAISERROR('Cannot Insert where salary < 1000',16,1) ROLLBACK
end
else
begin
Insert into Employee_Demo (Emp_Name,Emp_Sal) values (@emp_name,@emp_sal)
Insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@@identity,@emp_name,@emp_sal,@audit_action,getdate())
end
COMMIT
PRINT 'Record Inserted -- Instead Of Insert Trigger.'
END


Now try to insert data in Employee_Demo table

insert into Employee_Demo values ('Sapna,1300)
insert into Employee_Demo values ('Sapna',900) -- It will raise error since we are checking salary >=1000

Result:

instead of insert trigger

ii) Instead of Update Trigger

Create trigger on table Employee_Demo for Update statement

CREATE TRIGGER trgInsteadOfUpdate ON dbo.Employee_Demo
INSTEAD OF Update
AS
declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
select @emp_id=i.Emp_ID from inserted i;
select @emp_name=i.Emp_Name from inserted i;
select @emp_sal=i.Emp_Sal from inserted i;
BEGIN
BEGIN TRAN
if(@emp_sal>=1000)
begin
RAISERROR('Cannot Insert where salary < 1000',16,1); ROLLBACK; end
else begin
insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@emp_id,@emp_name,@emp_sal,@audit_action,getdate());
COMMIT;
PRINT 'Record Updated -- Instead Of Update Trigger.'; END


Now select data from both the tables to see trigger action

select * from Employee_Demo
select * from Employee_Demo_Audit

Result:

instead of update trigger

iii) Instead of Delete Trigger

Create trigger on table Employee_Demo for Delete statement

CREATE TRIGGER trgInsteadOfDelete ON dbo.Employee_Demo
INSTEAD OF DELETE
AS
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action
varchar(100); select @empid=d.Emp_ID FROM deleted d;
select @empname=d.Emp_Name from deleted d;
select @empsal=d.Emp_Sal from deleted d;
Begin
BEGIN TRAN if(@empsal>1200)
begin
RAISERROR('Cannot delete where salary > 1200',16,1);
ROLLBACK;
end
else
begin
delete from Employee_Demo where Emp_ID=@empid;
end
COMMIT
insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,'Deleted -- Instead Of Delete Trigger.',getdate())
PRINT 'Record Deleted -- Instead Of Delete Trigger.'  
END


Now try to delete data in Employee_Demo table

DELETE FROM Employee_Demo where emp_id = 1
DELETE FROM Employee_Demo where emp_id = 3

Result:

instead of delete trigger
▲