使用 MySQL 触发器将所有表更改记录到辅助表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/779230/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using MySQL triggers to log all table changes to a secondary table
提问by Rob
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:
我会登录以记录字段 1、2 或 3 的任何机会：
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.
我正在使用 MySQL 5，我想创建一个触发器来做。我想在 data_table 更新时在 data_tracking 中插入一个新行，并记录旧的/更新的值，以及更改的字段。我尝试了以下但没有任何成功。
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.
它在插入行上给出了一个错误，我不太确定它的语法应该是什么，或者我是否以正确的方式处理这个问题。任何帮助，将不胜感激。谢谢。
回答by Jhonny D. Cano -Leftware-
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):
所以你的插入看起来像这样（我不是 MySQL 专家，但你可以适应查询）：
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
并重复 field2 和 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 ;
