title: Using MySQL triggers to log all table changes to a secondary table
body: <p>I have a table:</p>
  
  <pre><code>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 ;
  </code></pre>
  
  <p>I would log to log any chances to field1, 2, or 3 to:</p>
  
  <pre><code>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 ; 
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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 ;
  </code></pre>
  
  <p>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.</p>