MySQL 更新触发器 : INSERTing the values of the columns that actually changed

Question

我正在开发一个相当简单的工单管理系统。我想为添加、删除和更改的内容保留日志。

我创建了三个触发器，AFTER INSERT、AFTER DELETE 和AFTER UPDATE。 INSERT/DELETE 触发器很简单，我遇到问题的是 UPDATE 触发器。

我想添加表中哪些列已更改及其旧值和新值，即 colname 从 X 更改为 Y

我现在拥有的触发器“有效”，当然它不会插入我想要的实际值。

如何使用 col_name 变量从 OLD 和 NEW 获取值？

我也不确定这是否是执行此操作的最佳可能方法...所以如果有人对此有想法，也欢迎他们...此触发器开始于简单多了...

BEGIN
    DECLARE num_rows, i int default 1;
    DECLARE col_name CHAR(255);
    DECLARE updated TEXT;

    DECLARE col_names CURSOR FOR
        SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'storing'
        ORDER BY ordinal_position;

    OPEN col_names;
    SELECT FOUND_ROWS() INTO num_rows;
    SET i = 1;
    SET @updated = 'Updated columns: ';

    the_loop: LOOP
        IF i > num_rows THEN
            LEAVE the_loop;
        END IF;

        FETCH col_names INTO col_name;

        /* So, how do I get the proper values? */
        /* IF NEW.@col_name != OLD.@col_name THEN */
        /*SET @updated = CONCAT(@updated, OLD.@col_name, ' changed into ', NEW.@col_name, ' ');*/
        SET @updated = CONCAT(@updated, 'OLD', ' changed into ', 'NEW', ' ');
        /* END IF;*/

        SET i = i + 1;
    END LOOP the_loop;

    CLOSE col_names;

    INSERT INTO `log` (`storing`, `medewerker`, `actie`, `data`)
    VALUES (NEW.`id`, NEW.`medewerker`, "Storing aangepast", @updated);
END

Answer 1

1. 由于这里不可能使用准备好的语句，我建议您调用一些 INSERT 语句，例如-

   IF NEW.column1 <> OLD.column1 THEN 插入...万一;如果 NEW.column2 <> OLD.column2 那么 插入...万一;...

2. 或者尝试将您需要的所有字段复制到另一个表中。

在这些情况下，您将避免使用游标。