AFTER UPDATE Trigger in sql server

AFTER UPDATE Trigger

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

--We create tblDeparment table below

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's into tblDeparment 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 tblEmployee table below
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's into tblEmployee 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.

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 tables are given below with datas.

Available Tables are selected

Now create the AFTER UPDATE Trigger for “tblEmployee”.

Create trigger trtblEmployeeForUpdate
on tblEmployee                                   
For UPDATE                                        --for Update trigger                                   
As
Begin
    Declare @Id int                                          --Id not change because of primary key
    Select @Id = Id from inserted                            --inserted table use by SQL Server to keep copy of Row which just insert into actual table, the structure of the inserted table is identical to the structure of the actual table
    insert into tblAudit values('An Exiting Employee With Id = ' + cast(@Id as varchar(10)) + 'is Update at' + cast(GETDATE() as nvarchar(20))) --Foreign Key

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

Now try to Update data from “tblEmployee” than "trtblEmployeeForDelete" trigger automatically fire and data insert into "tblAudit" and also show in message for two row affected.

delete from tblEmployee where Id=4

After Update trigger update the table

 Now check data from tables.

Select table After update trigger

Example 2

For more understand about AFTER DELETE Trigger we write above Query by different way when more than one update Query execute.

create trigger trtblEmployeeForUpdateByComprision
On tblEmployee
For Update                          --for Update trigger
As
Begin
    Declare @Id int                 --Declare variable Id Not Change because it is Primary key
    Declare @Old_Name nvarchar(30)
    Declare @Old_Gender nvarchar(8)
    declare @Old_DepartmentId int
    
    Declare @New_Name nvarchar(30)
    Declare @New_Gender nvarchar(8)
    declare @New_DepartmentId int
    
    declare @AuditDetails nvarchar(2000)
    
    Select * into #tblTemptblEmployee from inserted               --select data from inserted table (New data which want to update) and insert into temporary table "#tblTemptblEmployee"
    
    while(Exists (Select Id from #tblTemptblEmployee))            --check condition, Id is available or not for which we want to update
    Begin
        Set @AuditDetails = ''
        
        Select Top 1 @Id = Id,                          -- if there are many row in temporary table for updatation, than select top 1 row by id and put into @Id variable
               @New_Name =Name, @New_Gender = Gender,   --since inserted table contain new data, so take data from trigger inserted table(which are transfer into temporary table) and put int @New_Name variable, similarly all
               @New_DepartmentId = DeparmentId
        From #tblTemptblEmployee                        -- select from from temporary table new value and assign to variables
        
        Select @Old_Name = Name, @Old_Gender = Gender,
               @Old_DepartmentId = DeparmentId
        From deleted Where Id= @Id                       --select from deleted table
        
        Set @AuditDetails= 'Employee With Id = ' + Cast(@Id as nvarchar(5)) + 'Changed '        --CAST is used to convert int type Id to nvarchar that concatenate with "New Employee with Id"
        
        If(@Old_Name <> @New_Name)                                                              --check old name not equal to new name
        Set @AuditDetails= @AuditDetails + 'Name From' + @Old_Name + 'To ' + @New_Name          
        
        If(@Old_Gender <> @New_Gender)
        set @AuditDetails = @AuditDetails + ' New Gender ' + @Old_Gender + 'To ' + @New_Gender
        
        If(@Old_DepartmentId <> @New_DepartmentId)
        set @AuditDetails = @AuditDetails + ' New DeparmentId ' + Cast(@Old_DepartmentId as nvarchar(5)) + 'To ' + Cast(@New_DepartmentId as nvarchar(5)) 
        
        insert Into tblAudit values(@AuditDetails)
        delete from #tblTemptblEmployee where Id = @Id         --delete the data from temporary table otherwise it become infinite loop
    End
End 

Now try to Update data from “tblEmployee” than "trtblEmployeeForUpdateByComprision" trigger automatically fire and data insert into "tblAudit" and also show in message for no of row affected.

Update tblEmployee set Name='Rosery' where id=2

After updating check data from tables

Select tables after update the trigger

Now Update two row simultaneously.

update tblEmployee set Name='Rose Dosan1', Gender='Male' where id =2
update tblEmployee set Name='Rose Dosan4', Gender='Female' where id =4

After updating check data again from tables

After Update trigger in Sql Server

Now Update By IN

update tblEmployee set DeparmentId=4 where id IN(1,3)

Now check data from tables.

Select Table After update

Example 3

For more understand about AFTER Update Trigger we 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,
  EmpOldName varchar(30) Not Null,
  EmpNewName varchar(30) Not Null,
  EmpOldGender char(8) Not Null,
  EmpNewGender char(8) Not Null,
  EmpOldDeparmentId int Not Null,
  EmpNewDeparmentId int Not Null,
  AuditAction varchar(1000) 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 UPDATE Trigger.

CREATE TRIGGER trtblEmployeeForAfterUpdate
On tblEmployee
AFTER UPDATE                               -- We can use AFTER instead of FOR, in above Query we use FOR
AS
 Begin
 declare @EmpId int;            --Declare variable Id Not Change because it is Primary key
 
 declare @EmpOldName varchar(30);
 declare @EmpOldGender varchar(8);
 declare @EmpOldDepartmentId int;
 
 declare @EmpNewName varchar(30);
 declare @EmpNewGender varchar(8);
 declare @EmpNewDepartmentId int;
 
 declare @EmpAuditAction varchar(1000);
 declare @ActionTakenBy varchar(100);
    declare @ServerName varchar(100);
    declare @ServerInstanceName varchar(100);
    
    set @EmpAuditAction='Update Record ';
 select @EmpId=I.Id from inserted I;         --select value from inserted table(update ) and assign to @EmpId
  
 select @EmpNewName=I.Name,               --select value from inserted table(which we want to update) and assign to @EmpName
        @EmpNewGender=I.Gender,
        @EmpNewDepartmentId=I.DeparmentId from inserted I; 
 
 select @EmpOldName=D.Name,                    --select value from deleted table(old value) and assign to @EmpId
        @EmpOldGender=D.Gender,
        @EmpOldDepartmentId=D.DeparmentId from deleted D;
 
 If(@EmpNewName <> @EmpOldName)    -- We can use also If Update(Name), Name is tblEmployee ColumnName
 Begin  
   set @EmpAuditAction = @EmpAuditAction + ' Name from ' + @EmpOldName + ' to ' + @EmpNewName + ', ' 
 End
 If(@EmpNewGender <> @EmpOldGender)   
 set @EmpAuditAction = @EmpAuditAction + ' Gender from ' + @EmpOldGender + 'to ' + @EmpNewGender + ', '
 If(@EmpNewDepartmentId <> @EmpOldDepartmentId)   
 set @EmpAuditAction = @EmpAuditAction + ' Department Id from ' + CAST(@EmpOldDepartmentId as varchar(5)) + ' to ' + CAST(@EmpNewDepartmentId as varchar(5))
 
 
 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, EmpOldName, EmpNewName, EmpOldGender, EmpNewGender,
                                 EmpOldDeparmentId, EmpNewDeparmentId, AuditAction,
                                ActionTakenBy, AuditTime,ServerName,ServerInstanceName)
                         values(@EmpId,@EmpOldName, @EmpNewName, @EmpOldGender, @EmpNewGender,
                                @EmpOldDepartmentId, @EmpNewDepartmentId, @EmpAuditAction,
                                @ActionTakenBy,GETDATE(), @ServerName, @ServerInstanceName)

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

Now try to update data from "tblEmployee" than "trtblEmployeeForAfterUpdate" trigger automatically fire and data insert into "tblAuditDetailsBy" and also show in message row affected.

update tblEmployee set Name='John', Gender='Male' where id =1
update tblEmployee set Name='Leeky', Gender='Female', DeparmentId=2 where id =2

Update after update trigger

Now Check data from tables.

Select table aftre update trigger fire