Using MySQL triggers for audit logging

If you’ve ever had to create a web application that requires user authentication, you may have been required to implement some sort of audit logging. Whether this would be for auditing user logins, maintaining a history of changes to content or keeping a trail of events in say, a call centre environment, there are various ways to achieve this.

Writing methods/functions to insert audit log methods
The “old” way (for me, at least). I had a database table set up for my audit logs, and I would write a method to write to this table. It would take a few parameters and perform the database write based on these; something like:

public static function auditLogSave($type, $userID, $data) {
  if (empty($type) || empty($userID) || empty($data)) { return false; }

  // Validation code
  // Write to database

  return true;
}

While there is certainly nothing wrong with the above, there are a few considerations that might make it less than desirable:

  • It’s an additional method call within your code
  • Potential for speed decrease in your application (particularly if you have a lot of audit log calls)
  • What happens when you want to change how the logger works, but in doing so you want to add an additional parameter? Refactoring time just increased significantly…

MySQL triggers
One way I’ve really taken a good grasp on lately is using MySQL triggers to achieve the exact same task. If you haven’t come across MySQL triggers before, take a look at this introduction – they’re a really good weapon to have in your arsenal!

The idea behind using a trigger is that you can write a query – or a selection of queries – and set them up to automatically run based on an INSERT, UPDATE or DELETE.

  • No additional code to run in your application
  • All of the work is taken care of by the database server
  • Changes mean updates to the trigger(s), which is likely to take a small fraction of the time it would take to refactor your entire application.

I’m currently writing a very basic page manager for a transactional-based website and have just deployed triggers to log any changes made to the content. It took me 10 minutes to set up the triggers for two tables and now I don’t have to remember to run any methods in my application code to audit log any changes!

Leave a Reply

Your email address will not be published. Required fields are marked *