PostgreSQL如何仅在插入成功时触发触发器函数？

你的问题出在触发器时机的选择：BEFORE INSERT触发器会在主键约束检查前执行，哪怕后续因为ON CONFLICT DO NOTHING跳过了插入操作，触发器已经完成了对t2的写入。

要实现「仅当t1插入成功时才同步到t2」，只需把触发器改成AFTER INSERT类型，具体操作如下：

1. 修改触发器函数 #

把原函数的返回类型改为VOID，并移除RETURN New;语句（AFTER触发器不需要返回值）：

CREATE OR REPLACE FUNCTION copy_t1_to_t2()
RETURNS VOID AS $$
BEGIN
    INSERT INTO t2 (item, attr_1)
    VALUES (NEW.item, NEW.attr_1);
END;
$$ LANGUAGE plpgsql;

2. 重建触发器 #

删除原有的BEFORE触发器，创建AFTER INSERT触发器：

DROP TRIGGER IF EXISTS t1_sync_to_t2_trigger ON t1;
CREATE TRIGGER t1_sync_to_t2_trigger
AFTER INSERT ON t1
FOR EACH ROW EXECUTE FUNCTION copy_t1_to_t2();

验证效果 #

再次执行你之前的测试操作：

-- 第一次插入，t1和t2都会新增记录
insert into t1 (item,attr_1,attr_2,attr_3) values ('a',1,2,3) on conflict(item)do nothing;
-- 第二次插入，因主键冲突被跳过，t2不会新增记录
insert into t1 (item,attr_1,attr_2,attr_3) values ('a',1,2,3) on conflict(item)do nothing;

此时查询t2只会有一条('a',1)的记录，符合需求。

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