觸發器是Oracle數據庫中的一種特殊存儲過程，它會在特定數據庫事件發生時自動執行。觸發器通常用于實現復雜的業務規則、數據驗證、審計跟蹤等功能。
目錄
一、觸發器基本概念
1. 觸發器特點
2. 觸發器組成要素
二、觸發器類型
1. DML觸發器
2. DDL觸發器
3. 系統/數據庫事件觸發器
4. INSTEAD OF觸發器
三、創建DML觸發器
1. 基本語法
2. 行級觸發器示例
3. 語句級觸發器示例
四、特殊觸發器
1. 復合觸發器(11g+)
2. INSTEAD OF觸發器(用于視圖)
3. 系統事件觸發器
五、觸發器中的特殊變量和函數
1. OLD 和 NEW 偽記錄
2. 事件屬性函數
3. 條件謂詞
4. 簡單示例
5. 簡單練習
六、觸發器管理
1. 查看觸發器
2. 啟用/禁用觸發器
3. 重新編譯觸發器
4. 刪除觸發器
七、觸發器最佳實踐
八、常見問題解決方案
1. 避免觸發器遞歸
2. 處理大批量操作
3. 跨數據庫同步
九、觸發器使用注意事項
一、觸發器基本概念
觸發器在數據庫里以獨立的對象存儲，他與存儲過程不同的是，存儲過程通過其他程序來啟動運行或者直接運行，而觸發器是由一個事件來啟動運行。即觸發器是當某個事件發生時自動地隱式運行，并且觸發器不接收參數。?
1. 觸發器特點
-
自動執行：滿足條件時由數據庫自動觸發
-
事件驅動：響應特定DML或DDL操作
-
無顯式調用：不能像存儲過程那樣直接調用
-
事務感知：作為觸發語句的一部分執行
2. 觸發器組成要素
-
觸發事件：INSERT、UPDATE、DELETE等DML操作
-
觸發時機：BEFORE或AFTER
-
觸發級別：行級(FOR EACH ROW)或語句級
-
觸發條件：WHEN子句定義的條件
-
觸發體：PL/SQL代碼塊
二、觸發器類型
1. DML觸發器
響應數據操作語言(DML)事件：
-
INSERT
-
UPDATE
-
DELETE
-
MERGE (10g+)
2. DDL觸發器
響應數據定義語言(DDL)事件：
-
CREATE
-
ALTER
-
DROP
-
TRUNCATE等
3. 系統/數據庫事件觸發器
響應數據庫系統事件：
-
登錄/注銷(LOGON/LOGOFF)
-
服務器錯誤(SERVERERROR)
-
啟動/關閉(STARTUP/SHUTDOWN)
4. INSTEAD OF觸發器
用于視圖上的DML操作
三、創建DML觸發器
語句級觸發器(statement)：當某觸發事件發生時，該觸發器執行一次
行級觸發器(row)：當某觸發事件發生時，受到影響的每一行數據，觸發器都單獨執行一次
1. 基本語法
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE | DELETE [OR INSERT | UPDATE | DELETE...]}
ON {table_name | view_name}
[REFERENCING [OLD AS old] [NEW AS new]]
[FOR EACH ROW]
[WHEN (condition)]
[DECLAREdeclaration_statements]
BEGINexecutable_statements
[EXCEPTIONexception_handling_statements]
END [trigger_name];--化說明
create or replace trigger tri_name--創建觸發器
{before/after}--觸發的時間點{DML操作} on 表名
{for each row}--加上是行級觸發，不加是語句級
{when 條件}
begin
{referencing {OLD {as} old|new {as} NEW}new PARENT as parent}---觸發器的執行部分
end;
/-- 簡單示例1
create or replace trigger tr_1before delete on emp11
begininsert into emp12(ename)select ename from emp where empno = 7369;
end;
/
-- 簡單示例2
--創建一個觸發器 當刪除emp1表中的數據時，向emp2插入7788的員工信息
--并且刪除emp3中7788的信息。
-- 準備
create table emp1 as select * from emp;
create table emp2 as select * from emp;
create table emp3 as select * from emp;
-- 創建觸發器
create or replace trigger tr_1before delete on emp1
begininsert into emp2(ename, sal)select ename, sal from emp where empno = 7788;delete from emp3 where empno = 7788;
end;
/
-- 觸發
delete from emp1 where empno=7788;2. 行級觸發器示例
-- 薪資變更審計觸發器
CREATE OR REPLACE TRIGGER audit_salary_change
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary <> OLD.salary)
DECLAREv_change_type VARCHAR2(10);
BEGIN-- 確定變更類型IF :NEW.salary > :OLD.salary THENv_change_type := 'RAISE';ELSEv_change_type := 'CUT';END IF;-- 記錄審計信息INSERT INTO salary_audit (audit_id, employee_id, old_salary, new_salary,change_type, change_date, changed_by) VALUES (audit_seq.NEXTVAL, :NEW.employee_id,:OLD.salary, :NEW.salary,v_change_type, SYSDATE, USER);
END audit_salary_change;
/3. 語句級觸發器示例
-- 限制非工作時間操作
CREATE OR REPLACE TRIGGER restrict_after_hours_dml
BEFORE INSERT OR UPDATE OR DELETE ON employees
DECLAREv_current_time VARCHAR2(8) := TO_CHAR(SYSDATE, 'HH24:MI:SS');v_day_of_week VARCHAR2(3) := TO_CHAR(SYSDATE, 'DY');
BEGIN-- 工作日晚上8點到早上6點禁止操作IF v_day_of_week NOT IN ('SAT', 'SUN') AND (v_current_time > '20:00:00' OR v_current_time < '06:00:00') THENRAISE_APPLICATION_ERROR(-20001, '非工作時間(工作日6:00-20:00)不允許修改員工數據');END IF;
END restrict_after_hours_dml;
/四、特殊觸發器
1. 復合觸發器(11g+)
CREATE OR REPLACE TRIGGER compound_emp_trigger
FOR INSERT OR UPDATE OR DELETE ON employees
COMPOUND TRIGGER-- 在觸發語句開始前執行BEFORE STATEMENT ISBEGINDBMS_OUTPUT.PUT_LINE('開始員工數據變更操作');END BEFORE STATEMENT;-- 每行變更前執行BEFORE EACH ROW ISBEGINDBMS_OUTPUT.PUT_LINE('準備變更員工ID: ' || NVL(:NEW.employee_id, :OLD.employee_id));END BEFORE EACH ROW;-- 每行變更后執行AFTER EACH ROW ISBEGINDBMS_OUTPUT.PUT_LINE('已完成變更員工ID: ' || NVL(:NEW.employee_id, :OLD.employee_id));END AFTER EACH ROW;-- 在觸發語句結束后執行AFTER STATEMENT ISBEGINDBMS_OUTPUT.PUT_LINE('員工數據變更操作完成');END AFTER STATEMENT;
END compound_emp_trigger;
/2. INSTEAD OF觸發器(用于視圖)
-- 創建復雜視圖
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT e.employee_id, e.last_name, e.salary, d.department_id, d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;-- 創建INSTEAD OF觸發器支持插入
CREATE OR REPLACE TRIGGER io_emp_dept_insert
INSTEAD OF INSERT ON emp_dept_view
FOR EACH ROW
DECLAREv_dept_id NUMBER;
BEGIN-- 檢查部門是否存在BEGINSELECT department_id INTO v_dept_idFROM departmentsWHERE department_name = :NEW.department_name;EXCEPTIONWHEN NO_DATA_FOUND THENRAISE_APPLICATION_ERROR(-20001, '部門 ' || :NEW.department_name || ' 不存在');END;-- 插入員工記錄INSERT INTO employees (employee_id, last_name, salary, department_id) VALUES (employees_seq.NEXTVAL, :NEW.last_name, :NEW.salary, v_dept_id);
END io_emp_dept_insert;
/3. 系統事件觸發器
-- 登錄審計觸發器
CREATE OR REPLACE TRIGGER logon_audit_trigger
AFTER LOGON ON DATABASE
BEGININSERT INTO logon_audit (audit_id, username, logon_time, host, ip_address) VALUES (logon_seq.NEXTVAL, USER,SYSDATE, SYS_CONTEXT('USERENV', 'HOST'),SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
END logon_audit_trigger;
/-- DDL變更審計觸發器
CREATE OR REPLACE TRIGGER audit_ddl_changes
AFTER DDL ON SCHEMA
BEGININSERT INTO ddl_audit (audit_id, username, event_type, object_type,object_name, change_date) VALUES (ddl_audit_seq.NEXTVAL, USER,ORA_SYSEVENT, ORA_DICT_OBJ_TYPE,ORA_DICT_OBJ_NAME, SYSDATE);
END audit_ddl_changes;
/五、觸發器中的特殊變量和函數
1. OLD 和 NEW 偽記錄
-
:OLD：引用操作前的行值(UPDATE/DELETE)---old.列名：此列中的舊值
-
:NEW：引用操作后的行值(INSERT/UPDATE)---new.列名：此列中的新值
2. 事件屬性函數
-
ORA_SYSEVENT：觸發事件的名稱
-
ORA_DICT_OBJ_TYPE：DDL操作的對象類型
-
ORA_DICT_OBJ_NAME：DDL操作的對象名稱
-
ORA_IS_ALTER_COLUMN：檢查是否修改了特定列
3. 條件謂詞
-
INSERTING：觸發器由INSERT觸發時為TRUE
-
UPDATING：觸發器由UPDATE觸發時為TRUE
-
DELETING：觸發器由DELETE觸發時為TRUE
| 條件謂語 | old | new |
| update | 有 | 有 |
| delete | 有 | null |
| insert | null | 有 |
4. 簡單示例
--創建一個觸發器
--當emp1被更新是,將更新前和更新后的ename和empno插入到emp2
--的對應列，在job列顯示更新前還是更新后,并且hiredate列插入更新時間
CREATE OR REPLACE TRIGGER TRE_1BEFORE UPDATE ON EMP1FOR EACH ROW
BEGININSERT INTO EMP2 (ENAME, EMPNO,job,hiredate) VALUES (:old.ename,:old.empno,'更新前',sysdate);INSERT INTO EMP2 (ENAME, EMPNO,job,hiredate) VALUES (:new.ename,:new.empno,'更新后',SYSDATE);
END;TRUNCATE TABLE emp2
UPDATE emp1 SET sal=sal+10000;
SELECT * FROM emp2| 特性 | INSERT | UPDATE | DELETE |
| OLD | NULL | 實際值 | 實際值 |
| NEW | 實際值 | 實際值 | NULL |
5. 簡單練習
--創建一張emp1和emp內容一樣。創建emp2只保留emp的格式。
--創建一個觸發器
--當emp1表被更新時觸發，將更新前和更新后的ename,empno插入到emp2中
-- 創建觸發器
create or replace trigger tr_3
before update on emp1
for each rowbegininsert into emp2 (ename,empno,job) values(:old.ename,:old.empno,'old');insert into emp2 (ename,empno,job) values(:new.ename,:new.empno,'new');
end;
/
-- 觸發
update emp1 set empno=empno-7000,ename=lower(ename);
-- 查詢驗證變化
select * from emp1;
select * from emp2;六、觸發器管理
1. 查看觸發器
-- 查看觸發器定義
SELECT trigger_name, trigger_type, triggering_event, table_name, status
FROM user_triggers;-- 查看觸發器源代碼
SELECT text FROM user_source
WHERE name = 'AUDIT_SALARY_CHANGE' AND type = 'TRIGGER'
ORDER BY line;2. 啟用/禁用觸發器
-- 禁用單個觸發器
ALTER TRIGGER audit_salary_change DISABLE;-- 啟用單個觸發器
ALTER TRIGGER audit_salary_change ENABLE;-- 禁用表上的所有觸發器
ALTER TABLE employees DISABLE ALL TRIGGERS;-- 啟用表上的所有觸發器
ALTER TABLE employees ENABLE ALL TRIGGERS;3. 重新編譯觸發器
ALTER TRIGGER trigger_name COMPILE;4. 刪除觸發器
DROP TRIGGER trigger_name;七、觸發器最佳實踐
-
保持簡潔：觸發器應簡短高效，避免復雜業務邏輯
-
避免遞歸：注意觸發器可能導致的級聯觸發
-
考慮性能：行級觸發器對大批量操作影響較大
-
明確文檔：記錄觸發器目的和業務規則
-
異常處理：妥善處理可能出現的錯誤
-
避免事務控制：通常不應在觸發器中提交或回滾
-
測試充分：驗證觸發器在各種場景下的行為
八、常見問題解決方案
1. 避免觸發器遞歸
CREATE OR REPLACE TRIGGER prevent_recursion
BEFORE UPDATE ON employees
FOR EACH ROW
DECLAREv_recursion_flag BOOLEAN := FALSE;
BEGIN-- 檢查是否由觸發器調用IF UPDATING AND DBMS_UTILITY.FORMAT_CALL_STACK LIKE '%PREVENT_RECURSION%' THENv_recursion_flag := TRUE;END IF;-- 如果不是遞歸調用，則執行業務邏輯IF NOT v_recursion_flag THEN-- 業務邏輯代碼END IF;
END;
/2. 處理大批量操作
-- 使用BULK COLLECT和FORALL優化
CREATE OR REPLACE TRIGGER optimize_bulk_operation
AFTER INSERT ON large_table
DECLARETYPE id_array IS TABLE OF large_table.id%TYPE;v_ids id_array;
BEGIN-- 批量收集新插入的IDSELECT id BULK COLLECT INTO v_idsFROM large_tableWHERE status = 'NEW';-- 批量處理FORALL i IN 1..v_ids.COUNTUPDATE related_tableSET last_updated = SYSDATEWHERE large_table_id = v_ids(i);
END;
/3. 跨數據庫同步
CREATE OR REPLACE TRIGGER sync_cross_database
AFTER INSERT OR UPDATE OR DELETE ON local_table
FOR EACH ROW
DECLAREPRAGMA AUTONOMOUS_TRANSACTION;
BEGINIF INSERTING THENINSERT INTO remote_table@remote_db VALUES (:NEW.id, :NEW.name);ELSIF UPDATING THENUPDATE remote_table@remote_dbSET name = :NEW.nameWHERE id = :OLD.id;ELSIF DELETING THENDELETE FROM remote_table@remote_db WHERE id = :OLD.id;END IF;COMMIT;
EXCEPTIONWHEN OTHERS THENROLLBACK;-- 記錄錯誤但不中斷主事務INSERT INTO error_log VALUES (SYSDATE, 'sync_cross_database', SQLERRM);COMMIT;
END;
/九、觸發器使用注意事項
編寫觸發器時，需要注意一下幾點：
（1）觸發器不接受參數
（2）一個表上最多可有12個觸發器，但同一時間、同一事件、同一類型的觸發器只能有一個。并且各觸發器之間不能有矛盾。
（3）在一個表上的觸發器越多，對在該表上的DML操作的性能影響就越大
（4）觸發器最大為32KB。若確實需要，可以先建立過程，然后在觸發器中用CALL語句進行調用
（5）在觸發器的執行部分只能用DML語句（SELECT、INSERT、UDATE、DELETE）,不能使用DDL語句（CREATE、ALTER、DROP）
（6）觸發器中不能包含事物控制語句（COMMIT、ROLLBACK、SAVEPOINT）。因為觸發器是觸發語句的一部分，觸發語句被提交、回退時，觸發器也被提交、回退了。
（7）在觸發器主體中調用的任何過程、函數，都不能使用事務控制語句
（8）在觸發器主體中不能聲明任何Long和Blob變量。新值new和舊值old也不能向表中的任何Long和Blob列。
（9）不同類型的觸發器（如DML觸發器、INSTEAD OF觸發器、系統觸發器）的語法格式和作用有比較大區別
觸發器是Oracle數據庫強大的功能，合理使用可以實現復雜的業務規則、數據完整性和審計需求。但也需謹慎使用，避免過度依賴觸發器導致系統難以維護。
