MySQL option/feature to track history of changes to records

Hi, my name is Guillermo Antony Cava Nuñez, today while attending an issue at work, it happened that a user was claiming that they in fact submited information with something spelled one way but in fact in the database they submited it wrong. Long story short they changed it in the database but in order to not have these sorts of situtions occur again, the project director wanted to introduce a way to record all changes and past revisions beyond date of record changed. This lead me to dicover mysql triggers. A lass, stackover flow.

 

Guillermo Cava Nuñez

Guillermo Antony Cava Nuñez

Guillermo Cava Nunez

Guillermo Antony Cava Nunez

Guillermo Cava

Guillermo Cava Nuñez

Guillermo Antony Cava Nuñez

Guillermo Cava Nunez

Guillermo Antony Cava Nunez

Guillermo Cava

Here’s a straightforward way to do this:

First, create a history table for each data table you want to track (example query below). This table will have an entry for each insert, update, and delete query performed on each row in the data table.

The structure of the history table will be the same as the data table it tracks except for three additional columns: a column to store the operation that occured (let’s call it ‘action’), the date and time of the operation, and a column to store a sequence number (‘revision’), which increments per operation and is grouped by the primary key column of the data table.

To do this sequencing behavior a two column (composite) index is created on the primary key column and revision column. Note that you can only do sequencing in this fashion if the engine used by the history table is MyISAM (See ‘MyISAM Notes’ on this page)

The history table is fairly easy to create. In the ALTER TABLE query below (and in the trigger queries below that), replace ‘primary_key_column’ with the actual name of that column in your data table.

CREATE TABLE MyDB.data_history LIKE MyDB.data;

ALTER TABLE MyDB.data_history MODIFY COLUMN primary_key_column int(11) NOT NULL, 
   DROP PRIMARY KEY, ENGINE = MyISAM, ADD action VARCHAR(8) DEFAULT 'insert' FIRST, 
   ADD revision INT(6) NOT NULL AUTO_INCREMENT AFTER action,
   ADD dt_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER revision,
   ADD PRIMARY KEY (primary_key_column, revision);

 

And then you create the triggers:

DROP TRIGGER IF EXISTS MyDB.data__ai;
DROP TRIGGER IF EXISTS MyDB.data__au;
DROP TRIGGER IF EXISTS MyDB.data__bd;

CREATE TRIGGER MyDB.data__ai AFTER INSERT ON MyDB.data FOR EACH ROW
    INSERT INTO MyDB.data_history SELECT 'insert', NULL, NOW(), d.* 
    FROM MyDB.data AS d WHERE d.primary_key_column = NEW.primary_key_column;

CREATE TRIGGER MyDB.data__au AFTER UPDATE ON MyDB.data FOR EACH ROW
    INSERT INTO MyDB.data_history SELECT 'update', NULL, NOW(), d.*
    FROM MyDB.data AS d WHERE d.primary_key_column = NEW.primary_key_column;

CREATE TRIGGER MyDB.data__bd BEFORE DELETE ON MyDB.data FOR EACH ROW
    INSERT INTO MyDB.data_history SELECT 'delete', NULL, NOW(), d.* 
    FROM MyDB.data AS d WHERE d.primary_key_column = OLD.primary_key_column;

 

And you’re done. Now, all the inserts, updates and deletes in ‘MyDb.data’ will be recorded in ‘MyDb.data_history’, giving you a history table like this (minus the contrived ‘data_columns’ column)

ID    revision   action    data columns..
1     1         'insert'   ....          initial entry for row where ID = 1
1     2         'update'   ....          changes made to row where ID = 1
2     1         'insert'   ....          initial entry, ID = 2
3     1         'insert'   ....          initial entry, ID = 3 
1     3         'update'   ....          more changes made to row where ID = 1
3     2         'update'   ....          changes made to row where ID = 3
2     2         'delete'   ....          deletion of row where ID = 2

 

To display the changes for a given column or columns from update to update, you’ll need to join the history table to itself on the primary key and sequence columns. You could create a view for this purpose, for example:

CREATE VIEW data_history_changes AS 
   SELECT t2.dt_datetime, t2.action, t1.primary_key_column as 'row id', 
   IF(t1.a_column = t2.a_column, t1.a_column, CONCAT(t1.a_column, " to ", t2.a_column)) as a_column
   FROM MyDB.data_history as t1 INNER join MyDB.data_history as t2 on t1.primary_key_column = t2.primary_key_column 
   WHERE (t1.revision = 1 AND t2.revision = 1) OR t2.revision = t1.revision+1
   ORDER BY t1.primary_key_column ASC, t2.revision ASC

 

Guillermo Cava Nuñez

Guillermo Antony Cava Nuñez

Guillermo Cava Nunez

Guillermo Antony Cava Nunez

Guillermo Cava

Guillermo Cava Nuñez

Guillermo Antony Cava Nuñez

Guillermo Cava Nunez

Guillermo Antony Cava Nunez

Guillermo Cava

One thought on “MySQL option/feature to track history of changes to records

  1. Hi, I have followed all the steps and executed.
    When I try insert/update in my parent table, I am receiving the below error.
    “Unknown column `parent_table`.`id` in where clause”.

Leave a Reply

Your email address will not be published. Required fields are marked *