AFTER INSERT Trigger in SQL Server with examples

AFTER INSERT Trigger

AFTER INSERT Trigger fire after the triggering action Insert but before competition to the databases. AFTER Insert Trigger has the ability that it can rollback modification action or statements.

To Create AFTER INSERT Trigger first of all we need to create a table (but we create two tables) for which we apply Trigger and need another table that hold all activity in main table.

--We create below tblDeparment table

create table tblDeparment
(
 Id int identity(1,1) primary key not null,
 DeparmentName varchar(30) not null,
 Location Char(8) not null,
 DeparmentHead varchar(20) not null
)

--We insert following data into tblDeparment table below

Insert into tblDeparment values('IT','London','Carrie')
Insert into tblDeparment values('HR','Mexico','Toy')
Insert into tblDeparment values('Purchasing','Delhi','Ravi')
Insert into tblDeparment values('Finance','New York','Janie')

--We create another table tblEmployee 

create table tblEmployee
(
 Id int identity(1,1) primary key not null,
 Name varchar(30) not null,
 Gender Char(8) not null,
 DeparmentId int null constraint FK_tblEmployee_DeparmentId foreign key(DeparmentId) references tblDeparment(Id)   --foreign key 
)

--We insert following data into tblEmployee table below

insert into tblEmployee values('Jane', 'Female', 1) 
insert into tblEmployee values('Levis', 'Male', 3)
insert into tblEmployee values('Lee', 'Female', Null)
insert into tblEmployee values('Rose', 'Female', 1)

Here we create two table "tblDeparment" and "tblEmployee" which are inter-connected through Foreign key constraint.
We will apply trigger for these tables.

Inserted and deleted

Before understand trigger we need to understand "inserted" and "deleted" tables which are the special type of the tables use by the trigger and these tables only available in the context of the trigger.

Whenever insert or delete data into or from the table SQL Server manage these tables which retain copy of data which you insert or delete into or from the table.

So when insert or delete data into or from any table SQL Server behind the seen create a table called "inserted" and "deleted" in memory and copy of the row mainted in the inserted and deleted table, and this inserted table can be access inside the context of the trigger.

So structure of "inserted" and "deleted" table identical to the structure or the main table.
If you try to access outside the trigger it will show an error.

--this Trigger only for understand inserted and deleted tables, how usefull for trigger
Create trigger trtblEmployee_ForUnderstand_Inserted_DeletedTable    --"trtblEmployee_ForUnderstand_Inserted_DeletedTable" is a trigger name, tr is use before trigger name for name convention
on tblEmployee                    --create trigger on "tblEmployee"
For update                        --create trigger for update activity
as 
Begin
Select * from inserted            --retain copy of data which you insert into the "tblEmployee".
select * from deleted             --retain copy of data which you delete from the "tblEmployee".          
End

Now create table that hold activity of main table by user.

create table tblAudit
(
Id int primary key identity(1,1) not null,
AuditData nvarchar(500)
) 

We have table are given below with data.

Now create the AFTER INSERT Trigger for "tblEmployee".

Create trigger trtblEmployeeForInsert
On tblEmployee
For Insert
As 
Begin
Declare @Id int                         --Declare variable
Select @Id=Id from inserted             --select Id from Inserted table which copy of actual insert data of actaual table and pass to @Id
Insert into tblAudit values('New Employee with Id=' + CAST(@Id as nvarchar(10))+ 'is added at='+ CAST(GETDATE() as nvarchar(20)) )   --insert into tblAudit 

--CAST is used to convert int type Id to nvarchar that concatenate with "New Employee with Id"
End

Now insert data into “tblEmployee” than "trtblEmployeeForInsert" trigger automatically fire and data insert into "tblAudit" and also show in message for two row affected.

insert into tblEmployee values('Collie', 'Male', 2)

Now Check data from tables.

Above Query save data in "trtblEmployeeForInsert" trigger with id and date.


EXAMPLE 2
For more understand about AFTER INSERT Trigger we create take another trigger example.
First we create another table that hold all activity of main table done by user

Create table tblAuditDetailsBy
(
  Id int not null Primary key identity(1,1),
  EmpId int Not Null,
  EmpName varchar(30) Not Null,
  Gender char(8) Not Null,
  DeparmentId int Not Null,
  AuditAction varchar(30) Not Null,
  ActionTakenBy varchar(50) Not Null,
  AuditTime datetime not null default Getdate(),
  ServerName varchar(100) Not Null,
  ServerInstanceName varchar(100) Not Null
)

Now create AFTER INSERT trigger

CREATE TRIGGER trtblEmployeeForAfterInsert
On tblEmployee
AFTER INSERT                 -- We can use AFTER instead of FOR, in above Query we use FOR
AS
 Begin
 declare @EmpId int;
 declare @EmpName varchar(30);
 declare @EmpGender varchar(8);
 declare @EmpDepartmentId int;
 declare @EmpAuditAction varchar(100);
 declare @ActionTakenBy varchar(100);
        declare @ServerName varchar(100);
        declare @ServerInstanceName varchar(100);
    
 select @EmpId=I.Id from inserted I; 
 select @EmpName=I.Name from inserted I; 
 select @EmpGender=I.Gender from inserted I;
 select @EmpDepartmentId=I.DeparmentId from inserted I; 
 set @EmpAuditAction='Inserted Record ';
 set @ActionTakenBy = SYSTEM_USER;
 set @ServerName = CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50));          -- select @@SERVERNAME return server name
 set @ServerInstanceName = CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50));    --SELECT @@servicename return current machine name
 
 insert into tblAuditDetailsBy (EmpId, EmpName, Gender, DeparmentId, AuditAction,
                                ActionTakenBy, AuditTime,ServerName,ServerInstanceName)
                         values(@EmpId,@EmpName,@EmpGender,@EmpDepartmentId,@EmpAuditAction,
                                @ActionTakenBy,GETDATE(), @ServerName, @ServerInstanceName)

 PRINT 'Successfully Fired the AFTER INSERT Triggers in SQL Server'
End

Now insert data into “tblEmployee” than "trtblEmployeeForAfterInsert" trigger automatically fire and data insert into "tblAuditDetailsBy" and also show in message for two row affected.

insert into tblEmployee values('Janiffer', 'Female', 3)

Now check data from tables