PostgreSQL触发器开发求助：关联表列值求和更新

需求 #

将BuiltResourceLines表中Total列（原文拼写为Toatal，请核对实际列名）按BuiltResourceId分组求和，更新到BuiltResources表对应Id的Total字段，通过触发器在BuiltResourceLines更新后自动执行。

失败的尝试及问题 #

1. 直接关联表使用聚合函数报错（无法在SET中直接使用聚合函数关联外部表）：

UPDATE dbo."BuiltResources" BR
SET "Total" = SUM(BRL."Total")
FROM dbo."BuiltResourceLines" BRL
WHERE BR."Id" = BRL."BuiltResourceId";

2. 子查询写法存在引用错误且风险高（无法获取BRL引用，无WHERE条件会更新全表）：

UPDATE dbo."BuiltResources" BR
SET "Total" = 
    (
        SELECT SUM(BRL."Total")
        FROM dbo."BuiltResourceLines" BRL 
        WHERE BRL."BuiltResourceId" = BR."Id"
    ) 
WHERE BR."Id" = "HOW TO GET REFERENCE TO BRL HERE????;

正确的触发器实现方案 #

利用PostgreSQL触发器的NEW变量（代表触发更新后的行），仅针对当前更新行对应的BuiltResourceId进行求和更新，避免全表操作：

CREATE OR REPLACE FUNCTION update_builtresource_total()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        -- 仅更新当前修改行对应的BuiltResources记录
        UPDATE dbo."BuiltResources"
        SET "Total" = (
            SELECT SUM("Total") -- 若实际列名为Toatal则替换为"Toatal"
            FROM dbo."BuiltResourceLines"
            WHERE "BuiltResourceId" = NEW."BuiltResourceId"
        )
        WHERE "Id" = NEW."BuiltResourceId";
    END IF;
    RETURN NULL;
END
$$;

DROP TRIGGER IF EXISTS update_builtresource_total_trigger on dbo."BuiltResourceLines";

CREATE TRIGGER update_builtresource_total_trigger
    AFTER UPDATE
    ON dbo."BuiltResourceLines"
    FOR EACH ROW
EXECUTE PROCEDURE update_builtresource_total();

说明 #

• NEW."BuiltResourceId"获取当前更新的BuiltResourceLines行对应的关联ID，确保只更新目标BuiltResources记录，避免全表更新风险；
• 子查询内部针对该ID筛选后求和（等价于分组求和），返回单个值赋值给Total字段；
• 若BuiltResourceLines的列名实际为Toatal，需将子查询中的SUM("Total")替换为SUM("Toatal")。

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