DB / Oracle / SQL 开发

Oracle 触发器（Trigger）详解

2026.09.06

Oracle 触发器（Trigger）详解

适用版本：Oracle Database 8i / 9i / 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

触发器（Trigger） 在特定事件发生时自动执行[1]：

类型说明
DML 触发器INSERT/UPDATE/DELETE
DDL 触发器CREATE/ALTER/DROP
数据库事件STARTUP/SHUTDOWN/LOGON/LOGOFF
INSTEAD OF视图替代触发

2. DML 触发器

2.1 语法

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE | DELETE [OR ...]}
ON table_name
[FOR EACH ROW]
[WHEN condition]
[REFERENCING OLD AS old NEW AS new]
DECLARE
  -- 声明
BEGIN
  -- 逻辑
END;
/

2.2 BEFORE 触发器

-- BEFORE INSERT：数据校验
CREATE OR REPLACE TRIGGER trg_check_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  IF :NEW.salary < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary must be positive');
  END IF;
  
  IF :NEW.salary > 100000 THEN
    RAISE_APPLICATION_ERROR(-20002, 'Salary too high');
  END IF;
END;
/

2.3 AFTER 触发器

-- AFTER UPDATE：审计日志
CREATE OR REPLACE TRIGGER trg_audit_salary
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  INSERT INTO salary_audit (
    emp_id, old_salary, new_salary, 
    change_date, changed_by
  ) VALUES (
    :OLD.employee_id, :OLD.salary, :NEW.salary,
    SYSDATE, USER
  );
END;
/

2.4 :OLD 和 :NEW

事件:OLD:NEW
INSERTNULL插入值
UPDATE旧值新值
DELETE删除值NULL

2.5 多事件触发器

CREATE OR REPLACE TRIGGER trg_emp_changes
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
  v_action VARCHAR2(20);
BEGIN
  IF INSERTING THEN
    v_action := 'INSERT';
    :NEW.create_date := SYSDATE;
    :NEW.created_by := USER;
  ELSIF UPDATING THEN
    v_action := 'UPDATE';
    :NEW.update_date := SYSDATE;
    :NEW.updated_by := USER;
  ELSIF DELETING THEN
    v_action := 'DELETE';
    INSERT INTO deleted_emps VALUES (:OLD.employee_id, :OLD.last_name, SYSDATE);
  END IF;
END;
/

3. 语句级 vs 行级

3.1 语句级（默认）

-- 整个语句触发一次
CREATE OR REPLACE TRIGGER trg_log_dept_change
AFTER UPDATE ON departments
BEGIN
  INSERT INTO dept_change_log VALUES (SYSDATE, USER);
END;
/

3.2 行级（FOR EACH ROW）

-- 每行触发一次
CREATE OR REPLACE TRIGGER trg_check_dept
AFTER UPDATE ON departments
FOR EACH ROW
BEGIN
  INSERT INTO dept_audit VALUES (:OLD.id, :NEW.id, SYSDATE);
END;
/

4. INSTEAD OF 触发器

4.1 视图触发

-- 视图
CREATE VIEW emp_dept_view AS
SELECT e.employee_id, e.last_name, e.dept_id, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id;

-- INSTEAD OF
CREATE OR REPLACE TRIGGER trg_emp_dept_view
INSTEAD OF INSERT ON emp_dept_view
FOR EACH ROW
BEGIN
  INSERT INTO employees (employee_id, last_name, dept_id)
  VALUES (:NEW.employee_id, :NEW.last_name, :NEW.dept_id);
END;
/

5. 复合触发器（11g+）

5.1 优势

  • 避免 ORA-04091（变异表错误）
  • 共享变量
  • 性能优化

5.2 语法

