Oracle觸發器trigger詳解_oracle

觸發器相關概念及文法

概述

本篇博文中主要探討以下內容：

什麼是觸發器
觸發器的應用情境
觸發器的文法
觸發器的類型
案例

資料：

觸發器的概念和第一個觸發器

資料庫觸發器是一個與表相關聯的，儲存的PL/SQL 陳述式。

每當一個特定的資料動作陳述式（insert update delete）在指定的表上發出時，Oracle自動執行觸發器中定義的語句序列。

舉個簡單的例子：

當員工表中新增一條記錄後，自動列印“成功插入新員工”

create or replace trigger insertStaffHint after insert on xgj_test  for each rowdeclare -- local variables herebegin dbms_output.put_line('新增員工成功');end insertStaffHint;

觸發器的應用情境

複雜的安全性檢查
資料的確認
Database Audit
資料的備份和審計

觸發器的文法

CREATE [OR REPLACE] TRIGGER trigger_name{BEFORE | AFTER }{INSERT | DELETE | UPDATE [OF column [, column …]]}[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]ON [schema.]table_name | [schema.]view_name[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}][FOR EACH ROW ][WHEN condition]PL/SQL_BLOCK | CALL procedure_name;

其中：

BEFORE 和AFTER指出觸發器的觸發時序分別為前觸發和後觸發方式，前觸發是在執行觸發事件之前觸發當前所建立的觸發器，後觸發是在執行觸發事件之後觸發當前所建立的觸發器。

FOR EACH ROW選項說明觸發器為行觸發器。

行觸發器和語句觸發器的區別表現在：行觸發器要求當一個DML語句操走影響資料庫中的多行資料時，對於其中的每個資料行，只要它們符合觸發約束條件，均啟用一次觸發器；而語句觸發器將整個語句操作作為觸發事件，當它符合約束條件時，啟用一次觸發器。
當省略FOR EACH ROW 選項時，BEFORE 和AFTER 觸發器為語句觸發器，而INSTEAD OF 觸發器則只能為行觸發器
。

REFERENCING 子句說明相關名稱，在行觸發器的PL/SQL塊和WHEN 子句中可以使用相關名稱參照當前的新、舊列值，預設的相關名稱分別為OLD和NEW。觸發器的PL/SQL塊中應用相關名稱時，必須在它們之前加冒號(:)，但在WHEN子句中則不能加冒號。

WHEN 子句說明觸發約束條件。Condition 為一個邏輯表達時，其中必須包含相關名稱，而不能包含查詢語句，也不能調用PL/SQL 函數。WHEN 子句指定的觸發約束條件只能用在BEFORE 和AFTER 行觸發器中，不能用在INSTEAD OF 行觸發器和其它類型的觸發器中。

當一個基表被修改( INSERT, UPDATE, DELETE)時要執行的預存程序，執行時根據其所依附的基表改動而自動觸發，因此與應用程式無關，用資料庫觸發器可以保證資料的一致性和完整性。

每張表最多可建立12 種類型的觸發器：

BEFORE INSERTBEFORE INSERT FOR EACH ROWAFTER INSERTAFTER INSERT FOR EACH ROWBEFORE UPDATEBEFORE UPDATE FOR EACH ROWAFTER UPDATEAFTER UPDATE FOR EACH ROWBEFORE DELETEBEFORE DELETE FOR EACH ROWAFTER DELETEAFTER DELETE FOR EACH ROW

觸發器的類型

行觸發器要求當一個DML語句操作影響資料庫中的多行資料時，對於其中的每個資料行，只要它們符合觸發約束條件，均啟用一次觸發器； 在行級觸發器中，使用:old 和:new 偽記錄變數，識別值的狀態

語句觸發器將整個語句操作作為觸發事件，當它符合約束條件時，啟用一次觸發器。

當省略FOR EACH ROW 選項時，BEFORE 和AFTER 觸發器為語句觸發器，
而INSTEAD OF 觸發器則只能為行觸發器。

其他規則

觸發器名與過程名和包的名字不一樣，它是單獨的名字空間，因而觸發器名可以和表或過程有相同的名字，但在一個模式中觸發器名不能相同。

