• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4435
  • Last Modified:

Getting data for edited values in update trigger in sql server

Hi ,
 
I am writing update trigger for tables in SQL server 2008 r2

i need to store values (Old value, new value ) in Log table.

How to get the old values  ?

CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test]
FOR UPDATE
AS
      declare @empid int;
      declare @empname varchar(100);
      declare @empsal decimal(10,2);
      declare @audit_action varchar(100);

      select @empid=i.Emp_ID from inserted i;      
      select @empname=i.Emp_Name from inserted i;      
      select @empsal=i.Emp_Sal from inserted i;      
      
      if update(Emp_Name)insert into insert into Emp_Audit
           (User_ID,System_Time,Affected_Table,Affected_Field,Old_Data,New_Data)
      values(@user_id,@InsertDate,'Emp','EMP_ID','',@empid);
      if update(Emp_Sal)
            insert into insert into Emp_Audit
           (User_ID,System_Time,Affected_Table,Affected_Field,Old_Data,New_Data)
      values(@user_id,@InsertDate,'Emp','EMP_sal','',@empsal);

      insert into insert into Emp_Audit
           (User_ID,System_Time,Affected_Table,Affected_Field,Old_Data,New_Data)
      values(@user_id,@InsertDate,'Emp','EMP_ID','',@empid);

      PRINT 'AFTER UPDATE Trigger fired.'
GO
0
Vikash p
Asked:
Vikash p
  • 2
3 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
The old values will be stored in the deleted table.

http://technet.microsoft.com/en-us/library/ms191300.aspx
0
 
Anthony PerkinsCommented:
The TRIGGER as designed is fatally flawed.  A TRIGGER fires per statement not per row.  Therefore in your case if your UPDATE statement affects more than one row it will fail.
0
 
Scott PletcherSenior DBACommented:
The trigger won't "fail", that is, it won't abend; it will just capture only one row's values instead of all of the changed rows.  [In SQL Server, there is no "FOR EACH ROW" trigger, not even as an option.]
0
 
Anthony PerkinsCommented:
The trigger won't "fail", that is, it won't abend; it will just capture only one row's values instead of all of the changed rows
Absolutely.  I should have clarified.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now