Hi Team,
I would like to create an audit trail for a particular table with column name and the old value and the new value whenever some of the columns in the main table are being updated. I need to audit trail only a few columns. Is there an easy way to do this?
Audit_Trial ID
Table Name
Old Value
New Value
Updated StaffCode
Updated Time
Any help would be much appreciated.
Many Thanks,
The most reliable way to perform this is via a trigger. What version/edition of SQL Server are you using?
Co-FounderSQL Server MVPBrandonGalderisiSQL Server Nation
Thanks for your response BrandonGalderisi. I'm using SQL 2005.
Is there a way we can make it as a stored proc? Trigger has some disadvantages like it won't support 'Text'. Also if somebody do a back end modification, those information will also be stored in the Audit Trail table if I'm using Trigger.
Well if you are doing a true audit trail, then you want it to include modifications made through the backend. That is the benefit to using a trigger.
If you want it to be in stored procedures, then you should modify any and all stored procedures that update the tables which you want to audit. There is no way to do a generic procedure without it being very... well... hacky.