|Subject||Re:how to delayed update in trigger|
> hello,As it should be and hopefully it is saying:
> we have 2 tables
> 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.
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 10Why not, is there a performance problem or are you anticipating one?
> times update the field amount in the invoice ! this what i don't want,
> i want to update the invoice amount only at the end when all the itemsOK, then I would have a state on the invoice, for when it is being built or
> are inserted.
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 updateNo, other than the work arounds above I guess.
> it for every insert ?
> for exemple a trigger "before commit" will be a perfect solution for meThis would require the DB to track all changes to all rows and then fire
> (with the help of temp table)
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 advanceNo worries, just my 2c - happy Christmas.