第七章：触发器与事件调度器

文档信息

适用版本：Oracle 12c / 18c / 19c / 21c
阅读时间：约 45 分钟
前置知识：第六章视图与存储过程

7.1 触发器高级应用

7.1.1 条件谓词

-- 使用条件谓词区分触发事件
CREATE OR REPLACE TRIGGER trg_emp_dml_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
    v_action VARCHAR2(10);
BEGIN
    IF INSERTING THEN
        v_action := 'INSERT';
        INSERT INTO audit_log (action, table_name, new_value, action_date)
        VALUES (v_action, 'EMPLOYEES', :NEW.employee_id || ':' || :NEW.first_name, SYSDATE);
        
    ELSIF UPDATING THEN
        v_action := 'UPDATE';
        INSERT INTO audit_log (action, table_name, old_value, new_value, action_date)
        VALUES (v_action, 'EMPLOYEES', :OLD.employee_id || ':' || :OLD.salary,
                :NEW.employee_id || ':' || :NEW.salary, SYSDATE);
        
    ELSIF DELETING THEN
        v_action := 'DELETE';
        INSERT INTO audit_log (action, table_name, old_value, action_date)
        VALUES (v_action, 'EMPLOYEES', :OLD.employee_id || ':' || :OLD.first_name, SYSDATE);
    END IF;
END trg_emp_dml_audit;
/

-- 条件谓词在触发器中的使用
-- INSERTING: 是否为 INSERT
-- UPDATING: 是否为 UPDATE
-- UPDATING('column_name'): 是否更新了特定列
-- DELETING: 是否为 DELETE

7.1.2 变异表问题

变异表限制

触发器不能直接读取或修改触发器所在的表

-- 变异表问题示例
-- 错误示例：触发器不能查询正在被修改的表
CREATE OR REPLACE TRIGGER trg_count_employees
AFTER INSERT ON employees
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN
    -- 这会触发 ORA-04091 错误
    SELECT COUNT(*) INTO v_count FROM employees;
END;
/

-- 解决方案1：使用语句级触发器 + 包变量
CREATE OR REPLACE PACKAGE emp_trigger_pkg AS
    v_new_count NUMBER := 0;
END emp_trigger_pkg;
/

CREATE OR REPLACE TRIGGER trg_count_employees_stmt
AFTER INSERT ON employees
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM employees;
    DBMS_OUTPUT.PUT_LINE('Total employees after insert: ' || v_count);
END;
/

-- 解决方案2：使用自治事务
CREATE OR REPLACE TRIGGER trg_log_insert
AFTER INSERT ON employees
FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO emp_insert_log 
    VALUES (:NEW.employee_id, :NEW.first_name, SYSDATE);
    COMMIT;
END trg_log_insert;
/

-- 解决方案3：使用 INSTEAD OF 触发器（针对视图）
-- 或使用 AFTER 触发器而非 BEFORE

7.1.3 触发器与事务

-- 触发器与提交
-- 触发器内不能提交，但可以回滚
CREATE OR REPLACE TRIGGER trg_validate_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    IF :NEW.salary < 1000 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary must be at least 1000');
    END IF;
    
    -- 自治事务中可以提交
    -- COMMIT;  -- 通常不推荐在触发器中提交
END;
/

-- 多级触发器链
-- 触发器A → 触发器B → 触发器C
-- 注意：避免循环触发

-- 设置触发器顺序
-- Oracle 11g+ 支持 FOLLOWS 子句
CREATE OR REPLACE TRIGGER trg_audit_new
AFTER INSERT ON employees
FOR EACH ROW
FOLLOWS trg_validate_salary  -- 在 trg_validate_salary 之后执行
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO audit_trail VALUES ('INSERT', :NEW.employee_id, SYSDATE);
    COMMIT;
END trg_audit_new;
/

7.2 DDL 触发器

7.2.1 DDL 事件类型

事件	说明
CREATE	创建对象
ALTER	修改对象
DROP	删除对象
GRANT	授予权限
REVOKE	撤销权限
ANALYZE	分析表/索引
AUDIT	审计操作
COMMENT	添加注释

7.2.2 创建 DDL 触发器

-- 记录所有 DDL 操作
CREATE OR REPLACE TRIGGER trg_ddl_audit
AFTER DDL ON SCHEMA
DECLARE
    v_sql_text ORA_NAME_LIST_T;
    v_count NUMBER;
