> hello,
>
> we have 2 tables
>
> invoice
> items
>
> in invoice we have a field Amount. we update this field by the help of
> a triger, everytime an items is add, delete or updated.

As it should be and hopefully it is saying:
Update invoice i set i.amount = i.amount + (new.totalLine - old.totalLine)
where i.id = new.invoice_id or something like that.

As others have said, initial design would be to calculate this as and when
required. Actual implementations then depend on usage, e.g.:
- performs fine, leave it as it is
- can accommodate hotspot (the invoice becomes a "hotspot" as updated lots),
use the update above
- have a invoice_amount table that holds deltas that can be summed,
collapsed into one total per invoice.

> the problem is that, if i create an invoice with 10 items, i will 10
> times update the field amount in the invoice ! this what i don't want,

Why not, is there a performance problem or are you anticipating one?

> i want to update the invoice amount only at the end when all the items
> are inserted.

OK, then I would have a state on the invoice, for when it is being built or
complete.
When it is complete, then run the sum. So now you have:
1 insert for the invoice
10 inserts for the items
1 update to complete the invoice.
Updates to invoice can only happen when invoice is being built.

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

No, other than the work arounds above I guess.

> for exemple a trigger "before commit" will be a perfect solution for me
> (with the help of temp table)

This would require the DB to track all changes to all rows and then fire
this trigger at commit. This is definitely a double bite at the cherry and
would not be implemented as the performance hit would be huge.

> or any other suggestion ?

See above.

> thanks you by advance

No worries, just my 2c - happy Christmas.

> stephane
>