This is an excellent article which describes a simple and generic solution to database auditing. The solution is reduced to the essential minimum and therefore is mathematically elegant.

Coincidently, only 2 months ago I have created a generic auditing solution for one of my clients. The requirements were similar to Leo's with some differences, and my solution was slightly different. I was thinking of writing an article about it, but I was too busy and now after Leo's article it probably does not make much sense. So I decided to add a few comments here describe some highlights of my solution.

Requirements

My users wanted to have a full flexibility of deciding which tables and which operations are audited. For instance, they might've wanted to audit only insert operations to one table, but any data modifications on another table. Also, they wanted to be able to enable and disable auditing dynamically to bypass auditing for certain bulk operations.

This was catered for by introducing the AuditControl table:

Operation CHAR(1),         -- 'I', 'U', or 'D'

TableName SYSNAME,

IsAuditOn BIT

Another requirement was to have a single audit table instead of one audit table per audited table.

The next requirement was very important, and is adding some complexity. The customer did not want to loose the audit records when table definition changes. Metadata changes are not something unusual during the software lifecycle, and I'd like my audit solution to sail safely through these changes.

And finally, we designed the database from scratch, and had a luxury of  consistent column naming and primary keys on each audited table.

AuditLog table

My AuditLog table was designed to be generic as it is used to store changes to an arbitrary table.

The AuditLog table has columns for audited table name, content type ('M' for metadata, 'B' for "before" data image, and 'A' for the "after" image), unique identifier of the event (for instance, a mass update of 100 rows is a single event), and all default-populated current context fields (DateTime, SystemUser, DBUser, Workstation, Application). The actual data is stored in the Content column.

For each data modification statement our AuditLog would contain:

1. A row with table's metadata, which list column names and their data types.

2. Pre-modification image of data rows affected by the operation (for updates and deletes).

3. Post-modification image of data rows affected by the operation (for updates and inserts).

'Metadata' content is simply a CREATE TABLE statement. If the user decides to analyse some old values, they can simply use it to create a new table and populated it with data from the after-image rows.

'Before' and 'After' images of data rows are stored in strings with column values concatenated and delimited by a configurable delimiter string.

Setting up auditing on a table

In my implementation setting up an auditing trigger on a database table is a semi-manual operation. I created a Query Analyser template with a single parameter - TableName. To set up auditing on a table I open my template in Query Analyser and use Edit-SubstituteTemplateParameters menu to generate a trigger for a new table. This is not as automated as I'd like it to be, but that's how it is at the moment. Currently I have audit triggers set up on all tables, but only some of the operations are configured as auditable (this is done through the AuditControl table described earlier.

Anatol Romanov