Re: Multiple triggers on a table

N Prabhakar wrote:
>
> Hi All,
>
> I have a clarification on database triggers. I cam to know that from
> Oracle 7.2 onwards, you can have multiple triggers of the same type on a
> table. (ie., 2 before insert trigger, 2 after insert trigger ..etc)
>
> Let us say USER1 has a table EMP. He creates a BEFORE INSERT trigger on
> EMP.
>
> USER2 can have BEFORE INSERT trigger on USER1.EMP.
>
> My question is, which trigger will get executed upon INSERTION of a record
> on USER1.EMP.
>
> To enable multiple triggers on a table, you need to set init.ora parameter
> COMPATIBLE=7.2
>
> Did any one try this and if so, what is the behavior of the triggers?.
>
> I am not sure what is the underlying principle in implementing this
> strategy?
>
> Regards
>
> N.Prabhakar

If there are two PRE-INSERT triggers on a table, both the triggers will fire when someone tries to insert a row in the table. The order in which these triggers fire is arbitary. So it is possible that first time trigger1 fires before trigger2 and in the second insert trigger2 may fire before trigger1.

This capability was introduced in the Oracle server primarily due to Symmetric Replication. When you set up replication of snapshots on a table, Oracle creates triggers on the table. Earlier version of Oracle only allowed one occurance of each trigger type per table. Due to this limitation, if a wanted to create a trigger on a table which was being replicated, he/she would have had to add the trigger code to the server created trigger on the table. You can understand the messs this could lead to.

So, to keep the replication triggers separate from the user created triggers, Oracle removed the restriction of only having one occurance of a trigger type per table.

Regards,
Ashok

-- 
Ashok F. Kapur    | Galaxy Latin America |
Project Engineer  | (954)958-3373        |
afkapur_at_ccgate.hac.com