Oracle 数据库触发器高级应用

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

---

1. 概述

触发器高级应用场景[1]：

详细见：Oracle PL/SQL 触发器详解。

---

2. 审计触发器

2.1 通用审计

CREATE TABLE audit_log (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  table_name VARCHAR2(50),
  operation VARCHAR2(10),
  row_id ROWID,
  old_values CLOB,
  new_values CLOB,
  user_name VARCHAR2(50),
  action_time TIMESTAMP
);

CREATE OR REPLACE TRIGGER trg_audit_employees
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
  v_op VARCHAR2(10);
  v_old CLOB;
  v_new CLOB;
BEGIN
  v_op := CASE WHEN INSERTING THEN 'INSERT' 
               WHEN UPDATING THEN 'UPDATE' 
               WHEN DELETING THEN 'DELETE' END;
  
  v_old := :OLD.id || ',' || :OLD.name || ',' || :OLD.salary;
  v_new := :NEW.id || ',' || :NEW.name || ',' || :NEW.salary;
  
  INSERT INTO audit_log (table_name, operation, row_id, old_values, new_values, user_name, action_time)
  VALUES ('EMPLOYEES', v_op, :OLD.ROWID, v_old, v_new, USER, SYSTIMESTAMP);
END;
/

2.2 FGA 替代

-- Fine-Grained Auditing
BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema => 'SCOTT',
    object_name => 'employees',
    policy_name => 'audit_sensitive',
    audit_condition => 'salary > 10000',
    audit_column => 'salary',
    handler_schema => 'SCOTT',
    handler_module => 'audit_handler'
  );
END;
/

详细见：Oracle 审计详解。

---

3. 派生列

3.1 计算列

CREATE TABLE order_items (
  id NUMBER PRIMARY KEY,
  quantity NUMBER,
  price NUMBER,
  total NUMBER
);

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;
/

-- 12c+ 虚拟列
CREATE TABLE order_items (
  id NUMBER PRIMARY KEY,
  quantity NUMBER,
  price NUMBER,
  total NUMBER GENERATED ALWAYS AS (quantity * price) VIRTUAL
);

3.2 默认值

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;
  
  IF :NEW.id IS NULL THEN
    SELECT seq_emp.NEXTVAL INTO :NEW.id FROM dual;
  END IF;
END;
/

-- 12c+ IDENTITY / DEFAULT
CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status VARCHAR2(20) DEFAULT 'ACTIVE',
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

详细见：Oracle 序列与自增列详解。

---

4. 数据完整性

4.1 跨表约束

CREATE OR REPLACE TRIGGER trg_check_dept_budget
BEFORE INSERT OR UPDATE OF amount ON expenses
FOR EACH ROW
DECLARE
  v_budget NUMBER;
  v_total NUMBER;
BEGIN
  SELECT budget INTO v_budget FROM departments WHERE id = :NEW.dept_id;
  
  SELECT NVL(SUM(amount), 0) INTO v_total 
  FROM expenses WHERE dept_id = :NEW.dept_id;
  
  IF v_total + :NEW.amount > v_budget THEN
    RAISE_APPLICATION_ERROR(-20001, 'Budget exceeded');
  END IF;
END;
/

4.2 复杂业务规则

CREATE OR REPLACE TRIGGER trg_check_manager
BEFORE INSERT OR UPDATE OF manager_id ON employees
FOR EACH ROW
DECLARE
  v_count NUMBER;
BEGIN
  -- 经理必须是同部门
  SELECT COUNT(*) INTO v_count
  FROM employees
  WHERE id = :NEW.manager_id AND dept_id = :NEW.dept_id;
  
  IF v_count = 0 THEN
    RAISE_APPLICATION_ERROR(-20002, 'Manager must be in same dept');
  END IF;
END;
/

---

5. 变异表

5.1 问题

-- 错误：ORA-04091
CREATE OR REPLACE TRIGGER trg_bad
AFTER INSERT ON employees
FOR EACH ROW
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM employees WHERE dept_id = :NEW.dept_id;
  -- ERROR: employees is mutating
END;
/

5.2 复合触发器

