Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

After update trigger - Part 44

This video is a continuation of Part - 43, Please watch Part 43, before watching this video.

Triggers make use of 2 special tables, INSERTED and DELETED. The inserted table contains the updated data and the deleted table contains the old data. The After trigger for UPDATE event, makes use of both inserted and deleted tables. 

Create AFTER UPDATE trigger script:
Create trigger tr_tblEmployee_ForUpdate
on tblEmployee
for Update
Select * from deleted
Select * from inserted

Now, execute this query:
Update tblEmployee set Name = 'Tods', Salary = 2000, 
Gender = 'Female' where Id = 4

Immediately after the UPDATE statement execution, the AFTER UPDATE trigger gets fired, and you should see the contenets of INSERTED and DELETED tables.

The following AFTER UPDATE trigger, audits employee information upon UPDATE, and stores the audit data in tblEmployeeAudit table.
Alter trigger tr_tblEmployee_ForUpdate
on tblEmployee
for Update
      -- Declare variables to hold old and updated data
      Declare @Id int
      Declare @OldName nvarchar(20), @NewName nvarchar(20)
      Declare @OldSalary int, @NewSalary int
      Declare @OldGender nvarchar(20), @NewGender nvarchar(20)
      Declare @OldDeptId int, @NewDeptId int
      -- Variable to build the audit string
      Declare @AuditString nvarchar(1000)
      -- Load the updated records into temporary table
      Select *
      into #TempTable
      from inserted
      -- Loop thru the records in temp table
      While(Exists(Select Id from #TempTable))
            --Initialize the audit string to empty string
            Set @AuditString = ''
            -- Select first row data from temp table
            Select Top 1 @Id = Id, @NewName = Name, 
            @NewGender = Gender, @NewSalary = Salary,
            @NewDeptId = DepartmentId
            from #TempTable
            -- Select the corresponding row from deleted table
            Select @OldName = Name, @OldGender = Gender, 
            @OldSalary = Salary, @OldDeptId = DepartmentId
            from deleted where Id = @Id

    -- Build the audit string dynamically           
            Set @AuditString = 'Employee with Id = ' + Cast(@Id as nvarchar(4)) + ' changed'
            if(@OldName <> @NewName)
                  Set @AuditString = @AuditString + ' NAME from ' + @OldName + ' to ' + @NewName
            if(@OldGender <> @NewGender)
                  Set @AuditString = @AuditString + ' GENDER from ' + @OldGender + ' to ' + @NewGender
            if(@OldSalary <> @NewSalary)
                  Set @AuditString = @AuditString + ' SALARY from ' + Cast(@OldSalary as nvarchar(10))+ ' to ' + Cast(@NewSalary as nvarchar(10))
    if(@OldDeptId <> @NewDeptId)
                  Set @AuditString = @AuditString + ' DepartmentId from ' + Cast(@OldDeptId as nvarchar(10))+ ' to ' + Cast(@NewDeptId as nvarchar(10))
            insert into tblEmployeeAudit values(@AuditString)
            -- Delete the row from temp table, so we can move to the next row
            Delete from #TempTable where Id = @Id


  1. Heartly Ausome Explanation .... Thanks a lot.. :)

  2. When i tried to write this same code in my sql, i am getting an error "must declare scalar variable". Please advise

    1. Just declare variable without '@'
      Like @id = id.

  3. I have learned sql alot here then anywhere else.... Thanks for spending that much time on these tutorials... Specially Thanks to kodevenkut

  4. update tblEmployee set Name='clitas' where Id=2
    when I run this query I am getting below error
    "Conversion failed when converting the varchar value 'clitas' to data type int.
    " I unable to sole this error Plaease hep me sir

  5. These tutorials are excellent. I'm an application developer and never got a chance to work in databases extensively. This is a great resource to learn.

  6. Hi Vanket,

    Thanks for the tutorial, may I know why we don't use "inserted" table directly instead of using #TempTable? Why we can't we loop directly on "inserted" table?


  7. I have problem with audit in insert into OsobljeAudit values(@AuditString) (osoblje) is table from my data base, and this is error
    (1 row(s) affected)
    Msg 208, Level 16, State 1, Procedure tr_osoblje_zaupdate, Line 191
    Invalid object name 'Osobljeaudit'.

  8. Hi Mehdi

    when we use temptable, sql server will do operations on client . no need to get data from server for each and every loop iteration. simply we can get data at a time into temp table we can iterate loop on that temp table gives you more performance..

    Venkat please correct me if i am wrong.

  9. Hey Bro!! Thank for you helpful Video!! You've already saved my life and added me a great knowing to my SQL's Skills!! Sincerity Thank you!! :D


If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.