George, concerning audit tables, First we set them up on each table not one big one for everything because these tables are hit with every data change and one big table would be a bottleneck.
We do not store the whole record. What we store is first a record concerning the instance of the change (If I do an insert of 20,000 records, this table will get one row). This records the instance id which is autoincremented, the user doing the change, the application the change came from, and the date. Since we have many applications that potentially could change data in a table, it is useful when things start to go bad to see if all the bad records came from the same application.
Then we have a detail table which records the instance id, the primary key for the row being changed, the old and new values and the field being changed. So insert one row with 20 columns and there would be twenty rows in this table associated with that insert. Update one column and there would be one row. Since we don't know in advance what the data type of the column is they are all stored in ntext (which will be varchar (max) probably when we move all our dbs to 2008 and stop working in compatibility mode for 2000.
I don't do this part and don't have access to the code, but I understand that we have code that autogenerates dynamically a new audit table when a new table is added. This code is run as part of the deployment process when changes are made to the completely audited database.
We also have partial auditing on some client specific databases depending on the nature of the tables, these audit tables are created from the same script but not done unless specifically asked for.
Code is proprietary and I can't put it here on a public forum.
The advantage of our system is that all audit tables are exactly alike making it easy to autogenerate them and meaning we don't have to change structure when the audited table changes structure. They are a little difficult to query, but we have the queries to extract the data we need already written, so that's not a huge problem for us. Being able to research who made a change and when and what application is priceless as is being able to undo bad data changes (sure came in handy on one memorable occasion when someone accidentally changed all the records in one table (the one with user logins naturally) to relate to the same person. Oops someone forgot a where clause but production was down less than five minutes.) Also has been handy to undo a bad import due to the client sending data in the wrong columns (first name data belongs in the first name column not the last name column) or when they accidentally changed their own unique identifiers and they wondered why Sally Smith was now John Jones.
"NOTHING is more important in a database than integrity." ESquared
