【Oracle】触发器

个人主页：Guiat
归属专栏：Oracle

正文

1. 触发器基础概述

触发器是Oracle数据库中的一种特殊存储过程，当特定的数据库事件发生时自动执行。它是实现业务规则、数据完整性约束和审计功能的重要工具。

1.1 触发器的概念与特点

1.2 触发器的分类

1.3 触发器的执行顺序

2. DML触发器

2.1 基础DML触发器

2.1.1 INSERT触发器

-- 创建员工表和相关表用于演示
CREATE TABLE employees_demo AS SELECT * FROM employees WHERE 1=0;
ALTER TABLE employees_demo ADD CONSTRAINT pk_emp_demo PRIMARY KEY (employee_id);-- 创建员工审计表
CREATE TABLE employee_audit (audit_id NUMBER PRIMARY KEY,employee_id NUMBER,operation_type VARCHAR2(10),old_values VARCHAR2(4000),new_values VARCHAR2(4000),changed_by VARCHAR2(30),change_date DATE,session_id NUMBER
);CREATE SEQUENCE employee_audit_seq START WITH 1 INCREMENT BY 1;-- 创建INSERT触发器
CREATE OR REPLACE TRIGGER trg_employee_insert_auditAFTER INSERT ON employees_demoFOR EACH ROW
BEGIN-- 记录新员工插入的审计信息INSERT INTO employee_audit (audit_id,employee_id,operation_type,new_values,changed_by,change_date,session_id) VALUES (employee_audit_seq.NEXTVAL,:NEW.employee_id,'INSERT','ID: ' || :NEW.employee_id || ', Name: ' || :NEW.first_name || ' ' || :NEW.last_name ||', Email: ' || :NEW.email ||', Salary: ' || :NEW.salary ||', Hire Date: ' || TO_CHAR(:NEW.hire_date, 'YYYY-MM-DD'),USER,SYSDATE,SYS_CONTEXT('USERENV', 'SESSIONID'));-- 输出调试信息DBMS_OUTPUT.PUT_LINE('触发器执行: 新员工 ' || :NEW.first_name || ' ' || :NEW.last_name || ' 已插入');
END;
/-- 测试INSERT触发器
SET SERVEROUTPUT ONINSERT INTO employees_demo (employee_id, first_name, last_name, email, hire_date, job_id, salary
) VALUES (1001, 'John', 'Doe', 'john.doe@company.com', SYSDATE, 'IT_PROG', 6000
);INSERT INTO employees_demo (employee_id, first_name, last_name, email, hire_date, job_id, salary
) VALUES (1002, 'Jane', 'Smith', 'jane.smith@company.com', SYSDATE, 'SA_REP', 5500
);COMMIT;-- 查看审计记录
SELECT * FROM employee_audit ORDER BY change_date DESC;

2.1.2 UPDATE触发器

