RE: Audit systems: a good idea or a mess to maintain?

SSC Guru

Points: 1003863

February 27, 2018 at 7:45 am

#1981284

William Rayer - Tuesday, February 27, 2018 4:20 AM
I use a simple approach which works well:
1. Each table being audited should have a PK (or unique constraint). For each table create an audit table of the same schema (columns and data types). Give the audit table a name something like MainTable_A and add 3 extra columns for DateOfChange, Person, Action.
2. Create one trigger for insert, update and delete on the main table. The insert and update actions append the 'inserted' rows to the audit table, and the delete action appends the 'deleted' rows to the audit table. The extra audit columns can be populated with GETDATE(), SUSER_SNAME() and I, U or D to indicate the action.
The result is the audit table is maintained automatically and there is minimal coding effort. The edit history of each PK can be obtained from the audit table, and the most recent row of a PK value in the audit table will match the current value in the main table. The technique is loosely based on what I've seen in commercial apps, also on conversations with colleagues about different ways of implementing auditing,

There is absolutely no need to ever audit INSERTs. All they do is double the storage requirements. If you INSERT a row and never change it, the original row is in both the original table and the audit table. Instant duplication of data and audit tables get large enough without that guaranteed duplication of data.

Only audit updates and deletes (in other words, only audit changes to the data). If a row is INSERTED and never updated, the original data will be in the original table. If you update an original row in the original table, the old row (which is the original INSERTED data) will be copied to the audit table and the last update will be in the original table.

Auditing INSERTs is a complete waste of time and resources and even the built in auditing methods in SQL Server don't audit INSERTs... they only audit modifications.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)