系統觸發器的應用順序（二）

在寫一個AFTER SUSPEND觸發器的時候碰到了一個很有趣的現象。

看一個例外的情況。

系統觸發器的應用順序（一）：http://yangtingkun.itpub.net/post/468/486884

 

 

上一篇文章中透過測試說明，所有滿足觸發條件的觸發器都會執行，下面再看一個例子。這個例子將觸發器變得稍微複雜一點：

SQL> CONN YANGTK/YANGTK
已連線。
SQL> DROP TABLE T_TRIGGER PURGE;

表已刪除。

SQL> DROP TRIGGER TRI_SUSPEND;

觸發器已刪除。

SQL> CONN / AS SYSDBA
已連線。
SQL> DROP TRIGGER TRI_SUSPEND;

觸發器已刪除。

首先清除上一篇文章的測試環境，下面在當前使用者下建立觸發器，並在觸發器中執行DBMS_RESUMABLE包的過程：

SQL> CONN YANGTK/YANGTK
已連線。
SQL> CREATE TABLE T_TRIGGER (INFO VARCHAR2(20));

表已建立。

SQL> CREATE OR REPLACE TRIGGER TRI_SUSPEND
  2  AFTER SUSPEND ON DATABASE
  3  DECLARE
  4   PRAGMA AUTONOMOUS_TRANSACTION;
  5   V_RESULT BOOLEAN;
  6   V_ERROR_TYPE VARCHAR2(32767);
  7   V_OBJECT_TYPE VARCHAR2(32767);
  8   V_OBJECT_OWNER VARCHAR2(30);
  9   V_TABLESPACE_NAME VARCHAR2(30);
 10   V_OBJECT_NAME VARCHAR2(128);
 11   V_SUB_OBJECT_NAME VARCHAR2(128);
 12  BEGIN
 13   V_RESULT := DBMS_RESUMABLE.SPACE_ERROR_INFO(
 14    V_ERROR_TYPE,
 15    V_OBJECT_TYPE,
 16    V_OBJECT_OWNER,
 17    V_TABLESPACE_NAME,
 18    V_OBJECT_NAME,
 19    V_SUB_OBJECT_NAME);
 20   INSERT INTO T_TRIGGER VALUES ('YANGTK TRIGGER');
 21   COMMIT;
 22  END;
 23  /

警告: 建立的觸發器帶有編譯錯誤。

SQL> SHOW ERR
TRIGGER TRI_SUSPEND 出現錯誤:

LINE/COL ERROR
-------- -----------------------------------------------------------------
11/2     PL/SQL: Statement ignored
11/14    PLS-00201: 必須宣告識別符號 'DBMS_RESUMABLE'

觸發器的建立報錯了，這是由於當前使用者沒有執行DBMS_RESUMABLE的許可權。這時如果不刪除當前使用者下的觸發器，而在SYS使用者下建立同樣的觸發器：

SQL> CONN / AS SYSDBA
已連線。
SQL> CREATE OR REPLACE TRIGGER TRI_SUSPEND
  2  AFTER SUSPEND ON DATABASE
  3  DECLARE
  4   PRAGMA AUTONOMOUS_TRANSACTION;
  5   V_RESULT BOOLEAN;
  6   V_ERROR_TYPE VARCHAR2(32767);
  7   V_OBJECT_TYPE VARCHAR2(32767);
  8   V_OBJECT_OWNER VARCHAR2(30);
  9   V_TABLESPACE_NAME VARCHAR2(30);
 10   V_OBJECT_NAME VARCHAR2(128);
 11   V_SUB_OBJECT_NAME VARCHAR2(128);
 12  BEGIN
 13   V_RESULT := DBMS_RESUMABLE.SPACE_ERROR_INFO(
 14    V_ERROR_TYPE,
 15    V_OBJECT_TYPE,
 16    V_OBJECT_OWNER,
 17    V_TABLESPACE_NAME,
 18    V_OBJECT_NAME,
 19    V_SUB_OBJECT_NAME);
 20   INSERT INTO YANGTK.T_TRIGGER VALUES ('SYS TRIGGER');
 21   COMMIT;
 22  END;
 23  /

觸發器已建立

SYS使用者下的建立顯然沒有任何問題，下面回到YANGTK使用者引發觸發器執行條件：

SQL> CONN YANGTK/YANGTK
已連線。
SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 300;

會話已更改。

SQL> CREATE TABLE T_BIG (ID NUMBER)
  2  TABLESPACE YANGTK
  3  STORAGE (INITIAL 250M);
CREATE TABLE T_BIG (ID NUMBER)
*
第 1 行出現錯誤:
ORA-04098: 觸發器 'YANGTK.TRI_SUSPEND' 無效且未透過重新驗證
ORA-01659: 無法分配超出 27 的 MINEXTENTS (在表空間 YANGTK 中)

SQL> SELECT * FROM T_TRIGGER;

未選定行

顯然這次一個觸發器都沒有執行。不過這也不難理解，由於當前使用者下要執行的觸發器存在編譯錯誤，顯然執行就會報錯，因此Oracle沒有繼續呼叫其他的觸發器。

SQL> SHOW USER
USER 為 "YANGTK"
SQL> DROP TRIGGER TRI_SUSPEND;

觸發器已刪除。

SQL> CREATE TABLE T_BIG (ID NUMBER)
  2  TABLESPACE YANGTK
  3  STORAGE (INITIAL 250M);
CREATE TABLE T_BIG (ID NUMBER)
*
第 1 行出現錯誤:
ORA-01659: 無法分配超出 27 的 MINEXTENTS (在表空間 YANGTK 中)

SQL> SELECT * FROM T_TRIGGER;

未選定行

有意思的情況出現了，刪除當前存在編譯錯誤的觸發器，然後再次以發觸發器事件，而這次SYS使用者下的觸發器仍然沒有執行。

只有重新登陸，才能使得觸發器執行：

SQL> CONN YANGTK/YANGTK
已連線。
SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 300;

會話已更改。

SQL> CREATE TABLE T_BIG (ID NUMBER)
  2  TABLESPACE YANGTK
  3  STORAGE (INITIAL 250M);
CREATE TABLE T_BIG (ID NUMBER)
*
第 1 行出現錯誤:
ORA-30032: 掛起的 (可恢復) 語句已超時
ORA-01659: 無法分配超出 27 的 MINEXTENTS (在表空間 YANGTK 中)

SQL> SELECT * FROM T_TRIGGER;

INFO
--------------------
SYS TRIGGER

看來觸發器失效影響的並不只是當前的觸發器執行，還會影響到其他觸發器，而且失效觸發器被刪除後，仍然可能影響其他的觸發器直到會話結束。