如何修复PostgreSQL中关联表原材料价格自动汇总至成品表的触发器问题？

问题背景 #

我正在做一个大学项目，目前大部分内容都已完成。学校没有详细教过触发器和函数，所以我只能自行摸索学习。

我的数据库表结构如下：

create table finalproduct(
    id_final_product int primary key,
    price float default 0,
    usage text,
    -- + columns and foreign keys unrelated to the question
);

create table rawmaterials(
    id_product integer primary key,
    material varchar(100),
    price float,
    id_final_product int not null references finalproduct(id_final_product)
        on update cascade
        on delete restrict,
    -- + columns and foreign keys unrelated to the question
);

表中已插入数据，运行正常，但我编写的自动汇总逻辑存在问题，代码如下：

create function autosum_product_price()
returns trigger
as $autosum_product_price$
begin
    update finalproduct set price = sum(rpm.price)
    from finalproduct fpr
    left join rawmaterials rmat on rmat.id_final_product = fpr.id_final_product;
end;
$autosum_product_price$ language plpgsql;

create trigger autosum_product
after update
on rawmaterials
for each row
execute function autosum_product_price();

需求是让成品的价格自动汇总所有关联该成品的原材料价格。例如：添加ID为111、222、333的原材料，每个价格为11，关联ID为444的成品，理论上成品444的价格应为33，但实际仍保持默认值0。

问题分析 #

当前代码存在几个核心问题：

• 更新逻辑错误：原update语句无过滤条件，且未对sum结果分组，不仅无法正确计算单成品的原材料总价，还会触发语法或执行错误。
• 触发时机不全：仅监听rawmaterials的update事件，新增（insert）或删除（delete）原材料时，成品价格不会同步更新。
• 未利用触发器内置变量：没有借助new/old变量定位当前操作对应的成品ID，导致做了无意义的全表扫描。

修复后的代码 #

1. 修正触发器函数 #

create or replace function autosum_product_price()
returns trigger
as $autosum_product_price$
begin
    -- 仅更新当前操作关联的成品价格
    update finalproduct fpr
    set price = (
        select coalesce(sum(rmat.price), 0)
        from rawmaterials rmat
        where rmat.id_final_product = fpr.id_final_product
    )
    where fpr.id_final_product = coalesce(new.id_final_product, old.id_final_product);
    
    return null; -- 行级after触发器无需返回有效行，返回null即可
end;
$autosum_product_price$ language plpgsql;

2. 创建覆盖全操作的触发器 #

-- 监听原材料的新增、修改、删除操作
create trigger autosum_product
after insert or update or delete
on rawmaterials
for each row
execute function autosum_product_price();

关键说明 #

• coalesce(sum(...), 0)：确保成品无关联原材料时，价格保持默认值0而非null。
• coalesce(new.id_final_product, old.id_final_product)：兼容insert（仅存在new）、delete（仅存在old）、update（两者都存在）三种场景，精准定位需要更新的成品。
• 扩展触发事件到insert or update or delete：保证任何原材料变动都会同步更新对应成品的价格。

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