修复PostgreSQL触发器：实现单行多字段更新全审计

问题定位 #

原触发器tr_mbankactaud在单行多字段更新时仅生成首个变更字段的审计记录，原因是原逻辑未遍历所有字段做变更检查——要么只针对单个字段判断，要么用了排他性分支逻辑（比如ELSE IF）导致后续字段变更被忽略。

修复思路 #

针对mbank表的每个字段，逐一对比OLD（更新前）和NEW（更新后）记录的字段值，只要值发生变更（包括NULL与非NULL的切换），就向MAUDACTIVITYEVENTS表插入一条独立的审计记录。

具体实现 #

方案1：显式指定需审计字段（适合固定字段场景） #

如果仅需审计特定字段，直接逐个检查并插入审计记录：

CREATE OR REPLACE FUNCTION fn_mbankactaud()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'UPDATE' THEN
    -- 审计bank_abbreviation字段变更
    IF OLD.bank_abbreviation IS DISTINCT FROM NEW.bank_abbreviation THEN
      INSERT INTO MAUDACTIVITYEVENTS (table_name, column_name, old_value, new_value, record_id, operation)
      VALUES ('mbank', 'bank_abbreviation', OLD.bank_abbreviation::TEXT, NEW.bank_abbreviation::TEXT, OLD.bank_code::TEXT, 'UPDATE');
    END IF;
    -- 审计status字段变更
    IF OLD.status IS DISTINCT FROM NEW.status THEN
      INSERT INTO MAUDACTIVITYEVENTS (table_name, column_name, old_value, new_value, record_id, operation)
      VALUES ('mbank', 'status', OLD.status::TEXT, NEW.status::TEXT, OLD.bank_code::TEXT, 'UPDATE');
    END IF;
    -- 按需添加其他字段的审计逻辑
  ELSIF TG_OP = 'INSERT' THEN
    -- 插入操作审计示例（按需调整）
    INSERT INTO MAUDACTIVITYEVENTS (table_name, column_name, new_value, record_id, operation)
    VALUES ('mbank', 'bank_code', NEW.bank_code::TEXT, NEW.bank_code::TEXT, 'INSERT');
  ELSIF TG_OP = 'DELETE' THEN
    -- 删除操作审计示例（按需调整）
    INSERT INTO MAUDACTIVITYEVENTS (table_name, column_name, old_value, record_id, operation)
    VALUES ('mbank', 'bank_code', OLD.bank_code::TEXT, OLD.bank_code::TEXT, 'DELETE');
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

方案2：动态遍历所有字段（通用灵活场景） #

如果需要审计表中所有字段，通过查询information_schema动态获取字段列表，循环检查每个字段的变更：

CREATE OR REPLACE FUNCTION fn_mbankactaud()
RETURNS TRIGGER AS $$
DECLARE
  rec_field information_schema.columns%ROWTYPE;
  val_old TEXT;
  val_new TEXT;
BEGIN
  IF TG_OP = 'UPDATE' THEN
    -- 遍历当前表的所有字段
    FOR rec_field IN 
      SELECT column_name 
      FROM information_schema.columns 
      WHERE table_schema = TG_TABLE_SCHEMA 
        AND table_name = TG_TABLE_NAME
    LOOP
      -- 获取旧值与新值
      EXECUTE format('SELECT $1.%I::TEXT', rec_field.column_name) INTO val_old USING OLD;
      EXECUTE format('SELECT $1.%I::TEXT', rec_field.column_name) INTO val_new USING NEW;
      
      -- 检测字段值变更（含NULL值场景）
      IF val_old IS DISTINCT FROM val_new THEN
        INSERT INTO MAUDACTIVITYEVENTS (table_name, column_name, old_value, new_value, record_id, operation)
        VALUES (TG_TABLE_NAME, rec_field.column_name, val_old, val_new, OLD.bank_code::TEXT, 'UPDATE');
      END IF;
    END LOOP;
  ELSIF TG_OP = 'INSERT' THEN
    FOR rec_field IN 
      SELECT column_name 
      FROM information_schema.columns 
      WHERE table_schema = TG_TABLE_SCHEMA 
        AND table_name = TG_TABLE_NAME
    LOOP
      EXECUTE format('SELECT $1.%I::TEXT', rec_field.column_name) INTO val_new USING NEW;
      INSERT INTO MAUDACTIVITYEVENTS (table_name, column_name, new_value, record_id, operation)
      VALUES (TG_TABLE_NAME, rec_field.column_name, val_new, NEW.bank_code::TEXT, 'INSERT');
    END LOOP;
  ELSIF TG_OP = 'DELETE' THEN
    FOR rec_field IN 
      SELECT column_name 
      FROM information_schema.columns 
      WHERE table_schema = TG_TABLE_SCHEMA 
        AND table_name = TG_TABLE_NAME
    LOOP
      EXECUTE format('SELECT $1.%I::TEXT', rec_field.column_name) INTO val_old USING OLD;
      INSERT INTO MAUDACTIVITYEVENTS (table_name, column_name, old_value, record_id, operation)
      VALUES (TG_TABLE_NAME, rec_field.column_name, val_old, OLD.bank_code::TEXT, 'DELETE');
    END LOOP;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

重新绑定触发器 #

替换原有触发器，确保新函数生效：

DROP TRIGGER IF EXISTS tr_mbankactaud ON mbank;
CREATE TRIGGER tr_mbankactaud
AFTER INSERT OR UPDATE OR DELETE ON mbank
FOR EACH ROW EXECUTE FUNCTION fn_mbankactaud();

核心注意点 #

• 使用IS DISTINCT FROM替代<>：该运算符能正确处理NULL值对比（比如旧值为NULL、新值非NULL的场景，<>会返回NULL，无法触发审计）。
• FOR EACH ROW触发器：确保每行数据的每个字段变更都被单独检查和记录。

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