BEGIN
    v_count := ORA_SQL_TXT(v_sql_text);
    
    INSERT INTO ddl_audit_log (
        username, 
        ddl_date, 
        ddl_type, 
        object_type, 
        object_name,
        sql_text
    ) VALUES (
        ORA_LOGIN_USER,
        SYSDATE,
        ORA_SYSEVENT,
        ORA_DICT_OBJ_TYPE,
        ORA_DICT_OBJ_NAME,
        DBMS_LOB.SUBSTR(v_sql_text(1), 4000, 1)
    );
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        NULL;  -- 避免 DDL 失败
END trg_ddl_audit;
/

-- 禁止删除特定表
CREATE OR REPLACE TRIGGER trg_prevent_drop
BEFORE DROP ON SCHEMA
DECLARE
    v_obj_name VARCHAR2(100) := ORA_DICT_OBJ_NAME;
    v_obj_type VARCHAR2(50) := ORA_DICT_OBJ_TYPE;
BEGIN
    IF v_obj_name IN ('EMPLOYEES', 'DEPARTMENTS') 
       AND v_obj_type = 'TABLE' THEN
        RAISE_APPLICATION_ERROR(-20001, 
            'Cannot drop protected table: ' || v_obj_name);
    END IF;
END trg_prevent_drop;
/

-- 监控表结构变更
CREATE OR REPLACE TRIGGER trg_table_change
AFTER CREATE OR ALTER OR DROP ON SCHEMA
DECLARE
    v_event VARCHAR2(50) := ORA_SYSEVENT;
    v_obj_type VARCHAR2(50) := ORA_DICT_OBJ_TYPE;
    v_obj_name VARCHAR2(100) := ORA_DICT_OBJ_NAME;
BEGIN
    IF v_obj_type IN ('TABLE', 'INDEX', 'VIEW', 'PROCEDURE') THEN
        INSERT INTO object_change_log VALUES (
            v_event, v_obj_type, v_obj_name, USER, SYSDATE
        );
        COMMIT;
    END IF;
END trg_table_change;
/

7.2.3 数据库级 DDL 触发器

-- 需要 DBA 权限
-- 在 CDB 环境，需要在 PDB 级别创建

-- 创建登录/注销触发器
CREATE OR REPLACE TRIGGER trg_logon_audit
AFTER LOGON ON DATABASE
DECLARE
    v_user VARCHAR2(100) := USER;
BEGIN
    INSERT INTO session_log (
        username, logon_time, machine, program
    ) VALUES (
        v_user, SYSDATE, 
        SYS_CONTEXT('USERENV', 'HOST'),
        SYS_CONTEXT('USERENV', 'MODULE')
    );
    COMMIT;
END trg_logon_audit;
/

CREATE OR REPLACE TRIGGER trg_logoff_audit
BEFORE LOGOFF ON DATABASE
DECLARE
    v_user VARCHAR2(100) := USER;
BEGIN
    UPDATE session_log 
    SET logoff_time = SYSDATE
    WHERE username = v_user 
    AND logoff_time IS NULL
    AND ROWNUM = 1;
    COMMIT;
END trg_logoff_audit;
/

-- 限制特定用户登录时间
CREATE OR REPLACE TRIGGER trg_restrict_hours
AFTER LOGON ON DATABASE
DECLARE
    v_user VARCHAR2(50) := USER;
    v_hour NUMBER := TO_CHAR(SYSDATE, 'HH24');
BEGIN
    IF v_user = 'APP_USER' AND v_hour NOT BETWEEN 8 AND 18 THEN
        RAISE_APPLICATION_ERROR(-20001, 
            'Access allowed only during business hours');
    END IF;
END trg_restrict_hours;
/

7.3 事件触发器

7.3.1 事件触发器类型

事件	说明
AFTER STARTUP	数据库启动后
BEFORE SHUTDOWN	数据库关闭前
AFTER DB_ROLE_CHANGE	角色变更后
BEFORE SET ROLE	设置角色前
AFTER LOGON	用户登录后
BEFORE LOGOFF	用户登出前
AFTER SERVERERROR	服务器错误后

7.3.2 创建事件触发器

-- 数据库启动事件
CREATE OR REPLACE TRIGGER trg_after_startup
AFTER STARTUP ON DATABASE
BEGIN
    INSERT INTO startup_log VALUES (
        'Database started', SYSDATE, INSTANCE
    );
    COMMIT;
END trg_after_startup;
/

-- 数据库关闭事件
CREATE OR REPLACE TRIGGER trg_before_shutdown
BEFORE SHUTDOWN ON DATABASE
BEGIN
    INSERT INTO shutdown_log VALUES (
        'Shutdown initiated', SYSDATE
    );
    COMMIT;
