Oracle PL/SQL 触发器详解

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

---

1. 概述

PL/SQL 触发器是自动执行的程序单元[1]：

类型：

• DML 触发器
• DDL 触发器
• 数据库事件触发器
• INSTEAD OF 触发器

详细见：Oracle PL/SQL 基础与块结构。

---

2. DML 触发器

2.1 基本

CREATE OR REPLACE TRIGGER trg_audit_emp
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
  v_user VARCHAR2(30);
BEGIN
  v_user := SYS_CONTEXT('USERENV', 'OS_USER');
  
  INSERT INTO emp_audit (emp_id, action, old_salary, new_salary, action_user, action_time)
  VALUES (
    :NEW.id,
    CASE 
      WHEN INSERTING THEN 'INSERT'
      WHEN UPDATING THEN 'UPDATE'
      WHEN DELETING THEN 'DELETE'
    END,
    :OLD.salary,
    :NEW.salary,
    v_user,
    SYSTIMESTAMP
  );
END;
/

2.2 触发时机

BEFORE  - 操作前
AFTER   - 操作后

2.3 级别

STATEMENT       - 语句级（一次）
FOR EACH ROW    - 行级（每行）

2.4 事件

INSERT / UPDATE / DELETE
UPDATE OF col1, col2  - 特定列

2.5 :NEW / :OLD

事件	:OLD	:NEW
INSERT	NULL	新值
UPDATE	旧值	新值
DELETE	旧值	NULL

2.6 条件

CREATE OR REPLACE TRIGGER trg_check_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary > 0)
BEGIN
  IF :NEW.salary > 100000 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary too high');
  END IF;
END;
/

---

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

CREATE OR REPLACE TRIGGER trg_compound_emp
FOR INSERT OR UPDATE ON employees
COMPOUND TRIGGER
  -- 共享状态
  TYPE emp_list IS TABLE OF employees%ROWTYPE;
  v_emp emp_list := emp_list();
  
  BEFORE STATEMENT IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Before statement');
  END BEFORE STATEMENT;
  
  BEFORE EACH ROW IS
  BEGIN
    :NEW.updated_at := SYSTIMESTAMP;
  END BEFORE EACH ROW;
  
  AFTER EACH ROW IS
  BEGIN
    v_emp.EXTEND;
    v_emp(v_emp.LAST) := :NEW;
  END AFTER EACH ROW;
  
  AFTER STATEMENT IS
  BEGIN
    FORALL i IN 1..v_emp.COUNT
      INSERT INTO emp_log VALUES (v_emp(i).id, SYSTIMESTAMP);
  END AFTER STATEMENT;
END trg_compound_emp;
/

---

4. INSTEAD OF 触发器

4.1 视图

CREATE VIEW emp_dept_view AS
SELECT e.id, e.name, e.salary, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id;

-- 视图不可直接 DML
-- INSTEAD OF 触发器

CREATE OR REPLACE TRIGGER trg_emp_dept_view
INSTEAD OF INSERT ON emp_dept_view
FOR EACH ROW
BEGIN
  INSERT INTO employees (id, name, salary)
  VALUES (:NEW.id, :NEW.name, :NEW.salary);
END;
/

INSERT INTO emp_dept_view VALUES (1, 'Alice', 5000, 'IT');

---

5. DDL 触发器

CREATE OR REPLACE TRIGGER trg_ddl_audit
BEFORE CREATE OR ALTER OR DROP ON SCHEMA
DECLARE
  v_obj VARCHAR2(30);
BEGIN
  v_obj := ORA_DICT_OBJ_NAME;
  
  INSERT INTO ddl_audit (event, obj_type, obj_name, obj_owner, action_user, action_time)
  VALUES (
    ORA_SYSEVENT,
    ORA_DICT_OBJ_TYPE,
    v_obj,
    ORA_DICT_OBJ_OWNER,
    SYS_CONTEXT('USERENV', 'OS_USER'),
    SYSTIMESTAMP
  );
END;
/

5.1 数据库级

CREATE OR REPLACE TRIGGER trg_db_ddl
AFTER CREATE ON DATABASE
BEGIN
  -- 记录所有 DDL
  INSERT INTO db_ddl_log ...;
END;
/

---

6. 数据库事件触发器

CREATE OR REPLACE TRIGGER trg_startup
AFTER STARTUP ON DATABASE
BEGIN
  INSERT INTO db_events (event, time) VALUES ('STARTUP', SYSTIMESTAMP);
END;
/

CREATE OR REPLACE TRIGGER trg_shutdown
BEFORE SHUTDOWN ON DATABASE
BEGIN
  INSERT INTO db_events (event, time) VALUES ('SHUTDOWN', SYSTIMESTAMP);
END;
/

CREATE OR REPLACE TRIGGER trg_logon
AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO log_audit (username, logon_time) 
  VALUES (SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSTIMESTAMP);
END;
/

