How to get only updated fields from a table?

Posted by Modit under Sql Server on 1/2/2013 | Points: 10 | Views : 978 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts