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
as
Begin
Select * from deleted
Select * from inserted
End

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
as
Begin
      -- 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))
      Begin
            --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
      End
End

19 comments:

  1. very nice and useful thank u....

    ReplyDelete
  2. Heartly Ausome Explanation .... Thanks a lot.. :)

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

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

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

    ReplyDelete
  5. 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

    ReplyDelete
  6. 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.

    ReplyDelete
  7. 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?

    Cheers,
    Mehdi

    ReplyDelete
    Replies
    1. No, you can't update the INSERTED and DELETED tables.
      So, to remove the record which is already compared, we need a temp. table.

      Delete
  8. 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'.

    ReplyDelete
    Replies
    1. this OsobljeAudit(table) doesn't exist in your database.

      Delete
  9. 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.

    ReplyDelete
  10. 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

    ReplyDelete
  11. Thanks a lot for ur excellent work.. I learned a lot

    ReplyDelete
  12. Many companies offer online training with talking heads providing a lot of non-essential, unrelated communication that is only posted to make the presenters look like rock stars and add to their public stature. Few really know how to do teach technology concepts properly. This gentleman knows how to teach and explain technology properly so it will be retained.

    ReplyDelete
  13. Hi,I want to update both tables at a time using after update trigger,can any one help .

    ReplyDelete
  14. Column name or number of supplied values does not match table definition.
    i m getting this error i unable to resolve it

    ReplyDelete
  15. Best tutorials for c# and sql on youtube.

    ReplyDelete
  16. How to use text datatype with the inserted and deleted magic tables in a trigger?

    ReplyDelete

It would be great if you can help share these free resources