MySQL UPDATE触发器:插入实际更改的列的值 [英] MySQL UPDATE trigger: INSERTing the values of the columns that actually changed

问题描述

我正在开发一个非常简单的票务管理系统.我想保留添加，删除和更改的内容的日志.

I'm working on a fairly simple ticket managment system. I want to keep a log for stuff that gets added, deleted, and changed.

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

I created three triggers, AFTER INSERT, AFTER DELETE, and AFTER UPDATE. The INSERT/DELETE triggers are straightforward, it's theUPDATE trigger I'm having problems with.

我想用旧的&添加表中已更改的列.新值，即colname changed from X to Y

I would like to add which columns has changed in the table with their old & new values, i.e. colname changed from X to Y

我现在可以使用该触发器，但是它不会插入我想要的实际值.

The trigger I have now "works", except of course that it doesn't insert the actual values I'd like.

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

How do I get the value from OLD and NEW using the col_name variable?

我也不知道这是否是最好的方法.因此，如果有人对此有任何想法，也欢迎他们...这个触发开始于简单得多...

I'm also not sure if this is the best possible way of doing this ... So if anyone has ideas on that, they're welcome too ... This trigger started out a lot simpler ...

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

推荐答案

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

1. Since usage of prepared statements here is impossible, I would suggest you to call some INSERT statements, e.g. -

如果NEW.column1<> OLD.column1然后 插入... 万一; 如果NEW.column2<> OLD.column2然后 插入... 万一; ...

IF NEW.column1 <> OLD.column1 THEN INSERT INTO... END IF; IF NEW.column2 <> OLD.column2 THEN INSERT INTO... END IF; ...

或尝试将所需的所有字段复制到另一个表中.

Or try to copy all fields you need into another table.

在这种情况下，您将避免使用光标.

In these cases you will avoid using cursor.

这篇关于MySQL UPDATE触发器:插入实际更改的列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！