How to set up an audit trail in SQL 2005
Posted on 2007-10-02
I have a large database with a very key table (Tbl_General) that lots of people can update. I need to record all changes done by whom, and when. I do NOT need to worry about Inserts because they are very carefully controlled, and no DELETEs are allowed.
Is there a setting in SQL 2005 I can use to do this for me?
If not, I was thinking I could add 2 fields to the table (Last_User doing a change, and Date_Time_Changed), then make a copy of the table structure and call it Tbl_Audit_Trail. A trigger could then copy all of the fields for the record being updated to Tbl_Audit_Trail OR could copy only the fields being changed to Tbl_Audit_Trail.
What SQL statement would I use? Would this work?
INSERT into Tbl_Audit_Trail (*)
SELECT * FROM inserted