Trigger(觸發器)是儲存在資料庫中、由特定事件(DML/DDL/系統/使用者事件)自動執行的 PL/SQL 區塊。常用於:
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;
/
biufer_table_column(b=before, i=insert, u=update, f=for each row, er=row level)。無論影響多少列,每個觸發事件僅執行一次。適用於安全檢查、整張表級稽核。
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;
/
加上 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 兩者皆有。
用於不可直接更新的複雜視圖,由觸發器轉發至基底表。
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;
/
資料庫啟動/關閉、Server Error 等。
CREATE TRIGGER ad_startup
AFTER STARTUP ON DATABASE
BEGIN
-- 執行啟動後初始化工作
NULL;
END;
/
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;
COMMIT / ROLLBACK / SAVEPOINT。AUTONOMOUS_TRANSACTION 獨立交易)。| 陷阱 | 說明 | 建議 |
|---|---|---|
| 遞迴觸發 | Trigger 內執行 DML 再次觸發同一 Trigger | 用套件變數旗標或 WHEN 條件防止 |
| 效能影響 | Row-level 大量異動時逐列執行極慢 | 改用 Statement-level 或批次處理 |
| 隱含 COMMIT | DDL 導致隱含提交,破壞交易一致性 | 避免在 Trigger 執行 DDL,或用自治交易 |
| 命名混淆 | :NEW/:OLD 與資料表同名衝突 | 善用 REFERENCING 子句重新命名 |
| 權限不足 | 觸發器擁有者需有基底物件權限 | 確保 GRANT 權限給 Trigger Owner |
DBMS_OUTPUT.PUT_LINE 配合 SQL*Plus / SQL Developer 除錯。USER_ERRORS 檢視編譯錯誤。Trigger 是資料庫端強制商業規則、稽核、自動化衍生欄位的強大工具。善用 Statement/Row/INSTEAD OF/System/User 五大類型,配合 :OLD/:NEW、WHEN 條件、INSERTING/UPDATING/DELETING 謂詞,能實作靈活且健全的資料庫邏輯。開發時務必注意遞迴、效能、交易控制限制,並建立完整測試案例。