使用 MySQL 触发器将所有表更改记录到辅助表 [英] Using MySQL triggers to log all table changes to a secondary 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 ;

我会登录以记录任何进入 field1、2 或 3 的机会:

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 ; 

我使用的是 MySQL 5，我想创建一个触发器来完成.我想在 data_table 更新时在 data_tracking 中插入一个新行，并记录旧的/更新的值，以及更改的字段.我尝试了以下方法但没有成功.

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.

推荐答案

插入语法是

INSERT INTO table (columns_list) VALUES (values_list)

所以你的插入看起来像这样(我不是 MySQL 专家，但你可以适合查询):

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());

并在 field2 和 field3 上重复此操作

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 ;

这篇关于使用 MySQL 触发器将所有表更改记录到辅助表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！