【DB筆試面試448】Oracle中有哪幾類觸發器？

• 2019 年 10 月 11 日
• 筆記

Oracle中有哪幾類觸發器？

答案部分

Oracle資料庫有4種觸發器，分別是DML、替代觸發器（INSTEAD OF觸發器）、DDL和系統觸發器，一般的應用系統中都使用到DML、替代觸發器，而DDL和系統觸發器是DBA管理資料庫用得比較多。下圖是Oracle觸發器的分類圖：

圖 3-2 Oracle中觸發器的分類

觸發器（TRIGGER）的組成主要有以下幾部分：

①　觸發事件：即在何種情況下觸發TRIGGER。例如：DML語句（INSERT、UPDATE和DELETE語句對錶或視圖執行數據處理操作)、DDL語句（如CREATE、ALTER、DROP語句等在資料庫中創建、修改、刪除模式對象）、資料庫系統事件（如系統啟動或退出、異常錯誤）、用戶事件（如登錄或退出資料庫）。既可以是單個觸發事件，也可以是多個觸發事件的組合（只能使用OR邏輯組合，不能使用AND邏輯組合）。

②　觸發時間（觸發時機）：即該TRIGGER是在觸發事件發生之前（BEFORE）還是之後（AFTER）觸發，也就是觸發事件和該TRIGGER的操作順序。如果指定為BEFORE，那麼表示在執行DML操作之前觸發，以便防止某些錯誤操作發生或實現某些業務規則；如果指定為AFTER，那麼表示在執行DML操作之後觸發，以便記錄該操作或做某些事後處理。

③　觸發器本身：即該TRIGGER被觸發之後的目的和意圖，正是觸發器本身要做的事情。例如：PL/SQL塊。

④　觸發頻率：說明觸發器內定義的動作被執行的次數，分為語句級（STATEMENT）觸發器和行級（ROW）觸發器。

a. 語句級（STATEMENT）觸發器：是指當某觸發事件發生時，該觸發器只執行一次；語句級觸發器不允許和WHEN子句一起使用。

b. 行級（ROW）觸發器：是指當某觸發事件發生時，對受到該操作影響的每一行數據，觸發器都單獨執行一次。

c. 當某操作隻影響到表中的一行數據時，語句級觸發器與行級觸發器的效果相同。

換句話說，語句級觸發器針對某一條語句只觸發一次，而行級觸發器則針對語句所影響的每一行都觸發一次。例如：某條UPDATE語句修改了表中的100行數據，那麼針對該UPDATE事件的語句級觸發器將被觸發一次，而行級觸發器將被觸發100次。

⑤　觸發對象：包括表、視圖、用戶、資料庫。只有在這些對象上發生了符合觸發條件的觸發事件時，才會執行觸發操作。

⑥　觸發條件：由WHEN子句指定一個邏輯表達式。只有當該表達式的值為TRUE時，遇到觸發事件才會自動執行觸發器，使其執行觸發操作。

下面分別介紹這4類觸發器。

1、DML觸發器

DML觸發器由DML語句觸發，例如，INSERT、UPDATE和DELETE語句。針對所有的DML事件，按觸發的時間可以將DML觸發器分為BEFORE觸發器和AFTER觸發器，另外，DML觸發器也可以分為語句級觸發器與行級觸發器。

創建DML觸發器的一般語法如下所示：

CREATE [OR REPLACE] TRIGGER trigger_name  {BEFORE | AFTER }  {INSERT | DELETE | UPDATE [OF column [, column …]]}  ON [schema.] table_name  [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]  [FOR EACH ROW ]  [WHEN condition]  trigger_body; 

其中：

l BEFORE和AFTER指出觸發器的觸發時機為前觸發還是後觸發，前觸發是在執行觸發事件之前觸發，後觸發是在執行觸發事件之後觸發當前所創建的觸發器。

l FOR EACH ROW選項說明觸發器為行級觸發器。當省略FOR EACH ROW選項時，BEFORE和AFTER觸發器為語句級觸發器，而INSTEAD OF觸發器則為行級觸發器。

l REFERENCING子句說明相關名稱，在行級觸發器的PL/SQL塊和WHEN子句中可以使用相關名稱參照當前的新、舊列值，默認的相關名稱分別為OLD和NEW。在觸發器的PL/SQL塊中應用相關名稱時，必須在它們之前加冒號，但在WHEN子句中則不能加冒號。

l WHEN子句說明觸發約束條件。當Condition為一個邏輯表達時，其中必須包含相關名稱，而不能包含查詢語句，也不能調用PL/SQL函數。WHEN子句指定的觸發約束條件只能用在BEFORE和AFTER行觸發器中，不能用在INSTEAD OF行觸發器和其它類型的觸發器中。

