oracle中同一张表多个触发器的执行顺序

今天偶然看到一个面试题，同一张表下如果有多个触发器，那么这些触发器的执行顺序是什么？
一张表多个触发器的现象可能也是存在的，关系型数据库也没有禁用这种方式，但是还是建议不用或者少用触发器，更加不要出现一张表多个触发器的情况。

下面主要对dml触发器进行测试验证，实验环境：oracle 11.2.0.4
基础准备

CREATE TABLE tr_log(
tr_id INT,
tr_name VARCHAR2(30),
tr_date DATE
);

CREATE SEQUENCE tr_seq;
CREATE TABLE tr_table(ID INT);

创建触发器，验证before insert

-- tr1
CREATE OR REPLACE TRIGGER ar1
BEFORE INSERT ON tr_table
FOR EACH ROW
BEGIN
    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr1',SYSDATE); 
END;
/

-- tr2
CREATE OR REPLACE TRIGGER cr2
BEFORE INSERT ON tr_table
FOR EACH ROW
BEGIN
    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr2',SYSDATE); 
END;
/

-- tr3
CREATE OR REPLACE TRIGGER br3
BEFORE INSERT ON tr_table
FOR EACH ROW
BEGIN
    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr3',SYSDATE); 
END;
/

查看执行情况

SQL> INSERT INTO tr_table VALUES(1);
SQL> COMMIT;
SQL> SELECT * FROM tr_log;

     TR_ID TR_NAME                        TR_DATE
---------- ------------------------------ -----------------
         1 tr3                            20-08-26 10:45:50
         2 tr2                            20-08-26 10:45:50
         3 tr1                            20-08-26 10:45:50

由执行结果可以看出，功能一致的触发器是按照创建顺序逆序执行，即后创建的先执行。继续验证delete 和 update 结果是一样的。
那么 如果是不同功能的触发器，执行情况是怎么样的？
创建触发器

-- tr1
DROP TRIGGER ar1;
CREATE OR REPLACE TRIGGER ar1
AFTER insert ON tr_table
FOR EACH ROW
BEGIN
    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr1',SYSDATE); 
END;
/

-- tr2
DROP TRIGGER cr2;
CREATE OR REPLACE TRIGGER cr2
AFTER INSERT OR DELETE ON tr_table
FOR EACH ROW
BEGIN
    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr2',SYSDATE); 
END;
/

-- tr3
DROP TRIGGER br3;
CREATE OR REPLACE TRIGGER br3
AFTER INSERT OR DELETE OR UPDATE ON tr_table
FOR EACH ROW
BEGIN
    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr3',SYSDATE); 
END;
/

查看执行情况

SQL> INSERT INTO tr_table VALUES(1);
SQL> COMMIT;
SQL> SELECT * FROM tr_log;

     TR_ID TR_NAME                        TR_DATE
---------- ------------------------------ -----------------
         1 tr3                            20-08-26 11:01:41
         2 tr2                            20-08-26 11:01:41
         3 tr1                            20-08-26 11:01:41

看起来依然是按照创建顺序逆序执行。
如果是创建的触发器先drop掉再重新创建，会不会改变执行顺序？
如下：

-- tr1
CREATE OR REPLACE TRIGGER tr_1
AFTER insert ON tr_table
FOR EACH ROW
BEGIN
    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr1',SYSDATE); 
END;
/

-- tr2
CREATE OR REPLACE TRIGGER tr_2
AFTER INSERT OR DELETE ON tr_table
FOR EACH ROW
BEGIN
    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr2',SYSDATE); 
END;
/

-- tr3
CREATE OR REPLACE TRIGGER tr_3
AFTER INSERT OR DELETE OR UPDATE ON tr_table
FOR EACH ROW
BEGIN
    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr3',SYSDATE); 
END;
/


INSERT INTO tr_table VALUES(1);
COMMIT;
SELECT * FROM tr_log;

DROP TRIGGER tr_1;
DROP TRIGGER tr_2;
DROP TRIGGER tr_3;

-- tr2
CREATE OR REPLACE TRIGGER tr_2
AFTER INSERT OR DELETE ON tr_table
FOR EACH ROW
BEGIN
    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr2',SYSDATE); 
END;
/
-- tr1
CREATE OR REPLACE TRIGGER tr_1
AFTER insert ON tr_table
FOR EACH ROW
BEGIN
    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr1',SYSDATE); 
END;
/

-- tr3
CREATE OR REPLACE TRIGGER tr_3
AFTER INSERT OR DELETE OR UPDATE ON tr_table
FOR EACH ROW
BEGIN
    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr3',SYSDATE); 
END;
/
INSERT INTO tr_table VALUES(1);
COMMIT;
SELECT * FROM tr_log;
     TR_ID TR_NAME                        TR_DATE
---------- ------------------------------ -----------------
         1 tr3                            20-08-26 11:13:05
         2 tr2                            20-08-26 11:13:05
         3 tr1                            20-08-26 11:13:05
         4 tr3                            20-08-26 11:13:06
         5 tr2                            20-08-26 11:13:06
         6 tr1                            20-08-26 11:13:06

看起来好像跟创建顺序无关啊。那上面得出的结论看来是有问题。
通过跟踪10046可以发现如下的sql

SELECT DECODE(U.TYPE#, 2, U.EXT_USERNAME, U.NAME),
       O.NAME,
       T.UPDATE$,
       T.INSERT$,
       T.DELETE$,
       T.ENABLED,
       DECODE(BITAND(T.PROPERTY, 8192), 8192, 1, 0),
       DECODE(BITAND(T.PROPERTY, 65536), 65536, 1, 0),
       DECODE(BITAND(T.PROPERTY, 131072), 131072, 1, 0),
       (SELECT O.NAME
          FROM OBJ$ O
         WHERE O.OBJ# = U.SPARE2
           AND O.TYPE# = 57)
  FROM SYS.OBJ$ O, SYS.USER$ U, SYS.TRIGGER$ T, SYS.OBJ$ BO
 WHERE T.BASEOBJECT = BO.OBJ#
   AND BO.NAME = :1
   AND BO.SPARE3 = :2
   AND BO.NAMESPACE = 1
   AND T.OBJ# = O.OBJ#
   AND O.OWNER# = U.USER#
   AND O.TYPE# = 12
   AND BITAND(PROPERTY, 16) = 0
   AND BITAND(PROPERTY, 8) = 0
 ORDER BY O.OBJ#

所以归根到底是触发器对象在SYS.OBJ$.OBJ#的排序情况决定触发器的执行顺序，通过实验发现，虽然drop掉相关触发器，并再次创建，只要触发器名一致，OBJ#并没有改变，所以最终触发器的顺序都没有改变。

综上，触发器的执行顺序是根据SYS.OBJ$.OBJ#的顺序执行的。