How to update master table with value 0 if there are no records in the detail table

I have 2 tables in Firebird 3 db - master table "Docs" for output documents and detail table "Recs" for sold goods.

Columns of Docs: doc_id, summa
Columns of Recs: id, doc_id, goods, price, qnt

When customer cancels sale this record remains in detail table, didn't remove it and after update trigger updates total summa of master table.

Trigger works well before last record remains in output table. But if customer cancels last sale and detail dataset becomes empty trigger didn't work correctly and shows summa of last record instead of 0.

How correct this trigger?

CREATE OR ALTER TRIGGER RECS_AU FOR Recs
 ACTIVE AFTER UPDATE POSITION 0
 AS
  DECLARE VARIABLE Price DECIMAL(12, 4);
  DECLARE VARIABLE Doc_id INTEGER;
  DECLARE VARIABLE OLD_Price DECIMAL(12, 4);
  BEGIN
   select 
    r.Doc_id,
    coalesce(sum(r.Price*r.Qnt),0)
   from Recs r, Docs d
   where r.Doc_id=new.Doc_id and r.Doc_id=d.Doc_id and r.status<>'deleted'
   group by r.Doc_id
   into :Doc_id, :Summa;
    
     update Docs set
      Docs.Summa=:Summa:
     where Doc_id=:Doc_id;

I know that problem is in writing correct SQL, not in trigger. After dataset becomes empty it returns empty record set. Why doesn't work coalesce in case of empty dataset and doesn't show summa as 0?

SELECT 
    doc_id,
    coalesce(SUM(cast((r.price*r.qnt) as decimal(18,4))),0) as summa
FROM 
    recs r
WHERE 
    doc_id= :doc_id AND status <> 'deleted'
GROUP BY
    doc_id

Solution

As Freddie Bell mentions in the comments, the problem is that you're using an implicit inner join. Meaning that when there are no rows in RECS for a row in DOCS, then there will be no row due to the presence of the GROUP BY.

You either need to use an explicit LEFT JOIN as suggested by Freddie, only select from RECS without joining DOCS (and leave out the GROUP BY), or even simpler, just perform an update with a correlated sub-select without first executing a select separately:

update DOCS d set d.Summa = (
  select coalesce(sum(r.Price*r.Qnt), 0)
  from RECS r
  where r.DOC_ID = d.DOC_ID and r.STATUS <> 'deleted'
)
where d.DOC_ID = :NEW.DOC_ID

This will work, even if there are no rows in RECS, because absent a GROUP BY, this will produce a row when there are no matching rows in RECS.