After Trigger vs Instead of Trigger in SQL Server

Introduction

This article explains the "After Trigger" and "Instead of Trigger" using an example and their differences, but first, you need to look at an overview of both.

Before reading this article, I will suggest you all read the following: 

• Triggers in SQL Server: Trigger in SQL Server.

After Trigger in SQL Server

These kinds of triggers fire after the execution of an action query that can be either DDL statements like Create, Alter, and Drop or DML statements like Insert, Update, and Delete.

Instead of Trigger in SQL Server

These kinds of triggers fire before the execution of an action query that can only be DML statements like Insert, Update, and Delete but after the execution of that query. The table data will not be affected; in other words, if you want to insert or update the data of the table, then you need to write it in the trigger using "inserted" or "deleted" virtual tables.

Syntax of Trigger

CREATE TRIGGER trigger_name ON {table|view}  
[WITH ENCRYPTION|EXECUTE AS]  
{FOR|AFTER|INSTEAD OF} {[CREATE|ALTER|DROP|INSERT|UPDATE|DELETE ]}  
[NOT FOR REPLICATION]  
AS  
sql_statement [1...n ] 

Use the following procedure to understand the differences between them.

Step 1. Create a schema of a table named "Employee" in the database for acting such as insert.

create table Employee  
(  
ID int primary key,  
Name varchar(20),  
Salary float,  
Department varchar(20)  
)

Step 2. Create a schema table named "Logs" that will contain the activity of the trigger.

create table Logs  
(  
Activity varchar(20),  
Activity_date datetime  
)

Note. I am using SQL Server 2008 in this Demo.

After Trigger vs Instead of Trigger

Action Query

In "After Trigger," the table data is affected after the execution of the action query, whereas the table data isn't affected after the execution of an action query in "Instead of Trigger."

Examples

After Trigger

I create an After Trigger that executes an insertion in the "Logs" table when we insert the data in the "Employee" table. Check below for the details.

Create a Trigger where we insert a record in the "Logs" table at the time of insertion in the "Employee" table; we insert a paper in the "Logs" table.

CREATE TRIGGER trigger_example ON Employee  
AFTER INSERT  
AS  
Insert into Logs values('Data is inserted',getdate())

Insert the data in the "Employee" table that executes a trigger automatically and selects both tables to check the data.

Insert into Employee values(1,'Rahul',20000,'Finance')

select * from Employee  
select * from Logs

Instead of Trigger

Suppose I alter the preceding After Trigger to be an Instead of Trigger that executes an insertion into the "Logs" table when we fire the insertion query into the "Employee" table. In that case, the data will not be inserted into the Employee table. Check below for the details.

Alter the preceding triggsouch that when insertion into the "Employee" table, we insert a record into the "Logs" table.

Alter TRIGGER trigger_example ON Employee  
Instead of INSERT  
AS  
Insert into Logs values('Data is inserted',getdate()) 

Note. I have changed the existing trigger.

Insert the data in the "Employee" table that executes the trigger automatically.

Insert into Employee values(2,'Bansal',10000,'IT')

select * from Employee  
select * from Logs 

As you can see, the data wasn't inserted into the "Employee" table; that's why it's called an Instead of trigger, it does something else instead of the main thing.

Insertion and Update in the Table

In the "After Trigger," the table data can be affected without using the "inserted" or "deleted" table, whereas the table data isn't affected without using an "inserted" or "deleted" table in the "Instead of Trigger."

For example

After Trigger

As you can see, the data has been inserted into the "Employee" table without using an "inserted' virtual table. When I use an "After Trigger" and if I use "inserted," then it will give a primary key violation error. Check below for the details.

Alter the trigger and insert the data via the "inserted" virtual table.

Alter TRIGGER trigger_example ON Employee  
After INSERT  
AS  
begin  
Insert into Employee select * from inserted  
Insert into Logs values('Data is inserted',getdate())  
End

 

Inserting the new data will generate an error because the action query will fire and wants to insert the data, whereas the "inserted" virtual table also wants to insert the same row.

Insert into Employee values(3,'ABC',50000,'IT')  
select * from Employee  
select * from Logs

Instead of TriggThe data will be inserted if

If I create an instead of trigger using the "inserted" virtual tated.

Alter the trigger and insert the data via the "inserted" virtual table.

Alter TRIGGER trigger_example ON Employee  
Instead of INSERT  
AS  
begin  
Insert into Employee select * from inserted  
Insert into Logs values('Data is inserted',getdate())  
end

Insert the data in the following.

Insert into Employee values(3,'ABC',50000,'IT')  
select * from Employee  
select * from Logs

Summary

This article taught us about After Trigger vs Instead of SQL Server.

Reference

• Triggers in SQL Server: Trigger in SQL Server.