Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
martalex
Starting Member
17 Posts |
 Posted - 2004-08-13 : 19:11:35
|
| I'm developing a contact management application. I need to keep track of any changes made to a contact. In that past, I have been creating one audit table for each table however it get messy very quick. I seen example of centralized audit table where only the modified fields were saved. This seems an interesting alternative but I'm not sure how I would be able to restore a record let say as it was 2 weeks ago. Am I missing something? Sample centralized audit table.Audit_Table(audit_id, audit_table, audit_field, audit_OldValue, audit_NewValue, Audit_Date, audit_user) |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-14 : 12:09:33
|
| I think the history tables for each table are actually easier to audit and create views on to view "all the data". Alternatively, you might want to consider a "delete flag" field. Your "current view" would filter on that. Monthy, weekly, etc. you could run an archive process to move them all to an archive database. Takes a bit of logic to do this. It's nice though when you have it setup correctly.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-15 : 07:36:01
|
| We have an application here that has a single audit table with PK, Column Before and After values, who made the change and whether it was an Insert/Update or delete.The data in that one table is the most massive thing I have ever seen! and totally impossible to report on (well, you can do a list of what data changed at each step, but usually users need to see the other data in the record to decide whether the data that was changed was "reasonable" or not.The triggers that produce it are massive too. The DB is about 30 tables and the triggers script is about 5MB from memory.But the single table approach does provide very good chronological ordering of changes.Our main applications allow the user to "flick back" through previous changes to a record. They can see which fields changed at each edit, and UserName/Date of the change. This makes it easy to find what went wrong, or to "undo" an accidental error by cut&paste from a previous version. For this purpose we use one audit table per actual table.Kristen |
|
|
|
|
|
|
|
|
Audit Table: Central/Unique Audit Table