使用触发器同步父表到子表时遇唯一键冲突问题求助

有一个父表关联多个子表，每行均有唯一well_no（主键），所有表的elev值需保持一致。期望父表插入/更新时将elev值同步到子表。

已创建如下触发器函数：

CREATE OR REPLACE FUNCTION public.fn_elev_insert()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
    declare
    well_no_var varchar (50);
    elev_var numeric(6,2);
    begin
        select well_no from well_parent into well_no_var;
        select elev from well_parent into elev_var;
        insert into well_construct (well_no, elev)
        values (well_no_var, elev_var);
        
    end;
$BODY$;

以及调用该函数的触发器：

CREATE OR REPLACE TRIGGER tr_elev_insert
    AFTER INSERT
    ON public.well_parent
    FOR EACH ROW
    EXECUTE FUNCTION public.fn_elev_insert();

但插入父表时出现错误：

ERROR: Key (well_no)=(bh002) already exists.duplicate key value violates unique constraint "well_construct_pkey"
ERROR: duplicate key value violates unique constraint "well_construct_pkey"
SQL state: 23505
Detail: Key (well_no)=(bh002) already exists.
Context: SQL statement "insert into well_construct (well_no, elev)
values (well_no_var, elev_var)"
PL/pgSQL function fn_elev_insert() line 8 at SQL statement
SQL statement "insert into well_construct (well_no, elev)
values (well_no_var, elev_var)"
PL/pgSQL function fn_elev_insert() line 8 at SQL statement:

查询发现两张表均为空，无明显重复键冲突。尝试BEFORE触发器会得到空值行，使用子查询插入也无效，请问该如何解决？

你的触发器函数存在核心错误：

• 函数中select well_no from well_parent into well_no_var会取出父表中所有行的well_no，当表中有多行时会导致变量赋值失败；即使表中只有一行，也会重复插入该行数据引发唯一键冲突（可能存在未彻底清理的事务残留或重复触发触发器的情况）。
• 触发器未使用PostgreSQL内置的NEW变量访问当前操作的行数据，反而用全表查询，这是触发器逻辑的典型错误。
• 只处理了插入场景，未覆盖父表更新时的同步需求；插入子表时未处理“已存在则更新”的逻辑，必然触发唯一键约束。

创建同时支持插入和更新的触发器函数，用NEW变量获取当前操作行数据，结合INSERT ... ON CONFLICT语法实现无冲突同步：

CREATE OR REPLACE FUNCTION public.sync_elev_to_child_tables()
    RETURNS trigger
    LANGUAGE plpgsql
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
    -- 同步到well_construct子表：不存在则插入，存在则更新elev
    INSERT INTO well_construct (well_no, elev)
    VALUES (NEW.well_no, NEW.elev)
    ON CONFLICT (well_no) DO UPDATE
    SET elev = NEW.elev;

    -- 如有其他子表，复制上述块替换表名即可
    -- INSERT INTO other_child_table (well_no, elev)
    -- VALUES (NEW.well_no, NEW.elev)
    -- ON CONFLICT (well_no) DO UPDATE
    -- SET elev = NEW.elev;

    RETURN NEW;
END;
$BODY$;

创建触发器，同时监听父表的INSERT和UPDATE操作：

CREATE OR REPLACE TRIGGER tr_sync_elev
    AFTER INSERT OR UPDATE OF elev
    ON public.well_parent
    FOR EACH ROW
    EXECUTE FUNCTION public.sync_elev_to_child_tables();

关键说明 #

1. NEW变量的使用：直接从NEW中获取当前插入/更新行的well_no和elev，避免全表查询的错误。
2. ON CONFLICT语法：自动处理子表中已存在的well_no，实现“插入或更新”的逻辑，彻底规避唯一键冲突。
3. 精准触发条件：仅当父表的elev字段发生变化时触发触发器，减少不必要的执行开销。

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