mysql 表触发器 修改其他表_使用MySQL触发器将所有表更改记录到辅助表

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.

解决方案

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 ;