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

mapsonp_at_spam.com.au a écrit dans l'article <3431134c.0_at_porsche.ois.net.au>...
> 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"
> ______________________________________________________________
>
>

Hi,

I'm searching for something similar. I would like to code a trigger for this kind of action (not correct syntax):

  trigger on insert or delete or update
  if id < :OLD.id
  then commit
  else rollback

If you have any answer to your news or any ideas for my problem just write to me an email. TIA.

ALBERTY Pascal - pal_at_bsb.be*nospam*
(please remove *nospam* to reply to my email) Business Solutions Builders