Subject Re: [firebird-support] how to delayed update in trigger
Author unordained
---------- 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
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

create procedure retotal_invoices as
-- 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, sum(det.stuff) as det_tot from invoices_to_total as itt
left join details det on det.invoice_id = group by
as tot
on =
when matched update set = tot.det_tot;
-- in case you call this procedure directly too, better avoid repeating work on
delete from invoices_to_total;

create trigger retotal_on_commit on transaction commit as
execute procedure retotal_invoices;