DML觸發器有如下幾種事件：

1、 INSERTING：當觸發事件是INSERT時，取值為TRUE，否則為FALSE。

2、 UPDATING[（column_1,column_2,…,column_x）]：當觸發事件是UPDATING時，若修改了column_x列，則取值為TRUE，否則為FALSE。其中，column_x是可選的。

3、 DELETING：當觸發事件是DELETE時，則取值為TRUE，否則取值為FALSE。

DML觸發器有如下的限制條件：

l CREATE TRIGGER語句文本的字元長度不能超過32KB

l 觸發器體內的SELECT語句只能為SELECT … INTO … 結構，或者為定義游標所使用的SELECT語句

l 觸發器中不能使用事務控制語句COMMIT、ROLLBACK和SAVEPOINT

l 由觸發器所調用的存儲過程或函數也不能使用資料庫事務控制語句

l 觸發器中不能使用LONG、LONG RAW類型

l 觸發器內不能通過:NEW修改LOB列中的數據

l 觸發器最多可以嵌套32層

當觸發器被觸發時，要使用被插入、更新或刪除的記錄中的列值，有時要使用操作前或操作後列的值，這個時候可以使用:NEW或者:OLD來實現。其中，:NEW表示操作完成後列的值，而:OLD表示操作完成前列的值，如下表所示：

:OLD表和:NEW表是記憶體中的兩個表，其結構和源表結構完全一致。

（1） 當插入時，先將值插入到:NEW表中，在沒有控制的前提下才真正地插入到表中。

（2） 當刪除時，先將要刪除的數據移到:OLD表中，以前的表中的數據就沒有了。

（3） 當更新時，UPDATE XXX SET XXX=:NEW.XXX WHERE XXX=:OLD.XXX，WHERE條件是判斷已有的值，它就移動:OLD表中，SET是設置新的值先放到:NEW表中。

下面舉一個行級觸發器的例子。該觸發器可以實現，當職工表SCOTT.EMP表被刪除一條記錄時，把被刪除記錄寫到職工表刪除日誌表中去，程式碼如下所示：

--建立日誌表  CREATE TABLE SCOTT.EMP_HIS AS SELECT * FROM SCOTT.EMP WHERE 1=2;  --建立觸發器  CREATE OR REPLACE TRIGGER SCOTT.TR_DEL_EMP     BEFORE DELETE --指定觸發時機為刪除操作前觸發     ON SCOTT.EMP     FOR EACH ROW   --說明創建的是行級觸發器  BEGIN     --將修改前數據插入到日誌記錄表EMP_HIS ,以供監督使用。     INSERT INTO EMP_HIS(DEPTNO , EMPNO, ENAME , JOB ,MGR , SAL , COMM , HIREDATE )     VALUES( :OLD.DEPTNO, :OLD.EMPNO, :OLD.ENAME , :OLD.JOB,:OLD.MGR, :OLD.SAL, :OLD.COMM, :OLD.HIREDATE );  END;

測試行級觸發器：

SYS@lhrdb> SELECT * FROM SCOTT.EMP_HIS;  no rows selected  SYS@lhrdb> DELETE SCOTT.EMP WHERE EMPNO=7788;  1 row deleted.  SYS@lhrdb> SELECT * FROM SCOTT.EMP_HIS;       EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO  ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------        7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20

下面再舉一個語句級觸發器的例子。

--建立日誌表  CREATE TABLE  SCOTT.MYLOG_LHR        (CUR_USER VARCHAR2(30), CUR_DATE DATE, ACTION VARCHAR2(30));  --建立觸發器  CREATE OR REPLACE TRIGGER SCOTT.TRI_DML_EMP_LHR    AFTER INSERT OR DELETE OR UPDATE ON SCOTT.EMP  BEGIN    IF INSERTING THEN      INSERT INTO SCOTT.MYLOG_LHR        (CUR_USER, CUR_DATE, ACTION)      VALUES        (USER, SYSDATE, 'I');    ELSIF DELETING THEN      INSERT INTO SCOTT.MYLOG_LHR VALUES (USER, SYSDATE, 'D');    ELSE      INSERT INTO SCOTT.MYLOG_LHR VALUES (USER, SYSDATE, 'U');    END IF;  END;  /

測試語句級觸發器：

SYS@lhrdb> SELECT * FROM SCOTT.MYLOG_LHR;  no rows selected  SYS@lhrdb> DELETE SCOTT.EMP WHERE ROWNUM<=1;  1 row deleted.  SYS@lhrdb> SELECT * FROM SCOTT.MYLOG_LHR;  CUR_USER                       CUR_DATE            ACTION  ------------------------------ ------------------- ------------------------------  SYS                            2016-10-26 14:14:37 D

