Automatically Generating Audit Tables
Developers,
Have you ever been in a situation where data mysteriously goes awry in production? Your boss comes running in, asking "Why are the totals off on the Johnson account? What's wrong with your code!". You look at the data. Indeed it is incorrect. You search the code, you can't find any bugs. You even try some common operations in your test environment. Still you can't figure out why the data is incorrect. Was it invalid user input? A bug in your code? A new DBA with an overzealous update query? One might never know....
Unless that is, you have audit tables; tables that look just like your regular tables, but log every change in your DB! If you had audit tables, in the example above, you would just tell your boss to hang on a minute, run "select * from audit_account where AccountName='Johnson'" and five seconds later you could tell him that the new girl in marketing entered invalid data on 4/11/2007 at 10:00am local time. That sure makes for a better answer than a shrug and an "I donno..."
But, if you've ever used audit tables, you know what a pain they are to create and maintain. Each time you add a new column to a production table, you need to remember to update your audit table, or else you will be missing valuable information. Worse yet, if you remove a column, you will get an exception when you try to insert new data :-S. All in all they are invaluable when debugging, but a pain in the butt to implement.
Unless that is you have an audit table generator. What? You don't? For shame. There are probably some good ones out there -- I wouldn't know, I've never used one, and the ones I saw that looked good were pricey and overly-complicated. Being a DIY kind of guy, I wrote my own, and now I figured I would share it with you :-)
If you are merely here for the code, I suggest you scroll to the download link now. If you are a coder, or are for some incomprehensible way interested in the generation of audit tables, by all means, continue.
Explanation of Code
I'll try to avoid a long and tedious line by line analysis, but basically it works like this:
- The program connects to a database, and runs a query against the sysobjects table, and retrieves all the table names for a given DB. If you are not familiar, sysobjects is a very nifty "table of tables" and contains all the information about the structure of the database. Yes that's correct, T-SQL stores the schema of your database, *in* a database. Mind-bending huh?
- The list of tables gets populated into a ListBox, allowing users to select which ones they want to audit.
- The user hits "Generate" and the app loops through its list of tables, queries sysobjects for their schema, and writes a matching audit table.
- The program adds triggers to the target table for insert, update, and delete.
- Whenever a change to a target table is made, the trigger inserts a copy of the data into the audit table.
Usage Notes
Just paste in your connection string, select your tables, and hit Generate!
Compile with Visual Studio .NET 2005
This code is intended for example purposes only
If you do run it against a database, try it on a test DB first, and verify that it doesn't cause any unexpected results.
Screenshot:
Download Auditor
Happy coding!
- Brent
Attachment(s): Auditor.zip