One of the useful things that i’ve been asked to set-up lately is automatic logging of changes to several of our database tables.
My first thought was to do this in Perl (as the rest of the system is in Perl), but this would mean adding extra methods and calls in the Perl code to update the database (both the original tables and the new log tables). That seemed like a solution - a pain in the arse to implement, but a solution.
Thankfully one of the helpful chaps in my department suggested doing it all in the database with triggers as this is quite common in banks and the like. What a damn fine idea! Only a little SQL to write and no extra Perl.
Here’s an example…
First, here’s the table that we want to create an audit log for:
Now, we create the audit table - the audit table is exactly the same as the table that we wish to keep a log of, except for one extra column, “AUDIT_DATE”, this will keep record the date/time of when an change occurred on the original table.
Finally, we create the trigger to keep a log of changes to our original table.
Now whenever a delete or update action is performed on the “COMMENT” table, a record of the old values and the date/time at which they were changed is recorded in the audit table.