RE: Multiple triggers on same event

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 24 Sep 2001 20:06:27 -0700
Message-ID: <F001.00397265.20010924194519@fatcity.com>

> -----Original Message-----
> From: Glenn Travis [mailto:Glenn.Travis_at_sas.com]
>
> At what version did Oracle start allowing multiple triggers
> to be define on the same event (insert) for the same table?
>
> I have several triggers defined on a table (before insert)
> which add a value (:new.c1:=:new.c1+1) to a field.  These
> triggers are owned by several different users but all do the
> same thing.  When a row is inserted, they ALL fire and the
> value (c1) is cumulatively incremented...  Very interesting.
>
> Am I mistaken or was this not allowed prior to 8i?    I
> always thought only one trigger fired for each event.  But
> they all fired in 8.1.7.3.  Also, any trigger owned by the
> owner of the table always fires last...

I thought you could always do that. I'm pretty sure I remember doing it on Oracle 7.1. Here's an example in Oracle 7.3

SQL> select * from v$version ;

BANNER

---

Oracle7 Server Release 7.3.4.0.1 - Production PL/SQL Release 2.3.4.0.0 - Production
CORE Version 3.5.4.0.0 - Production
TNS for Solaris: Version 2.3.4.0.0 - Production NLSRTL Version 3.2.4.0.0 - Production

SQL> create table t (n number) ;

Table créée.

SQL> create trigger t1 before insert on t for each row

  2  begin
  3     :new.n := :new.n + 1 ;
  4  end ;
  5  /

Déclencheur créé.

SQL> create trigger t2 before insert on t for each row

  2  begin
  3     :new.n := :new.n + 1 ;
  4  end ;
  5  /

Déclencheur créé.

SQL> insert into t (n) values (1) ;

1 ligne créée.

SQL> select * from t ;

         N

---

         3 Received on Mon Sep 24 2001 - 22:06:27 CDT