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

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

尋找導致問題的原因。

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

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

 

 

上一篇文章介紹了，如果當前使用者下的觸發器出現了編譯錯誤，則會導致其他觸發器也無法觸發，即使當前觸發器被刪除。

是什麼原因導致了這種情況的產生，懷疑可能與觸發器的執行順序有關，由於觸發器的執行是根據觸發器的建立順序，具體描述可以參考：http://yangtingkun.itpub.net/post/468/398314

下面將SYS使用者下的觸發器和當前使用者下的觸發器建立順序倒置，看看是否出現同樣的問題：

SQL> DROP TABLE T_TRIGGER PURGE;

表已刪除。

SQL> CREATE TABLE T_TRIGGER (INFO VARCHAR2(20));

表已建立。

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

觸發器已刪除。

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  /

觸發器已建立

SQL> CONN YANGTK/YANGTK
已連線。
SQL> DROP TRIGGER TRI_SUSPEND;
DROP TRIGGER TRI_SUSPEND
*
第 1 行出現錯誤:
ORA-04080: 觸發器 'TRI_SUSPEND' 不存在

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> 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;

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

SQL> SELECT OBJECT_ID, OWNER
  2  FROM DBA_OBJECTS
  3  WHERE OBJECT_NAME = 'TRI_SUSPEND';

 OBJECT_ID OWNER
---------- ------------------------------
     92651 SYS
     92652 YANGTK

雖然錯誤資訊一樣，但是可以看到，這次T_TRIGGER表中包含一條記錄，說明SYS觸發器已經執行成功了。是在嘗試呼叫第二個觸發器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;

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

可以看到，就是由於觸發器狀態的不正確，導致當前會話呼叫觸發器出現了問題。即使刪除了問題觸發器，發生了SUSPEND事件後，SYS使用者下的AFTER SYSPEND也沒有再次被觸發。

嘗試在當前會話再次新增一個正確的觸發器：

SQL> CREATE OR REPLACE TRIGGER TRI_SUSPEND
  2  AFTER SUSPEND ON DATABASE
  3  DECLARE
  4   PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6   INSERT INTO T_TRIGGER VALUES ('YANGTK TRIGGER');
  7   COMMIT;
  8  END;
  9  /

觸發器已建立

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;

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

即使重建當前使用者下的觸發器，使其狀態正確，在當前的會話中，Oracle也不會再次呼叫觸發器了。