How can i optimize the Trigger

I have checked a lot of Question asked Previously but was not able to get how do optimize this trigger

ALTER trigger [dbo].[Expample] 
on [dbo].[AdminUsers]
for update
as begin

declare @Id int
declare @new_val nvarchar(max)
declare @old_val nvarchar(max)

Declare @AuditString nvarchar(max)
  Select *
      into #TempTable
      from inserted

 While(Exists(Select Id from #TempTable))
      Begin
      Set @AuditString = ''

      Select Top 1 @Id = Id,@new_val = Fname
        from #TempTable

        Select @old_val = Fname
        from deleted where Id = @Id

        Set @AuditString = 'Id = ' + Cast(@Id as nvarchar(4)) + ' changed'

         if(@old_val <> @new_val)
                  Set @AuditString = @AuditString + ' NAME from ' + @old_val + ' to ' + @new_val

                   insert into [dbo].[tbl_audit] (Id,Auditdata) values(@Id,@AuditString)

            -- Delete the row from temp table, so we can move to the next row
            Delete from #TempTable where Id = @Id
end
end

How I have changed this code to

ALTER trigger [dbo].[Expample] on [dbo].[AdminUsers] for update as begin

declare @Id int declare @new_val nvarchar(max) declare @old_val nvarchar(max)

Declare @AuditString nvarchar(max) Begin Set @AuditString = ”

  Select Top 1 @Id=inserted.Id,@new_val =inserted.Fname,@old_val = deleted.Fname from inserted join deleted on inserted.Id=deleted.Id 
 -- Select Top 1 @Id = Id,@new_val = Fname
 --   from #TempTable

    --Select @old_val = Fname
    --from deleted where Id = @Id

    Set @AuditString = 'Id = ' + Cast(@Id as nvarchar(4)) + ' changed'

     if(@old_val <> @new_val)
              Set @AuditString = @AuditString + ' NAME from ' + @old_val + ' to ' + @new_val

               insert into [dbo].[tbl_audit] (Id,Auditdata) values(@Id,@AuditString)

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

end end

Thanks for Help

Answer

I have not been able to test it, but it appears this is what you are trying to accomplish:

ALTER trigger [dbo].[Expample]
on [dbo].[AdminUsers]
for update
as begin

    insert into [dbo].[tbl_audit] (Id, Auditdata)
    select t1.Id, 'Id = ' + Cast(t1.Id as nvarchar(4)) + ' changed NAME from ' + t2.Fname + ' to ' + t1.Fname
    from inserted t1
    join deleted t2 on t2.Id = t1.Id and t2.Fname != t1.Fname

Advertisement

Answer