Re: Statement-Level Trigger Puzzle

Tim Romano wrote:
>
> I need to be able to prevent users from making changes to invoice lineitems
> that would cause sum(lineitem.amount) to not equal the amount of the
> invoice in the invoice header record.
>
> Such changes could be in the form of inserts or deletions of individual
> lineitems, or of updates to a lineitem row where either the
> lineitem.invoiceid or the lineitem.amount were changed; changes to the
> amount in the invoice header would also have to be checked (though that is
> easy enough).
> Changes to lineitem.invoiceid would require that both old and new invoice
> "object" would have to be checked to see if it jibed.
>
> I have not used statement-level triggers before. The documentation says
> they execute once with each transaction, so I presume that...

You can design a trigger to fire before OR after a DELETE, UPDATE, or INSERT is performed on a table (and in the case of UPDATE, you can trigger on a change to a column or set of columns instead of firing when ANY column is updated). You can have the trigger executed once for each row modified or once for the whole triggering statement (a "statement trigger"). If you need to ensure that the sum of columns in a child table stay equal to a value in a parent table where the parent table stays static, off the top of my head, I don't see how you can do it in a trigger. You could make the parent value always equal the sum of the children values, but could not do the reverse unless you could guarantee that the modifications to all the children would happen within a single SQL statement (in which case you could use a statement trigger). But often, child values are changed one at a time and committed irregularly, so if I update one child value, suddenly the sum of the children do not equal the parent value and the trigger rejects the update - even if my intention was to reduce one child value by 10 and increase two others by 5 each. Unless I'm missing something (which I may be since it's quittin' time and I'm thinking about the upcoming 3-day weekend), this would best be implemented by the application which could make the check of all the children values at a point where the user has indicated they are done changing all the children.

• TRW -- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| | Tim Witort || He is not silent. He is not | | trwATmedicalertDOTorg || whispering. We are not quiet | | || we are not listening. | | Pin: TZ, only one :^( || - Out of the Grey | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||