Re: SQL multiples triggers for the same table

Mohamad SALEH <msaleh_at_orsys.fr> wrote in message news:3781C47E.DE465B0B_at_orsys.fr...
> fumi a écrit :
> >
> > That is, you have a "statement level trigger", and a "row level trigger",
> > They are not the same type.
> > The statement level trigger is always fired before the row level trigger.
>
> Not true. In fact that depends on the type of the trigger : A BEFORE statement trigger
> fires first but an AFTER statement trigger fires after the row trigger.

Yes. It's my typo.

> > > In this table, the PK can be also a FK for the same table.
> > > Logically, the trigger "for each row" does'nt accept SQL witch call the
> > > table "in mutation".
> >
> > Yes, in a row level trigger, you can't reference the other rows in the same table.
> > (But, Oracle's reference constraints can!)
>
> Not true too. A row trigger of type BEFORE INSERT and AFTER INSERT can reference its
> associated table according to the documentation. However, it is not true for AFTER
> INSERT! A bug which is not repaired till Oracle 8.0.4. I didn't check after.

Yes. You are right.
I consulted the contents in Oracle8 Server Application Developer's Guide, and had tried it in Oracle 8.0.4.

A row trigger of type BEFORE INSERT fired by a "INSERT INTO ...VALUES..." statement can reference the other rows in the same table. However, the AFTER INSERT one occurs
"ORA-04091: table is mutating, trigger/function may not see it" error.

Thanks for corrections. Received on Thu Jul 08 1999 - 13:44:08 CDT