thanks this what i need
on transaction commit

:)

--- In firebird-support@yahoogroups.com, "unordained" <unordained_00@...> wrote:
>
> ---------- 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
>