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 to use 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 does have triggers.

Ricardo Olsen, MEng. :: https://dscsys.com

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