How Can I Track Record Changes in MySQL Using History Tables and Triggers?

Tracking Record Changes in MySQL Using History Table and Triggers

MySQL provides several options for tracking changes made to records in a database. One effective approach is to create a history table for each target data table. This history table will store all insert, update, and delete operations performed on each row in the original table.

Creating the History Table

To create a history table, you can use the following SQL query, replacing 'MyDB.data' with the name of the data table you want to track and 'primary_key_column' with the name of its primary key column:

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);

Using Triggers to Capture Changes

After creating the history table, you can set up triggers to automatically log changes to the original data table. For insert, update, and delete operations, the following triggers can be used:

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;

Example History Table

The history table will capture all changes made to the original data table. For example, given a data table with an 'ID' column as the primary key, the following history table entries might be generated:

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 

Viewing Changes

To view the changes made to specific columns, you can join the history table to itself on the primary key and revision columns and use appropriate SQL statements to compare previous and current values.

The above is the detailed content of How Can I Track Record Changes in MySQL Using History Tables and Triggers?. For more information, please follow other related articles on the PHP Chinese website!