Vikash p
asked on
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,Affec ted_Table, Affected_F ield,Old_D ata,New_Da ta)
values(@user_id,@InsertDat e,'Emp','E MP_ID','', @empid);
if update(Emp_Sal)
insert into insert into Emp_Audit
(User_ID,System_Time,Affec ted_Table, Affected_F ield,Old_D ata,New_Da ta)
values(@user_id,@InsertDat e,'Emp','E MP_sal','' ,@empsal);
insert into insert into Emp_Audit
(User_ID,System_Time,Affec ted_Table, Affected_F ield,Old_D ata,New_Da ta)
values(@user_id,@InsertDat e,'Emp','E MP_ID','', @empid);
PRINT 'AFTER UPDATE Trigger fired.'
GO
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,Affec
values(@user_id,@InsertDat
if update(Emp_Sal)
insert into insert into Emp_Audit
(User_ID,System_Time,Affec
values(@user_id,@InsertDat
insert into insert into Emp_Audit
(User_ID,System_Time,Affec
values(@user_id,@InsertDat
PRINT 'AFTER UPDATE Trigger fired.'
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Absolutely. I should have clarified.