PostgreSQL更新元组后自动更新Update Date字段的函数与触发器实现问题

原有实现的核心问题 #

• 误用AFTER UPDATE触发器：该类型触发器在数据写入表后才执行，此时已无法修改待写入的记录值，额外新增UPDATE语句还会触发触发器递归调用，形成死循环
• 触发器内UPDATE语句未加WHERE条件，执行后会直接更新全表的时间字段，完全不符合预期
• 触发器函数返回值错误：行级触发器需要返回TRIGGER类型的记录对象，而非直接返回时间戳
• 重复判断逻辑冗余，无需为每个字段单独写条件分支

正确实现方案 #

首先创建触发器函数：

CREATE OR REPLACE FUNCTION update_x_modify_time()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    -- 仅当3个业务字段任意一个发生变更时，更新更新时间
    -- IS DISTINCT FROM可兼容字段为NULL的判断场景，比<>更严谨
    IF (NEW.attribute1, NEW.attribute2, NEW.attribute3) IS DISTINCT FROM (OLD.attribute1, OLD.attribute2, OLD.attribute3) THEN
        -- 字段名带空格需要用双引号包裹以符合PostgreSQL标识符规则
        NEW."Update Date" = CURRENT_TIMESTAMP;
    END IF;
    -- 返回修改后的待写入记录
    RETURN NEW;
END;
$$;

然后绑定触发器，注意要使用BEFORE UPDATE类型：

CREATE TRIGGER trigger_x_update_modify_time
BEFORE UPDATE ON X
FOR EACH ROW EXECUTE FUNCTION update_x_modify_time();

逻辑说明 #

使用BEFORE UPDATE触发器，在每行更新写入数据库前触发，直接修改待写入的NEW记录的时间字段值，无需额外执行UPDATE语句，既避免了递归触发，也保证了修改后的时间会和其他字段一起写入表中。

内容的提问来源于stack exchange，提问作者fortnite