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在出现超过阈值上限的调用时会报错,这也和开始的猜测相吻合,不过相信在正常环境下,应该不会出现这种极端的错误。