After Insert Trigger in SQL Server with example

In this article, I am going to explain you AFTER Insert Trigger in SQL-Server with example. In the previous tutorial, I have explained introduction to triggers in SQL-Server.

Related Links

AFTER Insert Trigger (for insert) with Example

We will be discussing only after insert trigger in this demo. First of all, create a table Employee with three columns EmployeeId, Name and Age. EmployeeId will be auto generated. Below is the code to create Employee table and insert some dummy records.

create table Employee
(
EmployeeId int primary key identity,
Name varchar(20),
Age int
)
go
insert into Employee values('Mr. A',50)
insert into Employee values('Mr. B',55)
insert into Employee values('Mr. C',60)
insert into Employee values('Mr. D',65)
insert into Employee values('Mr. E',70)
go
select * from Employee
Creating table for triggers

Now, we will create a Audit Table for Employee table. When somebody insert record into Employee table, then we will insert same record in audit table by using after trigger. Below is the script to create Employee_Audit table.

create table Employee_Audit
(
Id int identity primary key,
EmployeeId int,
Name varchar(20),
Age int,
InsertedDate datetime
)

Creating AFTER INSERT Trigger

Now, we will create a trigger. Below is the script to create after insert trigger.

create trigger tr_Employee_AfterInsert
on Employee
after insert   --(you can also use 'for insert')

as
begin
     declare @EmployeeId int,
             @Name varchar(20),
             @Age int
             
     select @EmployeeId=i.EmployeeId,@Name=Name,@Age=Age from inserted as i
    
     insert into Employee_Audit values(@EmployeeId,@Name,@Age,GETDATE())
          
end

When you insert a record into Employee table then SQL-Server creates a magic table named as 'inserted' and same copy of record will we inserted into this magic table. Inserted table is accessible inside the context of trigger. You can not access inserted table outside of triggers.

Executing AFTER INSERT Trigger

As we can not execute triggers explicitly, so we will insert a new record Employee table. When you insert new record into Employee table, after insert trigger will be fired and a new record will be inserted into Employee_Audit table as shown below. Initially we are having only 5 record in Employee table as shown above.

Before executing triggers

Now we will insert a new record in Employee table.

insert into Employee values('Mr. G',75)

As you can see below a new record will be inserted into both tables Employee and Employee_Audit.

After executing triggers


Share this

Related Posts

Previous
Next Post »