由于 SQL 没有 FOR-EACH
语句,我们如何在不知道表列 [和表名] 的情况下验证AFTER UPDATE
类型TRIGGER
中从 OLD
对象到NEW
对象的每个值是否存在差异?
今天的例子:
CREATE TRIGGER `audit_events_ugly`
AFTER UPDATE ON `accounts`
FOR EACH ROW
BEGIN
DECLARE changes VARCHAR(8000);
IF OLD.user_name <> NEW.user_name THEN
SET changes = 'user_name from % to %';
END IF;
IF OLD.user_type <> NEW.user_type THEN
SET changes = CONCAT(changes, ', user_type from % to %');
END IF;
IF OLD.user_email <> NEW.user_email THEN
SET changes = CONCAT(changes, ', user_email from % to %');
END IF;
CALL reg_event(how_canI_get_tableName?, @user_id, changes);
-- and that can go on and on... differently for every table.
END;
我希望它可能是示例:
CREATE TRIGGER `audit_events_nice`
AFTER UPDATE ON `accounts`
FOR EACH ROW
BEGIN
DECLARE changes VARCHAR(8000);
DECLARE N INT DEFAULT 1;
FOREACH OLD, NEW as OldValue, NewValue
BEGIN
IF OldValue <> NewValue THEN
SET changes = CONCAT(changes, ', column N: % to %');
SET N = N + 1;
END IF;
CALL reg_event(how_canI_get_tableName?, @user_id, changes);
-- now I can paste this code in every table that is audited..
END;
有什么想法吗?而,福里奇,阵列...
我认为您不能直接在触发器级别的 for 循环中执行此操作。
但是,可以使用脚本生成触发器代码。每次向表添加/删除字段时都需要重新生成它(通常不经常)。