-- 创建UPDATE触发器
CREATE OR REPLACE TRIGGER trg_employee_update_auditAFTER UPDATE ON employees_demoFOR EACH ROW
DECLAREv_changes VARCHAR2(4000);
BEGIN-- 构建变更信息v_changes := '';IF :OLD.first_name != :NEW.first_name THENv_changes := v_changes || 'First Name: ' || :OLD.first_name || ' -> ' || :NEW.first_name || '; ';END IF;IF :OLD.last_name != :NEW.last_name THENv_changes := v_changes || 'Last Name: ' || :OLD.last_name || ' -> ' || :NEW.last_name || '; ';END IF;IF :OLD.email != :NEW.email THENv_changes := v_changes || 'Email: ' || :OLD.email || ' -> ' || :NEW.email || '; ';END IF;IF :OLD.salary != :NEW.salary THENv_changes := v_changes || 'Salary: ' || :OLD.salary || ' -> ' || :NEW.salary || '; ';END IF;IF :OLD.job_id != :NEW.job_id OR (:OLD.job_id IS NULL AND :NEW.job_id IS NOT NULL) OR (:OLD.job_id IS NOT NULL AND :NEW.job_id IS NULL) THENv_changes := v_changes || 'Job: ' || NVL(:OLD.job_id, 'NULL') || ' -> ' || NVL(:NEW.job_id, 'NULL') || '; ';END IF;-- 只有当有实际变更时才记录IF LENGTH(v_changes) > 0 THENINSERT INTO employee_audit (audit_id,employee_id,operation_type,old_values,new_values,changed_by,change_date,session_id) VALUES (employee_audit_seq.NEXTVAL,:NEW.employee_id,'UPDATE','Original: ID=' || :OLD.employee_id || ', Name=' || :OLD.first_name || ' ' || :OLD.last_name || ', Email=' || :OLD.email || ', Salary=' || :OLD.salary,'Updated: ' || RTRIM(v_changes, '; '),USER,SYSDATE,SYS_CONTEXT('USERENV', 'SESSIONID'));DBMS_OUTPUT.PUT_LINE('触发器执行: 员工 ' || :NEW.employee_id || ' 信息已更新');DBMS_OUTPUT.PUT_LINE('变更内容: ' || RTRIM(v_changes, '; '));END IF;
END;
/-- 测试UPDATE触发器
UPDATE employees_demo 
SET salary = 6500, email = 'john.doe.new@company.com'
WHERE employee_id = 1001;UPDATE employees_demo 
SET first_name = 'Janet'
WHERE employee_id = 1002;COMMIT;-- 查看更新审计记录
SELECT * FROM employee_audit WHERE operation_type = 'UPDATE' ORDER BY change_date DESC;

2.1.3 DELETE触发器

-- 创建DELETE触发器
CREATE OR REPLACE TRIGGER trg_employee_delete_auditBEFORE DELETE ON employees_demoFOR EACH ROW
BEGIN-- 记录删除前的员工信息INSERT INTO employee_audit (audit_id,employee_id,operation_type,old_values,changed_by,change_date,session_id) VALUES (employee_audit_seq.NEXTVAL,:OLD.employee_id,'DELETE','Deleted: ID=' || :OLD.employee_id || ', Name=' || :OLD.first_name || ' ' || :OLD.last_name ||', Email=' || :OLD.email ||', Salary=' || :OLD.salary ||', Job=' || :OLD.job_id,USER,SYSDATE,SYS_CONTEXT('USERENV', 'SESSIONID'));DBMS_OUTPUT.PUT_LINE('触发器执行: 员工 ' || :OLD.first_name || ' ' || :OLD.last_name || ' 即将被删除');
END;
/-- 测试DELETE触发器
DELETE FROM employees_demo WHERE employee_id = 1002;
COMMIT;-- 查看删除审计记录
SELECT * FROM employee_audit WHERE operation_type = 'DELETE' ORDER BY change_date DESC;

2.2 高级DML触发器

2.2.1 复合触发器

