[Oracle] Trigger

何謂觸發(Trigger) ?

觸發(Triggers)是當 Table 或是 View 因為 Insert、Delete、Update動作發生時自動觸發的程序(或是因為特定事件的發生，例如：服務啟動)。可以用來描述商業邏輯。跟 stored procedure 很類似，差別在於它不是用來直接呼叫的。

通常 trigger 被用來作為以下幾項用途：

1. 資料完整性的檢查
   
   
2. 稽核與日誌紀錄功能
   
   
3. 執行複雜的商業邏輯運算與處理
   
   
4. 衍生欄位資料的計算與產生
   
   
5. 讓 table 資料有限制的被修改

 

使用語法

首先是 trigger 的使用語法： (詳細語法可參考官方文件)
<br />TRIGGER trigger_name --trigger 名稱<br /> triggering_statement --trigger 觸發的條件、依附的 object ... etc<br /> [trigger_restriction]<br />BEGIN<br /> triggered_action; --trigger 所處理的事情<br />END;<br />

從語法可以看出，整個 trigger 可以分為四個部份：

1. trigger name
   在同一個 schema 中，trigger name 必須是唯一的。
   
   
2. triggering statement
   指定讓 trigger 啟動的事件，這些事情可能包含 DML(for table)、DDL(for schema object) 或是資料庫服務啟動、關閉、甚至是發生錯誤時。
   
   
3. trigger restriction
   額外設定的限制，可讓 trigger 在特定情況下(當 restriction = TRUE 時)才啟動。
   
   
4. triggered action
   trigger 所要執行的程式內容。

 

觸發(Trigger)的種類

在 Oracle 中，trigger 大致可分為五類：

1. statement trigger (詳細用法可參考官方文件)
   此種 trigger 跟 DML 操作(例如：DELETE、INSERT、UPDATE)有關聯，但每一次的 DML 操作僅會觸發一次 trigger。
   雖然設定在 DML 操作上的 trigger 在 DML 操作發生時僅會出發一次，但可以同時設定多個 trigger 在同一個 DML 操作上，而且還可以透過「FOLLOW」、「PRECEDES」… 等關鍵字來決定多個 trigger 觸發的先後順序。
   
   
2. row trigger (詳細用法可參考官方文件)
   當 table 中的每一列資料發生 INSERT、UPDATE 或是 DELETE 時，此種 trigger 就會觸發。大致跟 statement trigger 運作方式相同，但 row trigger 是針對「每一列」資料進行觸發；因此假設一個 DML 同時影響很多筆資料時，statement trigger 僅會出發一次，但 row trigger 卻會觸發很多次。
   
   
3. INSTEAD OF (詳細用法可參考官方文件)
   可以用在當使用者針對 VIEW 或是衍生欄位進行操作時，可透過 trigger 將資料進行正確的資料處理。
   
   
4. user event trigger
   用於 DDL(例如：CREATE、ALTER、DROP、USER LOGON/LOGOFF … 等等) 以及特定的 DML 操作(分析、統計、稽核、使用者權限管理)上。
   
   
5. system event trigger
   當資料庫啟動、關閉、或是發生錯誤的事件發生時，會進行觸發。

 

Trigger 發生的時間點

trigger 發生的時間點也必須要注意，因為在 trigger statement 中，有「BEFORE」與「AFTER」兩種可供使用，表示 trigger 觸發的時間點位於「事件發生前」或是「事件發生後」。

但也不是每個事件都有前後可以設定，例如：STARTUP、SUSPEND、LOGON … 等事件，就僅有 AFTER 可以用；而 SHUTDOWN、LOGOFF … 等事件就僅有 BEFORE 可以用。

 

Trigger 的設計原則與限制

雖然 trigger 看似很好用，可以作很多的管理與控制，但最好還是注意以下幾點原則與限制：

1. 過度使用 trigger 或許會產生複雜的依賴關係，後續的維護變得很困難。
   
   
2. 確定當某個特定工作執行後，所有相關與相依的工作都有被一一執行。
   
   
3. 避免將 recursive trigger 的發生，否則記憶體裝再多都沒用。
   
   
4. 避免 trigger 的工作會觸發另一個 trigger 而造成連鎖效應，以免造成非預期的情況發生，或是對資料庫效能造成影響。
   
   
5. 不要用 trigger 作一些資料庫已經能做的工作，例如：參考完整性的檢查。
   
   
6. 確定「BEFORE」、「AFTER」的使用有符合規則與需求。
   
   
7. trigger 的程式碼不宜過多(Oracle 限制不能超過 32 Kb)，若是程式碼太多，或許可以考慮寫成 stored procedure 來處理。
   
   
8. 確定 trigger 中運作的工作符合資料庫與商業邏輯規則，若是要針對特定人物、特定團體或是特定 application 客製化工作需求，別用 trigger，寫在 application 裡面吧!
   
   
9. 在 trigger 中無法使用 COMMIT、ROLLBACK、SAVEPOINT … 等關鍵字，因為 DDL 操作已經隱含 COMMIT 在裡面了。
   

 

建立 Trigger

Statement Trigger