END trg_before_shutdown;
/

-- 错误触发器
CREATE OR REPLACE TRIGGER trg_error_alert
AFTER SERVERERROR ON DATABASE
DECLARE
    v_msg VARCHAR2(2000);
BEGIN
    v_msg := DBMS_UTILITY.FORMAT_ERROR_STACK;
    
    IF SQLCODE = -1 THEN  -- ORA-00001: 唯一约束违反
        INSERT INTO error_alert_log VALUES (
            'DUP_VAL_ON_INDEX', v_msg, SYSDATE
        );
    ELSIF SQLCODE = -54 THEN  -- ORA-00054: 资源忙
        INSERT INTO error_alert_log VALUES (
            'RESOURCE_BUSY', v_msg, SYSDATE
        );
    END IF;
    
    COMMIT;
END trg_error_alert;
/

7.4 DBMS_SCHEDULER 调度器

7.4.1 调度器组件

┌─────────────────────────────────────────────────────────────────┐
│                  DBMS_SCHEDULER 架构                              │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  调度器对象                                                       │
│  ───────────                                                     │
│  • Programs (程序)     - 定义要执行的操作                        │
│  • Jobs (作业)         - 安排程序执行                            │
│  • Schedules (计划)    - 定义执行时间                           │
│  • Job Classes (作业类) - 分组和管理资源                         │
│  • Windows (窗口)      - 时间窗口内启用资源                      │
│                                                                 │
│  作业类型                                                         │
│  ───────                                                         │
│  • PL/SQL 块                                                      │
│  • 存储过程                                                      │
│  • 外部程序 (C, Java, OS command)                                │
│                                                                 │
│  调度类型                                                         │
│  ───────                                                         │
│  • 基于时间 - 定期执行                                           │
│  • 基于事件 - 响应事件执行                                       │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

7.4.2 创建程序对象

-- 创建 PL/SQL 程序
BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM(
        program_name   => 'PROG_ARCHIVE_OLD_DATA',
        program_type   => 'PLSQL_BLOCK',
        program_action => '
            BEGIN
                DELETE FROM sales 
                WHERE sale_date < ADD_MONTHS(SYSDATE, -24);
                COMMIT;
            END;',
        number_of_arguments => 0,
        enabled             => TRUE,
        comments            => 'Archive sales data older than 24 months'
    );
END;
/

-- 创建存储过程程序
BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM(
        program_name   => 'PROG_BACKUP_TABLE',
        program_type   => 'STORED_PROCEDURE',
        program_action => 'PKG_MAINTENANCE.BACKUP_TABLE',
        number_of_arguments => 1,
        enabled             => FALSE,  -- 需要先定义参数
        comments            => 'Backup specified table'
    );
END;
/

-- 定义程序参数
BEGIN
    DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
        program_name      => 'PROG_BACKUP_TABLE',
        argument_position => 1,
        argument_name     => 'P_TABLE_NAME',
        argument_type     => 'VARCHAR2',
        default_value     => NULL
    );
END;
/

-- 启用程序
BEGIN
    DBMS_SCHEDULER.ENABLE('PROG_ARCHIVE_OLD_DATA');
END;
/

7.4.3 创建计划对象

-- 一次性计划
BEGIN
    DBMS_SCHEDULER.CREATE_SCHEDULE(
        schedule_name => 'SCH_ONE_TIME',
        start_date    => TO_TIMESTAMP_TZ('2026-05-01 02:00:00 America/New_York', 
                                          'YYYY-MM-DD HH24:MI:SS TZR'),
        end_date      => NULL,
        repeat_interval => NULL,
        comments      => 'One-time maintenance job'
    );
END;
/

-- 重复执行计划 (日历表达式)
BEGIN
    -- 每周一凌晨 2:00 执行
    DBMS_SCHEDULER.CREATE_SCHEDULE(
        schedule_name   => 'SCH_WEEKLY_MAINT',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
        comments        => 'Weekly maintenance on Monday at 2 AM'
    );
END;
/

-- 每日执行
BEGIN
    DBMS_SCHEDULER.CREATE_SCHEDULE(
        schedule_name   => 'SCH_DAILY_NIGHT',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0',
        comments        => 'Daily at 2 AM'
    );
END;
/

-- 每月最后一天执行
BEGIN
    DBMS_SCHEDULER.CREATE_SCHEDULE(
        schedule_name   => 'SCH_MONTHLY_END',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=-1;BYHOUR=3;BYMINUTE=0',
        comments        => 'Last day of each month at 3 AM'
    );
