PostgreSQL如何通过触发器禁止手动在INSERT/UPDATE中设置审计列

最优实现方案（原生触发器特性，无需JSON转换） #

PostgreSQL 原生提供了UPDATE OF触发器触发条件，可以精准检测指定字段是否出现在UPDATE语句的SET子句中，无论字段值是否发生变化，只要出现在SET列表就会触发对应触发器，完全匹配需求。

步骤1：创建审计字段校验触发器函数 #

CREATE OR REPLACE FUNCTION check_audit_columns()
RETURNS TRIGGER AS $$
BEGIN
  -- 处理INSERT场景
  IF TG_OP = 'INSERT' THEN
    -- 检测是否手动传入了审计字段非空值
    IF NEW.created_by IS NOT NULL THEN
      RAISE EXCEPTION '不允许在INSERT语句中手动设置created_by字段';
    END IF;
    IF NEW.created_timestamp IS NOT NULL THEN
      RAISE EXCEPTION '不允许在INSERT语句中手动设置created_timestamp字段';
    END IF;
    IF NEW.modified_by IS NOT NULL THEN
      RAISE EXCEPTION '不允许在INSERT语句中手动设置modified_by字段';
    END IF;
    IF NEW.modified_timestamp IS NOT NULL THEN
      RAISE EXCEPTION '不允许在INSERT语句中手动设置modified_timestamp字段';
    END IF;
    -- 自动赋值审计字段
    NEW.created_by := current_user;
    NEW.created_timestamp := CURRENT_TIMESTAMP;
    NEW.modified_by := current_user;
    NEW.modified_timestamp := CURRENT_TIMESTAMP;
  
  -- 处理UPDATE场景：只要触发本触发器就说明UPDATE的SET中包含审计字段，直接抛错
  ELSIF TG_OP = 'UPDATE' THEN
    RAISE EXCEPTION '不允许在UPDATE语句中手动修改created_by、created_timestamp、modified_by、modified_timestamp字段';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

步骤2：创建INSERT场景校验触发器 #

CREATE TRIGGER trg_audit_insert_check
BEFORE INSERT ON 你的表名
FOR EACH ROW
EXECUTE FUNCTION check_audit_columns();

步骤3：创建UPDATE场景校验触发器 #

关键点是通过UPDATE OF指定要监控的审计字段，只要这些字段出现在UPDATE的SET子句中就会触发校验：

CREATE TRIGGER trg_audit_update_check
BEFORE UPDATE OF created_by, created_timestamp, modified_by, modified_timestamp ON 你的表名
FOR EACH ROW
EXECUTE FUNCTION check_audit_columns();

步骤4：可选：新增自动更新修改信息的触发器 #

如果需要每次UPDATE时自动刷新modified相关字段，额外创建以下触发器即可：

CREATE OR REPLACE FUNCTION auto_update_modified_columns()
RETURNS TRIGGER AS $$
BEGIN
  NEW.modified_by := current_user;
  NEW.modified_timestamp := CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER trg_auto_update_modified
BEFORE UPDATE ON 你的表名
FOR EACH ROW
EXECUTE FUNCTION auto_update_modified_columns();

方案优势 #

• 基于PostgreSQL原生触发器特性实现，性能损耗极低，兼容性强（支持PostgreSQL 9.1及以上所有版本）
• 精准命中需求：INSERT时只要手动传入审计字段非空值就抛出异常，UPDATE时只要审计字段出现在SET列表就抛出异常，无需对比NEW/OLD值，也无需做JSON转换
• 逻辑清晰易维护，新增审计字段只需同步修改UPDATE触发器的UPDATE OF列表和INSERT校验逻辑即可

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