---------- Original Message -----------
From: "svanderclock" <svanderclock@...>

> so is it possible to "delayed the update of the amount" to not update it for

every insert ?

>
> for exemple a trigger "before commit" will be a perfect solution for me (with

the help of temp table)
------- End of Original Message -------

Conveniently, that option is available to you. I use it, works like a champ.
Sample (not syntax-checked) code follows. Note: for very few rows, this is
likely more expensive than just going ahead and running that update for every row.

create global temporary table invoices_to_total (id integer not null);

create trigger detail_changed for details after insert or update or delete as
begin
insert or update into invoices_to_total (id) values (new.invoice_id) matching (id);
-- in case you move a detail item from one invoice to another, fix both
if (new.invoice_id is distinct from old.invoice_id) then
insert or update into invoices_to_total (id) values (old.invoice_id) matching
(id);
end

create procedure retotal_invoices as
begin
-- merge-into is sometimes more efficient (in my experience), but you should
compare to update-with-subselect method, just in case.
merge into invoices
using (select itt.id, sum(det.stuff) as det_tot from invoices_to_total as itt
left join details det on det.invoice_id = invoices_to_total.id group by itt.id)
as tot
on tot.id = invoices.id
when matched update set invoices.total = tot.det_tot;
-- in case you call this procedure directly too, better avoid repeating work on
commit
delete from invoices_to_total;
end

create trigger retotal_on_commit on transaction commit as
begin
execute procedure retotal_invoices;
end