How Can I Track and View Database Changes in MySQL Using Triggers and Views?

Tracking History of Database Changes with MySQL

Many database systems require the ability to track changes made to records over time. MySQL offers a straightforward technique to achieve this. Consider a data table named "data" that you want to monitor for changes.

To start, create a history table for "data" with the same structure, except for three additional columns:

• action: VARCHAR(8) default 'insert'
• revision: INT(6) NOT NULL auto_increment
• dt_datetime: DATETIME NOT NULL default CURRENT_TIMESTAMP

These columns store the operation (insert/update/delete), sequence number, and the date and time of the operation, respectively.

Next, create triggers to capture these operations and populate the history table:

• After Insert Trigger: INSERT INTO data_history (action, revision, dt_datetime, ...) SELECT 'insert', NULL, NOW(), ... FROM data WHERE ...
• After Update Trigger: INSERT INTO data_history (action, revision, dt_datetime, ...) SELECT 'update', NULL, NOW(), ... FROM data WHERE ...
• Before Delete Trigger: INSERT INTO data_history (action, revision, dt_datetime, ...) SELECT 'delete', NULL, NOW(), ... FROM data WHERE ...

Now, all data changes will be recorded in the "data_history" table. To display the changes for a specific column across updates, you can join the history table to itself on the primary key and sequence columns:

CREATE VIEW data_history_changes AS
SELECT t2.dt_datetime, t2.action, t1.row_id,
IF(t1.a_column = t2.a_column, t1.a_column, t1.a_column || " to " || t2.a_column) AS a_column
FROM data_history AS t1 INNER JOIN data_history AS t2
ON t1.row_id = t2.row_id
WHERE (t1.revision = 1 AND t2.revision = 1) OR t2.revision = t1.revision + 1
ORDER BY t1.row_id ASC, t2.revision ASC

This should provide a compact and historical view of all changes made to the "data" table.

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