PostgreSQL变更追踪触发器如何写入历史表外键mytable_id值

核心逻辑：绑定在业务表上的行级触发器，可以直接通过PL/pgSQL内置的NEW/OLD行变量访问操作行的所有字段（包括主键），无需额外关联查询，直接赋值给历史表的外键字段即可。

先修正现有代码的前置问题 #

你当前提供的表结构和触发器存在3个会直接运行报错的问题，需要先修复：

1. 历史表主键history_id设置了非空约束，但没有配置自增规则，插入时不手动赋值会触发非空报错
2. 历史表建表时定义的业务表名字段为table_name，但触发器函数中使用了tabname、schemaname两个未定义的字段，插入时会报字段不存在错误
3. 使用BEFORE时机的触发器存在数据不一致风险：如果存在其他前置BEFORE行触发器修改了行数据，你记录的new_val不是最终落库的值，极端情况下还可能拿到未完成赋值的主键值

先执行以下SQL修复历史表结构：

-- 给history_id配置自增主键（PostgreSQL 10+推荐用IDENTITY，低版本替换为serial类型即可）
ALTER TABLE sales.history ALTER COLUMN history_id ADD GENERATED ALWAYS AS IDENTITY;
-- 补充缺失的schema名字段
ALTER TABLE sales.history ADD COLUMN IF NOT EXISTS schemaname varchar(30);
-- 统一表名字段名和触发器逻辑对齐
ALTER TABLE sales.history RENAME COLUMN table_name TO tabname;

再重建触发器，改为AFTER行级触发，保证拿到的是最终落库的数据：

DROP TRIGGER IF EXISTS t_history ON sales.sales;
CREATE TRIGGER t_history AFTER INSERT OR UPDATE OR DELETE ON sales.sales
FOR EACH ROW EXECUTE PROCEDURE core.func_store_history_changes();

修改触发器函数补充主键赋值 #

不同操作类型下主键取值来源不同：

• INSERT操作：新写入的数据存在NEW变量中，主键从NEW.mytable_id取
• UPDATE操作：更新后的数据存在NEW变量中，主键从NEW.mytable_id取（即使业务上允许修改主键，该值也是最新的主键值，配合外键级联更新不会冲突）
• DELETE操作：被删除的数据仅存在OLD变量中（NEW为NULL），主键从OLD.mytable_id取

修正后的完整触发器函数代码如下：

CREATE OR REPLACE FUNCTION core.func_store_history_changes() RETURNS trigger AS $$ 
BEGIN 
    IF TG_OP = 'INSERT' THEN
        INSERT INTO sales.history (mytable_id, tabname, schemaname, operation, new_val)
            VALUES (NEW.mytable_id, TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN 
        INSERT INTO sales.history (mytable_id, tabname, schemaname, operation, new_val, old_val)
            VALUES (NEW.mytable_id, TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW), row_to_json(OLD));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO sales.history (mytable_id, tabname, schemaname, operation, old_val)
            VALUES (OLD.mytable_id, TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD));
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

额外优化建议 #

• 如果业务上允许修改sales.sales表的mytable_id主键，建议给历史表的外键约束加上ON UPDATE CASCADE规则，避免更新主键时触发外键校验报错
• 如果后续需要复用该触发器函数追踪多张业务表的历史，不要把外键字段写死为mytable_id，可以通过动态读取主键字段、通用字段存储的方式改造，当前仅追踪sales表的场景下上述写法性能最好、逻辑最简单

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