2、替代觸發器（INSTEAD OF觸發器）

由於在Oracle里，不能直接對由兩個以上的表建立的視圖進行操作，所以，給出了替代觸發器。可以用替代觸發器解決視圖的多表更新問題，並且替代觸發器只能用於視圖。例如：針對INSERT事件的替代觸發器，它由INSERT語句觸發，當出現INSERT語句時，該語句不會被執行，而是執行替代觸發器中定義的語句。

創建替代觸發器的一般語法如下所示:

CREATE [OR REPLACE] TRIGGER trigger_name  INSTEAD OF  {INSERT | DELETE | UPDATE [OF column [, column …]]}  [OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]  ON [schema.] view_name --只能定義在視圖上  [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]  [FOR EACH ROW ] --因為INSTEAD OF觸發器只能在行級上觸發,所以沒有必要指定  [WHEN condition]  PL/SQL_block | CALL procedure_name;

其中，INSTEAD OF選項使Oracle激活觸發器，而不執行觸發事件。只能對視圖和對象視圖建立INSTEAD OF觸發器，而不能對錶、模式和資料庫建立INSTEAD OF觸發器。其它選項和DML觸發器的語法相同。

創建替代觸發器需要注意以下幾點內容：

①　只能創建在視圖上，並且該視圖沒有指定WITH CHECK OPTION選項。

②　不能指定BEFORE或AFTER選項。

③　FOR EACH ROW是可選的，即替代觸發器只能是行級觸發器，所以，沒有必要指定。

④　沒有必要在針對一個表的視圖上創建替代觸發器，只要創建DML觸發器就可以了。

⑤　每一個表和視圖只能有一個替代觸發器。

⑥　替代觸發器被用於更新那些沒有辦法通過正常方式更新的視圖。

⑦　替代觸發器的主要優點就是可以使不能更新的視圖支援更新。它支援多個表中數據的插入、更新和刪除操作。

下面舉一個視圖觸發器的例子。首先創建一個含有聚合函數的視圖，如下所示：

CREATE OR REPLACE VIEW SCOTT.VW_EMP_LHR AS  SELECT DEPTNO, COUNT(*) TOTAL_EMPLOYEER, SUM(SAL) TOTAL_SALARY    FROM SCOTT.EMP   GROUP BY DEPTNO;

在此視圖中，當直接刪除時會報錯，如下所示：

SYS@lhrdb> DELETE FROM SCOTT.VW_EMP_LHR WHERE DEPTNO=10;  DELETE FROM VW_EMP_LHR WHERE DEPTNO=10              *  ERROR at line 1:  ORA-01732: data manipulation operation not legal on this view--此視圖的數據操縱操作非法

但是可以通過創建替代觸發器來為DELETE操作執行所需的處理，即刪除EMP表中所有基準行：

CREATE OR REPLACE TRIGGER SCOTT.EMP_VIEW_DELETE

INSTEAD OF DELETE ON SCOTT.VW_EMP_LHR

FOR EACH ROW

BEGIN

DELETE FROM SCOTT.EMP WHERE DEPTNO = :OLD.DEPTNO;

END;

測試替代觸發器，如下所示：

SYS@lhrdb> DELETE FROM SCOTT.VW_EMP_LHR WHERE DEPTNO=10;

1 row deleted.

SYS@lhrdb> SELECT * FROM SCOTT.EMP WHERE DEPTNO=10;

no rows selected

3、DDL觸發器和系統觸發器

由於DDL觸發器和系統觸發器的創建語法很相似，所以，在此一併講解。其實，該部分內容涉及到面試中的一個問題，那就是，「如何監控會話的登錄登出情況？」，答案就是使用審計或系統觸發器來實現。下面將詳細講解該部分的內容。

系統觸發器可以在DDL或資料庫系統上被觸發。DDL指的是數據定義語言，如CREATE、ALTER及DROP等。而資料庫系統事件包括資料庫伺服器的啟動或關閉，用戶的登錄與登出、資料庫服務錯誤等。

值得注意的一點是，隱含參數「_SYSTEM_TRIG_ENABLED」的默認值是TRUE，即允許DDL和系統觸發器。當設置隱含參數「_SYSTEM_TRIG_ENABLED」為FALSE的時候，將禁用DDL和系統觸發器。

創建系統觸發器的一般語法如下所示：

CREATE OR REPLACE TRIGGER [sachema.]trigger_name

{BEFORE|AFTER}

{DDL_EVENT_LIST | DATABASE_EVENT_LIST}

ON { DATABASE | [schema.]SCHEMA }

[WHEN condition]

PL/SQL_block | CALL procedure_name;

其中：

l DDL_EVENT_LIST：一個或多個DDL事件，事件間用OR分開，可以對所有DDL語句監控（直接寫DDL），也可以對個別DDL語句監控。

