1、觸發(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ǔ)句序列。

例如：當(dāng)員工信息插入后，自動(dòng)輸出“插入成功”的信息。

create or replace trigger empTrigger
 after insert on emp 
 for each row
declare
 -- 這里存放本地變量
begin
 dbms_output.put_line('插入成功！');
end empTrigger;

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

上面是一個(gè)觸發(fā)器簡(jiǎn)單的例子，我們接下來(lái)看下觸發(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;

其中：

（1）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ā)器。

（2）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ā)器。

（3）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)。

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

（5）當(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ù)的一致性和完整性。

行觸發(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ā)器。

3、觸發(fā)器的其他注意事項(xiàng)

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

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

（1）CREATE TRIGGER語(yǔ)句文本的字符長(zhǎng)度不能超過(guò)32KB。

（2）觸發(fā)器體內(nèi)的SELECT語(yǔ)句只能為SELECT … INTO結(jié)構(gòu)，或者為定義游標(biāo)所使用的SELECT語(yǔ)句。

（3）觸發(fā)器中不能使用數(shù)據(jù)庫(kù)事務(wù)控制語(yǔ)句COMMIT、ROLLBACK語(yǔ)句。

（4）由觸發(fā)器所調(diào)用的過(guò)程或函數(shù)也不能使用數(shù)據(jù)庫(kù)事務(wù)控制語(yǔ)句。

（5）觸發(fā)器中不能使用LONG、LONG RAW類型。

（6）觸發(fā)器內(nèi)可以參照LOB類型列的列值，但不能通過(guò) :NEW 修改LOB列中的數(shù)據(jù)。

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

（1）觸發(fā)時(shí)機(jī)：指定觸發(fā)器的觸發(fā)時(shí)間。如果指定為BEFORE，則表示在執(zhí)行DML操作之前觸發(fā)，以便防止某些錯(cuò)誤操作發(fā)生或?qū)崿F(xiàn)某些業(yè)務(wù)規(guī)則；如果指定為AFTER，則表示在執(zhí)行DML操作之后觸發(fā)，以便記錄該操作或做某些事后處理。

（2）觸發(fā)事件：引起觸發(fā)器被觸發(fā)的事件，即DML操作（INSERT、UPDATE、DELETE）。既可以是單個(gè)觸發(fā)事件，也可以是多個(gè)觸發(fā)事件的組合（只能使用OR邏輯組合，不能使用AND邏輯組合）。

（3）條件謂詞：當(dāng)在觸發(fā)器中包含多個(gè)觸發(fā)事件（INSERT、UPDATE、DELETE）的組合時(shí)，為了分別針對(duì)不同的事件進(jìn)行不同的處理，需要使用ORACLE提供的如下條件謂詞。

• INSERTING：當(dāng)觸發(fā)事件是INSERT時(shí)，取值為TRUE，否則為FALSE。
• UPDATING [（column_1,column_2,…,column_x）]：當(dāng)觸發(fā)事件是UPDATE時(shí)，如果修改了column_x列，則取值為TRUE，否則為FALSE。其中column_x是可選的。
• DELETING：當(dāng)觸發(fā)事件是DELETE時(shí)，則取值為TRUE，否則為FALSE。

（4）解發(fā)對(duì)象：指定觸發(fā)器是創(chuàng)建在哪個(gè)表、視圖上。

（5）觸發(fā)類型：是語(yǔ)句級(jí)還是行級(jí)觸發(fā)器。

（6）觸發(fā)條件：由WHEN子句指定一個(gè)邏輯表達(dá)式，只允許在行級(jí)觸發(fā)器上指定觸發(fā)條件，指定UPDATING后面的列的列表。

5、示例

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

create or replace trigger addEmpInfoCheck
 before insert on emp_info 
declare
begin

 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 addEmpInfoCheck;

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é)。

（2）漲薪后的工資應(yīng)該大于漲薪前的工資。

create or replace trigger checkSalary
 before update 
 on salary_info 
 for each row
declare
 --沒(méi)有變量聲明的話，declare可以省略
begin

 if :new.sal < :old.sal then
 raise_application_error(-20002,'漲后的薪水:'|| :new.sal ||'小于漲前的薪水：'||:old.sal);
 end if;
end checkSalary;

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

create table xzw_test(info varchar2(256));

create or replace trigger addData
 after update
 on xzw_test 
 for each row
declare
begin

 if :new.sal > 6000 then 
 insert into xzw_test values(:new.sal ||'-'|| :new.username ||'-'|| :new.job);
 end if;

end addData;

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論