Firebird 3中详情表无记录时如何将主表summa更新为0？

问题场景 #

在Firebird 3数据库中有两张业务表：

• 主表Docs：存储出库单据，包含字段doc_id（单据ID）、summa（单据总计金额）
• 详情表Recs：存储售出商品明细，包含字段id、doc_id（关联单据ID）、goods（商品）、price（单价）、qnt（数量）

当客户取消销售时，详情表记录保留不删除，通过更新后触发器同步更新主表summa的总计值。触发器在详情表还有有效记录（status <>'deleted'）时运行正常，但当取消最后一笔销售、详情表对应单据的有效记录为空时，主表summa仍显示最后一条记录的数值，无法归零。

问题根源 #

原触发器的查询逻辑存在本质缺陷：当Recs中没有符合status <>'deleted'的记录时，GROUP BY doc_id会直接返回空结果集，而非包含doc_id和0的记录。此时COALESCE(SUM(...), 0)根本不会执行——因为SUM函数在没有匹配行的情况下不会返回任何值，连NULL都没有，直接导致SELECT...INTO语句无法给变量赋值，后续的UPDATE操作也就不会修改主表的summa值。

修正方案 #

需要确保即使详情表没有有效记录，查询也能返回对应doc_id和0的结果。核心思路是以Docs为主表做左连接，保证基础的单据行始终存在，再聚合详情表的有效数据：

修正后的触发器代码 #

CREATE OR ALTER TRIGGER RECS_AU FOR Recs
ACTIVE AFTER UPDATE POSITION 0
AS
DECLARE VARIABLE v_Doc_id INTEGER;
DECLARE VARIABLE v_Summa DECIMAL(12, 4);
BEGIN
    -- 获取当前更新记录对应的单据ID
    v_Doc_id = NEW.Doc_id;
    
    -- 左连接确保无有效记录时仍返回单据行和0值
    SELECT 
        d.doc_id,
        COALESCE(SUM(r.Price * r.Qnt), 0)
    FROM Docs d
    LEFT JOIN Recs r ON r.Doc_id = d.Doc_id AND r.status <> 'deleted'
    WHERE d.doc_id = v_Doc_id
    GROUP BY d.doc_id
    INTO :v_Doc_id, :v_Summa;
    
    -- 更新主表总计金额
    UPDATE Docs 
    SET Summa = :v_Summa 
    WHERE Doc_id = :v_Doc_id;
END

核心改进点 #

1. 以主表为基础做左连接：保证即使Recs中没有对应有效记录，也能返回Docs中的单据行
2. 过滤条件移至连接子句：将r.status <>'deleted'放在LEFT JOIN的条件中，避免过滤掉Docs中的基础行
3. 清理冗余变量：原代码中声明的Price、OLD_Price未使用，已移除并规范变量命名

测试验证 #

可以用以下查询单独验证逻辑，即使无有效记录也会返回summa=0：

SELECT 
    d.doc_id,
    COALESCE(SUM(r.price * r.qnt), 0) AS summa
FROM Docs d
LEFT JOIN Recs r ON r.doc_id = d.doc_id AND r.status <> 'deleted'
WHERE d.doc_id = :doc_id
GROUP BY d.doc_id

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