If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
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.
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.