Create history record for every change in MySQL table - Audit trail; MySQL trigger

Tuesday August 30, 2011 ()

Here is a way of maintaining records of changes made to a table in your MySQL database using MySQL trigger.

Let us assume that we have a database called my_database, we also have a table called main_table, fields of this table are field1, field2, field3. We want history of changes made to this table into a table we call history_table. This has the same fields as main_table.

Let us call our trigger my_trigger.

  
DELIMITER $$

USE `my_database`$$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `my_database`.`my_trigger`
BEFORE UPDATE ON `my_database`.`main_table`
FOR EACH ROW
BEGIN
     INSERT INTO history_table                 
     VALUES
     (
          OLD.field1,                        
          OLD.field2,
          OLD.field3                       
     );
					
END$$
 
      

The OLD alias above refers the field value of our main_table before the edit. DEFINER is optional. Back quotes are also optional.

Below is a conditional trigger. It updates our history table only when a specific field value changed, in this case, only when the value field1 has changed.

  
DELIMITER $$

USE `my_database`$$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `my_database`.`my_trigger`
BEFORE UPDATE ON `my_database`.`main_table`
FOR EACH ROW
BEGIN
IF (OLD.field1 <> NEW.field1) THEN
     INSERT INTO history_table                 
     VALUES
     (
          OLD.field1,                        
          OLD.field2,
          OLD.field3                       
     );
END IF;					
END$$
 
      

If you are using phpmyadmin, enter the above command like a reqular SQL command in the SQL query window. If you are using MySQL Workbench, select the database, right click on main_table and select alter table. A window will open with options near the bottom of the window. Select trigger and enter your create trigger command.

Good luck.


10,036

Comments (Create history record for every change in MySQL table - Audit trail; MySQL trigger )