Oracle 触发器应用

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

1. 概述

触发器自动响应 DML/DDL 事件[1]：

类型：

DML 触发器
DDL 触发器
INSTEAD OF（视图）
SYSTEM 事件

2. DML 触发器

2.1 BEFORE INSERT

CREATE OR REPLACE TRIGGER trg_emp_audit
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  :new.created_at := SYSTIMESTAMP;
  :new.created_by := USER;
END;
/

2.2 AFTER INSERT/UPDATE/DELETE

CREATE OR REPLACE TRIGGER trg_emp_history
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO emp_history (id, action, action_time)
    VALUES (:new.id, 'INSERT', SYSTIMESTAMP);
  ELSIF UPDATING THEN
    INSERT INTO emp_history (id, action, action_time)
    VALUES (:new.id, 'UPDATE', SYSTIMESTAMP);
  ELSIF DELETING THEN
    INSERT INTO emp_history (id, action, action_time)
    VALUES (:old.id, 'DELETE', SYSTIMESTAMP);
  END IF;
END;
/

2.3 复合触发器（11g+）

CREATE OR REPLACE TRIGGER trg_emp_compound
FOR INSERT OR UPDATE ON employees
COMPOUND TRIGGER
  -- 声明（仅一次）
  v_count NUMBER := 0;
  
  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_count := v_count + 1;
  END AFTER EACH ROW;
  
  AFTER STATEMENT IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Rows: ' || v_count);
  END AFTER STATEMENT;
END;
/

3. INSTEAD OF 触发器

3.1 视图触发

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

CREATE OR REPLACE TRIGGER trg_v_emp_dept
INSTEAD OF INSERT ON v_emp_dept
FOR EACH ROW
BEGIN
  INSERT INTO employees (id, name, dept_id) VALUES (:new.id, :new.name, :new.dept_id);
END;
/

-- 插入视图
INSERT INTO v_emp_dept (id, name, dept_name, dept_id) 
VALUES (1, 'Alice', 'IT', 10);

4. DDL 触发器

4.1 数据库级

CREATE OR REPLACE TRIGGER trg_ddl_log
AFTER CREATE OR ALTER OR DROP ON DATABASE
BEGIN
  INSERT INTO ddl_log (event, object_owner, object_name, object_type, event_time, username)
  VALUES (
    ORA_SYSEVENT, ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME, 
    ORA_DICT_OBJ_TYPE, SYSTIMESTAMP, ORA_LOGIN_USER
  );
END;
/

4.2 SCHEMA 级

CREATE OR REPLACE TRIGGER trg_no_drop
BEFORE DROP ON SCOTT.SCHEMA
BEGIN
  IF ORA_DICT_OBJ_NAME LIKE 'EMP%' THEN
    RAISE_APPLICATION_ERROR(-20001, 'Cannot drop EMP tables');
  END IF;
END;
/

5. SYSTEM 事件

5.1 LOGON/LOGOFF

CREATE OR REPLACE TRIGGER trg_logon
AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO logon_log (username, logon_time)
  VALUES (USER, SYSTIMESTAMP);
END;
/

CREATE OR REPLACE TRIGGER trg_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
  UPDATE logon_log 
  SET logoff_time = SYSTIMESTAMP
  WHERE username = USER AND logoff_time IS NULL;
END;
/

5.2 STARTUP/SHUTDOWN

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

6. FOLLOWS

6.1 触发顺序

CREATE OR REPLACE TRIGGER trg_emp_1
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('Trigger 1');
END;
/

CREATE OR REPLACE TRIGGER trg_emp_2
BEFORE INSERT ON employees
FOR EACH ROW
FOLLOWS trg_emp_1
BEGIN
  DBMS_OUTPUT.PUT_LINE('Trigger 2');
END;
/

7. ENABLE/DISABLE

ALTER TRIGGER trg_emp_audit DISABLE;
ALTER TRIGGER trg_emp_audit ENABLE;

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

8. 触发器状态

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

9. 触发器源代码

SELECT trigger_body FROM user_triggers WHERE trigger_name = 'TRG_EMP_AUDIT';

10. 触发器管理

10.1 编译

ALTER TRIGGER trg_emp_audit COMPILE;

10.2 删除

DROP TRIGGER trg_emp_audit;

11. 性能影响

11.1 DML 开销

- 行级触发器：每行执行
- 大量 DML：累积开销
- 谨慎使用

11.2 替代方案

- 默认值：DEFAULT
- 序列：12c+ IDENTITY
- 审计：统一审计
- 业务逻辑：存储过程

11.3 复合触发器

- 减少：BEFORE/AFTER STATEMENT
- 性能：比独立行级好

12. 自治事务

12.1 审计

CREATE OR REPLACE TRIGGER trg_audit
AFTER INSERT ON employees
FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_table (table_name, action, row_id, action_time)
  VALUES ('EMPLOYEES', 'INSERT', :new.id, SYSTIMESTAMP);
  COMMIT;
END;
/

12.2 注意

- 独立事务
- 主事务失败不影响
- COMMIT 必须

13. 常见坑与排错

13.1 变异表

ORA-04091: table ... is mutating
- 行级触发器不能查询/修改当前表
- 用复合触发器或 AFTER STATEMENT

13.2 递归

- 触发器调用导致自身
- ORA-00036
- 避免自调用

13.3 性能

- 大量 DML
- 行级触发器
- 替代为过程

14. 最佳实践

谨慎使用：性能
复合触发器：11g+
避免变异表：复合
自治事务审计：独立
FOLLOWS 控制顺序：12c+
替代方案优先：DEFAULT/IDENTITY
DDL 审计：管理
监控使用：禁用无用
测试：业务
文档化：设计

15. 参考资料

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