Using MySQL Triggers to Audit Field Changes from a Database Table

Original Source: http://www.phpugph.com/blog/2008/03/21/using-mysql-triggers-to-audit-field-changes-from-a-database-table/

Author: Arpee Ong

A MySQL trigger is an object that is associated to a table. It is a set of tasks that is ran/activated when a particular operation is imposed on a table. A couple of operations that can invoke a MySQL trigger is an Insert and Update.

This article will discuss how this feature can be used to audit field changes that will natively be handled via the MySQL DB engine. This is useful for the following scenarios.

1.) When there is a need to return multiple previous versions of a given data
2.) Track recent changes with a possibility to rollback a field to previous states
3.) When field data should not be lost upon update
4.) When there is a need to do all of the above without the intervention of webserver scripting methods

We will use two tables for this article as an example as follows:

Table 1. `content` table - will be the table whose content field will be audited:

Code:

CREATE TABLE  `content` (
`id` int(11) unsigned NOT NULL auto_increment,
`title` varchar(100),
`content` varchar(100),
PRIMARY KEY  (`id`)
)

Prefill the `content` table with sample Data

Code:

INSERT INTO content VALUES
(1, "1st Article", "1st Content"),
(2, "2nd Article", "2nd Content"),
(3, "3rd Article", "2nd Content");

SELECT * from `content`; now will return the following:

Code:

+----+-------------+-------------+
| id | title       | content     |
+----+-------------+-------------+
|  1 | 1st Article | 1st Content |
|  2 | 2nd Article | 2nd Content |
|  3 | 3rd Article | 2nd Content |
+----+-------------+-------------+
3 rows in set (0.00 sec)

Table 2. `content_audit` table - will audit the `content.content` field.

Code:

CREATE TABLE  `tester2`.`content_audit` (
`id` int(11) unsigned NOT NULL auto_increment,
`content_id` int(11) NOT NULL default '0',
`before_value` mediumtext,
`after_value` mediumtext,
`date_created` timestamp NOT NULL default CURRENT_TIMESTAMP
           on update CURRENT_TIMESTAMP,
PRIMARY KEY  (`id`)
)

The Goal: Keep track of the changes made to `content.content` field utilizing `content_audit` table.

Step 1: Create the Trigger by executing the following SQL Query

Code:

DELIMITER |
CREATE TRIGGER audit_content BEFORE UPDATE ON content
FOR EACH ROW BEGIN
INSERT INTO content_audit SET content_id = OLD.id;
UPDATE content_audit SET before_value = OLD.content
           WHERE id = last_insert_id();
UPDATE content_audit SET after_value = NEW.content
           WHERE id = last_insert_id();
END;
|
DELIMITER ;

Explanation:

Code:

DELIMITER |

Tells MySQL to use “|” as the delimiter instead of the default “;”. This makes it possible to execute the whole create trigger statement without the mysql cli having to halt on “;”.

Code:

CREATE TRIGGER audit_content BEFORE UPDATE ON content

CREATE TRIGGER invokes the creation of the trigger, in the case above, it accepted several parameters as follows:

audit_content = the name of the trigger
BEFORE = when to execute the statement {BEFORE|AFTER} the event
UPDATE = the operation that should activate the trigger
ON = specifier of the table where to associate the trigger
content = the name of the table to associate the trigger

Code:

FOR EACH ROW BEGIN

For every row UPDATE imposed on the `content` table, the succeeding statements will be executed

Code:

  INSERT INTO content_audit SET content_id = OLD.id;
UPDATE content_audit SET before_value = OLD.content
                              WHERE id = last_insert_id();
UPDATE content_audit SET after_value = NEW.content
                              WHERE id = last_insert_id();

The Statement above will be executed everytime an UPDATE operation is imposed on `content` table

OLD.id = the value of `content.id`.
last_insert_id() = the value of the last ID inserted on the same table, in this case, the last ID inserted into content_audit

Code:

END;

Ends the FOR EACH statement of the trigger

Code:

|

Halts the script

Code:

DELIMITER ;

Switches the DELIMITER back to the default “;”

Step 2: Update one of the `content`.`content` field

Code:

UPDATE content SET content = "2nd Content - 2nd Version" WHERE id = "2";

The above code alters the content of the “2nd Article” and should activate the Trigger `audit_content` trigger.

To check if the Trigger was activated. Run the following Query to Return all prior versions of the “2nd Article”

Code:

SELECT
c.title as "Title,
c.content as "Current Content",
ca.before_value as "Content before latest Update"
FROM content c
JOIN content_audit ca on ca.content_id = c.id;

which should output (if the trigger is working)

Code:

+-------------+---------------------------+------------------------------+
| title       | Current Content           | Content before latest Update |
+-------------+---------------------------+------------------------------+
| 2nd Article | 2nd Content - 2nd Version | 2nd Content                  |
+-------------+---------------------------+------------------------------+
1 row in set (0.00 sec)

Notice that in the above resultset, the old value of the field is not lost and we were able to return it side by side the current data.

If there were more than 1 revision of the `content`.`content`, the same query will enable you to cascade through the different prior versions and even sort them by date using `content_audit`.`date_created` field.

A discrepancy/sanity check can be done by checking if the latest `content_audit`.`after_value` is equal to the `content`.`content`.

END