如何在PostgreSQL触发器中校验UPDATE语句是否传入了指定字段值

你可以通过以下几种方式实现UPDATE操作必须显式传入updated_by字段的校验：

方案1：比对新旧记录差异（无需额外扩展，适配绝大多数场景） #

该方案通过JSONB比对新旧记录的变化字段，判断updated_by是否在本次更新中被修改，适用于要求每次更新必须修改updated_by值的业务场景：

CREATE OR REPLACE FUNCTION UPDATED_BY_TRIGGER()
RETURNS TRIGGER AS
$BODY$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        -- 提取变化的字段，判断是否包含updated_by
        IF NOT (to_jsonb(NEW) - to_jsonb(OLD) ? 'updated_by') THEN
            RAISE EXCEPTION 'updated_by is missing in UPDATE to %', TG_TABLE_NAME;
        END IF;
    END IF;
    RETURN NEW;
END;
$BODY$ LANGUAGE PLPGSQL;

注意：如果业务允许显式将updated_by设置为和旧值完全相同的内容，该方案会误判为未传入字段。

方案2：SQL语句正则匹配（支持显式传入相同值的场景） #

PostgreSQL没有原生方法可以100%准确判断字段是否出现在UPDATE的SET子句中，如果必须支持显式传入相同值也能通过校验，可以使用正则匹配当前执行的SQL，存在边缘场景误判风险：

CREATE OR REPLACE FUNCTION UPDATED_BY_TRIGGER()
RETURNS TRIGGER AS
$BODY$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        -- 匹配SET子句中是否包含updated_by赋值
        IF current_query() !~* '\mupdated_by\s*=' THEN
            RAISE EXCEPTION 'updated_by is missing in UPDATE to %', TG_TABLE_NAME;
        END IF;
    END IF;
    RETURN NEW;
END;
$BODY$ LANGUAGE PLPGSQL;

缺陷说明：无法识别注释中出现的updated_by、字段加双引号、updated_by出现在WHERE子句等特殊场景。

方案3：触发器自动赋值（最安全，推荐优先使用） #

如果业务允许数据库层自动维护updated_by字段，不需要业务端显式传入，直接在触发器中自动赋值即可从根源避免漏传问题：

CREATE OR REPLACE FUNCTION UPDATED_BY_TRIGGER()
RETURNS TRIGGER AS
$BODY$
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        -- 可根据业务替换为实际取当前操作用户的逻辑，比如current_setting('app.current_user_id')
        NEW.updated_by := current_user;
        -- 通常可搭配自动维护updated_at字段：NEW.updated_at := now();
    END IF;
    RETURN NEW;
END;
$BODY$ LANGUAGE PLPGSQL;

根据选择的方案创建对应触发器，替换为实际的表名即可：

-- 方案1、2使用UPDATE触发器
CREATE TRIGGER trg_check_updated_by
BEFORE UPDATE ON 你的表名
FOR EACH ROW
EXECUTE FUNCTION UPDATED_BY_TRIGGER();

-- 方案3支持插入和更新操作，使用INSERT OR UPDATE触发器
CREATE TRIGGER trg_set_updated_by
BEFORE INSERT OR UPDATE ON 你的表名
FOR EACH ROW
EXECUTE FUNCTION UPDATED_BY_TRIGGER();

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