A table that stores all [updated | deleted] transactions in a database
i.e.
TableName, TableId, ColumnName, ValueType, NewValue, OldValue, DateChanged

or

A copy of each individual table, which could add up to a lot of tables

A lot depends on just what you want to know about the changes. My guess would be that the audit table would provide more usable information, and the table copies would be easier to do and simpler to explain.

One option that you've missed is to use SQL Profiler to record whatever information you need. This consumes ENORMOUS amounts of disk, but it allows you to track every change to the table, without exceptions, in nausiating detail if you wish.

-PatP

I've only ever implemented auditing (using triggers) for selected tables in a database; I've never tried it for all the tables.

That said, I would think that the former solution (one table) might be a source for contention as an update to a single row on one table appears to require multiple inserts into the table (one for each column being changed). It would also seem to be a bit of a nightmare to keep up with changes and updates.

I am seeing more and more references to using Lumigent's products for doing auditing. But that could be just an advertising push. Since I've never used their products, I don't have an informed opinion about them.

Personally, I think I would choose the latter option, but I would be as selective as possible with respect to which tables need to be audited.

regards,

hmscott

I like having history tables. It makes archiving and warehousing off the tables easier. It also keeps the OLTP system thin and fast where you can design to look at current data.

the best thing for an audit trail is Lumigent Entegra and for minimal auditing Lumigent Log Explorer