Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Statement-Level Trigger Puzzle
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 IF INSERTING, IF DELETING, IF UPDATING and ON INSERT OR UPDATE OR DELETE would not be available to me, as a transaction could be wrapping a combination of those events. (That is, a user might have deleted a lineitem, updated a lineitem, and added a lineitem, all within the confines of a single transaction.) Or is event-level granularity available within a statement-level trigger?
How does one solve this data-integrity problem in Oracle (rather than in the front-end)?
TIA for any suggestions or pointers to articles or books that treat this
topic.
Tim Romano
Swarthmore PA
@tim_at_ot.com@
(ignore the bookends)
Received on Wed Jul 02 1997 - 00:00:00 CDT
![]() |
![]() |