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
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
very nice and useful thank u....
ReplyDeleteHeartly Ausome Explanation .... Thanks a lot.. :)
ReplyDeleteWhen i tried to write this same code in my sql, i am getting an error "must declare scalar variable". Please advise
ReplyDeleteJust declare variable without '@'
DeleteLike @id = id.
I have learned sql alot here then anywhere else.... Thanks for spending that much time on these tutorials... Specially Thanks to kodevenkut
ReplyDeleteupdate tblEmployee set Name='clitas' where Id=2
ReplyDeletewhen 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
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.
ReplyDeleteHi Vanket,
ReplyDeleteThanks 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
No, you can't update the INSERTED and DELETED tables.
DeleteSo, to remove the record which is already compared, we need a temp. table.
I have problem with audit in insert into OsobljeAudit values(@AuditString) (osoblje) is table from my data base, and this is error
ReplyDelete(1 row(s) affected)
Msg 208, Level 16, State 1, Procedure tr_osoblje_zaupdate, Line 191
Invalid object name 'Osobljeaudit'.
this OsobljeAudit(table) doesn't exist in your database.
DeleteHi Mehdi
ReplyDeletewhen 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.
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
ReplyDeleteThanks a lot for ur excellent work.. I learned a lot
ReplyDeleteMany 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.
ReplyDeleteHi,I want to update both tables at a time using after update trigger,can any one help .
ReplyDeleteColumn name or number of supplied values does not match table definition.
ReplyDeletei m getting this error i unable to resolve it
Best tutorials for c# and sql on youtube.
ReplyDeleteHow to use text datatype with the inserted and deleted magic tables in a trigger?
ReplyDelete