> 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.

unfortunatly, it's cost also 10 update in the table invoice (nothing really change)

> 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.

yes, i use invoice and items as a sample. but i have no other choice than denormalize the data to have good performance. now i just want to not to 10 times the same things :(

> > 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.

yes, but this will be dependant of the code (ie not the database) ... many software are connected to the database, many SQL, many thing to check... poorly solution that will in any case one day cause some trouble ...

> > 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.

no my idea would be for exemple :
trigger before update
begin
insert if not exist in TMP_table (IDinvoice) values ...
END

Trigger before commit
begin
For every row in IDinvoice do updatesum
end


really easy no? at the end one solution could be to have a guardian software that will every minutes check the table tmp_table and do the update... but if i could not have this soft only better

> No worries, just my 2c - happy Christmas.

happy christmas too

stephane