萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 電腦基礎知識 >  >> 電腦基本常識

Oracle Trigger(觸發器)完整學習筆記:類型、語法、範例與最佳實務

什麼是 Trigger?

Trigger(觸發器)是儲存在資料庫中、由特定事件(DML/DDL/系統/使用者事件)自動執行的 PL/SQL 區塊。常用於:

  • 資料完整性與商業邏輯強制執行
  • 稽核日誌(Audit Trail)
  • 自動衍生欄位(如自增序號、衍生計算)
  • 防止無效交易、複雜安全性檢查
  • 視圖的 INSTEAD OF 更新

Trigger 核心組成

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE | DELETE | ...} [OF column_list]
ON table_name | view_name | DATABASE | SCHEMA
[REFERENCING OLD AS old_name NEW AS new_name]
[FOR EACH ROW [WHEN (condition)]]
[FOLLOWS | PRECEDES other_trigger]
[ENABLE | DISABLE]
DECLARE
  -- 變數宣告
BEGIN
  -- 觸發器主體:PL/SQL 程式碼
  NULL;
END;
/

四大要素

  1. 觸發器名稱:命名慣例建議 biufer_table_column(b=before, i=insert, u=update, f=for each row, er=row level)。
  2. 觸發事件/時機:BEFORE/AFTER/INSTEAD OF + DML 或 DDL 事件。
  3. 觸發限制 (WHEN):可選,僅 ROW LEVEL 可用,條件成立才執行主體。
  4. 觸發動作 (主體):BEGIN ... END; 區塊,不可包含 COMMIT/ROLLBACK(DDL 隱含 COMMIT 亦不可用)。

五大 Trigger 類型詳解

1. Statement-Level Trigger(語句級)

無論影響多少列,每個觸發事件僅執行一次。適用於安全檢查、整張表級稽核。

CREATE OR REPLACE TRIGGER biud_foo
BEFORE INSERT OR UPDATE OR DELETE ON foo
BEGIN
  IF USER NOT IN ('DONNY') THEN
    RAISE_APPLICATION_ERROR(-20001, 'You don''t have access to modify this table.');
  END IF;
END;
/

2. Row-Level Trigger(列級)

加上 FOR EACH ROW,每受影響的一列執行一次。可透過 :OLD、:NEW 存取舊/新值。

CREATE OR REPLACE TRIGGER bifer_foo_id_pk
BEFORE INSERT ON foo
FOR EACH ROW
BEGIN
  SELECT foo_seq.NEXTVAL INTO :NEW.id FROM dual;
END;
/

:OLD / :NEW 規則:INSERT 只有 :NEW;DELETE 只有 :OLD;UPDATE 兩者皆有。

3. INSTEAD OF Trigger(視圖更新)

用於不可直接更新的複雜視圖,由觸發器轉發至基底表。

CREATE OR REPLACE TRIGGER update_name_company_phone_book
INSTEAD OF UPDATE ON hr.company_phone_book
BEGIN
  UPDATE hr.employees
  SET first_name = SUBSTR(:NEW.name, INSTR(:NEW.name, ',')+2),
      last_name  = SUBSTR(:NEW.name, 1, INSTR(:NEW.name, ',')-1),
      phone_number = :NEW.phone_number,
      email      = :NEW.email
  WHERE employee_id = :OLD.emp_id;
END;
/

4. System Event Trigger(系統事件)

資料庫啟動/關閉、Server Error 等。

CREATE TRIGGER ad_startup
AFTER STARTUP ON DATABASE
BEGIN
  -- 執行啟動後初始化工作
  NULL;
END;
/

5. User Event Trigger(使用者事件)

LOGON/LOGOFF、DDL 操作(CREATE/ALTER/DROP...)等。

CREATE OR REPLACE TRIGGER log_drop_trigger
BEFORE DROP ON DONNY.SCHEMA
BEGIN
  INSERT INTO dropped_objects (object_name, object_type, dropped_on)
  VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);
END;
/

實戰範例:稽核日誌表

-- 1. 建立測試表與日誌表
CREATE TABLE employees_copy AS SELECT * FROM hr.employees;
CREATE TABLE employees_log (
  who     VARCHAR2(30),
  action  VARCHAR2(20),
  when_dt DATE
);

-- 2. Statement-Level 稽核觸發器
CREATE OR REPLACE TRIGGER biud_employee_copy
BEFORE INSERT OR UPDATE OR DELETE ON employees_copy
DECLARE
  l_action employees_log.action%TYPE;
BEGIN
  IF INSERTING THEN l_action := 'INSERT';
  ELSIF UPDATING THEN l_action := 'UPDATE';
  ELSIF DELETING THEN l_action := 'DELETE';
  END IF;
  INSERT INTO employees_log (who, action, when_dt)
  VALUES (USER, l_action, SYSDATE);
END;
/

啟用/停用與管理

ALTER TRIGGER trigger_name DISABLE;
ALTER TRIGGER trigger_name ENABLE;
-- 查看所有觸發器
SELECT trigger_name, trigger_type, triggering_event, table_name, status
FROM user_triggers;
-- DBA 檢視所有使用者
SELECT * FROM dba_triggers;

交易控制限制

  • Trigger 本體 不可 包含 COMMIT / ROLLBACK / SAVEPOINT。
  • DDL 陳述式隱含 COMMIT,故 Trigger 中亦不可執行 DDL(除非使用 AUTONOMOUS_TRANSACTION 獨立交易)。

常見陷阱與最佳實務

陷阱說明建議
遞迴觸發Trigger 內執行 DML 再次觸發同一 Trigger用套件變數旗標或 WHEN 條件防止
效能影響Row-level 大量異動時逐列執行極慢改用 Statement-level 或批次處理
隱含 COMMITDDL 導致隱含提交,破壞交易一致性避免在 Trigger 執行 DDL,或用自治交易
命名混淆:NEW/:OLD 與資料表同名衝突善用 REFERENCING 子句重新命名
權限不足觸發器擁有者需有基底物件權限確保 GRANT 權限給 Trigger Owner

除錯技巧

  • 使用 DBMS_OUTPUT.PUT_LINE 配合 SQL*Plus / SQL Developer 除錯。
  • 建立測試腳本,單元測試每個觸發條件(INSERT/UPDATE/DELETE)。
  • 查詢 USER_ERRORS 檢視編譯錯誤。

總結

Trigger 是資料庫端強制商業規則、稽核、自動化衍生欄位的強大工具。善用 Statement/Row/INSTEAD OF/System/User 五大類型,配合 :OLD/:NEW、WHEN 條件、INSERTING/UPDATING/DELETING 謂詞,能實作靈活且健全的資料庫邏輯。開發時務必注意遞迴、效能、交易控制限制,並建立完整測試案例。

copyright © 萬盛學電腦網 all rights reserved