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

The trigger will be fired when the host in SQL Server will update any data.

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.