I was looking for a way in which i can get the field values which was updated , for example i have a table called "test"
create table test(tid int identity,name varchar(100),marks int)
go
insert into test (name,marks) values('a','1')
insert into test (name,marks) values('b','2')
insert into test (name,marks) values('c','3')
insert into test (name,marks) values('d','4')
go
i have a log table which will store table name , field name , old value and new value
create table tbl_logs(logid int identity,tableName varchar(100),fieldName varchar(100),oldValue varchar(100),newValues varchar(100),
updatedOn varchar(100),employeeId varchar(100))
go
I have created a procedure to update the table
create procedure updateTest
@tid int,
@name varchar(100),
@marks varchar(100),
@employeeId varchar(100)
as
DECLARE @context_info varbinary(100);
SET @context_info = cast(@employeeId as varbinary(100));
SET CONTEXT_INFO @context_info;
update test set name=@name , marks=@marks where tid=@tid
go
I have created a trigger to check if old values have been changed and then store it in the table
create trigger trigTest on test
for update
as
declare @oldVal varchar(100),@newVal varchar(100),@sql varchar(100),@employeeid varchar(100)
SELECT @employeeid = CAST(CONTEXT_INFO() as varchar(100));
if UPDATE(name)
SELECT @oldVal=d.name, @newVal=i.name
FROM inserted i
INNER JOIN deleted d
ON i.tid = d.tid
WHERE d.name <> i.name
if(@oldVal is not null)
insert into tbl_logs (tablename,fieldname,oldvalue,newvalues,updatedon,employeeid) values('test','name',@oldVal,@newVal,GETDATE(),@employeeid)
if UPDATE(marks)
SELECT @oldVal=d.marks, @newVal=i.marks
FROM inserted i
INNER JOIN deleted d
ON i.tid = d.tid
WHERE d.marks <> i.marks
if(@oldVal is not null)
insert into tbl_logs (tablename,fieldname,oldvalue,newvalues,updatedon,employeeid) values('test','name',@oldVal,@newVal,GETDATE(),@employeeid)
go
when i execute the procedure
exec updateTest @tid=2,@name='a',@marks='100',@employeeid='Emp0001'
it stores the values but i want to know if there is any easier way to do this