Re: Multiple triggers on the same event

Jim Kennedy wrote:
> "Bill" <no_at_no.com> wrote in message
> news:Kax4h.265$7L1.1390_at_eagle.america.net...
> > Forgive the beginner question but, does Oracle allow multiple triggers
> > on the same event? For example, can I create more than on before update
> > trigger on the Customer table? Thanks.
> >
> > --
> > .Bill.
> Yes, but you have no guarantee on what the order of execution is. To solve
> that have 1 trigger that calls multiple stored procedures and call the
> stored procedures in the order you want them to execute.
> Jim

You know, I have always heard this, but as an academic exercise, if you trace an execution, you will see (at least in 10.2) it is recursively selected by object_id of the trigger. Now in 9.2, I remember doing this same test, and IIRC, it did by LAST_DDL_TIME. In other words, this is hardly a repeatable test case across versions, anyway :)

select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled from obj$ o,user$ u,trigger$ t where t.baseobject=:1 and t.obj#=o.obj# and o.owner#=u.user# and bitand(property,16)=0 and bitand(property,8)=0 order by o.obj#

SQL> create table t1109(c number);

Table created.

SQL> create or replace trigger t1109_t1
  2 before insert on t1109
  3 for each row
  4 begin
  5 dbms_output.put_line('in the first trigger');   6 end;
  7 /

Trigger created.

SQL> create or replace trigger t1109_t2
  2 before insert on t1109
  3 for each row
  4 begin
  5 dbms_output.put_line('in the second trigger');   6 end;
  7 /

Trigger created.

SQL> insert into t1109 values(40);
in the second trigger
in the first trigger

1 row created.

SQL> create or replace trigger t1109_t3
  2 before insert on t1109
  3 for each row
  4 begin
  5 dbms_output.put_line('in the third trigger');   6 end;
  7 /

Trigger created.

SQL> insert into t1109 values(40);
in the third trigger
in the second trigger
in the first trigger

1 row created.

SQL> drop trigger t1109_t1;

Trigger dropped.

SQL> create or replace trigger t1109_t1
  2 before insert on t1109
  3 for each row
  4 begin
  5 dbms_output.put_line('in the first trigger');   6 end;
  7 /

Trigger created.

SQL> insert into t1109 values(40);
in the first trigger
in the third trigger
in the seconds trigger

1 row created.

SQL> It makes sense that Oracle has to have some algorithm in place to decide how to do it, i.e., it can't vary across statement executions (at least within the same version on the same platform). I'm not even sure why it would differ across platforms, but versions i could understand.

I wouldn't base an application on the above (your method is much better), but it is interesting.

Regards,

Steve Received on Thu Nov 09 2006 - 08:17:44 CST