Triggers in SQL Server

So, What are Triggers ?

If we want some action to take place when data is deleted, inserted or updated in the table, we need to create triggers.
In short, triggers are event driven stored procedures. They get executed when a certain event like insert/delete/update operation occurs on a table.
It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action on the table that they are assigned to.

Types of Triggers

INSERT, UPDATE and DELETE are three action query types used in SQL Server.

Basically, triggers are classified into two main types :-

  • After Triggers (‘For’ Triggers)
  • Instead Of Triggers

(i) After Triggers

These triggers run after an insert, update or delete on a table. They are not supported for views.
AFTER TRIGGERS can be classified further according to action query types as:

  • AFTER INSERT Trigger.
  • AFTER UPDATE Trigger.
  • AFTER DELETE Trigger.

Example:-

Let’s create a table and insert some sample data.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE Employee
(
Id INT Identity,
Name Varchar(50),
Salary Decimal (10,2)
)
 
INSERT INTO Employee VALUES ('Craig',6000);
INSERT INTO Employee VALUES ('Lucy',10000);
INSERT INTO Employee VALUES ('Bob',7100);
INSERT INTO Employee VALUES ('Hogan',8900);
INSERT INTO Employee VALUES ('Paddy',6600);

We will be creating an AFTER INSERT TRIGGER which will insert the rows inserted into the table into another audit table. How does that Happen ? By using magic tables in SQL Server.

The main purpose of this audit table is to record the changes in the main table.

Now, create the audit table :-

1
2
3
4
5
6
7
8
CREATE TABLE Employee_Audit
(
Id int,
Name varchar(50),
Salary decimal (10,2),
Action varchar(100),
Timestamp datetime
)

(a) AFTER INSERT Trigger

This trigger is fired after an INSERT on the table. Let’s create the trigger as:-

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TRIGGER Trg_AfterInsert ON [dbo].[Employee]
FOR INSERT
AS
declare @id int;
declare @name varchar(50);
declare @salary decimal(10,2);
declare @action varchar(100);
 
select @id=i.Id from inserted i;
select @name=i.Name from inserted i;
select @salary=i.Salary from inserted i;
set @action='Inserted Record -- After Insert Trigger.';
 
insert into Employee_Audit
(Id, Name, Salary, Action, Timestamp)
values(@id, @name, @salary, @action, getdate());
 
PRINT 'After Insert trigger is fired.'
GO

Explanation :-
The CREATE TRIGGER statement is used to create the trigger. THE ON clause specifies the table name to which the trigger is bounded. FOR INSERT specifies that this is an AFTER INSERT trigger. Here FOR INSERT and AFTER INSERT are one and the same thing. You can use either one of them.
In the trigger, table named inserted has been used. This table is a logical table and contains the row that has been inserted.
To see this trigger in action, lets insert a row into the main table as :

insert into Employee values('Shane',7000);

Now, a record has been inserted into the Employee table thus activating the AFTER INSERT trigger and following message is printed:-

Triggers - After Insert

After Insert Trigger

(b) AFTER UPDATE Trigger

This trigger is fired after an update on the table. Let’s create the trigger as:-

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TRIGGER trg_AfterUpdate ON [dbo].[Employee]
FOR UPDATE
AS
declare @id int;
declare @name varchar(50);
declare @salary decimal(10,2);
declare @action varchar(100);
 
select @id=i.Id from inserted i;
select @name=i.Name from inserted i;
select @salary=i.Salary from inserted i;
 
if update(Name)
set @action='Updated Record -- After Update Trigger.';
if update(Salary)
set @action='Updated Record -- After Update Trigger.';
 
insert into Employee_Audit(Id, Name,Salary,Action,Timestamp)
values(@id,@name,@salary,@action,getdate());
 
PRINT 'AFTER UPDATE Trigger fired.'
GO

The AFTER UPDATE Trigger is created in which the updated record is inserted into the audit table. There is no logical table called ‘updated’ like the logical table ‘inserted’. We can obtain the updated value of a field using update(column_name) function. In our trigger, we have used, if update(Name) to check if the column ‘Name’ has been updated. We have similarly checked the column ‘Salary’ for an update.
Let’s update a record column and see what happens.

update Employee set Salary=8000 where Id=6

This inserts the row into the audit table as:-

Trigger - After Update

After Update Trigger

(c) AFTER DELETE Trigger

This trigger is fired after a delete on the table. Let’s create the trigger as:-

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TRIGGER trg_AfterDelete ON [dbo].[Employee]
AFTER DELETE
AS
declare @id int;
declare @name varchar(50);
declare @salary decimal(10,2);
declare @action varchar(100);
 
select @id=d.Id from deleted d;
select @name=d.Name from deleted d;
select @salary=d.Salary from deleted d;
set @action='Deleted -- After Delete Trigger.';
 
insert into Employee_Audit
(Id, Name, Salary, Action, Timestamp)
values(@id,@name,@salary,@action,getdate());
 
PRINT 'AFTER DELETE TRIGGER fired.'
GO

In this trigger, the deleted record’s data is picked from the logical ‘deleted’ table and inserted into the audit table.
Let’s fire a delete on the main table.

delete from Employee where Id = 6

A record has been inserted into the audit table as:-

Trigger - After Delete

After Delete Trigger

 

(ii) Instead Of Triggers

An INSTEAD OF trigger can take actions such as:

  • Ignoring parts of a batch and logging the problem rows.
  • Taking an alternative action when an error condition is encountered.

These can be used to block the modification of your table or view.

INSTEAD OF TRIGGERS can be classified further into three types as:-

  • INSTEAD OF INSERT Trigger
  • INSTEAD OF UPDATE Trigger
  • INSTEAD OF DELETE Trigger

(a) Let’s create an Instead Of Delete Trigger as:-

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE TRIGGER trg_InsteadOfDelete ON [dbo].[Employee]
INSTEAD OF DELETE
AS
declare @id int;
declare @name varchar(50);
declare @salary int;
 
select @id=d.Id from deleted d;
select @name=d.Name from deleted d;
select @salary=d.Salary from Employee d;
 
BEGIN
if(@salary>5000)
begin
RAISERROR('Cannot delete where salary > 5000',16,1);
ROLLBACK;
end
else
begin
delete from Employee where Id=@id;
COMMIT;
insert into Employee_Audit(Id,Name,Salary,Action,Timestamp)
values(@id,@name,@salary,'Deleted -- Instead Of Delete Trigger.',getdate());
PRINT 'Record Deleted -- Instead Of Delete Trigger.'
end
END
GO

This trigger will prevent the deletion of records from the table where Salary > 5000. If such a record is deleted, the Instead Of Trigger will rollback the transaction, otherwise the transaction will be committed.
Now, let’s try to delete a record with the Salary >5000 as:-

delete from Employee where Id = 4

This will print an error message as defined in the RAISE ERROR statement as:-

Trigger - Instead of Delete

Instead of Delete Trigger

In a similar fashion, you can code Instead of Insert and Instead Of Update triggers on your tables.

Extras :-

To Enable / Disable Triggers :-

ALTER TABLE Employee {ENABLE|DISABLE} TRIGGER ALL -- Affects all Triggers
ALTER TABLE Employee DISABLE TRIGGER Trigger_Name -- Affects a Specific Trigger

Note:-
Make sure that the records your updating/deleting exists in the Employee table or NULL values will be inserted into the audit table.

2 Responses to “Triggers in SQL Server”

  1. Shashank says:

    Nicely covered. This post helped me in preparing triggers in sql server for interview.

  2. marc says:

    wonderful post, thank you.

Leave a Reply