mysql - SQL触发器动态访问行的字段

Question

当前正在尝试拥有一个通用事件日志表来存储更改的表、字段、值(+必要的主键)

DELIMITER $$
CREATE TRIGGER tr_customers_insert_activity_log AFTER INSERT ON `customers`
    FOR EACH ROW
    BEGIN

      DECLARE curr_column CHAR(255);
      DECLARE finished INT DEFAULT false;
      DECLARE column_name_cursor CURSOR FOR SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'customers' ORDER BY ordinal_position;
      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET finished = 1;

      OPEN column_name_cursor;
        column_loop: LOOP
          IF finished THEN
            LEAVE column_loop;
          END IF;
          FETCH column_name_cursor INTO curr_column;
          INSERT INTO activity_log(`cid`, `table`, `field`, `value`, `modified_by`, `modified_at`) 
            VALUES (NEW.cid, 'customers', curr_column, NEW.@curr_column, NEW.modified_by, NEW.modified_at);

        END LOOP column_loop;
      CLOSE column_name_cursor;
    END$$
DELIMITER ;

我遇到的问题在这里:

INSERT INTO activity_log(`cid`, `table`, `field`, `value`, `modified_by`, `modified_at`) 
            VALUES (NEW.cid, 'customers', curr_column, NEW.@curr_column, NEW.modified_by, NEW.modified_at);

由于我按名称动态循环每个字段，因此我不知道如何获取 NEW.@curr_column 值。如何使用变量的值访问 NEW/OLD 对象的属性？

澄清语法错误是:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@curr_column, NEW.modified_by, NEW.modified_at); END LOOP column_loop' at line 17

谢谢!

Answer 1

不可能在触发器中动态处理 NEW 和 OLD 值。

<小时/>

我们可以使用 CASE 表达式。但这可能不是您想要的。它并不是真正的“动态”。我们需要静态地处理我们感兴趣的每个列名称。

  CASE curr_column 
  WHEN 'cid'       THEN NEW.cid
  WHEN 'foo'       THEN NEW.foo
  WHEN 'othercol'  THEN NEW.othercol
  END  

<小时/>

另一个问题是您可能想要存储在 activity_log 中的列的各种数据类型表value列... DATE、INTEGER、DECIMAL、ENUM、VARCHAR...这些都需要转换为 value 的单一数据类型专栏。

一些可供考虑的替代方案:

• 让触发器保存整行的副本

• 不要使触发器“动态”，而是使触发器的创建更加动态...即使用 information_schema.columns 中的 SELECT 来帮助生成触发器定义中所需的内容