I am currently responsible for providing a solution to audit data within a SQL Server 2005 database. The auditing should include all data change acitivities (i.e. if data for a field changes, I need to track the old and new value, as well as who changed it and when).
I am thinking about two solutions, but was wondering if there is a better way to achieve this:
1. Create one audit table, that has the following columns: TableName, FieldName, OldValue, NewValue, UserID, UpdatedOn. Then, any data change would insert a new record in this "logging" table, specifically writing out the table name and field name, as well as the old and new values for the field.
2. On every table, create an "active" column that designates that particular record as the current record for the piece of data. Any updates to the primary key's record would instead flag this "active" column to "false," and a new record would be inserted into the table. History could be tracked via a "parentID" column, where the current/active record would reference the previous record's primary key ID.
Are these both valid approaches Is there anything in SQL Server 2005 that I can take advantage of
Thanks in advance for your suggestions and opinions.
Data Auditing Techniques in SQL Server
Wasim
You already have an Audit for SQL Server - it is called the transaction log
You can use tools like that allow viewing/auditing the log (e.g. ApexSQL Log) to do the actual auditing
Arnon