October 10, 2002 at 6:26 am
I am looking for the best way to track changes in data.
Example:
Table XYZ with 20 columns.
User can change data in some or all of the columns via app per record
What I need is a history tbl where I can see which user updated which data.The user
user and date history is no problem but getting the column data is a problem, because
it must be only the change data.
example : "10/10/2002 13:00:00","WORKGROUP\USERNAME","Column:Surname CHANGE TO 'Kraai';Column:Name Change to 'Pompei'";
Thanks
Spaski
October 10, 2002 at 6:44 am
You will need to use a Trigger on the table being updated.
Inside the trigger you can use the IF UPDATE(columnname) to see if the SQL statement had a SET statement on the column.
You can use this in conjunction with the INSERTED and DELETED pseudo tables that are available to you in triggers to see the before and after images of the rows updated.
October 10, 2002 at 7:36 am
I need more isn't there a better way than this because, this trigger must be on alot
of tables. To make a If Update(columnname) for each column in each table is alot of
of prgramming.
I am trying with "IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] " function but this one is a bit hard to figure out
Thanks
October 10, 2002 at 8:03 am
Here is an example that might help you use the COLUMNS_UPDATED() bitwise function to determine the columns update.
set nocount on
create table my_table (a int, b int, c int, d int, e int)
go
create TRIGGER my_trig2
ON my_table
FOR insert, update
AS
if exists(select * from deleted) and
exists(select * from inserted)
print 'updating a row'
else if exists(select * from deleted)
print 'deleting rows'
else if exists(select * from inserted)
print 'inserting rows'
declare @I int
declare @nc int -- number of columns
declare @x int
set @nc = 5
set @x = 0
set @i = 1
while @x < @nc
begin
IF ( COLUMNS_UPDATED() & @i = @i )
PRINT 'Column ' + cast(@x+1 as char(1)) + ' Modified'
set @I = @I * 2
set @x = @x + 1
end
GO
insert into my_table (a) values('1')
update my_table
set a = '2'
update my_table
set b = '2'
update my_table
set c = '2'
update my_table
set d = '2'
update my_table
set e = '2'
drop table my_table
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply