Using Triggers To Keep Track Of MySQL Table Changes

There are many ways to log changes in MySQL tables:

  • MySQL server query log files;
  • Dumps combined with versioning;
  • Log created by the application;
  • Database diff tools;
  • Table triggers.

All of them are very useful but have different characteristics and drawbacks.

Server query log files is by far the easiest method, just enable it in the configuration file. The problem with this method is that it logs all queries to all tables of the database, so if your MySQL server is somewhat loaded, the performance can be severely affected.

I’ve found that triggers can enable the finest grain of control on table changes. Triggers allows to record only the changes you need to see, by whom and when the modifications were made.

Applying this technique can be moderately tricky, so I’m sharing my experience with it here.

I’ve created a log table like this:

CREATE TABLE logtable
(
serialnum INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
fwd_sql VARCHAR(512) DEFAULT "",
backwd_sql VARCHAR(512) DEFAULT "",
modtable VARCHAR(64) NOT NULL,
moduser VARCHAR(64) DEFAULT "",
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The fwd_sql field records the modifier query and the backwd_sql keeps an undo query statement. The modtable field keeps the modified table name. The fields serialnum and ts are automatically created for each record.

An example logged table is:

CREATE TABLE loggedtable
(
primaryfield INTEGER PRIMARY KEY NOT NULL,
intfield1 INTEGER NOT NULL, 
charfield2 VARCHAR(512) DEFAULT "",
charfield3 VARCHAR(512) DEFAULT ""
);

It’s necessary 3 triggers for each table that needs logging (exemplified as loggedtable), for inserts, deletes and updates, like this:

DROP TRIGGER IF EXISTS loggedtable_after_insert;
DELIMITER $$
CREATE TRIGGER loggedtable_after_insert                 -- trigger name
AFTER INSERT ON loggedtable                             -- table being triggered after insert queries
FOR EACH ROW
BEGIN
 INSERT INTO logtable                                   -- table that records the changes
   ( fwd_sql, backwd_sql, modtable, moduser ) 
 VALUES
   (
   CONCAT(
     "INSERT INTO loggedtable (primaryfield, intfield1, charfield2, charfield3 ) VALUES (",
     CAST( NEW.primaryfield AS CHAR ), ",",
     CAST( NEW.intfield1 AS CHAR ), ",",
     "'", NEW.charfield2, "'", ",",
     "'", NEW.charfield3, "'",
     ")" 
   ),                                                                                         -- modifying operation
   CONCAT( "DELETE FROM loggedtable WHERE primaryfield=", CAST( NEW.primaryfield AS CHAR ) ), -- undo operation
   "loggedtable",                                                                             -- table affected
   user()                                                                                     -- modifier user
   );
END $$
DELIMITER ;
DROP TRIGGER IF EXISTS loggedtable_after_delete;
DELIMITER $$
CREATE TRIGGER loggedtable_after_delete
AFTER DELETE ON loggedtable
FOR EACH ROW
BEGIN
 INSERT INTO logtable 
   ( fwd_sql, backwd_sql, modtable, moduser ) 
 VALUES 
   ( 
   CONCAT( "DELETE FROM loggedtable WHERE primaryfield=", CAST( OLD.primaryfield AS CHAR ) ),
   CONCAT(
   "INSERT INTO loggedtable (primaryfield, intfield1, charfield2, charfield3) VALUES (", 
      CAST( OLD.primaryfield AS CHAR ), ",",
      CAST( OLD.intfield1 AS CHAR ), ",",
      "'", OLD.charfield2, "'", ",",
      "'", OLD.charfield3, "'",
      ")" 
   ),
 "loggedtable",
 user() 
 );
END $$
DELIMITER ;
DROP TRIGGER IF EXISTS loggedtable_after_update;
DELIMITER $$
CREATE TRIGGER loggedtable_after_update
AFTER UPDATE ON loggedtable
FOR EACH ROW
BEGIN
 INSERT INTO logtable
   ( fwd_sql, backwd_sql, modtable, moduser )
 VALUES
   (
   CONCAT( 
     "UPDATE loggedtable SET ",
     if( NEW.intfield1 = OLD.intfield1, "", CONCAT( "intfield1=", CAST( NEW.intfield1 AS CHAR ), "," ) ),
     if( NEW.charfield2 = OLD.charfield2, "", CONCAT( "charfield2=", "'", CAST( NEW.charfield2 AS CHAR ), "'," ) ),
     if( NEW.charfield3 = OLD.charfield3, "", CONCAT( "charfield3=", "'", CAST( NEW.charfield3 AS CHAR ), "'," ) ),
     CONCAT( "primaryfield=", CAST( NEW.primaryfield AS CHAR ) ),
   " WHERE ", "primaryfield=", CAST( NEW.primaryfield AS CHAR )
   ),
   CONCAT(
     "UPDATE loggedtable SET ",
     if( NEW.intfield1 = OLD.intfield1, "", CONCAT( "intfield1=", CAST( OLD.intfield1 AS CHAR ), "," ) ),
     if( NEW.charfield2 = OLD.charfield2, "", CONCAT( "charfield2=", "'", CAST( OLD.charfield2 AS CHAR ), "'," ) ),
     if( NEW.charfield3 = OLD.charfield3, "", CONCAT( "charfield3=", "'", CAST( OLD.charfield3 AS CHAR ), "'," ) ),
     CONCAT( "primaryfield=", CAST( OLD.primaryfield AS CHAR ) ),
   " WHERE ", "primaryfield=", CAST( OLD.primaryfield AS CHAR )
   ),
   "loggedtable",
   user()
   );
END $$
DELIMITER ;

The NEW and OLD are special objects inside triggers containing the new and old state of the row that is being modified. The function user() retrieves the user name, in the form “user@host“.

If record fields of the logged table can contain NULL, a COALESCE operation must be applied like COALESCE(OLD.charfield2, “”), or the log record may not be inserted as desired.

In MySQL, the CREATE TRIGGER command requires special rights (SUPER privilege) to be executed.

In other databases servers systems like PostgreSQL and Oracle, things should be accomplished in a similar way. Even SQLite has triggers.

Ricardo Olsen in-2c-14px, MEng. :: https://dscsys.com

Copyright © 2014 Ricardo L. Olsen. All rights reserved.

Advertisements

9 thoughts on “Using Triggers To Keep Track Of MySQL Table Changes

  1. Hi. Thanks. I’m trying to test this. But creating the last trigger I get:
    Error Code : 1583
    Incorrect parameters in the call to native function ‘CONCAT’

    1. That was a missing comma after ‘CAST( NEW.primaryfield AS CHAR ) )’. Solving that, now i’m getting:

      Error Code : 1064
      You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ AS CHAR ), “,” ) ),
      if( NEW.charfield2 = OLD.charfield2, “”, CONCAT( “char’ at line 19

      😦

  2. This was a very descriptive and easy to follow article. It helped me greatly and was my first use of COALESCE. In regards to NULL values you may also want to be sure to use a null safe operator to compare the new and old values in the update trigger.
    Thanks again.

  3. Hi – thanks for the useful article. It’s worth saying that within the trigger you can access the original sql query, that caused the trigger invocation, without having to manually recreate it, as you’re doing here – see http://stackoverflow.com/questions/10628640/get-full-mysql-query-string-on-insert-or-update.

    “You can get the current SQL query as a string with the following statement:
    SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s