PostgreSQL中如何强制UPDATE语句设置指定列的值？

这问题我之前做权限审计系统时碰到过，核心痛点就是UPDATE时如果不显式修改列，NEW.id_user_update会继承OLD的值，导致原来的null检查触发器形同虚设。给你几个不用强制用函数的实用思路：

思路1：修改触发器逻辑，区分「未修改列」和「主动设为null」 #

你可以在触发器里判断当前操作是INSERT还是UPDATE，针对UPDATE做特殊处理：

• 如果用户显式修改了id_user_update但设为null：直接抛出异常
• 如果用户没修改该列：自动将其替换为当前操作的用户ID（不用沿用旧值）

以PostgreSQL为例，触发器函数可以这么写：

CREATE OR REPLACE FUNCTION enforce_id_user_update()
RETURNS TRIGGER AS $$
BEGIN
    CASE TG_OP
        WHEN 'INSERT' THEN
            IF NEW.id_user_update IS NULL THEN
                RAISE EXCEPTION '插入数据时必须指定id_user_update，不能为NULL';
            END IF;
        WHEN 'UPDATE' THEN
            -- 检查列是否被显式修改
            IF NEW.id_user_update IS DISTINCT FROM OLD.id_user_update THEN
                -- 显式修改但设为null，报错
                IF NEW.id_user_update IS NULL THEN
                    RAISE EXCEPTION '更新时不能将id_user_update设为NULL';
                END IF;
            ELSE
                -- 未修改列，自动填充当前用户ID（替换成你获取用户ID的逻辑）
                NEW.id_user_update := current_user::integer;
            END IF;
    END CASE;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

把这个函数绑定到表的BEFORE INSERT OR UPDATE触发器上，就能同时覆盖两种操作场景。

思路2：限制列级更新权限，自动维护列值 #

不用移除用户整张表的更新权限，只禁止他们直接修改id_user_update列，然后通过触发器自动填充该列的值：

1. 先收回用户修改该列的权限：

-- PostgreSQL语法，MySQL可替换为：REVOKE UPDATE (id_user_update) ON your_table FROM 'user'@'%';
REVOKE UPDATE (id_user_update) ON your_table FROM your_db_user;

2. 创建触发器自动维护列值：

CREATE OR REPLACE FUNCTION auto_set_id_user_update()
RETURNS TRIGGER AS $$
BEGIN
    -- 不管插入还是更新，都自动设置为当前操作的用户ID
    NEW.id_user_update := current_user::integer;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_auto_set_user
BEFORE INSERT OR UPDATE ON your_table
FOR EACH ROW EXECUTE FUNCTION auto_set_id_user_update();

这样用户只能修改其他列，id_user_update完全由触发器维护，既避免了null的问题，也不用强制用户调用函数。

思路3：利用数据库会话变量传递用户ID #

如果你的应用框架能在数据库会话中设置变量（比如PostgreSQL的SET app.current_user_id = '123'），可以在触发器里直接读取这个变量来填充列，同时禁止用户手动设置该列：

CREATE OR REPLACE FUNCTION set_user_from_session()
RETURNS TRIGGER AS $$
DECLARE
    current_user_id integer;
BEGIN
    SELECT current_setting('app.current_user_id')::integer INTO current_user_id;
    NEW.id_user_update := current_user_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

这种方式适合多租户或复杂权限场景，能确保列值始终和当前会话的用户一致，不会被篡改。

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