PostgreSQL创建条件触发更新双表功能时插入操作不生效问题求解

核心问题原因 #

• 你代码中所有RAISE EXCEPTION语句都会触发当前事务全量回滚：
  • 当你在INSERT INTO summary_table后执行RAISE EXCEPTION 'correct'，这条插入操作会被直接回滚
  • 触发器抛出异常后，原表detailed_table的插入/更新操作也会被强制取消，所以两张表都没有新数据
• 现有逻辑未实现你需求中「先清空两张表、再调用自定义函数重填充」的流程，仅写了单条插入summary_table的逻辑

修复方案 #

调整触发器函数逻辑 #

1. 仅在校验不通过的错误场景使用RAISE EXCEPTION，业务正常执行的场景不要抛异常
2. BEFORE行级触发器完成逻辑后必须返回NEW，才能让原表的插入/更新操作正常生效
3. 补充清空表、调用自定义函数的逻辑，以下是可直接运行的修改后代码：

CREATE OR REPLACE FUNCTION test_fxn() RETURNS TRIGGER AS $test_fxn$
DECLARE
    -- 此处替换为你实际业务中x的取值/取值逻辑
    x INT := 10;
BEGIN
    -- 校验不通过场景抛异常，回滚所有操作
    IF NEW.variable2 > x THEN
        RAISE EXCEPTION 'too long';
    END IF;

    -- 满足指定条件时执行同步逻辑
    IF NEW.variable2 < x THEN
        -- 按需求先清空两张表，若需要事务回滚能力可将TRUNCATE替换为DELETE FROM
        TRUNCATE TABLE detailed_table;
        TRUNCATE TABLE summary_table;
        -- 此处替换为你实际的自定义函数调用逻辑
        -- 示例：PERFORM 自定义函数名(入参1, 入参2...);
        -- 原业务的插入逻辑保留
        INSERT INTO summary_table (variable1, variable2) VALUES (NEW.variable1, NEW.variable2);
    END IF;

    -- 必须返回NEW，才能让detailed_table的当前插入/更新操作生效
    RETURN NEW;
END;
$test_fxn$ LANGUAGE plpgsql;

-- 触发器定义无需修改
CREATE TRIGGER test_fxn BEFORE INSERT OR UPDATE ON detailed_table
    FOR EACH ROW EXECUTE PROCEDURE test_fxn();

注意事项 #

• TRUNCATE属于DDL操作，会触发隐式事务提交，无法被回滚，如果需要事务原子性，可改用DELETE FROM 表名实现清空
• 如果你的业务逻辑不需要在插入/更新之前执行，可以将触发器调整为AFTER INSERT OR UPDATE触发，对应触发器函数返回值改为NULL即可

内容的提问来源于stack exchange，提问作者M. G