statement trigger 是與 DML 的操作(Insert、Update、Delete)有關，以下用範例說明：
<br />--建立 table 存放 trigger 產生的 log 之用<br />CREATE TABLE evaluations_log (<br /> log_date DATE, <br /> action VARCHAR2(50)<br />);<br /><br />CREATE OR REPLACE TRIGGER eval_modification_trigger<br /> --設定當發生 insert、update、delete 以後會觸發<br /> AFTER INSERT OR UPDATE OR DELETE<br /> ON evaluations --指定 table<br />DECLARE <br /> log_action evaluations_log.action%TYPE;<br />BEGIN<br /> IF INSERTING THEN --發生 insert<br /> log_action := 'Insert';<br /> ELSIF UPDATING THEN --發生 update<br /> log_action := 'Update';<br /> ELSIF DELETING THEN --發生 delete<br /> log_action := 'Delete';<br /> ELSE <br /> DBMS_OUTPUT.PUT_LINE('This code is not reachable.');<br /> END IF;<br /> <br /> --執行 log 的動作<br /> INSERT INTO evaluations_log(log_date, action) VALUES(SYSDATE, log_action);<br />END <br />

 

Row Trigger

與 statement trigger 不同的是，DML 對於每個資料列的操作都會觸發 row trigger 動作，但在 trigger 寫法上，僅有一點點不同而已：
<br />CREATE OR REPLACE TRIGGER new_evaluation<br /> --設定發生 insert 前會觸發<br /> BEFORE INSERT<br /> ON evaluations --指定 table<br /> FOR EACH ROW --當每筆資料新增時，都會觸發此 trigger<br />BEGIN<br /> NULL<br />END<br />

 

INSTEAD OF Trigger

instead of trigger 通常用於發生使用者對 view 進行操作時，可以針對使用者傳入的資料進行額外的處理，以下是範例：
<br />CREATE OR REPLACE TRIGGER update_name_view_trigger<br /> INSTEAD OF UPDATE --當發生 update 時，用以下程式碼取代 update 的動作<br /> ON emp_locations --指定 view<br />BEGIN<br /> --將更新的字串拆開並分別更新 table 中的資料<br /> UPDATE employees<br /> SET first_name = substr(:NEW.name, instr(:NEW.name, ',') + 2),<br /> last_name = substr(:NEW.name, 1, instr(:NEW.name) - 1);<br /> WHERE employee_id = :OLD.employee;<br />END <br />

 

LOGON & LOGOFF Trigger

通常此種 trigger 是用於紀錄使用者登入登出 schema object 時紀錄 log 之用，以下為範例：
<br />--建立 table 儲存使用者登入登出的紀錄<br />CREATE TABLE hr_user_log(<br /> user_name VARCHAR2(30), <br /> activity VARCHAR2(20), <br /> event_date DATE<br />);<br /><br />--建立 LOGON Trigger<br />CREATE OR REPLACE TRIGGER note_hr_logon_trigger<br /> AFTER LOGON --使用者登入前觸發<br /> ON HR.SCHEMA --指定 schema object<br />BEGIN<br /> INSERT INTO hr_user_log VALUES(USER, 'LOGON', SYSDATE);<br />END <br /><br />--建立 LOGOFF Trigger<br />CREATE OR REPLACE TRIGGER note_hr_logoff_trigger<br /> BEFORE LOGOFF --使用者登出前觸發<br /> ON HR.SCHEMA --指定 schema object<br />BEGIN<br /> INSERT INTO hr_user_log VALUES(USER, 'LOGOFF', SYSDATE);<br />END <br />

 

停用 & 啟用 Trigger

trigger 建立完成後，也可以暫時停用的，之後也還是可以繼續啟用，語法範例如下：
<br />--停用特定的 trigger<br />ALTER TRIGGER eval_change_trigger DISABLE;<br /><br />--啟用特定的 trigger<br />ALTER TRIGGER eval_change_trigger ENABLE;<br /><br />--停用指定 table 相關的所有 trigger<br />ALTER TABLE evaluations DISABLE ALL TRIGGERS;<br /><br /> --啟用指定 table 相關的所有 trigger<br />ALTER TABLE evaluations ENABLE ALL TRIGGERS;<br />

 

錯誤排除

由於 trigger 建立時會經過 compile 的動作，若是錯誤發生後，想要瞭解錯誤的詳細描述進行排除，或是想要瞭解 trigger 之間有哪些相依的關係，就必須透過 Oracle 中 Data Dictionary 的幫忙! 詳細的 Data Dictionary 資訊可以參考官方網站文件。

以下用範例作說明：
<br />--顯示 trigger 編譯時的錯誤<br />SELECT * FROM USER_ERROR WHERE TYPE = 'TRIGGER';<br /><br />--顯示 trigger 之間的相依性<br />SELECT * FROM ALL_DEPENDENCIES WHERE TYPE = 'TRIGGER';<br />

當然範例中僅使用到了 USER_ERRORS 與 ALL_DEPENDENCIES 兩個 system view，還有很多其他的 system tables & views 可作為它用，就之後再來慢慢探討了!