PostgreSQL中如何动态传参创建触发器与触发器函数

错误原因 #

1. PostgreSQL 的触发器函数不支持直接声明自定义入参，触发器调用时传入的参数需要通过系统内置变量 TG_ARGV 读取，数组下标从 0 开始
2. 你的示例中触发器绑定在 test 表，触发逻辑又往 test 表插入数据，会造成无限循环触发，直接导致数据库写入异常。通常审计表和业务表是两张独立的表，避免循环问题
3. 同一张表的触发器名称不能重复，你定义的两个触发器都叫 trigger_test 会触发命名冲突
4. DELETE 操作没有 NEW 变量，返回值需要适配操作类型返回 OLD

正确实现方案（独立审计表） #

1. 先创建审计表

-- 审计表存储操作记录，和业务表分离
CREATE TABLE audit_log (
    a boolean,
    b boolean,
    c boolean,
    operate_time timestamp DEFAULT now() -- 可选，补充操作时间便于追溯
);

2. 创建触发器函数

CREATE OR REPLACE FUNCTION function_test()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
BEGIN
    -- 从TG_ARGV读取触发器传入的参数，转布尔类型后写入审计表
    INSERT INTO audit_log(a, b, c)
    VALUES (TG_ARGV[0]::boolean, TG_ARGV[1]::boolean, TG_ARGV[2]::boolean);
    
    -- 适配不同操作的返回值要求
    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$;

3. 创建对应操作的触发器

将下面语句中的你的业务表名替换为实际需要监听操作的业务表名称：

-- 监听INSERT操作，传入参数true,false,false对应a=true,b=false,c=false
CREATE TRIGGER trigger_test_insert
AFTER INSERT ON 你的业务表名
FOR EACH ROW
EXECUTE FUNCTION function_test(true, false, false);

-- 监听UPDATE操作
CREATE TRIGGER trigger_test_update
AFTER UPDATE ON 你的业务表名
FOR EACH ROW
EXECUTE FUNCTION function_test(false, true, false);

-- 监听DELETE操作
CREATE TRIGGER trigger_test_delete
AFTER DELETE ON 你的业务表名
FOR EACH ROW
EXECUTE FUNCTION function_test(false, false, true);

可选方案：同表修改字段（软删除/操作标记场景） #

如果你的需求是直接修改当前操作行的a/b/c字段做标记，不需要独立审计表，可以用BEFORE触发器直接修改NEW变量，不会触发循环：

CREATE OR REPLACE FUNCTION function_test()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        NEW.b = true;
    ELSIF TG_OP = 'UPDATE' THEN
        NEW.a = true;
    -- DELETE场景没有NEW，要打删除标记建议用软删除，即执行UPDATE set c=true替代DELETE操作
    END IF;
    RETURN NEW;
END;
$$;

-- 触发器用BEFORE类型，在操作执行前修改字段值
CREATE TRIGGER trigger_test_insert
BEFORE INSERT ON 你的表名
FOR EACH ROW
EXECUTE FUNCTION function_test();

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

内容的提问来源于stack exchange，提问作者e-info128