Blog

How To: Use MySQL triggers to log table changes

So you are looking for a simple way to log a history of changes to a table. There is a common practice for that, and it involves creating a logging function in your source code (maybe php) that will require you to connect to the database, and then write the values into the log table. However, I prefer using MySQL triggers for logging table changes for efficiency and simplicity.
 

CREATE TABLE data (
	  id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	  timestamp   TIMESTAMP,
	  data1 VARCHAR(255) NOT NULL,
	  data2  DECIMAL(5,2) NOT NULL
);

CREATE TABLE data_log (
	  action ENUM('insert','update','delete'),
	  id   INT UNSIGNED NOT NULL INDEX,
	  timestamp   TIMESTAMP,
	  data1 VARCHAR(255) NOT NULL,
	  data2  DECIMAL(5,2) NOT NULL
);

The data table contains all the information pertaining to the current data values. When an action occurs (such as an insert, update, or delete) to that table we want it logged to the log table, data_log.

You will notice 2 differences between the tables.

1. The id field has the nonunique index rather than being a primary key. This allows multiple rows per id value as primary keys are required to be unique values.
2. It contains an action field to indicate which type of MySQL statement was executed.

To ensure that changes to the data table are logged to the data_log table, we need to create a set of triggers. The triggers need to adhere to the following rules:

• For inserts, log an insert operation showing the values in the new row.
• For updates, log an update operation showing the new values in the updated row.
• For deletes, log a removal operation showing the values in the deleted row.

For this example we will specify AFTER triggers because we only want the log table to track successful changes to the data table (BEFORE triggers will activate even if row-change operation fails).
 

CREATE TRIGGER ai_data AFTER INSERT ON data
	FOR EACH ROW
	BEGIN
	  INSERT INTO data_log (action,id,timestamp,data1,data2)
	  VALUES('insert',NEW.id,NOW(),NEW.data1,NEW.data2);
	END;

	CREATE TRIGGER au_data AFTER INSERT ON data
	FOR EACH ROW
	BEGIN
	  INSERT INTO data_log (action,id,timestamp,data1,data2)
	  VALUES('update,NEW.id,NOW(),NEW.data1,NEW.data2);
	END;

	CREATE TRIGGER ad_data AFTER INSERT ON data
	FOR EACH ROW
	BEGIN
	  INSERT INTO data_log (action,id,timestamp,data1,data2)
	  VALUES('delete',OLD.id,NOW(),OLD.data1,OLD.data2);
	END;

NOTE: If you are performing this via phpMyAdmin the you will need to modify the above trigger creation code. The reason being that the semicolon causes an issue as it is usually defaulted to the Delimiter. Therefore, change 'END;' to 'END$$' for each trigger, and you will need to also change the Delimiter of phpMyAdmin to '$$'. The Delimiter field in phpMyAdmin will be located just below the SQL box in the browser.

Tags: mysql, triggers