1、觸發(fā)器的定義

數(shù)據(jù)庫觸發(fā)器是一個與表相關(guān)聯(lián)、存儲PL/SQL語句的“東西”。

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

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

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

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

上面是一個觸發(fā)器簡單的例子，我們接下來看下觸發(fā)器的語法：

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

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

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

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

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

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

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

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

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

（1）CREATE TRIGGER語句文本的字符長度不能超過32KB。

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

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

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

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

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

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

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

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

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

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

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

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

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

5、示例

（1）禁止在非工作時間插入數(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ù)據(jù)！');
 end if;
end addEmpInfoCheck;

raise_application_error用于在plsql使用程序中自定義不正確消息。

該異常只在數(shù)據(jù)庫端的子程序（流程、函數(shù)、包、觸發(fā)器）中運用，而無法在匿名塊和客戶端的子程序中運用。

語法為raise_application_error(error_number,message[,[truefalse]])。

其中error_number用于定義不正確號，該不正確號必須在-20000到-20999之間的負(fù)整數(shù)；message用于指定不正確消息，并且該消息的長度無法超過2048字節(jié)。

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

create or replace trigger checkSalary
 before update 
 on salary_info 
 for each row
declare
 --沒有變量聲明的話，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é)

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

最新評論