Using MySQL triggers to log all table changes to a secondary table

Question

I have a table:

CREATE TABLE `data_table` (
`data_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`field1` INT NOT NULL ,
`field2` INT NOT NULL ,
`field3` INT NOT NULL
) ENGINE = MYISAM ;

I would log to log any chances to field1, 2, or 3 to:

CREATE TABLE `data_tracking` (
`tracking_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`data_id` INT NOT NULL ,
`field` VARCHAR( 50 ) NOT NULL ,
`old_value` INT NOT NULL ,
`new_value` INT NOT NULL ,
`modified` DATETIME NOT NULL
) ENGINE = MYISAM ; 

I'm using MySQL 5, and I would like to create a trigger to do. I would like to insert a new row into data_tracking anytime data_table is updated, and record the old/updated value, as well as the field changed. I tried the following without any success.

DELIMITER $$

DROP TRIGGER `update_data `$$

CREATE TRIGGER `update_data` AFTER UPDATE on `data_table`
FOR EACH ROW
BEGIN
    IF (NEW.field1 != OLD.field1) THEN
        INSERT INTO data_tracking set old_value = OLD.field1, new_value = NEW.field1, field = "field1";
    END IF;
END$$

DELIMITER ;

It gave an error on the insert line, I'm not quite sure what the syntax on that should be, or if I'm going about this the right way. Any help would be appreciated. Thanks.

Marmolada

Answer 1

The insert syntax is

INSERT INTO table (columns_list) VALUES (values_list)

so your insert would look something like this (i'm not a MySQL expert but you can fit the query):

INSERT INTO data_tracking 
(`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
VALUES 
(NEW.data_id, 'field1', OLD.field, NEW.field, CURRENT_DATETIME());

And repeat it for variation on field2 and field3

I think this would be the complete trigger, please try:

DELIMITER $$

DROP TRIGGER `update_data `$$

CREATE TRIGGER `update_data` AFTER UPDATE on `data_table`
FOR EACH ROW
BEGIN
    IF (NEW.field1 != OLD.field1) THEN
        INSERT INTO data_tracking 
            (`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
        VALUES 
            (NEW.data_id, "field1", OLD.field1, NEW.field1, NOW());
    END IF;
    IF (NEW.field2 != OLD.field2) THEN
        INSERT INTO data_tracking 
            (`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
        VALUES 
            (NEW.data_id, "field2", OLD.field2, NEW.field2, NOW());
    END IF;
    IF (NEW.field3 != OLD.field3) THEN
        INSERT INTO data_tracking 
            (`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
        VALUES 
            (NEW.data_id, "field3", OLD.field3, NEW.field3, NOW());
    END IF;
END$$

DELIMITER ;

Petromilli

I just added my thought about the trigger – Petromilli

are you sure your "data_table" has a field named data_id? – Petromilli

Oops, sorry, you're right, when I created my example tables I changed one of them at the last minute. This worked perfectly. Sorry for the confusion :( – Marmolada

+1, for perfect solution; is there any way by which we can check all colums data without writing a lot of IF conditions??? – Rugose

@diEcho did you ever find a way to do that? – Gurge

@Ascherer; no i didn't find any alternative – Rugose

Define a MySQL variable to have same timestamp values: SET @ts=NOW() – Cilla