CREATE OR REPLACE TRIGGER trg_check_dept_size
FOR INSERT OR UPDATE ON employees
COMPOUND TRIGGER
  TYPE id_list IS TABLE OF NUMBER;
  v_dept_ids id_list := id_list();
  
  AFTER EACH ROW IS
  BEGIN
    v_dept_ids.EXTEND;
    v_dept_ids(v_dept_ids.LAST) := :NEW.dept_id;
  END AFTER EACH ROW;
  
  AFTER STATEMENT IS
    v_count NUMBER;
  BEGIN
    FOR i IN 1..v_dept_ids.COUNT LOOP
      SELECT COUNT(*) INTO v_count FROM employees WHERE dept_id = v_dept_ids(i);
      IF v_count > 100 THEN
        -- 处理
      END IF;
    END LOOP;
  END AFTER STATEMENT;
END;
/

详细见：Oracle PL/SQL 触发器详解。

---

6. 同步

6.1 表同步

CREATE OR REPLACE TRIGGER trg_sync_emp
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO employees_backup VALUES (:NEW.id, :NEW.name, :NEW.salary, SYSTIMESTAMP);
  ELSIF UPDATING THEN
    UPDATE employees_backup 
    SET name = :NEW.name, salary = :NEW.salary, updated_at = SYSTIMESTAMP
    WHERE id = :NEW.id;
  ELSIF DELETING THEN
    DELETE FROM employees_backup WHERE id = :OLD.id;
  END IF;
END;
/

6.2 物化视图日志

- 推荐
- 异步
- 性能

详细见：Oracle 视图与物化视图详解。

---

7. INSTEAD OF

7.1 复杂视图

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

CREATE OR REPLACE TRIGGER trg_emp_dept_view
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_dept_view
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO employees (id, name, salary, dept_id)
    VALUES (:NEW.id, :NEW.name, :NEW.salary, :NEW.dept_id);
  ELSIF UPDATING THEN
    UPDATE employees SET name = :NEW.name, salary = :NEW.salary
    WHERE id = :NEW.id;
    
    UPDATE departments SET dept_name = :NEW.dept_name
    WHERE id = :NEW.dept_id;
  ELSIF DELETING THEN
    DELETE FROM employees WHERE id = :OLD.id;
  END IF;
END;
/

详细见：Oracle 视图与物化视图详解。

---

8. 事件触发器

8.1 DDL

CREATE OR REPLACE TRIGGER trg_ddl_protect
BEFORE DROP OR TRUNCATE ON SCHEMA
BEGIN
  IF ORA_DICT_OBJ_NAME LIKE 'SYS_%' THEN
    RAISE_APPLICATION_ERROR(-20003, 'Cannot drop system objects');
  END IF;
END;
/

8.2 数据库

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

---

9. 自治事务

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

---

10. 性能

10.1 开销

- 每行触发
- DML 性能
- 谨慎

10.2 替代

- 约束（CHECK）
- 默认值
- 虚拟列（12c+）
- 应用层
- 物化视图日志

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

---

11. 应用场景

11.1 审计

-- 完整审计
AFTER INSERT OR UPDATE OR DELETE ...
-- 记录所有变更

11.2 业务规则

-- 复杂约束
BEFORE INSERT OR UPDATE ...
-- 跨表检查

11.3 同步

-- 表同步
AFTER ...
-- 备份 / 物化

11.4 事件

-- DDL / 数据库事件
-- 日志 / 安全

---

12. 禁用

-- 批量加载
ALTER TABLE employees DISABLE ALL TRIGGERS;

-- 加载
INSERT /*+ APPEND */ INTO employees SELECT * FROM source;

-- 启用
ALTER TABLE employees ENABLE ALL TRIGGERS;

---

13. 常见坑与排错

13.1 变异表

- ORA-04091
- 复合触发器
- 临时表

13.2 递归

- 触发器引发触发器
- 避免

13.3 性能

- 行级开销
- 批量禁用

13.4 事务

- 自治事务
- 日志

---

14. 最佳实践

1. 谨慎使用：性能
2. 业务最小：简单
3. 复合触发器：变异表
4. 自治事务：日志
5. 约束优先：替代
6. 审计：合规
7. 禁用批量：加载
8. INSTEAD OF：视图
9. 事件触发器：管理
10. 测试：完整

---

15. 参考资料

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