DML觸發器的限制：

CREATE TRIGGER語句文本的字元長度不能超過32KB；
觸發器體內的SELECT 語句只能為SELECT … INTO …結構，或者為定義遊標所使用的SELECT 語句。
觸發器中不能使用資料庫事務控制語句 COMMIT; ROLLBACK, SVAEPOINT 語句；
由觸發器所調用的過程或函數也不能使用資料庫事務控制語句；
觸發器中不能使用LONG, LONG RAW 類型；
觸發器內可以參照LOB 類型列的列值，但不能通過 :NEW 修改LOB列中的資料；

DML觸發器基本要點：

觸發時機：指定觸發器的觸發時間。如果指定為BEFORE，則表示在執行DML操作之前觸發，以便防止某些錯誤操作發生或實現某些商務規則；如果指定為AFTER，則表示在執行DML操作之後觸發，以便記錄該操作或做某些事後處理。
觸發事件：引起觸發器被觸發的事件，即DML操作（INSERT、UPDATE、DELETE）。既可以是單個觸發事件，也可以是多個觸發事件的組合（只能使用OR邏輯組合，不能使用AND邏輯組合）。
條件謂詞：當在觸發器中包含多個觸發事件（INSERT、UPDATE、DELETE）的組合時，為了分別針對不同的事件進行不同的處理，需要使用ORACLE提供的如下條件謂詞。
1）.INSERTING：當觸發事件是INSERT時，取值為TRUE，否則為FALSE。
2）.UPDATING [（column_1,column_2,…,column_x）]：當觸發事件是UPDATE 時，如果修改了column_x列，則取值為TRUE，否則為FALSE。其中column_x是可選的。
3）.DELETING：當觸發事件是DELETE時，則取值為TRUE，否則為FALSE。
解發對象：指定觸發器是建立在哪個表、視圖上。
觸發類型：是語句級還是行級觸發器
觸發條件：由WHEN子句指定一個邏輯運算式，只允許在行級觸發器上指定觸發條件，指定UPDATING後面的列的列表。

觸發器應用

觸發器應用一 複雜的安全性檢查

禁止在非工作時間插入資料

/**非工作時間（星球六 星期日， 非9點~18點的區間）禁止寫入資料首先要搞清楚： 觸發器的類型--語句級觸發器。不管插入了多少條資料，沒有必要對每一行資料都進行校正，只要不在這個時間段內，都不讓插入。*/create or replace trigger addStafffCheck before insert on xgj_test declare -- local variables herebegin if to_char(sysdate, 'day') in ('星期六', '星期日') or to_number(to_char(sysdate, 'hh24')) not between 9 and 18 then --禁止insert  raise_application_error(-20001,'非工作時間禁止插入資料'); end if;end addStafffCheck;

raise_application_error用於在plsql使用程式中自訂不正確訊息。
該異常只好在資料庫端的子程式（流程、函數、包、觸發器）中運用，而無法在匿名塊和用戶端的子程式中運用。
文法為raise_application_error(error_number,message[,[truefalse]]); 其中 error_number用於定義不正確號，
該不正確號必須在-20000到-20999之間的負整數； message用於指定不正確訊息，並且該訊息的長度無法超過2048位元組；

觸發器應用二 資料確認

工資不能越漲越少

分析： 首先確認下觸發器的類型， 因為需要對每一條資料進行確認，所以是個行級觸發器。 需要for each row

/**漲後的薪水不能低於漲前的薪水1 :old 和 :new 代表同一條記錄2 :old 代表操作該行之前，這一行的值 :new 代表操作該行之後，這一行的值*/create or replace trigger checkSalary before update  on xgj_test  for each rowdeclare -- local variables here 沒有變數聲明的話，declare可以省略begin --- if 漲後的薪水 < 漲前的薪水 then 如何表示呢 ？ if :new.sal < :old.sal then raise_application_error(-20002,'漲後的薪水:'|| :new.sal ||'小於漲前的薪水：'||:old.sal); end if;end checkSalary;