l DATABASE_EVENT_LIST：一個或多個資料庫事件，事件之間用OR分開。

l ON：系統觸發器按照作用範圍，分為SCHEMA觸發器和DATABASE觸發器。SCHEMA觸發器作用在單個用戶上，而DATABASE觸發器作用在整個資料庫所有用戶上。

l 對於WHEN子句後邊的內容需要由小括弧括起來。

需要注意的是，系統觸發器既可以建立在一個模式上，又可以建立在整個資料庫上。當建立在模式（SCHEMA）之上時，只有模式所指定用戶的DDL操作和該用戶操作所導致的錯誤才能激活觸發器，默認時為當前用戶模式。當建立在資料庫（DATABASE）之上時，該資料庫所有用戶的DDL操作和所有用戶操作所導致的錯誤，以及資料庫的啟動和關閉均可激活觸發器。要在資料庫之上建立觸發器，要求用戶具有ADMINISTER DATABASE TRIGGER許可權或具有DBA角色。一般應該賦予的許可權包括ADMINISTER DATABASE TRIGGER、ALTER ANY TRIGGER和CREATE ANY TRIGGER。

下表給出了系統觸發器的種類和事件出現的時機：

表 3-4系統觸發器的種類和事件

除DML語句的列屬性外，其餘事件屬性值可通過調用Oracle定義的事件屬性函數來讀取，參考下表：

表 3-5事件屬性函數

上表中的函數和同義詞的對應關係可以通過如下的SQL語句找到：

SELECT * FROM DBA_SYNONYMS D WHERE D.SYNONYM_NAME LIKE 'ORA%';

就像DML觸發器一樣，系統觸發器可以使用WHEN子句來指定觸發器激活條件。關於系統觸發器需要了解以下幾點：

l STARTUP和SHUTDOWN觸發器不能帶有任何條件。

l SERVERERROR觸發器可以使用ERRNO測試來檢查特定的錯誤。

l LOGON和LOGOFF觸發器可以使用USERID或USERNAME測試來檢查用戶標識或用戶名。

l DDL觸發器可以檢查正在修改對象的名稱、類型和操作類別。

DDL觸發器有很多實際用途，如下所示：

①　建表的同時建立公共同義詞

②　阻止非授權用戶的TRUNCATE操作

③　記錄所有的DDL語句（包括SERVERERROR、GRANT、SHUTDDOWN、ALTER、REVOKE、DROP、TRUNCATE、COMMENT、STARTUP、AUDIT、CREATE、ANALYZE），以便查找責任人

④　阻止DDL操作

⑤　記錄伺服器錯誤

⑥　填充V$SESSION的CLIENT_INFO和CLIENT_IDENTIFIER列

⑦　記錄用戶登錄資料庫失敗的詳細資訊

⑧　監控會話的登錄登出情況

要禁用或啟用表的所有觸發器，可以使用ALTER TABLE語句，如下所示：

ALTER TABLE T_20161026_LHR DISABLE ALL TRIGGERS; –禁用觸發器

ALTER TABLE T_20161026_LHR ENABLE ALL TRIGGERS; –啟用觸發器

將觸發器設置為禁用或啟用使用ALTER TRIGGER語句，如下所示：

ALTER TRIGGER TRIGGER_NAME ENABLE;

ALTER TRIGGER TRIGGER_NAME DISABLE;

重新編譯觸發器的語句為：

ALTER TRIGGER [SCHEMA.] TRIGGER_NAME COMPILE;

最後，介紹一下編寫觸發器的一些注意事項：

l 觸發器不接受參數。

l 一個表上最多可以有12個觸發器，但同一時間、同一事件、同一類型的觸發器只能有一個。

l 在一個表上的觸發器越多，對在該表上的DML操作的性能影響就越大。

l 觸發器最大為32KB。若確實需要，則可以先建立存儲過程，然後在觸發器中調用存儲過程。

l 在觸發器的執行部分只能使用DML語句（例如SELECT、INSERT、UPDATE、DELETE等），不能使用DDL語句（例如CREATE、ALTER、DROP等）。

l 觸發器中不能包含事務控制語句（例如COMMIT、ROLLBACK、SAVEPOINT等）。因為觸發器是觸發語句的一部分，當觸發語句被提交、回退時，觸發器也被提交、回退了。

l 在觸發器主體中調用的任何存儲過程、函數，都不能使用事務控制語句。

l 在觸發器主體中不能聲明任何LONG或BLOB變數。

About Me：小麥苗

● 本文作者：小麥苗，只專註於資料庫的技術，更注重技術的運用

● 作者部落格地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列題目來源於作者的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

● 題目解答若有不當之處，還望各位朋友批評指正，共同進步