Simple Audit Logging of Table Changes Using Triggers in MySQL

Tracking changes to database data can be used to provide a simple history of the data over time or an audit log of who changed what in order to increase the accountability of system users. Logging can be done in application layer code in an easy manner if you are using a database abstraction layer (DAL) / Data-Access-Objects (DAO) / Object-Relational-Mapper (ORM) (e.g. Propel, Doctrine, Java’s Hibernate, etc.) by writing a hook into the DAL/DAO/ORM’s insert/update routines to log any changes. In the past I’ve done this and you can choose different methods to log the data change:

  1. select
    the record that’s about to be updated and store a serialized map of the data into the log table, then when the user looks at the log, the serialized data can be retrieved and compared to the latest record and changes highlighted – clumsy and slow – but can be applied to any/all tables with little table-structure-specific code.
  2. select the record that’s about to be updated and compare the pre and post update field values and log those fields that have changed.
  3. Use database triggers to automatically log changes to the database tables of interest. cleanest and least error prone – ONCE it’s tested properly.

What follows is an example of method B above, which tracks changes to records in a user table, using PHP:

Assuming you have a logging table something like this:

## Create a logging table
CREATE TABLE `audit_log` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `field_name` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `old_value` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `new_value` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `activity` char(6) CHARACTER SET utf8 NOT NULL DEFAULT 'UPDATE',
  `modified` datetime DEFAULT NULL,
  `modified_by_user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)

Then in PHP, you would select the record before the update/insert and pass it to this function

	 * Logs changes to user table on key fields to
	 * the [audit_log] table.
         * @param mixed. pre-update [user] table record object
         * @param mixed. post-update [user] table record object
	 * @return boolean. true on success 
	function logUserChanges($pre_update, $post_update){

		$log_success = true;

			// define a list of fields of interest to log changes for
			$log_fields = array( 'user_group', 'user_active');

			// loop through and compare fields pre/post update
			$update_type		= isset($pre_update->user_id) ? 'UPDATE' : 'INSERT';
			$modified_by_user_id = $_SESSION['user_id'] // $field_of_interest) ? $pre_update->$field_of_interest : null;
				$post_update_value = $post_update->$field_of_interest;
				if ($pre_update_value != $post_update_value) {

					$log_sql =   "INSERT INTO audit_log SET
											user_id 			= '".mysql_real_escape_string($post_update->user_id)."',
											field_name 			= '".mysql_real_escape_string($field_of_interest)."',
											old_value 			= '".mysql_real_escape_string($pre_update_value)."',
											new_value 			= '".mysql_real_escape_string($post_update_value)."',
											activity			= '$update_type', 	
											modified_by_user_id	= '$modified_by_user_id'";

					// log change
					if (db::sendQuery($log_sql) != true) {
						// log failed, set flag
						$log_success = false;

		return $log_success;

This method works, but obviously takes a lot of code and is slower than the third option. The next example is written for mySQL (version 5+,

Here are the two triggers (one for after insert, the other for after update). They assume that you have a field on the table of interest (in this example [users]) called user_lastupdated_by_user_id that gets updated with the user ID of the last user to edit the table. Since MySQL triggers can’t be passed variables, this field is used on the [user] table to insert with the log record to record who made the change, into the [audit_log] table.

## Add trigger
## Logs insertions to the [user] table on key fields.
## Logged to [audit_log].

        INSERT INTO audit_log 
                (`user_id` , `field_name` , `new_value` , `activity`, `modified`, `modified_by_user_id` ) 
                (NEW.user_id, "user_group", NEW.ug_id, "INSERT", NOW(), NEW.user_lastupdated_by_user_id);

        INSERT INTO audit_log 
                (`user_id` , `field_name` , `new_value` , `activity`, `modified`, `modified_by_user_id` ) 
                (NEW.user_id, "user_active", NEW.user_active, "INSERT", NOW(), NEW.user_lastupdated_by_user_id);

## Add update trigger: update_to_users_table
## Logs changes to the [user] on key fields.
## Specific IF statements are used to check if the field is of interest
## and if so, log it to [audit_log].
    IF (NEW. user_group != OLD. user_group) THEN
        INSERT INTO audit_log 
                (`user_id` , `field_name` , `old_value` , `new_value` , `modified`, `modified_by_user_id` ) 
                (NEW.user_id, "user_group", OLD. user_group, NEW. user_group, NOW(), NEW.user_lastupdated_by_user_id);
    END IF;
    IF (NEW.user_active != OLD.user_active) THEN
        INSERT INTO audit_log 
                (`user_id` , `field_name` , `old_value` , `new_value` , `modified`, `modified_by_user_id` ) 
                (NEW.user_id, "user_active", OLD.user_active, NEW.user_active, NOW(), NEW.user_lastupdated_by_user_id);
    END IF;    

This approach means that changes to data in the fields of interest get logged automatically internally by the database on every insert/update (and delete if you write a similar trigger for those). For high frequency database calls, using triggers is much more efficient than method B above.

Tags: ,

One Response to “Simple Audit Logging of Table Changes Using Triggers in MySQL”