CREATE OR REPLACE COMPOUND TRIGGER trg_compound_emp
  -- 公共变量
  v_count NUMBER := 0;
  TYPE id_list IS TABLE OF NUMBER;
  v_ids id_list := id_list();
  
  BEFORE STATEMENT IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Before statement');
  END BEFORE STATEMENT;
  
  BEFORE EACH ROW IS
  BEGIN
    v_count := v_count + 1;
    :NEW.update_date := SYSDATE;
  END BEFORE EACH ROW;
  
  AFTER EACH ROW IS
  BEGIN
    v_ids.EXTEND;
    v_ids(v_ids.COUNT) := :NEW.employee_id;
  END AFTER EACH ROW;
  
  AFTER STATEMENT IS
  BEGIN
    FORALL i IN 1..v_ids.COUNT
      INSERT INTO emp_log VALUES (v_ids(i), SYSDATE);
    DBMS_OUTPUT.PUT_LINE('Total: ' || v_count);
  END AFTER STATEMENT;
END trg_compound_emp;
/

6. DDL 触发器

CREATE OR REPLACE TRIGGER trg_ddl_audit
AFTER CREATE OR ALTER OR DROP ON SCHEMA
DECLARE
  v_event VARCHAR2(30);
  v_obj VARCHAR2(100);
BEGIN
  v_event := ORA_DICT_OBJ_TYPE || ' ' || ORA_SYSEVENT;
  v_obj := ORA_DICT_OBJ_NAME;
  
  INSERT INTO ddl_audit (event, object_name, owner, event_date)
  VALUES (v_event, v_obj, ORA_DICT_OBJ_OWNER, SYSDATE);
END;
/

7. 数据库事件触发器

-- LOGON 触发
CREATE OR REPLACE TRIGGER trg_logon
AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO logon_audit (username, logon_time, ip)
  VALUES (USER, SYSDATE, SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
END;
/

-- STARTUP 触发
CREATE OR REPLACE TRIGGER trg_startup
AFTER STARTUP ON DATABASE
BEGIN
  INSERT INTO startup_log VALUES (SYSDATE);
END;
/

8. 触发器管理

8.1 启用/禁用

ALTER TRIGGER trg_name DISABLE;
ALTER TRIGGER trg_name ENABLE;

-- 表所有触发器
ALTER TABLE employees DISABLE ALL TRIGGERS;
ALTER TABLE employees ENABLE ALL TRIGGERS;

8.2 查看

SELECT trigger_name, trigger_type, triggering_event, status
FROM user_triggers
WHERE table_name = 'EMPLOYEES';

8.3 删除

DROP TRIGGER trg_name;

9. 触发器顺序

9.1 同一事件多个触发器

-- 11g+ 指定顺序
CREATE OR REPLACE TRIGGER trg_first
BEFORE INSERT ON employees
FOR EACH ROW
FOLLOWS trg_other
BEGIN
  ...
END;
/

9.2 执行顺序

1. BEFORE STATEMENT
2. BEFORE EACH ROW
3. DML 操作
4. AFTER EACH ROW
5. AFTER STATEMENT

10. 常见坑与排错

10.1 ORA-04091: 变异表

-- 错误：触发器查询自身表
CREATE OR REPLACE TRIGGER trg_mutating
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  SELECT COUNT(*) INTO v_count FROM employees;  -- 错误
END;
/

-- 修复：使用复合触发器或 PRAGMA AUTONOMOUS_TRANSACTION
CREATE OR REPLACE TRIGGER trg_mutating
AFTER INSERT ON employees
FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM employees;
  COMMIT;
END;
/

10.2 递归触发

-- 触发器引起自身触发
-- 修复：逻辑检查
IF UPDATING('salary') THEN
  -- 不触发其他列更新
END IF;

10.3 性能差

修复：

-- 1. 避免行级触发器复杂逻辑
-- 2. 使用复合触发器
-- 3. 逻辑移到过程

10.4 :NEW 不可修改（AFTER）

-- AFTER 触发器不能修改 :NEW
-- 使用 BEFORE 触发器

11. 最佳实践

  1. 慎用触发器：易遗忘
  2. 优先约束：CHECK/FOREIGN KEY
  3. 审计用 AFTER：记录已发生
  4. 校验用 BEFORE：阻止错误
  5. 避免变异表：复合触发器
  6. 性能谨慎：行级触发慢
  7. 逻辑清晰：单一职责
  8. 异常处理：避免隐藏错误
  9. 文档完整：说明用途
  10. 测试充分：验证逻辑

12. 参考资料

[1] Oracle Database PL/SQL Language Reference 19c, “Triggers” https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/triggers.html