-- 创建复合触发器，处理工资历史记录
CREATE TABLE salary_history (history_id NUMBER PRIMARY KEY,employee_id NUMBER,old_salary NUMBER,new_salary NUMBER,change_date DATE,change_reason VARCHAR2(200),effective_date DATE
);CREATE SEQUENCE salary_history_seq START WITH 1 INCREMENT BY 1;-- 复合触发器
CREATE OR REPLACE TRIGGER trg_salary_managementFOR UPDATE OF salary ON employees_demoCOMPOUND TRIGGER-- 声明部分：定义包级变量TYPE emp_salary_rec IS RECORD (employee_id NUMBER,old_salary NUMBER,new_salary NUMBER);TYPE emp_salary_tab IS TABLE OF emp_salary_rec INDEX BY PLS_INTEGER;g_salary_changes emp_salary_tab;g_change_count PLS_INTEGER := 0;-- BEFORE STATEMENT: 语句执行前BEFORE STATEMENT ISBEGINDBMS_OUTPUT.PUT_LINE('=== 工资更新开始 ===');DBMS_OUTPUT.PUT_LINE('时间: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));g_change_count := 0;g_salary_changes.DELETE; -- 清空集合END BEFORE STATEMENT;-- BEFORE EACH ROW: 每行处理前BEFORE EACH ROW ISBEGIN-- 验证工资变更规则IF :NEW.salary <= 0 THENRAISE_APPLICATION_ERROR(-20001, '工资必须大于0');END IF;IF :NEW.salary > :OLD.salary * 2 THENRAISE_APPLICATION_ERROR(-20002, '工资增长不能超过100%');END IF;IF :NEW.salary < :OLD.salary * 0.5 THENRAISE_APPLICATION_ERROR(-20003, '工资减少不能超过50%');END IF;DBMS_OUTPUT.PUT_LINE('验证通过: 员工' || :NEW.employee_id || ' 工资从 $' || :OLD.salary || ' 变更为 $' || :NEW.salary);END BEFORE EACH ROW;-- AFTER EACH ROW: 每行处理后AFTER EACH ROW ISBEGIN-- 收集变更信息g_change_count := g_change_count + 1;g_salary_changes(g_change_count).employee_id := :NEW.employee_id;g_salary_changes(g_change_count).old_salary := :OLD.salary;g_salary_changes(g_change_count).new_salary := :NEW.salary;DBMS_OUTPUT.PUT_LINE('记录变更: 员工' || :NEW.employee_id || ' 工资变更已收集');END AFTER EACH ROW;-- AFTER STATEMENT: 语句执行后AFTER STATEMENT ISv_total_old_salary NUMBER := 0;v_total_new_salary NUMBER := 0;v_avg_increase_pct NUMBER;BEGIN-- 批量插入工资历史记录FOR i IN 1..g_change_count LOOPINSERT INTO salary_history (history_id,employee_id,old_salary,new_salary,change_date,change_reason,effective_date) VALUES (salary_history_seq.NEXTVAL,g_salary_changes(i).employee_id,g_salary_changes(i).old_salary,g_salary_changes(i).new_salary,SYSDATE,'系统更新',SYSDATE);v_total_old_salary := v_total_old_salary + g_salary_changes(i).old_salary;v_total_new_salary := v_total_new_salary + g_salary_changes(i).new_salary;END LOOP;-- 计算统计信息IF v_total_old_salary > 0 THENv_avg_increase_pct := ROUND((v_total_new_salary - v_total_old_salary) / v_total_old_salary * 100, 2);END IF;DBMS_OUTPUT.PUT_LINE('=== 工资更新完成 ===');DBMS_OUTPUT.PUT_LINE('更新员工数: ' || g_change_count);DBMS_OUTPUT.PUT_LINE('总原工资: $' || v_total_old_salary);DBMS_OUTPUT.PUT_LINE('总新工资: $' || v_total_new_salary);DBMS_OUTPUT.PUT_LINE('平均增长: ' || NVL(v_avg_increase_pct, 0) || '%');END AFTER STATEMENT;END trg_salary_management;
/-- 测试复合触发器
-- 插入测试数据
INSERT INTO employees_demo (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (1003, 'Bob', 'Johnson', 'bob.johnson@company.com', SYSDATE, 'IT_PROG', 5000);INSERT INTO employees_demo (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (1004, 'Alice', 'Brown', 'alice.brown@company.com', SYSDATE, 'SA_REP', 4500);COMMIT;-- 测试批量工资更新
UPDATE employees_demo 
SET salary = salary * 1.1 
WHERE employee_id IN (1001, 1003, 1004);COMMIT;-- 查看工资历史记录
SELECT * FROM salary_history ORDER BY change_date DESC;

2.2.2 条件触发器

-- 创建条件触发器：只在特定条件下触发
CREATE OR REPLACE TRIGGER trg_high_salary_alertAFTER UPDATE OF salary ON employees_demoFOR EACH ROWWHEN (NEW.salary > 10000) -- 条件：新工资超过10000
DECLAREv_manager_email VARCHAR2(100);v_dept_name VARCHAR2(50);
BEGIN-- 获取部门经理信息（模拟）DBMS_OUTPUT.PUT_LINE('=== 高工资预警 ===');DBMS_OUTPUT.PUT_LINE('员工: ' || :NEW.first_name || ' ' || :NEW.last_name);DBMS_OUTPUT.PUT_LINE('工资: $' || :OLD.salary || ' -> $' || :NEW.salary);DBMS_OUTPUT.PUT_LINE('增长: $' || (:NEW.salary - :OLD.salary));DBMS_OUTPUT.PUT_LINE('触发高工资预警，需要管理层审批');-- 记录到预警表INSERT INTO salary_alerts (alert_id,employee_id,old_salary,new_salary,alert_date,alert_type,status) VALUES (salary_alert_seq.NEXTVAL,:NEW.employee_id,:OLD.salary,:NEW.salary,SYSDATE,'HIGH_SALARY','PENDING');EXCEPTIONWHEN OTHERS THEN-- 创建预警表（如果不存在）EXECUTE IMMEDIATE 'CREATE TABLE salary_alerts (alert_id NUMBER PRIMARY KEY,employee_id NUMBER,old_salary NUMBER,new_salary NUMBER,alert_date DATE,alert_type VARCHAR2(20),status VARCHAR2(20))';EXECUTE IMMEDIATE 'CREATE SEQUENCE salary_alert_seq START WITH 1 INCREMENT BY 1';-- 重新插入记录INSERT INTO salary_alerts VALUES (1, :NEW.employee_id, :OLD.salary, :NEW.salary, SYSDATE, 'HIGH_SALARY', 'PENDING');
END;
/-- 测试条件触发器
UPDATE employees_demo SET salary = 12000 WHERE employee_id = 1001; -- 触发
UPDATE employees_demo SET salary = 8000 WHERE employee_id = 1003;  -- 不触发COMMIT;

3. INSTEAD OF 触发器

3.1 视图上的INSTEAD OF触发器

3.1.1 复杂视图的DML操作

-- 创建复杂视图
CREATE OR REPLACE VIEW employee_dept_view AS
SELECT e.employee_id,e.first_name,e.last_name,e.email,e.salary,e.hire_date,d.department_id,d.department_name,d.location_id,l.city,l.country_id
FROM employees_demo e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id;-- 创建INSTEAD OF INSERT触发器
CREATE OR REPLACE TRIGGER trg_employee_dept_insertINSTEAD OF INSERT ON employee_dept_viewFOR EACH ROW
DECLAREv_dept_exists NUMBER;v_location_exists NUMBER;
BEGINDBMS_OUTPUT.PUT_LINE('=== INSTEAD OF INSERT 触发器执行 ===');-- 检查部门是否存在SELECT COUNT(*) INTO v_dept_existsFROM departmentsWHERE department_id = :NEW.department_id;IF v_dept_exists = 0 THEN-- 如果部门不存在，先创建部门DBMS_OUTPUT.PUT_LINE('部门不存在，创建新部门: ' || :NEW.department_name);-- 检查位置是否存在SELECT COUNT(*) INTO v_location_existsFROM locationsWHERE location_id = :NEW.location_id;IF v_location_exists = 0 THEN-- 创建位置（简化处理）INSERT INTO locations (location_id, city, country_id)VALUES (:NEW.location_id, :NEW.city, :NEW.country_id);DBMS_OUTPUT.PUT_LINE('创建新位置: ' || :NEW.city);END IF;-- 创建部门INSERT INTO departments (department_id, department_name, location_id)VALUES (:NEW.department_id, :NEW.department_name, :NEW.location_id);END IF;-- 插入员工记录INSERT INTO employees_demo (employee_id, first_name, last_name, email, salary, hire_date, department_id) VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.email,:NEW.salary, :NEW.hire_date, :NEW.department_id);DBMS_OUTPUT.PUT_LINE('员工插入成功: ' || :NEW.first_name || ' ' || :NEW.last_name);EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('插入失败: ' || SQLERRM);RAISE;
END;
/-- 创建INSTEAD OF UPDATE触发器
CREATE OR REPLACE TRIGGER trg_employee_dept_updateINSTEAD OF UPDATE ON employee_dept_viewFOR EACH ROW
BEGINDBMS_OUTPUT.PUT_LINE('=== INSTEAD OF UPDATE 触发器执行 ===');-- 更新员工信息UPDATE employees_demoSET first_name = :NEW.first_name,last_name = :NEW.last_name,email = :NEW.email,salary = :NEW.salary,department_id = :NEW.department_idWHERE employee_id = :OLD.employee_id;DBMS_OUTPUT.PUT_LINE('员工更新: ' || SQL%ROWCOUNT || ' 行');-- 如果部门信息有变化，更新部门表IF :OLD.department_name != :NEW.department_name OR:OLD.location_id != :NEW.location_id THENUPDATE departmentsSET department_name = :NEW.department_name,location_id = :NEW.location_idWHERE department_id = :NEW.department_id;DBMS_OUTPUT.PUT_LINE('部门更新: ' || SQL%ROWCOUNT || ' 行');END IF;-- 如果位置信息有变化，更新位置表IF :OLD.city != :NEW.city OR :OLD.country_id != :NEW.country_id THENUPDATE locationsSET city = :NEW.city,country_id = :NEW.country_idWHERE location_id = :NEW.location_id;DBMS_OUTPUT.PUT_LINE('位置更新: ' || SQL%ROWCOUNT || ' 行');END IF;EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('更新失败: ' || SQLERRM);RAISE;
END;
/-- 创建INSTEAD OF DELETE触发器
CREATE OR REPLACE TRIGGER trg_employee_dept_deleteINSTEAD OF DELETE ON employee_dept_viewFOR EACH ROW
DECLAREv_emp_count NUMBER;
BEGINDBMS_OUTPUT.PUT_LINE('=== INSTEAD OF DELETE 触发器执行 ===');-- 删除员工DELETE FROM employees_demoWHERE employee_id = :OLD.employee_id;DBMS_OUTPUT.PUT_LINE('员工删除: ' || :OLD.first_name || ' ' || :OLD.last_name);-- 检查部门是否还有员工SELECT COUNT(*) INTO v_emp_countFROM employees_demoWHERE department_id = :OLD.department_id;IF v_emp_count = 0 THENDBMS_OUTPUT.PUT_LINE('部门 ' || :OLD.department_name || ' 已无员工，考虑删除部门');-- 可以选择删除空部门或保留END IF;EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('删除失败: ' || SQLERRM);RAISE;
END;
/-- 测试INSTEAD OF触发器
-- 通过视图插入数据
INSERT INTO employee_dept_view (employee_id, first_name, last_name, email, salary, hire_date,department_id, department_name, location_id, city, country_id
) VALUES (1005, 'Mike', 'Wilson', 'mike.wilson@company.com', 7000, SYSDATE,999, 'Research Lab', 9999, 'Innovation City', 'US'
);-- 通过视图更新数据
UPDATE employee_dept_view 
SET salary = 7500, department_name = 'Advanced Research Lab'
WHERE employee_id = 1005;-- 查看结果
SELECT * FROM employee_dept_view WHERE employee_id = 1005;COMMIT;

4. DDL触发器

4.1 系统级DDL触发器

4.1.1 数据库对象变更监控

-- 创建DDL审计表
CREATE TABLE ddl_audit_log (audit_id NUMBER PRIMARY KEY,username VARCHAR2(30),object_type VARCHAR2(30),object_name VARCHAR2(128),object_owner VARCHAR2(30),ddl_operation VARCHAR2(30),ddl_text CLOB,client_info VARCHAR2(64),host_name VARCHAR2(64),ip_address VARCHAR2(15),audit_timestamp DATE
);CREATE SEQUENCE ddl_audit_seq START WITH 1 INCREMENT BY 1;-- 创建DDL触发器
CREATE OR REPLACE TRIGGER trg_ddl_auditAFTER DDL ON SCHEMA -- 在当前SCHEMA上的DDL操作后触发
DECLAREv_ddl_text CLOB;v_client_info VARCHAR2(64);v_host_name VARCHAR2(64);v_ip_address VARCHAR2(15);
BEGIN-- 获取DDL语句文本SELECT xmlserialize(content xmlquery('//text()' passing xmltype(ora_sql_txt(1)) returning content)) INTO v_ddl_text FROM dual;-- 获取客户端信息v_client_info := SYS_CONTEXT('USERENV', 'CLIENT_INFO');v_host_name := SYS_CONTEXT('USERENV', 'HOST');v_ip_address := SYS_CONTEXT('USERENV', 'IP_ADDRESS');-- 记录DDL操作INSERT INTO ddl_audit_log (audit_id,username,object_type,object_name,object_owner,ddl_operation,ddl_text,client_info,host_name,ip_address,audit_timestamp) VALUES (ddl_audit_seq.NEXTVAL,ora_login_user,ora_dict_obj_type,ora_dict_obj_name,ora_dict_obj_owner,ora_sysevent,v_ddl_text,v_client_info,v_host_name,v_ip_address,SYSDATE);-- 输出监控信息DBMS_OUTPUT.PUT_LINE('=== DDL操作监控 ===');DBMS_OUTPUT.PUT_LINE('用户: ' || ora_login_user);DBMS_OUTPUT.PUT_LINE('操作: ' || ora_sysevent);DBMS_OUTPUT.PUT_LINE('对象类型: ' || ora_dict_obj_type);DBMS_OUTPUT.PUT_LINE('对象名称: ' || ora_dict_obj_name);DBMS_OUTPUT.PUT_LINE('对象所有者: ' || ora_dict_obj_owner);DBMS_OUTPUT.PUT_LINE('时间: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));EXCEPTIONWHEN OTHERS THEN-- DDL触发器中的异常处理要小心，避免阻止正常DDL操作INSERT INTO ddl_audit_log (audit_id, username, ddl_operation, ddl_text, audit_timestamp) VALUES (ddl_audit_seq.NEXTVAL, USER, 'ERROR', 'DDL audit error: ' || SQLERRM, SYSDATE);COMMIT;
END;
/-- 测试DDL触发器
-- 创建表
CREATE TABLE test_ddl_table (id NUMBER,description VARCHAR2(100)
);-- 修改表
ALTER TABLE test_ddl_table ADD created_date DATE DEFAULT SYSDATE;-- 创建索引
CREATE INDEX idx_test_ddl_id ON test_ddl_table(id);-- 删除对象
DROP INDEX idx_test_ddl_id;
DROP TABLE test_ddl_table;-- 查看DDL审计记录
SELECT username, ddl_operation, object_type, object_name, audit_timestamp
FROM ddl_audit_log
ORDER BY audit_timestamp DESC;

4.1.2 防止意外删除的保护触发器

-- 创建保护重要表的DDL触发器
CREATE OR REPLACE TRIGGER trg_protect_critical_tablesBEFORE DROP ON SCHEMA
DECLAREv_object_name VARCHAR2(128);v_current_time VARCHAR2(10);v_is_protected BOOLEAN := FALSE;-- 定义受保护的表列表TYPE protected_tables_type IS TABLE OF VARCHAR2(128);protected_tables protected_tables_type := protected_tables_type('EMPLOYEES_DEMO','EMPLOYEE_AUDIT', 'SALARY_HISTORY','DDL_AUDIT_LOG');BEGINv_object_name := ora_dict_obj_name;v_current_time := TO_CHAR(SYSDATE, 'HH24:MI:SS');-- 检查是否为受保护的表FOR i IN 1..protected_tables.COUNT LOOPIF UPPER(v_object_name) = protected_tables(i) THENv_is_protected := TRUE;EXIT;END IF;END LOOP;IF v_is_protected THEN-- 在工作时间（9:00-18:00）禁止删除重要表IF TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) BETWEEN 9 AND 18 THENRAISE_APPLICATION_ERROR(-20100, '禁止在工作时间（9:00-18:00）删除重要表: ' || v_object_name);END IF;-- 记录删除尝试INSERT INTO ddl_audit_log (audit_id, username, object_type, object_name, ddl_operation,ddl_text, audit_timestamp) VALUES (ddl_audit_seq.NEXTVAL, USER, ora_dict_obj_type, v_object_name, 'DROP_ATTEMPT','尝试删除受保护的表: ' || v_object_name, SYSDATE);COMMIT;-- 发出警告但允许删除（非工作时间）DBMS_OUTPUT.PUT_LINE('警告: 正在删除重要表 ' || v_object_name);DBMS_OUTPUT.PUT_LINE('删除时间: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));DBMS_OUTPUT.PUT_LINE('操作用户: ' || USER);END IF;EXCEPTIONWHEN OTHERS THEN-- 记录错误但不阻止操作NULL;
END;
/-- 测试保护触发器
-- 创建测试表
CREATE TABLE test_protected_table AS SELECT * FROM employees_demo WHERE 1=0;-- 尝试删除（应该成功，因为不在保护列表中）
DROP TABLE test_protected_table;-- 尝试删除受保护的表（在工作时间会被阻止）
-- DROP TABLE employees_demo; -- 取消注释来测试

5. 系统事件触发器

5.1 登录和注销触发器

5.1.1 用户会话监控

-- 创建会话监控表
CREATE TABLE user_session_log (log_id NUMBER PRIMARY KEY,username VARCHAR2(30),session_id NUMBER,host_name VARCHAR2(64),ip_address VARCHAR2(15),program VARCHAR2(64),module VARCHAR2(64),login_time DATE,logout_time DATE,session_duration NUMBER, -- 分钟status VARCHAR2(20)
);CREATE SEQUENCE session_log_seq START WITH 1 INCREMENT BY 1;-- 创建登录触发器
CREATE OR REPLACE TRIGGER trg_user_loginAFTER LOGON ON SCHEMA
DECLAREv_session_id NUMBER;v_host_name VARCHAR2(64);v_ip_address VARCHAR2(15);v_program VARCHAR2(64);v_module VARCHAR2(64);
BEGIN-- 获取会话信息v_session_id := SYS_CONTEXT('USERENV', 'SESSIONID');v_host_name := SYS_CONTEXT('USERENV', 'HOST');v_ip_address := SYS_CONTEXT('USERENV', 'IP_ADDRESS');v_program := SYS_CONTEXT('USERENV', 'PROGRAM');v_module := SYS_CONTEXT('USERENV', 'MODULE');-- 记录登录信息INSERT INTO user_session_log (log_id,username,session_id,host_name,ip_address,program,module,login_time,status) VALUES (session_log_seq.NEXTVAL,USER,v_session_id,v_host_name,v_ip_address,v_program,v_module,SYSDATE,'ACTIVE');COMMIT;-- 检查是否为可疑登录DECLAREv_recent_logins NUMBER;v_different_hosts NUMBER;BEGIN-- 检查最近1小时内的登录次数SELECT COUNT(*) INTO v_recent_loginsFROM user_session_logWHERE username = USERAND login_time >= SYSDATE - 1/24;-- 检查是否从不同主机登录SELECT COUNT(DISTINCT host_name) INTO v_different_hostsFROM user_session_logWHERE username = USERAND login_time >= SYSDATE - 1/24;-- 可疑活动警报IF v_recent_logins > 10 THENDBMS_OUTPUT.PUT_LINE('警告: 用户 ' || USER || ' 1小时内登录超过10次');END IF;IF v_different_hosts > 3 THENDBMS_OUTPUT.PUT_LINE('警告: 用户 ' || USER || ' 从多个不同主机登录');END IF;END;EXCEPTIONWHEN OTHERS THEN-- 登录触发器不应阻止用户登录NULL;
END;
/-- 创建注销触发器
CREATE OR REPLACE TRIGGER trg_user_logoutBEFORE LOGOFF ON SCHEMA
DECLAREv_session_id NUMBER;v_login_time DATE;v_duration NUMBER;
BEGINv_session_id := SYS_CONTEXT('USERENV', 'SESSIONID');-- 获取登录时间SELECT login_time INTO v_login_timeFROM user_session_logWHERE session_id = v_session_idAND username = USERAND status = 'ACTIVE'AND ROWNUM = 1ORDER BY login_time DESC;-- 计算会话持续时间（分钟）v_duration := ROUND((SYSDATE - v_login_time) * 24 * 60, 2);-- 更新注销信息UPDATE user_session_logSET logout_time = SYSDATE,session_duration = v_duration,status = 'COMPLETED'WHERE session_id = v_session_idAND username = USERAND status = 'ACTIVE';COMMIT;EXCEPTIONWHEN NO_DATA_FOUND THEN-- 可能是登录触发器未记录的会话INSERT INTO user_session_log (log_id, username, session_id, logout_time, status) VALUES (session_log_seq.NEXTVAL, USER, v_session_id, SYSDATE, 'LOGOUT_ONLY');COMMIT;WHEN OTHERS THEN-- 注销触发器也不应阻止用户注销NULL;
END;
/-- 创建会话统计查询
CREATE OR REPLACE PROCEDURE show_session_statistics(p_username IN VARCHAR2 DEFAULT NULL,p_days_back IN NUMBER DEFAULT 7
)
AS
BEGINDBMS_OUTPUT.PUT_LINE('=== 用户会话统计 ===');DBMS_OUTPUT.PUT_LINE('统计期间: 最近 ' || p_days_back || ' 天');IF p_username IS NOT NULL THENDBMS_OUTPUT.PUT_LINE('用户: ' || p_username);END IF;DBMS_OUTPUT.PUT_LINE('');-- 显示会话统计FOR rec IN (SELECT username,COUNT(*) AS total_sessions,ROUND(AVG(session_duration), 2) AS avg_duration_min,MAX(session_duration) AS max_duration_min,COUNT(DISTINCT host_name) AS unique_hosts,MAX(login_time) AS last_loginFROM user_session_logWHERE (p_username IS NULL OR username = p_username)AND login_time >= SYSDATE - p_days_backGROUP BY usernameORDER BY total_sessions DESC) LOOPDBMS_OUTPUT.PUT_LINE('用户: ' || rec.username);DBMS_OUTPUT.PUT_LINE('  总会话数: ' || rec.total_sessions);DBMS_OUTPUT.PUT_LINE('  平均时长: ' || NVL(rec.avg_duration_min, 0) || ' 分钟');DBMS_OUTPUT.PUT_LINE('  最长时长: ' || NVL(rec.max_duration_min, 0) || ' 分钟');DBMS_OUTPUT.PUT_LINE('  不同主机: ' || rec.unique_hosts);DBMS_OUTPUT.PUT_LINE('  最后登录: ' || TO_CHAR(rec.last_login, 'YYYY-MM-DD HH24:MI:SS'));DBMS_OUTPUT.PUT_LINE('---');END LOOP;
END;
/-- 查看会话监控结果
SELECT * FROM user_session_log ORDER BY login_time DESC;-- 执行统计查询
EXEC show_session_statistics;

结语
感谢您的阅读！期待您的一键三连！欢迎指正！