Hello, i want to create an audit script, to audit record updates inside a table, and i want to store on a table that information. I want to know how can i store on a column the name of the column that was modified (updated) on the table that I'm auditing, in order to track only the column that was update, the previous value and the new value, date, time and user that did the update. Somebody can tell me how can i do this?

within a trigger, there is a couple of functions you can use you can use to determine if a column has changed:IF (UPDATE(columnname) ) returns true or false, if you are testing a single column, so you could do something like this:

CREATE TRIGGER TR_WHATEVER ON WHATEVER FOR INSERT,UPDATE AS IF (UPDATE(DESCRIP) ) BEGIN INSERT INTO AUDIT(WHATEVERID,NEWVAL,OLDVAL,UPDATEDDT) SELECT INSERTED.WHATEVERID, INSERTED.DESCRIP AS NEWVAL, DELETED.DESCRIP AS OLDVAL, GETDATE() AS UPDATEDDT FROM INSERTED INNER JOIN DELETED ON INSERTED.WHATEVERID=DELETED.WHATEVERID END

the other function, COLUMNS_UPDATED, uses a bitmask, and references columns by their ordinal position...NOT by name, to determine whether the columns have had changes:

adonado (5/8/2009)I want to know how can i store on a column the name of the column that was modified (updated) on the table that I'm auditing, in order to track only the column that was update, the previous value and the new value, date, time and user that did the update.