2011-05-11

Oracle Triggers 觸發器

Oracle Database triggers 的使用時機:想要於資料庫有變化時,自動執行某種程序,如:
  • 當資料庫伺服器發生資料操作語言(DML) 事件-包含對資料表或檢視發出的 UPDATE、INSERT 或 DELETE 陳述式。
    觸發程序可協助強制執行商業規則,以及當資料表或檢視中的資料修改時協助擴充資料的完整性。

  • 當資料庫伺服器發生「資料定義語言」(Data Definition Language,DDL) 事件-包含 CREATE、ALTER、DROP 陳述式。
    觸發程序可以用來執行資料庫中的管理工作,例如稽核與管理資料庫作業。

Oracle Database triggers 的五種類型
  1. Statement triggers

    此種 trigger 跟 DML 操作(例如:DELETE、INSERT、UPDATE)有關聯,但每一次的 DML 操作僅會觸發一次 trigger。

    雖然設定在 DML 操作上的 trigger 在 DML 操作發生時僅會出發一次,但可以同時設定多個 trigger 在同一個 DML 操作上,而且還可以透過「FOLLOW」、「PRECEDES」… 等關鍵字來決定多個 trigger 觸發的先後順序。

  2. Row triggers
    當 table 中的每一列資料發生 INSERT、UPDATE 或是 DELETE 時,此種 trigger 就會觸發。大致跟 statement trigger 運作方式相同,但 row trigger 是針對「每一列」資料進行觸發;因此假設一個 DML 同時影響很多筆資料時,statement trigger 僅會出發一次,但 row trigger 卻會觸發很多次。

  3. INSTEAD OF triggers
    可以用在當使用者針對 VIEW 或是衍生欄位進行操作時,可透過 trigger 將資料進行正確的資料處理。

  4. User event triggers
    用於 DDL(例如:CREATE、ALTER、DROP、USER LOGON/LOGOFF … 等等) 以及特定的 DML 操作(分析、統計、稽核、使用者權限管理)上。

  5. System event triggers
    當資料庫啟動、關閉、或是發生錯誤的事件發生時,會進行觸發。
Trigger 語法與部份說明:

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

[WHEN condition]

BEGIN
triggered_action;
END
  • trigger name 在同一個 schema 中,trigger name 必須是唯一的。

  • {BEFORE | AFTER | INSTEAD OF}
    即該TRIGGER 是在觸發事件發生之前(BEFORE)還是之後(AFTER)觸發,也就是觸發事件和該TRIGGER 的操作順序。不是每個事件都有前後可以設定,例如:STARTUP、SUSPEND、LOGON … 等事件,就僅有 AFTER 可以用;而 SHUTDOWN、LOGOFF … 等事件就僅有 BEFORE 可以用。

  • {INSERT | DELETE | UPDATE [OF column [, column …]]} ON {[schema.] table_name | [schema.] view_name}

    何種情況下觸發TRIGGER; 例如:INSERT, UPDATE, DELETE。

  • REFERENCING
    說明相關名稱,在ROW Triggers的PL/SQL塊和WHEN 子句中可以使用相關名稱參照當前的新、舊列值,預設的相關名稱分別為OLD和NEW。觸發器的PL/SQL塊中應用相關名稱時,必須在它們之前加冒號(:),但在WHEN子句中則不能加冒號。

  • WHEN
    說明觸發約束條件。Condition 為一個邏輯表達時,其中必須包含相關名稱,而不能包含查詢語句,也不能調用PL/SQL 函數。WHEN 子句指定的觸發約束條件只能用在ROW Triggers的BEFORE 和AFTER 不能用在INSTEAD OF。
  • triggered_action:trigger 所要執行的程式內容。



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 在裡面了。

<範例>
資料表 employees 內容若有修改,記錄修改的使用者、時間、及修改的型態到employees_log。(在employees建立Row triggers 名為 biud_employee)

Create or replace trigger biud_employee Before insert or update or delete On employees FOR EACH ROW;

Begin
if inserting then l_action:=’Insert’;
elsif updating then l_action:=’Update’;
elsif deleting then l_action:=’Delete’;
else raise_application_error(-20001,’You should never ever get this error.’);
Insert into employees_log(Who,action,when) Values( user, l_action,sysdate);
End;


參考網站:

沒有留言: