PL/SQL触发器 #

触发器特点
├── 自动执行
│   └── 事件触发
├── 不能直接调用
│   └── 由数据库自动触发
├── 事件类型
│   ├── DML事件
│   ├── DDL事件
│   └── 系统事件
└── 执行时机
    ├── BEFORE
    ├── AFTER
    └── INSTEAD OF

触发器类型
├── DML触发器
│   ├── 语句级触发器
│   └── 行级触发器
├── INSTEAD OF触发器
│   └── 用于视图
├── DDL触发器
│   └── CREATE, ALTER, DROP
└── 系统触发器
    ├── 登录/登出
    └── 启动/关闭

-- DML触发器基本语法
CREATE [OR REPLACE] TRIGGER trigger_name
    {BEFORE|AFTER|INSTEAD OF}
    {INSERT|UPDATE|DELETE} [OR {INSERT|UPDATE|DELETE}...]
    ON table_name
    [FOR EACH ROW]
    [WHEN (condition)]
    [FOLLOWS trigger_name]
    [ENABLE|DISABLE]
AS|IS
    -- 声明部分
BEGIN
    -- 执行部分
END;

-- 语句级触发器：每条语句执行一次
CREATE OR REPLACE TRIGGER trg_audit_employees
AFTER INSERT OR UPDATE OR DELETE ON employees
DECLARE
    v_action VARCHAR2(20);
BEGIN
    IF INSERTING THEN
        v_action := 'INSERT';
    ELSIF UPDATING THEN
        v_action := 'UPDATE';
    ELSIF DELETING THEN
        v_action := 'DELETE';
    END IF;
    
    INSERT INTO audit_log (table_name, action, action_time, user_name)
    VALUES ('employees', v_action, SYSTIMESTAMP, USER);
END;
/

-- 测试
INSERT INTO employees (employee_id, first_name, last_name) VALUES (999, 'Test', 'User');
SELECT * FROM audit_log;

-- 行级触发器：每行执行一次
CREATE OR REPLACE TRIGGER trg_salary_audit
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary != :OLD.salary THEN
        INSERT INTO salary_audit (
            employee_id,
            old_salary,
            new_salary,
            change_date,
            changed_by
        ) VALUES (
            :NEW.employee_id,
            :OLD.salary,
            :NEW.salary,
            SYSTIMESTAMP,
            USER
        );
    END IF;
END;
/

-- 测试
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
SELECT * FROM salary_audit;

-- :NEW和:OLD说明
-- INSERT：:NEW可用，:OLD不可用
-- UPDATE：:NEW和:OLD都可用
-- DELETE：:OLD可用，:NEW不可用

-- WHEN条件：限制触发器执行
CREATE OR REPLACE TRIGGER trg_high_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary > 10000)  -- 注意：不使用冒号
BEGIN
    INSERT INTO high_salary_audit (employee_id, salary)
    VALUES (:NEW.employee_id, :NEW.salary);
END;
/

-- 复杂WHEN条件
CREATE OR REPLACE TRIGGER trg_salary_increase_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary > OLD.salary * 1.5)  -- 薪资增长超过50%
BEGIN
    INSERT INTO salary_increase_audit (
        employee_id, old_salary, new_salary, increase_pct
    ) VALUES (
        :NEW.employee_id, :OLD.salary, :NEW.salary,
        (:NEW.salary - :OLD.salary) / :OLD.salary * 100
    );
END;
/

-- 多事件触发器
CREATE OR REPLACE TRIGGER trg_employee_audit
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO employee_audit (
            employee_id, action, action_time, new_data
        ) VALUES (
            :NEW.employee_id, 'INSERT', SYSTIMESTAMP,
            :NEW.first_name || ' ' || :NEW.last_name
        );
    ELSIF UPDATING THEN
        INSERT INTO employee_audit (
            employee_id, action, action_time, old_data, new_data
        ) VALUES (
            :NEW.employee_id, 'UPDATE', SYSTIMESTAMP,
            :OLD.first_name || ' ' || :OLD.last_name,
            :NEW.first_name || ' ' || :NEW.last_name
        );
    ELSIF DELETING THEN
        INSERT INTO employee_audit (
            employee_id, action, action_time, old_data
        ) VALUES (
            :OLD.employee_id, 'DELETE', SYSTIMESTAMP,
            :OLD.first_name || ' ' || :OLD.last_name
        );
    END IF;
END;
/

-- 检查特定列是否更新
CREATE OR REPLACE TRIGGER trg_check_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF UPDATING('salary') THEN
        DBMS_OUTPUT.PUT_LINE('Salary updated');
    END IF;
    
    IF UPDATING('department_id') THEN
        DBMS_OUTPUT.PUT_LINE('Department updated');
    END IF;
END;
/

-- 创建不可更新视图
CREATE VIEW emp_dept_view AS
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    e.salary,
    d.department_id,
    d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 尝试更新视图
UPDATE emp_dept_view SET department_name = 'New IT' WHERE employee_id = 100;
-- ORA-01779: cannot modify a column which maps to a non key-preserved table

-- 创建INSTEAD OF触发器
CREATE OR REPLACE TRIGGER trg_emp_dept_view_update
INSTEAD OF UPDATE ON emp_dept_view
FOR EACH ROW
BEGIN
    -- 更新employees表
    UPDATE employees
    SET 
        first_name = :NEW.first_name,
        last_name = :NEW.last_name,
        salary = :NEW.salary
    WHERE employee_id = :NEW.employee_id;
    
    -- 更新departments表
    UPDATE departments
    SET department_name = :NEW.department_name
    WHERE department_id = :NEW.department_id;
END;
/

-- 测试
UPDATE emp_dept_view 
SET first_name = 'John', salary = 15000, department_name = 'IT Department'
WHERE employee_id = 100;

-- INSTEAD OF INSERT触发器
CREATE OR REPLACE TRIGGER trg_emp_dept_view_insert
INSTEAD OF INSERT ON emp_dept_view
FOR EACH ROW
DECLARE
    v_dept_id NUMBER;
BEGIN
    -- 查找或创建部门
    BEGIN
        SELECT department_id INTO v_dept_id
        FROM departments
        WHERE department_name = :NEW.department_name;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            INSERT INTO departments (department_id, department_name)
            VALUES (dept_seq.NEXTVAL, :NEW.department_name)
            RETURNING department_id INTO v_dept_id;
    END;
    
    -- 插入员工
    INSERT INTO employees (employee_id, first_name, last_name, salary, department_id)
    VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.salary, v_dept_id);
END;
/

-- DDL触发器：监控DDL操作
CREATE OR REPLACE TRIGGER trg_ddl_audit
AFTER DDL ON SCHEMA
DECLARE
    v_sql_text VARCHAR2(4000);
BEGIN
    SELECT sql_text INTO v_sql_text
    FROM v$sql
    WHERE sql_id = (SELECT prev_sql_id FROM v$session WHERE sid = SYS_CONTEXT('USERENV', 'SID'));
    
    INSERT INTO ddl_audit (
        object_type,
        object_name,
        ddl_action,
        ddl_sql,
        action_time,
        user_name
    ) VALUES (
        ORA_DICT_OBJ_TYPE,
        ORA_DICT_OBJ_NAME,
        ORA_SYSEVENT,
        v_sql_text,
        SYSTIMESTAMP,
        USER
    );
END;
/

-- 测试
CREATE TABLE test_table (id NUMBER);
DROP TABLE test_table;
SELECT * FROM ddl_audit;

-- DDL事件属性函数
-- ORA_DICT_OBJ_NAME：对象名称
-- ORA_DICT_OBJ_TYPE：对象类型
-- ORA_DICT_OBJ_OWNER：对象所有者
-- ORA_SYSEVENT：系统事件
-- ORA_LOGIN_USER：登录用户

CREATE OR REPLACE TRIGGER trg_ddl_detail
AFTER DDL ON SCHEMA
BEGIN
    DBMS_OUTPUT.PUT_LINE('Event: ' || ORA_SYSEVENT);
    DBMS_OUTPUT.PUT_LINE('Object: ' || ORA_DICT_OBJ_NAME);
    DBMS_OUTPUT.PUT_LINE('Type: ' || ORA_DICT_OBJ_TYPE);
    DBMS_OUTPUT.PUT_LINE('Owner: ' || ORA_DICT_OBJ_OWNER);
    DBMS_OUTPUT.PUT_LINE('User: ' || ORA_LOGIN_USER);
END;
/

-- 登录触发器
CREATE OR REPLACE TRIGGER trg_logon_audit
AFTER LOGON ON DATABASE
BEGIN
    INSERT INTO logon_audit (
        user_name,
        session_id,
        logon_time,
        ip_address
    ) VALUES (
        USER,
        SYS_CONTEXT('USERENV', 'SESSIONID'),
        SYSTIMESTAMP,
        SYS_CONTEXT('USERENV', 'IP_ADDRESS')
    );
END;
/

-- 登出触发器
CREATE OR REPLACE TRIGGER trg_logoff_audit
BEFORE LOGOFF ON DATABASE
BEGIN
    UPDATE logon_audit
    SET logoff_time = SYSTIMESTAMP
    WHERE session_id = SYS_CONTEXT('USERENV', 'SESSIONID')
    AND logoff_time IS NULL;
END;
/

-- 限制登录时间
CREATE OR REPLACE TRIGGER trg_restrict_logon
BEFORE LOGON ON DATABASE
BEGIN
    IF TO_CHAR(SYSDATE, 'HH24') NOT BETWEEN 8 AND 18 THEN
        IF USER NOT IN ('SYS', 'SYSTEM', 'ADMIN') THEN
            RAISE_APPLICATION_ERROR(-20001, 'Login only allowed during business hours');
        END IF;
    END IF;
END;
/

-- 数据库启动触发器
CREATE OR REPLACE TRIGGER trg_startup
AFTER STARTUP ON DATABASE
BEGIN
    INSERT INTO db_event_log (event_type, event_time)
    VALUES ('STARTUP', SYSTIMESTAMP);
END;
/

-- 数据库关闭触发器
CREATE OR REPLACE TRIGGER trg_shutdown
BEFORE SHUTDOWN ON DATABASE
BEGIN
    INSERT INTO db_event_log (event_type, event_time)
    VALUES ('SHUTDOWN', SYSTIMESTAMP);
END;
/

-- 查看触发器信息
SELECT trigger_name, trigger_type, triggering_event, status
FROM user_triggers
WHERE table_name = 'EMPLOYEES';

-- 查看触发器源代码
SELECT text FROM user_source
WHERE name = 'TRG_SALARY_AUDIT' AND type = 'TRIGGER'
ORDER BY line;

-- 查看触发器详细信息
SELECT * FROM user_triggers WHERE trigger_name = 'TRG_SALARY_AUDIT';

-- 禁用触发器
ALTER TRIGGER trg_salary_audit DISABLE;

-- 启用触发器
ALTER TRIGGER trg_salary_audit ENABLE;

-- 禁用表的所有触发器
ALTER TABLE employees DISABLE ALL TRIGGERS;

-- 启用表的所有触发器
ALTER TABLE employees ENABLE ALL TRIGGERS;

-- 编译触发器
ALTER TRIGGER trg_salary_audit COMPILE;

-- 带调试信息编译
ALTER TRIGGER trg_salary_audit COMPILE DEBUG;

-- 删除触发器
DROP TRIGGER trg_salary_audit;

-- 删除不存在的触发器（避免错误）
DROP TRIGGER IF EXISTS trg_salary_audit;

-- 问题：递归触发
CREATE OR REPLACE TRIGGER trg_recursive
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    UPDATE employees SET salary = :NEW.salary * 1.1
    WHERE employee_id = :NEW.employee_id;  -- 再次触发UPDATE
END;
/

-- 解决：使用条件判断
CREATE OR REPLACE TRIGGER trg_no_recursive
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary != :OLD.salary * 1.1 THEN
        UPDATE employees SET salary = :NEW.salary * 1.1
        WHERE employee_id = :NEW.employee_id;
    END IF;
END;
/

-- 或使用包变量控制
CREATE OR REPLACE PACKAGE pkg_trigger_control AS
    g_in_trigger BOOLEAN := FALSE;
END;
/

CREATE OR REPLACE TRIGGER trg_controlled
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NOT pkg_trigger_control.g_in_trigger THEN
        pkg_trigger_control.g_in_trigger := TRUE;
        
        UPDATE employees SET salary = :NEW.salary * 1.1
        WHERE employee_id = :NEW.employee_id;
        
        pkg_trigger_control.g_in_trigger := FALSE;
    END IF;
END;
/

-- 1. 避免在触发器中执行复杂操作
-- 2. 使用WHEN条件减少触发次数
-- 3. 避免在触发器中查询同一表
-- 4. 考虑使用复合触发器（11g+）

-- 复合触发器（11g+）
CREATE OR REPLACE TRIGGER trg_compound_employee
FOR INSERT OR UPDATE ON employees
COMPOUND TRIGGER
    -- 声明部分（共享）
    TYPE t_emp_list IS TABLE OF NUMBER;
    v_emp_list t_emp_list := t_emp_list();
    
    -- 语句级BEFORE
    BEFORE STATEMENT IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Before statement');
    END BEFORE STATEMENT;
    
    -- 行级BEFORE
    BEFORE EACH ROW IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Before row: ' || :NEW.employee_id);
    END BEFORE EACH ROW;
    
    -- 行级AFTER
    AFTER EACH ROW IS
    BEGIN
        v_emp_list.EXTEND;
        v_emp_list(v_emp_list.LAST) := :NEW.employee_id;
    END AFTER EACH ROW;
    
    -- 语句级AFTER
    AFTER STATEMENT IS
    BEGIN
        FOR i IN 1..v_emp_list.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('After statement: ' || v_emp_list(i));
        END LOOP;
    END AFTER STATEMENT;
END;
/

-- 触发器中的错误处理
CREATE OR REPLACE TRIGGER trg_check_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
    v_max_salary NUMBER;
BEGIN
    SELECT max_salary INTO v_max_salary
    FROM jobs
    WHERE job_id = :NEW.job_id;
    
    IF :NEW.salary > v_max_salary THEN
        RAISE_APPLICATION_ERROR(-20001, 
            'Salary exceeds maximum for job: ' || v_max_salary);
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        NULL;  -- 允许没有job_id的员工
    WHEN OTHERS THEN
        -- 记录错误但允许操作继续
        INSERT INTO error_log (error_message, error_time)
        VALUES (SQLERRM, SYSTIMESTAMP);
END;
/

-- 通用审计触发器
CREATE OR REPLACE TRIGGER trg_audit_generic
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
    v_operation VARCHAR2(10);
BEGIN
    IF INSERTING THEN
        v_operation := 'INSERT';
    ELSIF UPDATING THEN
        v_operation := 'UPDATE';
    ELSIF DELETING THEN
        v_operation := 'DELETE';
    END IF;
    
    INSERT INTO audit_trail (
        table_name,
        operation,
        primary_key,
        old_values,
        new_values,
        operation_time,
        user_name
    ) VALUES (
        'EMPLOYEES',
        v_operation,
        COALESCE(:NEW.employee_id, :OLD.employee_id),
        CASE WHEN v_operation IN ('UPDATE', 'DELETE') 
             THEN :OLD.first_name || '|' || :OLD.last_name || '|' || :OLD.salary
        END,
        CASE WHEN v_operation IN ('INSERT', 'UPDATE')
             THEN :NEW.first_name || '|' || :NEW.last_name || '|' || :NEW.salary
        END,
        SYSTIMESTAMP,
        USER
    );
END;
/

-- 数据验证触发器
CREATE OR REPLACE TRIGGER trg_validate_employee
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
    -- 验证邮箱格式
    IF :NEW.email IS NOT NULL THEN
        IF NOT REGEXP_LIKE(:NEW.email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') THEN
            RAISE_APPLICATION_ERROR(-20001, 'Invalid email format');
        END IF;
    END IF;
    
    -- 验证薪资范围
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20002, 'Salary cannot be negative');
    END IF;
    
    -- 验证入职日期
    IF :NEW.hire_date > SYSDATE THEN
        RAISE_APPLICATION_ERROR(-20003, 'Hire date cannot be in the future');
    END IF;
END;
/

-- 自动计算字段
CREATE OR REPLACE TRIGGER trg_calculate_total
BEFORE INSERT OR UPDATE ON order_items
FOR EACH ROW
BEGIN
    :NEW.total_price := :NEW.quantity * :NEW.unit_price;
    :NEW.discount_amount := :NEW.total_price * NVL(:NEW.discount_pct, 0) / 100;
    :NEW.final_price := :NEW.total_price - :NEW.discount_amount;
END;
/

-- 自动更新时间戳
CREATE OR REPLACE TRIGGER trg_update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    :NEW.last_update_time := SYSTIMESTAMP;
    :NEW.last_update_user := USER;
END;
/