Update Triggers

Question

Post reply

Update Triggers

koustav_1982

SSCommitted

Points: 1788
More actions
January 24, 2011 at 8:19 am

#233215

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

koustav_1982 SSCommitted Points: 1788 More actions · Answer 1

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

koustav_1982 SSCommitted Points: 1788 More actions · Answer 3

Thanks a ton guys for your replies.Ultimately used Columns_Updated().

markanderson SSC-Addicted Points: 412 More actions · Answer 4

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!!!

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

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