END;
/

-- 使用 PL/SQL 表达式定义间隔
BEGIN
    DBMS_SCHEDULER.CREATE_SCHEDULE(
        schedule_name   => 'SCH_EVERY_6_HOURS',
        start_date      => SYSTIMESTAMP,
        repeat_interval => '
            DECLARE
                next_time TIMESTAMP;
            BEGIN
                next_time := SYSTIMESTAMP + INTERVAL ''6'' HOUR;
                RETURN TO_CHAR(next_time, ''YYYYMMDD HH24MI'');
            END;',
        comments        => 'Every 6 hours'
    );
END;
/

7.4.4 创建作业

-- 基础作业
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'JOB_ARCHIVE_DATA',
        program_name    => 'PROG_ARCHIVE_OLD_DATA',
        schedule_name   => 'SCH_WEEKLY_MAINT',
        job_class       => 'DEFAULT_JOB_CLASS',
        enabled         => TRUE,
        auto_drop       => FALSE,      -- 不要自动删除
        comments        => 'Weekly archive job'
    );
END;
/

-- 作业与内联计划组合
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'JOB_REFRESH_REPORT',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN P_REFRESH_MONTHLY_REPORT; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=1',
        enabled         => TRUE,
        comments        => 'Refresh monthly report on 1st of each month'
    );
END;
/

-- 带参数的作业
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'JOB_BACKUP_EMP',
        program_name    => 'PROG_BACKUP_TABLE',
        schedule_name   => 'SCH_DAILY_NIGHT',
        job_class       => 'DEFAULT_JOB_CLASS',
        enabled         => TRUE,
        comments        => 'Daily backup of employees table'
    );
END;
/

-- 设置作业参数
BEGIN
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
        job_name            => 'JOB_BACKUP_EMP',
        argument_position   => 1,
        argument_value      => 'EMPLOYEES'
    );
END;
/

-- 立即执行作业
BEGIN
    DBMS_SCHEDULER.RUN_JOB(
        job_name  => 'JOB_ARCHIVE_DATA',
        use_current_session => FALSE  -- 在新会话中运行
    );
END;
/

-- 停止作业
BEGIN
    DBMS_SCHEDULER.STOP_JOB(
        job_name          => 'JOB_ARCHIVE_DATA',
        force             => TRUE
    );
END;
/

7.4.5 作业管理

-- 查看作业状态
SELECT 
    job_name,
    job_type,
    status,
    enabled,
    run_count,
    failure_count,
    last_start_date,
    last_run_duration,
    next_run_date
FROM user_scheduler_jobs;

-- 查看作业运行历史
SELECT 
    job_name,
    log_id,
    operation,
    status,
    TO_CHAR(log_date, 'YYYY-MM-DD HH24:MI:SS') AS log_date,
    additional_info
FROM user_scheduler_job_run_details
ORDER BY log_date DESC;

-- 启用/禁用作业
BEGIN
    DBMS_SCHEDULER.ENABLE('JOB_ARCHIVE_DATA');
    -- 或
    DBMS_SCHEDULER.DISABLE('JOB_ARCHIVE_DATA');
END;
/

-- 删除作业
BEGIN
    DBMS_SCHEDULER.DROP_JOB(
        job_name       => 'JOB_ARCHIVE_DATA',
        force          => TRUE
    );
END;
/

-- 修改作业
BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
        name      => 'JOB_ARCHIVE_DATA',
        attribute => 'repeat_interval',
        value     => 'FREQ=DAILY;BYHOUR=3'
    );
END;
/

7.4.6 作业类与资源管理

-- 创建作业类
BEGIN
    DBMS_SCHEDULER.CREATE_JOB_CLASS(
        job_class_name  => 'MAINTENANCE_JOBS',
        resource_provider_plan => 'MAINTENANCE_PLAN',
        service         => NULL,
        logging_level   => DBMS_SCHEDULER.LOGGING_RUNS,
        log_history     => 30,  -- 保留 30 天日志
        comments        => 'Maintenance job class'
    );
END;
/

-- 将作业分配到作业类
BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
        name      => 'JOB_ARCHIVE_DATA',
        attribute => 'job_class',
        value     => 'MAINTENANCE_JOBS'
    );
END;
/

