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.

Answer 1

The insert sintax is

INSERT INTO table (columns_list) VALUES (values_list)

so your insert would looks something like this (i'm not 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 ;