Re: SQL multiples triggers for the same table

fumi a écrit :

> Erwin jasinski <hexalog_at_wanadoo.fr> wrote in message news:7lii4l$qmk$1_at_wanadoo.fr...
> > I tried to have 2 triggers before insert. Only the second "for each row".
>
> 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.

> > 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.

> > I am writting a trigger witch call Strored Procedures. But the SQL shall be
> > for all the rows.

Do you need to reference the touched row and to manipulate its values? Do you need really to fire the trigger for each row? If the reply to these two questions is no, then there is no need to use a row trigger. The problem doesn't exist for statement triggers.

--
Mohamad Received on Tue Jul 06 1999 - 03:55:26 CDT