-- 创建时间窗口（配合资源计划）
BEGIN
    DBMS_SCHEDULER.CREATE_WINDOW(
        window_name     => 'WEEKEND_WINDOW',
        resource_plan   => 'MAINTENANCE_PLAN',
        start_date      => TO_TIMESTAMP_TZ('2026-01-01 00:00:00 America/New_York',
                                           'YYYY-MM-DD HH24:MI:SS TZR'),
        repeat_interval => 'FREQ=WEEKLY;BYDAY=SAT,SUN;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
        duration       => INTERVAL '12' HOUR,
        window_priority => 'LOW',
        comments        => 'Weekend maintenance window'
    );
END;
/

-- 创建窗口组
BEGIN
    DBMS_SCHEDULER.CREATE_WINDOW_GROUP(
        group_name => 'OFF_HOURS_WINDOW',
        window_list => ARRAY('WEEKDAY_WINDOW', 'WEEKEND_WINDOW')
    );
END;
/

7.4.7 事件驱动作业

-- 创建事件生产者（表）
CREATE TABLE job_event_queue (
    event_id NUMBER PRIMARY KEY,
    event_type VARCHAR2(50),
    target_table VARCHAR2(100),
    event_time TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 创建队列
BEGIN
    DBMS_AQADM.CREATE_QUEUE(
        queue_name  => 'JOB_EVENT_QUEUE',
        queue_table => 'JOB_EVENT_QTABLE',
        queue_type  => DBMS_AQADM.NORMAL_QUEUE
    );
END;
/

-- 创建事件作业
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'JOB_EVENT_HANDLER',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN P_HANDLE_JOB_EVENT; END;',
        event_condition => 'TABLENAME = ''JOB_EVENT_QUEUE'' AND EVENT_TYPE = ''INSERT''',
        queue_spec      => 'JOB_EVENT_QUEUE',
        enabled         => TRUE,
        comments        => 'Event-driven job'
    );
END;
/

-- 基于错误事件的作业
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name          => 'JOB_ERROR_HANDLER',
        job_type          => 'PLSQL_BLOCK',
        job_action        => 'BEGIN P_HANDLE_ERROR; END;',
        start_date        => SYSTIMESTAMP,
        event_condition   => 'EVENT_TYPE = ''SERVERERROR'' AND ERROR_NUMBER = 604',
        queue_spec        => 'SCHEDULER$_EVENT_QUEUE',
        enabled           => TRUE
    );
END;
/

7.5 DBMS_JOB (旧版对比)

-- DBMS_JOB 是旧版调度器，仍可用但推荐使用 DBMS_SCHEDULER

-- 提交作业
DECLARE
    v_job_id NUMBER;
BEGIN
    DBMS_JOB.SUBMIT(
        job       => v_job_id,
        what      => 'P_GENERATE_REPORT;',
        next_date => TRUNC(SYSDATE + 1) + 1/24,  -- 明天凌晨 1 点
        interval  => 'TRUNC(SYSDATE + 1) + 1/24'  -- 每天执行
    );
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Job ID: ' || v_job_id);
END;
/

-- 管理作业
BEGIN
    DBMS_JOB.BROKEN(v_job_id, TRUE);   -- 标记为损坏
    DBMS_JOB.RUN(v_job_id);             -- 立即运行
    DBMS_JOB.REMOVE(v_job_id);          -- 删除作业
END;
/

-- DBMS_JOB vs DBMS_SCHEDULER 对比
-- ┌──────────────────────────────────────────────────────────────┐
-- │ 特性             │ DBMS_JOB        │ DBMS_SCHEDULER           │
-- ├──────────────────────────────────────────────────────────────┤
-- │ 资源管理         │ 无              │ 支持作业类、资源计划      │
-- │ 事件驱动         │ 无              │ 支持事件触发              │
-- │ 外部程序         │ 无              │ 支持 OS 命令              │
-- │ 依赖关系         │ 无              │ 支持作业链                │
-- │ 日志保留         │ 有限            │ 可配置                     │
-- │ 推荐程度         │ 不推荐          │ 推荐                       │
-- └──────────────────────────────────────────────────────────────┘

7.6 本章小结

✅ DDL 触发器：审计和防止危险 DDL 操作
✅ 事件触发器：LOGON/LOGOFF/SHUTDOWN 等事件
✅ 触发器高级特性：条件谓词、FOLLOWS、变异表处理
✅ DBMS_SCHEDULER：现代调度框架
✅ 调度组件：Programs、Schedules、Jobs、Job Classes、Windows
✅ 事件驱动作业：响应队列事件执行

📖 下章预告：事务与并发控制

下一步：学习第八章：事务与并发控制