After Trigger vs Instead of Trigger in SQL Server

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 ] 

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

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

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

select * from Employee  
select * from Logs

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.