This article explains the "After Trigger" and "Instead of Trigger" using an example and the differences between them but first you need to look at an overview of both.
After Trigger: 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: 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 performiing an action such as insert.
create table Employee
(
ID int primary key,
Name varchar(20),
Salary float,
Department varchar(20)
)
![Employee Table]()
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.
Differences
1. Action Query: In the "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 an "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.
a. Create a Trigger where, at the time of insertion in the "Employee" table, we insert a record in the "Logs" table.
CREATE TRIGGER trigger_example ON Employee
AFTER INSERT
AS
Insert into Logs values('Data is inserted',getdate())
b. 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: If 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 then the data will not be inserted into the Employee table. Check below for the details.
a. Alter the preceding trigger such that at the time of 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.
![Instead of Trigger]()
b. 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 eles instead of the main thing.
2. 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 used an "After Trigger" and if I use "inserted" then it will give a primary key violation error. Check below for the details.
a. 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
b. 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 Trigger: If I create an instead of trigger using the "inserted" virtual table then the data will be inserted.
a. 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
b. Insert the data as in the following:
Insert into Employee values(3,'ABC',50000,'IT')
select * from Employee
select * from Logs