使用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屋！