How to get only updated fields from a table?

Posted by Modit under Sql Server on 1/2/2013 | Points: 10 | Views : 823 | Status : [Member] | Replies : 1
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




Responses

Posted by: Shubham on: 1/2/2013 [Member] Starter | Points: 25

Up
0
Down
Hi modit
if u want to see who records is updated
i m written a trigger , i think it should work!!!!


If you want your trigger to report all affected rows, you need to write a loop

USE CenterData;
GO
select * from users;

-- reporting the first affected row only
UPDATE users SET email=name;
GO
Email changed from NULL to John King
(5 row(s) affected)

-- reporting all affected rows
ALTER TRIGGER update_user ON users
AFTER UPDATE
AS
SELECT 'Email changed from '
+ ISNULL(d.email,'NULL')
+ ' to '
+ ISNULL(i.email,'NULL')
FROM INSERTED AS i, DELETED AS d
WHERE i.id = d.id;
GO

UPDATE users SET email=REVERSE(name);
GO
------------------------------------------------------
Email changed from Kumar to Choudahry
Email changed from ram to shayam
Email changed from Jack to jill
Email changed from shubham to kumar
Email changed from John King to gniK nhoJ
(5 row(s) affected)


Thanks&Regards:-
Shubham Choudhary
Software Engineer
www.facebook.com/shubham.kunar

Modit, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response