觸發器應用三 Database Audit

建立基於值的觸發器

create table xgj_record(info varchar2(256)) ;create or replace trigger addrecord after update on xgj_test  for each rowdeclare -- local variables herebegin if :new.sal > 6000 then  insert into xgj_record values(:new.sal ||'-'|| :new.username ||'-'|| :new.job); end if;end addrecord;

觸發器應用四 資料的備份和同步

當給員工漲完工資後，自動備份到備份表中

create table xgj_test_bak as select *　from xgj_test ;create or replace trigger databack after update on xgj_test for each row begin update xgj_test_bak set sal = :new.sal where username = :new.username ;end databack;

oracle中還有個利用快照備份，是非同步。 而利用觸發器，是同步的。

其他

建立一個觸發器, 當職工表 emp 表被刪除一條記錄時，把被刪除記錄寫到職工表刪除日誌表中去

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;CREATE OR REPLACE TRIGGER tr_del_emp BEFORE DELETE --指定觸發時機為刪除操作前觸發 ON scott.emp FOR EACH ROW --說明建立的是行級觸發器BEGIN --將修改前資料插入到記錄資料表 del_emp ,以供監督使用。 INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate ) VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );END;DELETE emp WHERE empno=7788;DROP TABLE emp_his;DROP TRIGGER del_emp;

限制對Departments表修改（包括INSERT,DELETE,UPDATE）的時間範圍，即不允許在非工作時間修改departments表。

CREATE OR REPLACE TRIGGER tr_dept_timeBEFORE INSERT OR DELETE OR UPDATEON departmentsBEGIN IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN RAISE_APPLICATION_ERROR(-20001, '不是上班時間，不能修改departments表'); END IF;END;

限定只對部門號為80的記錄進行行觸發器操作。

CREATE OR REPLACE TRIGGER tr_emp_sal_commBEFORE UPDATE OF salary, commission_pct OR DELETEON HR.employeesFOR EACH ROWWHEN (old.department_id = 80)BEGIN CASE WHEN UPDATING ('salary') THEN IF :NEW.salary < :old.salary THEN  RAISE_APPLICATION_ERROR(-20001, '部門80的人員的工資不能降'); END IF; WHEN UPDATING ('commission_pct') THEN IF :NEW.commission_pct < :old.commission_pct THEN  RAISE_APPLICATION_ERROR(-20002, '部門80的人員的獎金不能降'); END IF; WHEN DELETING THEN  RAISE_APPLICATION_ERROR(-20003, '不能刪除部門80的個人記錄'); END CASE;END;/*執行個體：UPDATE employees SET salary = 8000 WHERE employee_id = 177;DELETE FROM employees WHERE employee_id in (177,170);*/

利用行觸發器實現串聯更新。在修改了主表regions中的region_id之後（AFTER），級聯的、自動的更新子表countries表中原來在該地區的國家的region_id。

CREATE OR REPLACE TRIGGER tr_reg_couAFTER update OF region_idON regionsFOR EACH ROWBEGIN DBMS_OUTPUT.PUT_LINE('舊的region_id值是'||:old.region_id   ||'、新的region_id值是'||:new.region_id); UPDATE countries SET region_id = :new.region_id WHERE region_id = :old.region_id;END;

在觸發器中調用過程。

CREATE OR REPLACE PROCEDURE add_job_history ( p_emp_id  job_history.employee_id%type , p_start_date job_history.start_date%type , p_end_date job_history.end_date%type , p_job_id  job_history.job_id%type , p_department_id job_history.department_id%type )ISBEGIN INSERT INTO job_history (employee_id, start_date, end_date,    job_id, department_id) VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);END add_job_history;--建立觸發器調用預存程序...CREATE OR REPLACE TRIGGER update_job_history AFTER UPDATE OF job_id, department_id ON employees FOR EACH ROWBEGIN add_job_history(:old.employee_id, :old.hire_date, sysdate,   :old.job_id, :old.department_id);END;

以上就是本文的全部內容，希望對大家的學習有所協助，也希望大家多多支援雲棲社區。