More an exercise in stored procedures, triggers, and building queries culled from the information_schema than anything else, here’s an audit trail setup.
Audit trails let you see how data has changed over the course of time, so you know who to blame when something goes wrong.
This particular setup works by adding an AFTER UPDATE trigger to each table to be audited. That trigger looks at the OLD and NEW values of each field, and if there is a difference, inserts a record into the auditLog.
- auditTrail.sql – Creates the table to hold the audit data and the stored procedure which is called to add a trigger to each table you wish to audit.
- setupTest.sql – Creates an example data table such as you might want to audit, and calls the stored procedure to build the trigger for it. The output from that stored procedure should be piped back in to MySQL to actually create the trigger. Why doesn’t it do this automatically? Because prepared statements don’t support CREATE TRIGGER statements as of 5.0.27.
- runTest.sql – Inserts a few rows of data into the example data table, updates them, and displays the changes recorded in the audit table.
Here’s the Windows command line to run the test in the `test` database:
mysql test < auditTrail.sql && mysql -N test < setupTest.sql | mysql --delimiter=$ test && mysql -t test < runTest.sql
For my purposes, watching updates was sufficient. You could extend this to cover inserts and deletes too.
There are a number of drawbacks to this particular method:
- It's a bit unwieldy, since the SQL output has to be piped back in to create the triggers. But hey, if the Wizard can do it, it's ok for us mere mortals too. This could be wrapped in a script. Or maybe it's a good thing, because then you can go edit the output and exclude fields you don't want audited.
- The tigger has to be created for each table separately. Again, handy if there are only a couple tables you want to audit, but a real pain if you wanted to audit all 297 tables in your database.
- If the table structure changes, you'll need to drop and recreate the trigger.
- The stored procedure assumes the current database. Again, something trivial to change.
- Hope you didn't want to use your AFTER UPDATE trigger for something else.
My next trick will be to add something so that we know which user made the change. Not which MySQL user, mind you, since these changes are issued from PHP scripts which all run as the same MySQL user. Somehow I need to pass in my application user id. Any suggestions?