Is it possible to fake a "before commit" statement level trigger?

On a recent project I found myself desperately longing for a table trigger which would have had the following declaration:

CREATE OR REPLACE TRIGGER trigger_name AS BEFORE COMMIT
ON TABLE table_name....

I know no such trigger exists (at least under version 7.3), but it would have proved very usefull for enforcing certain business rules that could not be enforced after a single INSERT, UPDATE or DELETE statement. What I would have liked to have been able to do is allow any combination of one or more of the three statements to be issued, and then validate the status of the data in the table after all statements have finished and just prior to the actual commit. As long as the user left the data in a consistent state when they issued a commit, then any "temporary" data inconsistencies could be safely ignored.

Most other business rules were implemented as combinations of declarative constraints and row and statement level triggers, and it is basically these "cross statement" validations that remained impossible to enforce at the server level.

Note that I am not talking here about a situation where there is a set of specific steps that must always occur together, and which could be packaged together in a single procedure. The situation is one where a user could be interactively adding, deleting or updating any number of related records at their own discretion.

In effect, what I would have liked to have used would have been a database trigger that was the equivalent of a Forms' POST-FORMS-COMMIT trigger (which fires after all inserts, updates and deletes have been posted, but before the actual database commit). And, in fact, that is how the business rules that required "cross statement" validation were handled in this particular application. However, this does not protect the database against updates from sources other than the intended Forms application.

Does anyone have any ideas on how such business constraints could be implemented at the database level using a combination of triggers and packages?

Peter Mapson
mapsonp_at_spam.com.au

To reply to my email, replace "spam" with "ois"