Update Trigger Syntax
  1. Create trigger tr_tblStaff_ForUpdate
  2. on tblStaff
  3. for Update
  4. as
  5. Begin
  6. Declare @Id int
  7. Declare @OldName nvarchar(20), @NewName nvarchar(20)
  8. Declare @OldSalary int, @NewSalary int
  9. Declare @OldGender nvarchar(20), @NewGender nvarchar(20)
  10. Declare @OldDeptId int, @NewDeptId int
  11. declare @hostname varchar(30)
  12. select @hostname = hostname FROM sys.sysprocesses WHERE spid = @@SPID
  13. Declare @AuditString nvarchar(1000)
  15. Select *
  16. into #TempTable
  17. from inserted
  19. While(Exists(Select Id from #TempTable))
  20. Begin
  21. Set @AuditString = ''
  23. Select Top 1 @Id = Id, @NewName = Name,
  24. @NewGender = Gender, @NewSalary = Salary,
  25. @NewDeptId = DepartmentId
  26. from #TempTable
  28. Select @OldName = Name, @OldGender = Gender,
  29. @OldSalary = Salary, @OldDeptId = DepartmentId
  30. from deleted where Id = @Id
  32. Set @AuditString = 'Staff with Id = ' + Cast(@Id as nvarchar(4)) + ' changed' + 'Using Host' + @hostname + ' Is Updated At ' + cast(Getdate() as nvarchar(20))
  33. if(@OldName <> @NewName)
  34. Set @AuditString = @AuditString + ' NAME from ' + @OldName + ' to ' + @NewName + 'Using Host' + @hostname
  36. if(@OldGender <> @NewGender)
  37. Set @AuditString = @AuditString + ' GENDER from ' + @OldGender + ' to ' + @NewGender + 'Using Host' + @hostname
  39. if(@OldSalary <> @NewSalary)
  40. Set @AuditString = @AuditString + ' SALARY from ' + Cast(@OldSalary as nvarchar(10))+ ' to ' + Cast(@NewSalary as nvarchar(10)) + 'Using Host' + @hostname
  42. if(@OldDeptId <> @NewDeptId)
  43. Set @AuditString = @AuditString + ' DepartmentId from ' + Cast(@OldDeptId as nvarchar(10))+ ' to ' + Cast(@NewDeptId as nvarchar(10)) + 'Using Host' + @hostname
  45. insert into tblStaffAudit values(@AuditString)
  47. Delete from #TempTable where Id = @Id
  48. End
  49. End
Update Trigger Syntax Description
Declare the variables to hold old and updated data.
  1. Declare @Id int
  2. Declare @OldName nvarchar(20), @NewName nvarchar(20)
  3. Declare @OldSalary int, @NewSalary int
  4. Declare @OldGender nvarchar(20), @NewGender nvarchar(20)
  5. Declare @OldDeptId int, @NewDeptId int
  6. declare @hostname varchar(30)
  7. select @hostname = hostname FROM sys.sysprocesses WHERE spid = @@SPID
Variable to build the audit string.
  1. Declare @AuditString nvarchar(1000)
Load the updated records into a temporary table.
  1. Select *
  2. into #TempTable
  3. from inserted
Loop through the records in temp table.
  1. While(Exists(Select Id from #TempTable))
  2. Begin
Initialize the audit string to an empty string.
  1. Set @AuditString = ''
Select first row data from temp table.
  1. Select Top 1 @Id = Id, @NewName = Name,
  2. @NewGender = Gender, @NewSalary = Salary,
  3. @NewDeptId = DepartmentId
  4. from #TempTable
Select the corresponding row from the deleted table.
  1. Select @OldName = Name, @OldGender = Gender,
  2. @OldSalary = Salary, @OldDeptId = DepartmentId
  3. from deleted where Id = @Id
Build the audit string dynamically.
  1. Set @AuditString = 'Employee with Id = ' + Cast(@Id as nvarchar(4)) + ' changed' + 'Using Host' + @hostname
  2. if(@OldName <> @NewName)
  3. Set @AuditString = @AuditString + ' NAME from ' + @OldName + ' to ' + @NewName + 'Using Host' + @hostname
  5. if(@OldGender <> @NewGender)
  6. Set @AuditString = @AuditString + ' GENDER from ' + @OldGender + ' to ' + @NewGender + 'Using Host' + @hostname
  8. if(@OldSalary <> @NewSalary)
  9. Set @AuditString = @AuditString + ' SALARY from ' + Cast(@OldSalary as nvarchar(10))+ ' to ' + Cast(@NewSalary as nvarchar(10)) + 'Using Host' + @hostname
  11. if(@OldDeptId <> @NewDeptId)
  12. Set @AuditString = @AuditString + ' DepartmentId from ' + Cast(@OldDeptId as nvarchar(10))+ ' to ' + Cast(@NewDeptId as nvarchar(10)) + 'Using Host' + @hostname
Insert all @AuditString parameter value, which is passed to tblStaffAudit table data.
  1. insert into tblStaffAudit values(@AuditString)
Delete the row from temp table, so we can move to the next row.
  1. Delete from #TempTable where Id = @Id
Afterwards, update some data.
  1. update tblStaff set Name='SatyaPrakash-KULU' where Id=3
  3. select * from tblStaff where id=3
Execute this table mentioned in an update trigger.
  1. select * from tblStaffAudit

The update statement notofication like this,

Staff with Id = 3 changedUsing HostV4UDT-09 Is Updated At Apr 12 2017 3:16PM NAME from SatyaPrakash-KULUe to SatyaPrakash-KULUUsing HostV4UDT-09

Summary

Update the records, using update trigger.