Update Trigger To Find Out Which Host Name Update Records In What Date Time

Update Trigger Syntax

Create trigger tr_tblStaff_ForUpdate
on tblStaff
for Update
as
Begin
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
declare @hostname varchar(30)
select @hostname = hostname FROM sys.sysprocesses WHERE spid = @@SPID
Declare @AuditString nvarchar(1000)
Select *
into #TempTable
from inserted
While(Exists(Select Id from #TempTable))
Begin
Set @AuditString = ''
Select Top 1 @Id = Id, @NewName = Name,
@NewGender = Gender, @NewSalary = Salary,
@NewDeptId = DepartmentId
from #TempTable
Select @OldName = Name, @OldGender = Gender,
@OldSalary = Salary, @OldDeptId = DepartmentId
from deleted where Id = @Id
Set @AuditString = 'Staff with Id = ' + Cast(@Id as nvarchar(4)) + ' changed' + 'Using Host' + @hostname + ' Is Updated At ' + cast(Getdate() as nvarchar(20))
if(@OldName <> @NewName)
Set @AuditString = @AuditString + ' NAME from ' + @OldName + ' to ' + @NewName + 'Using Host' + @hostname
if(@OldGender <> @NewGender)
Set @AuditString = @AuditString + ' GENDER from ' + @OldGender + ' to ' + @NewGender + 'Using Host' + @hostname
if(@OldSalary <> @NewSalary)
Set @AuditString = @AuditString + ' SALARY from ' + Cast(@OldSalary as nvarchar(10))+ ' to ' + Cast(@NewSalary as nvarchar(10)) + 'Using Host' + @hostname
if(@OldDeptId <> @NewDeptId)
Set @AuditString = @AuditString + ' DepartmentId from ' + Cast(@OldDeptId as nvarchar(10))+ ' to ' + Cast(@NewDeptId as nvarchar(10)) + 'Using Host' + @hostname
insert into tblStaffAudit values(@AuditString)
Delete from #TempTable where Id = @Id
End
End

Update Trigger Syntax Description

Declare the 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
declare @hostname varchar(30)
select @hostname = hostname FROM sys.sysprocesses WHERE spid = @@SPID

Variable to build the audit string.

Declare @AuditString nvarchar(1000)

Load the updated records into a temporary table.

Select *
into #TempTable
from inserted

Loop through the records in temp table.

While(Exists(Select Id from #TempTable))
Begin

Initialize the audit string to an 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 the 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' + 'Using Host' + @hostname
if(@OldName <> @NewName)
Set @AuditString = @AuditString + ' NAME from ' + @OldName + ' to ' + @NewName + 'Using Host' + @hostname
if(@OldGender <> @NewGender)
Set @AuditString = @AuditString + ' GENDER from ' + @OldGender + ' to ' + @NewGender + 'Using Host' + @hostname
if(@OldSalary <> @NewSalary)
Set @AuditString = @AuditString + ' SALARY from ' + Cast(@OldSalary as nvarchar(10))+ ' to ' + Cast(@NewSalary as nvarchar(10)) + 'Using Host' + @hostname
if(@OldDeptId <> @NewDeptId)
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.

insert into tblStaffAudit values(@AuditString)

Delete the row from temp table, so we can move to the next row.

Delete from #TempTable where Id = @Id

Afterwards, update some data.

update tblStaff set Name='SatyaPrakash-KULU' where Id=3
select * from tblStaff where id=3

Execute this table mentioned in an update trigger.

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.