CREATE OR REPLACE TRIGGER trg_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
  INSERT INTO log_audit (username, logoff_time) 
  VALUES (SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSTIMESTAMP);
END;
/

CREATE OR REPLACE TRIGGER trg_servererror
AFTER SERVERERROR ON DATABASE
BEGIN
  INSERT INTO error_log (error_code, error_msg, time)
  VALUES (ORA_SERVER_ERROR(1), ORA_SERVER_ERROR_MSG(1), SYSTIMESTAMP);
END;
/

---

7. 启用/禁用

ALTER TRIGGER trg_audit_emp DISABLE;
ALTER TRIGGER trg_audit_emp ENABLE;

ALTER TABLE employees DISABLE ALL TRIGGERS;
ALTER TABLE employees ENABLE ALL TRIGGERS;

---

8. 编译

ALTER TRIGGER trg_audit_emp COMPILE;

SELECT object_name, status FROM user_objects WHERE object_type = 'TRIGGER';

---

9. 查看

SELECT trigger_name, trigger_type, triggering_event, table_name, status
FROM user_triggers;

SELECT trigger_body FROM user_triggers WHERE trigger_name = 'TRG_AUDIT_EMP';

---

10. 删除

DROP TRIGGER trg_audit_emp;

---

11. 触发顺序

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

---

12. 限制

12.1 不能使用

• COMMIT / ROLLBACK（非自治）
• SAVEPOINT
• 事务控制
• DDL

12.2 自治事务

CREATE OR REPLACE TRIGGER trg_log
AFTER INSERT ON employees
FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO log VALUES (:NEW.id, SYSTIMESTAMP);
  COMMIT;
END;
/

---

13. 变异表

13.1 错误

ORA-04091: table xxx is mutating

13.2 解决

• 复合触发器（11g+）
• 临时表
• 自治事务

CREATE OR REPLACE TRIGGER trg_emp_check
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
COMPOUND TRIGGER
  TYPE id_list IS TABLE OF NUMBER;
  v_ids id_list := id_list();
  
  AFTER EACH ROW IS
  BEGIN
    v_ids.EXTEND;
    v_ids(v_ids.LAST) := :NEW.dept_id;
  END AFTER EACH ROW;
  
  AFTER STATEMENT IS
    v_count NUMBER;
  BEGIN
    FOR i IN 1..v_ids.COUNT LOOP
      SELECT COUNT(*) INTO v_count FROM employees WHERE dept_id = v_ids(i);
      -- 检查
    END LOOP;
  END AFTER STATEMENT;
END;
/

---

14. 应用场景

14.1 审计

CREATE OR REPLACE TRIGGER trg_audit
AFTER INSERT OR UPDATE OR DELETE ON sensitive_table
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (table_name, action, old_data, new_data, user, time)
  VALUES ('SENSITIVE_TABLE', 
    CASE WHEN INSERTING THEN 'I' WHEN UPDATING THEN 'U' WHEN DELETING THEN 'D' END,
    :OLD.id, :NEW.id, USER, SYSTIMESTAMP);
END;
/

14.2 派生列

CREATE OR REPLACE TRIGGER trg_calc_total
BEFORE INSERT OR UPDATE OF quantity, price ON order_items
FOR EACH ROW
BEGIN
  :NEW.total := :NEW.quantity * :NEW.price;
END;
/

14.3 数据完整性

CREATE OR REPLACE TRIGGER trg_check_balance
BEFORE UPDATE OF amount ON accounts
FOR EACH ROW
DECLARE
  v_balance NUMBER;
BEGIN
  SELECT balance INTO v_balance FROM accounts WHERE id = :NEW.id;
  IF v_balance - :OLD.amount + :NEW.amount < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Insufficient balance');
  END IF;
END;
/

14.4 默认值

CREATE OR REPLACE TRIGGER trg_default
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF :NEW.created_at IS NULL THEN
    :NEW.created_at := SYSTIMESTAMP;
  END IF;
  
  IF :NEW.status IS NULL THEN
    :NEW.status := 'ACTIVE';
  END IF;
END;
/

---

15. 性能

15.1 行级开销

- 每行触发
- 性能影响
- 谨慎使用

15.2 替代

- 约束（CHECK / NOT NULL）
- 默认值
- 应用层

详细见：Oracle PL/SQL 性能优化。

---

16. 常见坑与排错

16.1 ORA-04091

- 变异表
- 复合触发器

16.2 ORA-04092

- 事务控制
- 自治事务

16.3 ORA-04098

- 触发器无效
- 编译

16.4 递归

- 触发器调用自己
- 避免

---

17. 最佳实践

1. 谨慎使用：性能
2. 业务逻辑最小：简单
3. 复合触发器：11g+
4. 自治事务：日志
5. 约束优先：替代
6. 审计：合规
7. 派生列：自动
8. 默认值：便利
9. 测试：完整
10. 文档化：说明

---

18. 参考资料

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