Oracle的触发器迭代限制

Oracle的触发器在触发事件发生后，会自动执行触发器的操作，而这个触发器的操作又可能会触发另一个触发器，这就导致了触发器的迭代。

今天在看Concept的时候，发现Oracle对于触发器迭代是有限制的，最多允许32层迭代，不过Oracle并没有说明超过这个限制会怎样，是报错，还是第33个触发器不被触发。下面通过一个例子来验证一下，是否存在这个迭代限制，以及超过迭代限制后，Oracle如何进行处理。

这里不妨先假设一下，个人认为Oracle应该报错。报错虽然不友好，但是至少使终端用户了解发生了什么问题，并有机会解决，而如果不报错，只是简单的不触发触发器的话，可能会留下一个很难排查的系统bug。

SQL> DECLARE
2 V_STR VARCHAR2(32767);
3 BEGIN
4 FOR I IN 1..33 LOOP
5 V_STR := 'CREATE TABLE T_TRIGGER' || I || '(ID NUMBER)';
6 EXECUTE IMMEDIATE V_STR;
7 END LOOP;
8 FOR I IN 1..32 LOOP
9 V_STR := 'CREATE TRIGGER T_TRIGGER' || I || ' AFTER INSERT ON T_TRIGGER' || I
10 || ' FOR EACH ROW
11 BEGIN
12 INSERT INTO T_TRIGGER' || TO_CHAR(I + 1) || ' VALUES (:NEW.ID);
13 END;';
14 EXECUTE IMMEDIATE V_STR;
15 END LOOP;
16 END;
17 /

PL/SQL 过程已成功完成。

SQL> SELECT TABLE_NAME FROM USER_TABLES
2 WHERE TABLE_NAME LIKE 'T_TRIGGER%';

TABLE_NAME
------------------------------
T_TRIGGER9
T_TRIGGER8
T_TRIGGER7
T_TRIGGER6
T_TRIGGER5
T_TRIGGER4
T_TRIGGER33
T_TRIGGER32
T_TRIGGER31
T_TRIGGER30
T_TRIGGER3
T_TRIGGER29
T_TRIGGER28
T_TRIGGER27
T_TRIGGER26
T_TRIGGER25
T_TRIGGER24
T_TRIGGER23
T_TRIGGER22
T_TRIGGER21
T_TRIGGER20
T_TRIGGER2
T_TRIGGER19
T_TRIGGER18
T_TRIGGER17
T_TRIGGER16
T_TRIGGER15
T_TRIGGER14
T_TRIGGER13
T_TRIGGER12
T_TRIGGER11
T_TRIGGER10
T_TRIGGER1

已选择33行。

现在已经创建了33张表和32个触发器，尝试在第一张表插入一条数据：

SQL> INSERT INTO T_TRIGGER1 VALUES (1);

已创建 1 行。

SQL> SELECT * FROM T_TRIGGER1;

ID
----------
1

SQL> SELECT * FROM T_TRIGGER32;

ID
----------
1

SQL> SELECT * FROM T_TRIGGER33;

ID
----------
1

32个触发器都已经生效，下面创建第34张表和第33个触发器：

SQL> CREATE TABLE T_TRIGGER34 (ID NUMBER);

表已创建。

SQL> CREATE TRIGGER T_TRIGGER33 AFTER INSERT ON T_TRIGGER33
2 FOR EACH ROW
3 BEGIN
4 INSERT INTO T_TRIGGER34 VALUES (:NEW.ID);
5 END;
6 /

触发器已创建

SQL> INSERT INTO T_TRIGGER1 VALUES (2);

已创建 1 行。

SQL> SELECT * FROM T_TRIGGER1;

ID
----------
1
2

SQL> SELECT * FROM T_TRIGGER33;

ID
----------
1
2

SQL> SELECT * FROM T_TRIGGER34;

ID
----------
2

第34张表中也存在记录，看来第33个触发器也生效了，莫非是Oracle的Concept的描述错误，再次检查Concept上的描述，发现Oracle允许32个触发器迭代触发，而第一个触发器只是正常触发，应该不算迭代触发：

SQL> CREATE TABLE T_TRIGGER35 (ID NUMBER);

表已创建。

SQL> CREATE TRIGGER T_TRIGGER34 AFTER INSERT ON T_TRIGGER34
2 FOR EACH ROW
3 BEGIN
4 INSERT INTO T_TRIGGER35 VALUES (:NEW.ID);
5 END;
6 /

触发器已创建

SQL> INSERT INTO T_TRIGGER1 VALUES (3);

已创建 1 行。

SQL> SELECT * FROM T_TRIGGER1;

ID
----------
1
2
3

SQL> SELECT * FROM T_TRIGGER33;

ID
----------
1
2
3

SQL> SELECT * FROM T_TRIGGER34;

ID
----------
2
3

SQL> SELECT * FROM T_TRIGGER35;

ID
----------
3

这次唯一的解释只能是Concept的描述出现了错误。

SQL> BEGIN
2 FOR I IN 1..35 LOOP
3 EXECUTE IMMEDIATE 'DROP TABLE T_TRIGGER' || I;
4 END LOOP;
5 END;
6 /

PL/SQL 过程已成功完成。

SQL> DECLARE
2 V_STR VARCHAR2(32767);
3 BEGIN
4 FOR I IN 1..101 LOOP
5 V_STR := 'CREATE TABLE T_TRIGGER' || I || '(ID NUMBER)';
6 EXECUTE IMMEDIATE V_STR;
7 END LOOP;
8 FOR I IN 1..100 LOOP
9 V_STR := 'CREATE TRIGGER T_TRIGGER' || I || ' AFTER INSERT ON T_TRIGGER' || I
10 || ' FOR EACH ROW
11 BEGIN
12 INSERT INTO T_TRIGGER' || TO_CHAR(I + 1) || ' VALUES (:NEW.ID);
13 END;';
14 EXECUTE IMMEDIATE V_STR;
15 END LOOP;
16 END;
17 /

PL/SQL 过程已成功完成。

SQL> INSERT INTO T_TRIGGER1 VALUES (1);
INSERT INTO T_TRIGGER1 VALUES (1)
*第 1 行出现错误:
ORA-00036: 超过递归 SQL 级别的最大值 50
ORA-06512: 在 "TEST.T_TRIGGER50", line 2
ORA-04088: 触发器 'TEST.T_TRIGGER50' 执行过程中出错
ORA-06512: 在 "TEST.T_TRIGGER49", line 2
ORA-04088: 触发器 'TEST.T_TRIGGER49' 执行过程中出错
ORA-06512: 在 "TEST.T_TRIGGER48", line 2
ORA-04088: 触发器 'TEST.T_TRIGGER48' 执行过程中出错
ORA-06512: 在 "TEST.T_TRIGGER47", line 2
ORA-04088: 触发器 'TEST.T_TRIGGER47' 执行过程中出错
ORA-06512: 在 "TEST.T_TRIGGER46", line 2
ORA-04088: 触发器 'TEST.T_TRIGGER46' 执行过程中出错
ORA-06512: 在 "TEST.T_TRIGGER45", line 2
ORA-04088: 触发器 'TEST.T_TRIGGER45' 执行过程中出错
ORA-06512: 在 "TEST.T_TRIGGER44", line 2
ORA-04088: 触发器 'TEST.T_TRIGGER44' 执行过程中出错
ORA-06512: 在 "TEST.T_TRIGGER43", line 2
ORA-04088: 触发器 'TEST.T_TRIGGER43' 执行过程中出错
ORA-06512: 在 "TEST.T_TRIGGER42", line 2
ORA-04088: 触发器 'TEST.T_TRIGGER42' 执行过程中出错
ORA-06512: 在 "TEST.T_TRIGGER41", line 2
ORA-04088: 触发器 'TEST.T_TRIGGER41' 执行过程中出错
ORA-06512: 在 "TEST.T_TRIGGER40", line 2
ORA-04088:

调整了一下并发触发器的个数，再次测试，可以发现，导致错误的原因是超过了SQL递归调用的上限50，而并不是文档中描述的32。

Oracle在出现超过阈值上限的调用时会报错，这也和开始的猜测相吻合，不过相信在正常环境下，应该不会出现这种极端的错误。