如何确定字段/列是否受 UPDATE 语句影响

Question

大家好，感谢您的回答和评论。

我有一个包含多个字段的表，其中包括 version、last_modified 和 modified_by

我正在编写一个触发器来：

• 增加 version 每次更新后加 1，
• 将 last_modified 设置为当前时间戳，
• 将最新更改的用户 id 设置为 modified_by，并
• 防止程序员不会忽略/忘记通过引发信号（在这种情况下）在 UPDATE 语句中设置 modified_by = userid。

我怎样才能实现这个目标？

我尝试检查是否 isnull(NEW.modified_by)，但随后意识到 NEW.modified_by 与 OLD.modified_by 如果不是的话，则采用相同的值不受影响。另外，检查NEW.modified_by是否等于OLD.modified_by并不完全有效，因为它可能是用户修改了自己之前修改过的记录。

有没有办法确定哪些字段受到 UPDATE 语句的影响？或者如果某个特定字段 (modified_by) 受到影响？

Answer 1

我找不到任何可以让您检查传入信息以查看哪些字段受到影响的内容。虽然我知道您正试图在触发器级别阻止此问题，但明智的做法是要求所有表写入都通过存储过程。这样您就可以需要用户字段。

如果这不可能，我想你可能需要采取一些技巧。例如，您可能要求将 user_id 写入两个字段（为此目的创建一个空白的额外列）。然后，将虚拟列中的 user_id 与您要更新的 user_id 进行比较。一旦确定是否需要修改 user_id，请再次清空虚拟列。它并不漂亮，但它可以完成工作。

Answer 2

我有类似的问题。我有一个表，其中有一个名为 isDirty 的tinyint 列，并且希望在更新该行时将其设置为 1，并在该行被“清理”时将其清除为 0。

问题是，NEW 和 OLD 与值 0 和 1 的组合并没有给我足够的值来解决我的问题。因此，我制定了一个“规则”，当该列更新为值 100 时，它被设置为干净值“0”，其他任何值都将其设置为“1”。这样做的原因是该列只会有 2 个值之一：0 或 1，因此您可以使用 100（或您选择的任何值）作为“标志”，表明它是干净的。

我知道将其设置为非零值以返回到 0 听起来有点倒退，但这就是我选择的方向并且它有效。该触发器如下所示：

CREATE TRIGGER calls_update BEFORE UPDATE ON `calls`
    FOR EACH ROW
    BEGIN
        IF ( NEW.isDirty = 100 ) THEN
            SET NEW.isDirty = 0;
    ELSE
            SET NEW.isDirty = 1;
        END IF;
    END
$

Answer 3

据我所知，您唯一的选择是对照旧值检查每列的新值。

SET `ColumnAChanged` = NEW.ColumnA <=> OLD.ColumnA;
SET `ColumnBChanged` = NEW.ColumnB <=> OLD.ColumnB;