個人主頁：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;

結語
感謝您的閱讀！期待您的一鍵三連！歡迎指正！