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.