觸發(fā)器相關(guān)概念及語(yǔ)法

概述

本篇博文中主要探討以下內(nèi)容：

什么是觸發(fā)器
觸發(fā)器的應(yīng)用場(chǎng)景
觸發(fā)器的語(yǔ)法
觸發(fā)器的類型
案例

數(shù)據(jù)：

觸發(fā)器的概念和第一個(gè)觸發(fā)器

數(shù)據(jù)庫(kù)觸發(fā)器是一個(gè)與表相關(guān)聯(lián)的，存儲(chǔ)的PL/SQL 語(yǔ)句。

每當(dāng)一個(gè)特定的數(shù)據(jù)操作語(yǔ)句（insert update delete）在指定的表上發(fā)出時(shí)，Oracle自動(dòng)執(zhí)行觸發(fā)器中定義的語(yǔ)句序列。

舉個(gè)簡(jiǎn)單的例子：

當(dāng)員工表中新增一條記錄后，自動(dòng)打印“成功插入新員工”

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

觸發(fā)器的應(yīng)用場(chǎng)景

復(fù)雜的安全性檢查
數(shù)據(jù)的確認(rèn)
數(shù)據(jù)庫(kù)審計(jì)
數(shù)據(jù)的備份和審計(jì)

觸發(fā)器的語(yǔ)法

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指出觸發(fā)器的觸發(fā)時(shí)序分別為前觸發(fā)和后觸發(fā)方式，前觸發(fā)是在執(zhí)行觸發(fā)事件之前觸發(fā)當(dāng)前所創(chuàng)建的觸發(fā)器，后觸發(fā)是在執(zhí)行觸發(fā)事件之后觸發(fā)當(dāng)前所創(chuàng)建的觸發(fā)器。

FOR EACH ROW選項(xiàng)說(shuō)明觸發(fā)器為行觸發(fā)器。

行觸發(fā)器和語(yǔ)句觸發(fā)器的區(qū)別表現(xiàn)在：行觸發(fā)器要求當(dāng)一個(gè)DML語(yǔ)句操走影響數(shù)據(jù)庫(kù)中的多行數(shù)據(jù)時(shí)，對(duì)于其中的每個(gè)數(shù)據(jù)行，只要它們符合觸發(fā)約束條件，均激活一次觸發(fā)器；而語(yǔ)句觸發(fā)器將整個(gè)語(yǔ)句操作作為觸發(fā)事件，當(dāng)它符合約束條件時(shí)，激活一次觸發(fā)器。
當(dāng)省略FOR EACH ROW 選項(xiàng)時(shí)，BEFORE 和AFTER 觸發(fā)器為語(yǔ)句觸發(fā)器，而INSTEAD OF 觸發(fā)器則只能為行觸發(fā)器
。

REFERENCING 子句說(shuō)明相關(guān)名稱，在行觸發(fā)器的PL/SQL塊和WHEN 子句中可以使用相關(guān)名稱參照當(dāng)前的新、舊列值，默認(rèn)的相關(guān)名稱分別為OLD和NEW。觸發(fā)器的PL/SQL塊中應(yīng)用相關(guān)名稱時(shí)，必須在它們之前加冒號(hào)(:)，但在WHEN子句中則不能加冒號(hào)。

WHEN 子句說(shuō)明觸發(fā)約束條件。Condition 為一個(gè)邏輯表達(dá)時(shí)，其中必須包含相關(guān)名稱，而不能包含查詢語(yǔ)句，也不能調(diào)用PL/SQL 函數(shù)。WHEN 子句指定的觸發(fā)約束條件只能用在BEFORE 和AFTER 行觸發(fā)器中，不能用在INSTEAD OF 行觸發(fā)器和其它類型的觸發(fā)器中。

當(dāng)一個(gè)基表被修改( INSERT, UPDATE, DELETE)時(shí)要執(zhí)行的存儲(chǔ)過(guò)程，執(zhí)行時(shí)根據(jù)其所依附的基表改動(dòng)而自動(dòng)觸發(fā)，因此與應(yīng)用程序無(wú)關(guān)，用數(shù)據(jù)庫(kù)觸發(fā)器可以保證數(shù)據(jù)的一致性和完整性。

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

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

觸發(fā)器的類型

行觸發(fā)器要求當(dāng)一個(gè)DML語(yǔ)句操作影響數(shù)據(jù)庫(kù)中的多行數(shù)據(jù)時(shí)，對(duì)于其中的每個(gè)數(shù)據(jù)行，只要它們符合觸發(fā)約束條件，均激活一次觸發(fā)器； 在行級(jí)觸發(fā)器中，使用:old 和:new 偽記錄變量，識(shí)別值的狀態(tài)

語(yǔ)句觸發(fā)器將整個(gè)語(yǔ)句操作作為觸發(fā)事件，當(dāng)它符合約束條件時(shí)，激活一次觸發(fā)器。

當(dāng)省略FOR EACH ROW 選項(xiàng)時(shí)，BEFORE 和AFTER 觸發(fā)器為語(yǔ)句觸發(fā)器，
而INSTEAD OF 觸發(fā)器則只能為行觸發(fā)器。

其他規(guī)則

觸發(fā)器名與過(guò)程名和包的名字不一樣，它是單獨(dú)的名字空間，因而觸發(fā)器名可以和表或過(guò)程有相同的名字，但在一個(gè)模式中觸發(fā)器名不能相同。

DML觸發(fā)器的限制：

CREATE TRIGGER語(yǔ)句文本的字符長(zhǎng)度不能超過(guò)32KB；
觸發(fā)器體內(nèi)的SELECT 語(yǔ)句只能為SELECT … INTO …結(jié)構(gòu)，或者為定義游標(biāo)所使用的SELECT 語(yǔ)句。
觸發(fā)器中不能使用數(shù)據(jù)庫(kù)事務(wù)控制語(yǔ)句 COMMIT; ROLLBACK, SVAEPOINT 語(yǔ)句；
由觸發(fā)器所調(diào)用的過(guò)程或函數(shù)也不能使用數(shù)據(jù)庫(kù)事務(wù)控制語(yǔ)句；
觸發(fā)器中不能使用LONG, LONG RAW 類型；
觸發(fā)器內(nèi)可以參照LOB 類型列的列值，但不能通過(guò) :NEW 修改LOB列中的數(shù)據(jù)；

DML觸發(fā)器基本要點(diǎn)：

觸發(fā)時(shí)機(jī)：指定觸發(fā)器的觸發(fā)時(shí)間。如果指定為BEFORE，則表示在執(zhí)行DML操作之前觸發(fā)，以便防止某些錯(cuò)誤操作發(fā)生或?qū)崿F(xiàn)某些業(yè)務(wù)規(guī)則；如果指定為AFTER，則表示在執(zhí)行DML操作之后觸發(fā)，以便記錄該操作或做某些事后處理。
觸發(fā)事件：引起觸發(fā)器被觸發(fā)的事件，即DML操作（INSERT、UPDATE、DELETE）。既可以是單個(gè)觸發(fā)事件，也可以是多個(gè)觸發(fā)事件的組合（只能使用OR邏輯組合，不能使用AND邏輯組合）。
條件謂詞：當(dāng)在觸發(fā)器中包含多個(gè)觸發(fā)事件（INSERT、UPDATE、DELETE）的組合時(shí)，為了分別針對(duì)不同的事件進(jìn)行不同的處理，需要使用ORACLE提供的如下條件謂詞。
1）.INSERTING：當(dāng)觸發(fā)事件是INSERT時(shí)，取值為TRUE，否則為FALSE。
2）.UPDATING [（column_1,column_2,…,column_x）]：當(dāng)觸發(fā)事件是UPDATE 時(shí)，如果修改了column_x列，則取值為TRUE，否則為FALSE。其中column_x是可選的。
3）.DELETING：當(dāng)觸發(fā)事件是DELETE時(shí)，則取值為TRUE，否則為FALSE。
解發(fā)對(duì)象：指定觸發(fā)器是創(chuàng)建在哪個(gè)表、視圖上。
觸發(fā)類型：是語(yǔ)句級(jí)還是行級(jí)觸發(fā)器
觸發(fā)條件：由WHEN子句指定一個(gè)邏輯表達(dá)式，只允許在行級(jí)觸發(fā)器上指定觸發(fā)條件，指定UPDATING后面的列的列表。

觸發(fā)器應(yīng)用

觸發(fā)器應(yīng)用一 復(fù)雜的安全性檢查

禁止在非工作時(shí)間插入數(shù)據(jù)

/**非工作時(shí)間（星球六 星期日， 非9點(diǎn)~18點(diǎn)的區(qū)間）禁止寫入數(shù)據(jù)首先要搞清楚： 觸發(fā)器的類型--語(yǔ)句級(jí)觸發(fā)器。不管插入了多少條數(shù)據(jù)，沒(méi)有必要對(duì)每一行數(shù)據(jù)都進(jìn)行校驗(yàn)，只要不在這個(gè)時(shí)間段內(nèi)，都不讓插入。*/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,'非工作時(shí)間禁止插入數(shù)據(jù)'); end if;end addStafffCheck;

raise_application_error用于在plsql使用程序中自定義不正確消息。
該異常只好在數(shù)據(jù)庫(kù)端的子程序（流程、函數(shù)、包、觸發(fā)器）中運(yùn)用，而無(wú)法在匿名塊和客戶端的子程序中運(yùn)用。
語(yǔ)法為raise_application_error(error_number,message[,[truefalse]]); 其中 error_number用于定義不正確號(hào)，
該不正確號(hào)必須在-20000到-20999之間的負(fù)整數(shù)； message用于指定不正確消息，并且該消息的長(zhǎng)度無(wú)法超過(guò)2048字節(jié)；

觸發(fā)器應(yīng)用二 數(shù)據(jù)確認(rèn)

工資不能越漲越少

分析： 首先確認(rèn)下觸發(fā)器的類型， 因?yàn)樾枰獙?duì)每一條數(shù)據(jù)進(jìn)行確認(rèn)，所以是個(gè)行級(jí)觸發(fā)器。 需要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 沒(méi)有變量聲明的話，declare可以省略begin --- if 漲后的薪水 < 漲前的薪水 then 如何表示呢 ？ if :new.sal < :old.sal then raise_application_error(-20002,'漲后的薪水:'|| :new.sal ||'小于漲前的薪水：'||:old.sal); end if;end checkSalary;

觸發(fā)器應(yīng)用三 數(shù)據(jù)庫(kù)審計(jì)

創(chuàng)建基于值的觸發(fā)器

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;

觸發(fā)器應(yīng)用四 數(shù)據(jù)的備份和同步

當(dāng)給員工漲完工資后，自動(dòng)備份到備份表中

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中還有個(gè)利用快照備份，是異步的。 而利用觸發(fā)器，是同步的。

其他

建立一個(gè)觸發(fā)器, 當(dāng)職工表 emp 表被刪除一條記錄時(shí)，把被刪除記錄寫到職工表刪除日志表中去

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;CREATE OR REPLACE TRIGGER tr_del_emp BEFORE DELETE --指定觸發(fā)時(shí)機(jī)為刪除操作前觸發(fā) ON scott.emp FOR EACH ROW --說(shuō)明創(chuàng)建的是行級(jí)觸發(fā)器BEGIN --將修改前數(shù)據(jù)插入到日志記錄表 del_emp ,以供監(jiān)督使用。 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;

限制對(duì)Departments表修改（包括INSERT,DELETE,UPDATE）的時(shí)間范圍，即不允許在非工作時(shí)間修改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, '不是上班時(shí)間，不能修改departments表'); END IF;END;

限定只對(duì)部門號(hào)為80的記錄進(jìn)行行觸發(fā)器操作。

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的人員的獎(jiǎng)金不能降'); END IF; WHEN DELETING THEN  RAISE_APPLICATION_ERROR(-20003, '不能刪除部門80的人員記錄'); END CASE;END;/*實(shí)例：UPDATE employees SET salary = 8000 WHERE employee_id = 177;DELETE FROM employees WHERE employee_id in (177,170);*/

利用行觸發(fā)器實(shí)現(xiàn)級(jí)聯(lián)更新。在修改了主表regions中的region_id之后（AFTER），級(jí)聯(lián)的、自動(dòng)的更新子表countries表中原來(lái)在該地區(qū)的國(guó)家的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;

在觸發(fā)器中調(diào)用過(guò)程。

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;--創(chuàng)建觸發(fā)器調(diào)用存儲(chǔ)過(guò)程...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;

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持VeVb武林網(wǎng)。