January 24, 2011 at 8:19 am
hi
Objective:I need to find out what are the columns that were updated and the old and new values in them before and after the update to put into an audit table .
The table has around 80 columns and i need to track all of them .
My approach:
1.CDC, but my client wants us to use Trgger
2.Trigger:
1.Find out all the column names for the table from syscolumns and storeit in atemp table
2. Run a cursor through all the columns and use Update() function to determine if it has been updated
3. If yes take the values from Inserted and Deleted tables
Issue:Update() function accepts only column names and even if i tr to build the query dynamically , it throws an error.
Have triedto simulate the error in the below code.
WHat s the easiest way to achieve this through triggers?
Any help will be appreciated.
create table test (id int,col1 nvarchar(3),col2 nvarchar(3),col3 nvarchar(3))
go
insert into test(id,col1,col2,col3)values (1,'a','a1','a2')
insert into test(id,col1,col2,col3)values (2,'b','b1','b2')
insert into test(id,col1,col2,col3)values (3,'c','c1','c2')
insert into test(id,col1,col2,col3)values (4,'d','d1','d2')
go
alter trigger trg
on dbo.test
after update
as
select [name] into #cols from syscolumns where id=(
select [id] from sysobjects where [name]='test')
declare @qry nvarchar(max)
declare @col nvarchar(5)
set @col='col1'
set @qry='if UPDATE('+@col+') select 1'
exec(@qry)
--select * from inserted
--select * from deleted
go
January 24, 2011 at 8:33 am
This was removed by the editor as SPAM
January 24, 2011 at 8:51 am
how to determine the bit value thats going to have & to the columns_updated() functon?
The formula 2^(column ordinal-1) rsults to a huge value when summed for 80 cols
January 24, 2011 at 9:13 am
even worse, i believe the UPDATE() and COLUMNS_UPDATED() returns which columns were REFERENCED in the update statement, not which ones actually changed their value...since a lot of datalayers would send all columns in the table, but maybe actually only change, say the "descrip" column, the COLUMNS_UPDATED() function would probably not be useful.
Lowell
January 25, 2011 at 2:45 am
Thanks a ton guys for your replies.Ultimately used Columns_Updated().
June 15, 2011 at 8:20 pm
Koustav:
Would you mind sharing how you used columns_updated() with that many columns. I am getting overflow errors and I would greatly appreciate seeing your example code if you wouldn't mind sharing.
Thanks!!!
June 16, 2011 at 7:29 am
NOTE: I just came across columnar auditing at a client a few weeks ago. I found through testing that these triggers slowed down the DML by TWO ORDERS OF MAGNITUDE!!!!! If you MUST track everything, please do yourself a favor and create a single table with before/after value columns and simply insert the data into that single table by rows (in a SET-BASED manner!!). Be prepared for pain if you don't do this.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 16, 2011 at 8:34 am
In this respect, the OUTPUT clause is your friend.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply