轉(zhuǎn)載自http://blog.csdn.net/indexman/article/details/8023740/
觸發(fā)器是許多關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)都提供的一項(xiàng)技術(shù)。在oracle系統(tǒng)里财破，觸發(fā)器類似過(guò)程和函數(shù)烦秩，都有聲明，執(zhí)行和異常處理過(guò)程的PL/SQL塊尊搬。
8.1?觸發(fā)器類型
??? 觸發(fā)器在數(shù)據(jù)庫(kù)里以獨(dú)立的對(duì)象存儲(chǔ)扛或，它與存儲(chǔ)過(guò)程和函數(shù)不同的是洲劣，存儲(chǔ)過(guò)程與函數(shù)需要用戶顯示調(diào)用才執(zhí)行，而觸發(fā)器是由一個(gè)事件來(lái)啟動(dòng)運(yùn)行空幻。即觸發(fā)器是當(dāng)某個(gè)事件發(fā)生時(shí)自動(dòng)地隱式運(yùn)行烁峭。并且，觸發(fā)器不能接收參數(shù)秕铛。所以運(yùn)行觸發(fā)器就叫觸發(fā)或點(diǎn)火（firing）约郁。ORACLE事件指的是對(duì)數(shù)據(jù)庫(kù)的表進(jìn)行的INSERT缩挑、UPDATE及DELETE操作或?qū)σ晥D進(jìn)行類似的操作。ORACLE將觸發(fā)器的功能擴(kuò)展到了觸發(fā)ORACLE棍现，如數(shù)據(jù)庫(kù)的啟動(dòng)與關(guān)閉等调煎。所以觸發(fā)器常用來(lái)完成由數(shù)據(jù)庫(kù)的完整性約束難以完成的復(fù)雜業(yè)務(wù)規(guī)則的約束，或用來(lái)監(jiān)視對(duì)數(shù)據(jù)庫(kù)的各種操作己肮，實(shí)現(xiàn)審計(jì)的功能士袄。
8.1.1 DML觸發(fā)器
??? ORACLE可以在DML語(yǔ)句進(jìn)行觸發(fā)，可以在DML操作前或操作后進(jìn)行觸發(fā)谎僻，并且可以對(duì)每個(gè)行或語(yǔ)句操作上進(jìn)行觸發(fā)娄柳。
8.1.2 替代觸發(fā)器
??? 由于在ORACLE里，不能直接對(duì)由兩個(gè)以上的表建立的視圖進(jìn)行操作艘绍。所以給出了替代觸發(fā)器赤拒。它就是ORACLE 8專門(mén)為進(jìn)行視圖操作的一種處理方法。
8.1.3 系統(tǒng)觸發(fā)器
ORACLE 8i 提供了第三種類型的觸發(fā)器叫系統(tǒng)觸發(fā)器诱鞠。它可以在ORACLE數(shù)據(jù)庫(kù)系統(tǒng)的事件中進(jìn)行觸發(fā)挎挖，如ORACLE系統(tǒng)的啟動(dòng)與關(guān)閉等。
觸發(fā)器組成:?
l?????????觸發(fā)事件：引起觸發(fā)器被觸發(fā)的事件航夺。 例如：DML語(yǔ)句(INSERT, UPDATE, DELETE語(yǔ)句對(duì)表或視圖執(zhí)行數(shù)據(jù)處理操作)蕉朵、DDL語(yǔ)句（如CREATE、ALTER阳掐、DROP語(yǔ)句在數(shù)據(jù)庫(kù)中創(chuàng)建始衅、修改、刪除模式對(duì)象）缭保、數(shù)據(jù)庫(kù)系統(tǒng)事件（如系統(tǒng)啟動(dòng)或退出汛闸、異常錯(cuò)誤）、用戶事件（如登錄或退出數(shù)據(jù)庫(kù)）艺骂。
l?????????觸發(fā)時(shí)間：即該TRIGGER 是在觸發(fā)事件發(fā)生之前（BEFORE）還是之后(AFTER)觸發(fā)诸老，也就是觸發(fā)事件和該TRIGGER 的操作順序。
l?????????觸發(fā)操作：即該TRIGGER 被觸發(fā)之后的目的和意圖彻亲，正是觸發(fā)器本身要做的事情孕锄。 例如：PL/SQL 塊。
l?????????觸發(fā)對(duì)象：包括表苞尝、視圖、模式宦芦、數(shù)據(jù)庫(kù)宙址。只有在這些對(duì)象上發(fā)生了符合觸發(fā)條件的觸發(fā)事件，才會(huì)執(zhí)行觸發(fā)操作调卑。
l?????????觸發(fā)條件：由WHEN子句指定一個(gè)邏輯表達(dá)式抡砂。只有當(dāng)該表達(dá)式的值為T(mén)RUE時(shí)大咱，遇到觸發(fā)事件才會(huì)自動(dòng)執(zhí)行觸發(fā)器，使其執(zhí)行觸發(fā)操作注益。
l?????????觸發(fā)頻率：說(shuō)明觸發(fā)器內(nèi)定義的動(dòng)作被執(zhí)行的次數(shù)碴巾。即語(yǔ)句級(jí)(STATEMENT)觸發(fā)器和行級(jí)(ROW)觸發(fā)器。
語(yǔ)句級(jí)(STATEMENT)觸發(fā)器：是指當(dāng)某觸發(fā)事件發(fā)生時(shí)丑搔，該觸發(fā)器只執(zhí)行一次厦瓢；
行級(jí)(ROW)觸發(fā)器：是指當(dāng)某觸發(fā)事件發(fā)生時(shí)，對(duì)受到該操作影響的每一行數(shù)據(jù)啤月，觸發(fā)器都單獨(dú)執(zhí)行一次煮仇。
編寫(xiě)觸發(fā)器時(shí)，需要注意以下幾點(diǎn)：
l???????? 觸發(fā)器不接受參數(shù)谎仲。
l???????? 一個(gè)表上最多可有12個(gè)觸發(fā)器浙垫，但同一時(shí)間、同一事件郑诺、同一類型的觸發(fā)器只能有一個(gè)夹姥。并各觸發(fā)器之間不能有矛盾。
l???????? 在一個(gè)表上的觸發(fā)器越多辙诞，對(duì)在該表上的DML操作的性能影響就越大辙售。
l????????觸發(fā)器最大為32KB。若確實(shí)需要倘要，可以先建立過(guò)程圾亏，然后在觸發(fā)器中用CALL語(yǔ)句進(jìn)行調(diào)用。
l在觸發(fā)器的執(zhí)行部分只能用DML語(yǔ)句（SELECT封拧、INSERT志鹃、UPDATE、DELETE）泽西，不能使用DDL語(yǔ)句（CREATE曹铃、ALTER、DROP）捧杉。
l???????? 觸發(fā)器中不能包含事務(wù)控制語(yǔ)句(COMMIT陕见，ROLLBACK，SAVEPOINT)味抖。因?yàn)橛|發(fā)器是觸發(fā)語(yǔ)句的一部分评甜，觸發(fā)語(yǔ)句被提交、回退時(shí)仔涩，觸發(fā)器也被提交忍坷、回退了。
l???????? 在觸發(fā)器主體中調(diào)用的任何過(guò)程、函數(shù)佩研，都不能使用事務(wù)控制語(yǔ)句柑肴。
l???????? 在觸發(fā)器主體中不能申明任何Long和blob變量。新值new和舊值old也不能是表中的任何long和blob列旬薯。
l???????? 不同類型的觸發(fā)器(如DML觸發(fā)器晰骑、INSTEAD OF觸發(fā)器、系統(tǒng)觸發(fā)器)的語(yǔ)法格式和作用有較大區(qū)別绊序。
8.2?創(chuàng)建觸發(fā)器
創(chuàng)建觸發(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 INSERT
BEFORE INSERT FOR EACH ROW
AFTER INSERT
AFTER INSERT FOR EACH ROW
BEFORE UPDATE
BEFORE UPDATE FOR EACH ROW
AFTER UPDATE
AFTER UPDATE FOR EACH ROW
BEFORE DELETE
BEFORE DELETE FOR EACH ROW
AFTER DELETE
AFTER DELETE FOR EACH ROW
8.2.1 觸發(fā)器觸發(fā)次序
1.???????執(zhí)行 BEFORE語(yǔ)句級(jí)觸發(fā)器;
2.???????對(duì)與受語(yǔ)句影響的每一行：
l???????? 執(zhí)行 BEFORE行級(jí)觸發(fā)器
l???????? 執(zhí)行 DML語(yǔ)句
l???????? 執(zhí)行 AFTER行級(jí)觸發(fā)器?
3.???????執(zhí)行 AFTER語(yǔ)句級(jí)觸發(fā)器
8.2.2 創(chuàng)建DML觸發(fā)器
??? 觸發(fā)器名與過(guò)程名和包的名字不一樣介杆，它是單獨(dú)的名字空間膀值，因而觸發(fā)器名可以和表或過(guò)程有相同的名字，但在一個(gè)模式中觸發(fā)器名不能相同。
?
DML觸發(fā)器的限制
l???????? CREATE TRIGGER語(yǔ)句文本的字符長(zhǎng)度不能超過(guò)32KB奴曙；
l???????? 觸發(fā)器體內(nèi)的SELECT 語(yǔ)句只能為SELECT … INTO …結(jié)構(gòu)，或者為定義游標(biāo)所使用的SELECT 語(yǔ)句逢防。
l???????? 觸發(fā)器中不能使用數(shù)據(jù)庫(kù)事務(wù)控制語(yǔ)句 COMMIT; ROLLBACK, SVAEPOINT 語(yǔ)句犀农；
l???????? 由觸發(fā)器所調(diào)用的過(guò)程或函數(shù)也不能使用數(shù)據(jù)庫(kù)事務(wù)控制語(yǔ)句；
l???????? 觸發(fā)器中不能使用LONG, LONG RAW 類型硫眯；
l???????? 觸發(fā)器內(nèi)可以參照LOB 類型列的列值蕴侧，但不能通過(guò) :NEW 修改LOB列中的數(shù)據(jù)；
?
DML觸發(fā)器基本要點(diǎn)
l觸發(fā)時(shí)機(jī)：指定觸發(fā)器的觸發(fā)時(shí)間两入。如果指定為BEFORE净宵，則表示在執(zhí)行DML操作之前觸發(fā)，以便防止某些錯(cuò)誤操作發(fā)生或?qū)崿F(xiàn)某些業(yè)務(wù)規(guī)則裹纳；如果指定為AFTER择葡，則表示在執(zhí)行DML操作之后觸發(fā)，以便記錄該操作或做某些事后處理剃氧。
l觸發(fā)事件：引起觸發(fā)器被觸發(fā)的事件敏储，即DML操作（INSERT朋鞍、UPDATE已添、DELETE）。既可以是單個(gè)觸發(fā)事件滥酥，也可以是多個(gè)觸發(fā)事件的組合（只能使用OR邏輯組合更舞，不能使用AND邏輯組合）。
l條件謂詞：當(dāng)在觸發(fā)器中包含多個(gè)觸發(fā)事件（INSERT恨狈、UPDATE疏哗、DELETE）的組合時(shí)，為了分別針對(duì)不同的事件進(jìn)行不同的處理禾怠，需要使用ORACLE提供的如下條件謂詞返奉。
1）。INSERTING：當(dāng)觸發(fā)事件是INSERT時(shí)吗氏，取值為T(mén)RUE芽偏，否則為FALSE。
2）弦讽。UPDATING [（column_1,column_2,…,column_x）]：當(dāng)觸發(fā)事件是UPDATE????? 時(shí)污尉，如果修改了column_x列膀哲，則取值為T(mén)RUE，否則為FALSE被碗。其中column_x是可選的某宪。
3）。DELETING：當(dāng)觸發(fā)事件是DELETE時(shí)锐朴，則取值為T(mén)RUE兴喂，否則為FALSE。
解發(fā)對(duì)象：指定觸發(fā)器是創(chuàng)建在哪個(gè)表焚志、視圖上衣迷。
l觸發(fā)類型：是語(yǔ)句級(jí)還是行級(jí)觸發(fā)器。
l觸發(fā)條件：由WHEN子句指定一個(gè)邏輯表達(dá)式酱酬，只允許在行級(jí)觸發(fā)器上指定觸發(fā)條件壶谒，指定UPDATING后面的列的列表。
?
問(wèn)題：當(dāng)觸發(fā)器被觸發(fā)時(shí)膳沽，要使用被插入汗菜、更新或刪除的記錄中的列值，有時(shí)要使用操作前贵少、??????? 后列的值.
實(shí)現(xiàn):??:NEW?修飾符訪問(wèn)操作完成后列的值
???????:OLD?修飾符訪問(wèn)操作完成前列的值
特性INSERTUPDATEDELETE
OLDNULL實(shí)際值實(shí)際值
NEW實(shí)際值實(shí)際值NULL
例1:建立一個(gè)觸發(fā)器, 當(dāng)職工表 emp 表被刪除一條記錄時(shí)呵俏，把被刪除記錄寫(xiě)到職工表刪除日志表中去。
CREATE?TABLE?emp_his?AS?SELECT?*?FROM?EMP?WHERE1=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;
例2：限制對(duì)Departments表修改（包括INSERT,DELETE,UPDATE）的時(shí)間范圍普碎，即不允許在非工作時(shí)間修改departments表。
CREATE?OR?REPLACE?TRIGGER?tr_dept_time
BEFORE?INSERT?OR?DELETE?OR?UPDATE
ON?departments
BEGIN
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;
例3：限定只對(duì)部門(mén)號(hào)為80的記錄進(jìn)行行觸發(fā)器操作麻车。
CREATE?OR?REPLACE?TRIGGER?tr_emp_sal_comm
BEFORE?UPDATE?OF?salary,?commission_pct
OR?DELETE
ON?HR.employees
FOR?EACH?ROW
WHEN?(old.department_id?=80)
BEGIN
CASE
WHEN?UPDATING?('salary')?THEN
IF?:NEW.salary?<?:old.salary?THEN
RAISE_APPLICATION_ERROR(-20001,?'部門(mén)80的人員的工資不能降');
END?IF;
WHEN?UPDATING?('commission_pct')?THEN
IF?:NEW.commission_pct?<?:old.commission_pct?THEN
RAISE_APPLICATION_ERROR(-20002,?'部門(mén)80的人員的獎(jiǎng)金不能降');
END?IF;
WHEN?DELETING?THEN
RAISE_APPLICATION_ERROR(-20003,?'不能刪除部門(mén)80的人員記錄');
END?CASE;
END;
/*
實(shí)例：
UPDATE?employees?SET?salary?=?8000?WHERE?employee_id?=?177;
DELETE?FROM?employees?WHERE?employee_id?in?(177,170);
*/
例4：利用行觸發(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_cou
AFTER?update?OF?region_id
ON?regions
FOR?EACH?ROW
BEGIN
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;
例5：在觸發(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
)
IS
BEGIN
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?ROW
BEGIN
add_job_history(:old.employee_id,?:old.hire_date,?sysdate,
:old.job_id,?:old.department_id);
END;
8.2.3 創(chuàng)建替代(INSTEAD OF)觸發(fā)器
創(chuàng)建觸發(fā)器的一般語(yǔ)法是:
CREATE?[OR?REPLACE]?TRIGGER?trigger_name
INSTEAD?OF
{INSERT?|?DELETE?|?UPDATE?[OF?column?[,?column?…]]}
[OR?{INSERT?|?DELETE?|?UPDATE?[OF?column?[,?column?…]]}...]
ON?[schema.]?view_name?--只能定義在視圖上
[REFERENCING?{OLD?[AS]?old?|?NEW?[AS]?new|?PARENT?as?parent}]
[FOR?EACH?ROW?]?--因?yàn)镮NSTEAD?OF觸發(fā)器只能在行級(jí)上觸發(fā),所以沒(méi)有必要指定
[WHEN?condition]
PL/SQL_block?|?CALL?procedure_name;
其中：
INSTEAD OF 選項(xiàng)使ORACLE激活觸發(fā)器，而不執(zhí)行觸發(fā)事件免钻。只能對(duì)視圖和對(duì)象視圖建立INSTEAD OF觸發(fā)器彼水，而不能對(duì)表、模式和數(shù)據(jù)庫(kù)建立INSTEAD OF 觸發(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ā)器中户矢。
??? INSTEAD_OF 用于對(duì)視圖的DML觸發(fā)，由于視圖有可能是由多個(gè)表進(jìn)行聯(lián)結(jié)(join)而成殉疼，因而并非是所有的聯(lián)結(jié)都是可更新的梯浪。但可以按照所需的方式執(zhí)行更新，例如下面情況：
例1：
CREATE?OR?REPLACE?VIEW?emp_view?AS
SELECT?deptno,?count(*)?total_employeer,?sum(sal)?total_salary
FROM?emp?GROUP?BY?deptno;
在此視圖中直接刪除是非法：
SQL>DELETE?FROM?emp_view?WHERE?deptno=10;
DELETE?FROM?emp_view?WHERE?deptno=10
ERROR 位于第 1 行:
ORA-01732: 此視圖的數(shù)據(jù)操縱操作非法
但是我們可以創(chuàng)建INSTEAD_OF觸發(fā)器來(lái)為 DELETE 操作執(zhí)行所需的處理瓢娜，即刪除EMP表中所有基準(zhǔn)行：
?
CREATE?OR?REPLACE?TRIGGER?emp_view_delete
INSTEAD?OF?DELETE?ON?emp_view?FOR?EACH?ROW
BEGIN
DELETE?FROM?emp?WHERE?deptno=?:old.deptno;
END?emp_view_delete;
DELETE?FROM?emp_view?WHERE?deptno=10;
DROP?TRIGGER?emp_view_delete;
DROP?VIEW?emp_view;
例2：創(chuàng)建復(fù)雜視圖挂洛，針對(duì)INSERT操作創(chuàng)建INSTEAD OF觸發(fā)器，向復(fù)雜視圖插入數(shù)據(jù)眠砾。
l???????? 創(chuàng)建視圖：
CREATE?OR?REPLACE?FORCE?VIEW?"HR"."V_REG_COU"?("R_ID",?"R_NAME",?"C_ID",?"C_NAME")
AS
SELECT?r.region_id,
r.region_name,
c.country_id,
c.country_name
FROM?regions?r,
countries?c
WHERE?r.region_id?=?c.region_id;
l????????創(chuàng)建觸發(fā)器：
?
CREATE?OR?REPLACE?TRIGGER?"HR"."TR_I_O_REG_COU"?INSTEAD?OF
INSERT?ON?v_reg_cou?FOR?EACH?ROW?DECLARE?v_count?NUMBER;
BEGIN
SELECT?COUNT(*)?INTO?v_count?FROM?regions?WHERE?region_id?=?:new.r_id;
IF?v_count?=?0?THEN
INSERT?INTO?regions
(region_id,?region_name
)?VALUES
(:new.r_id,?:new.r_name
);
END?IF;
SELECT?COUNT(*)?INTO?v_count?FROM?countries?WHERE?country_id?=?:new.c_id;
IF?v_count?=?0?THEN
INSERT
INTO?countries
(
country_id,
country_name,
region_id
)
VALUES
(
:new.c_id,
:new.c_name,
:new.r_id
);
END?IF;
END;
?
創(chuàng)建INSTEAD OF觸發(fā)器需要注意以下幾點(diǎn)：
l???????? 只能被創(chuàng)建在視圖上虏劲，并且該視圖沒(méi)有指定WITH CHECK OPTION選項(xiàng)。
l???????? 不能指定BEFORE 或 AFTER選項(xiàng)褒颈。
l???????? FOR EACH ROW子可是可選的柒巫，即INSTEAD OF觸發(fā)器只能在行級(jí)上觸發(fā)、或只能是行級(jí)觸發(fā)器哈肖，沒(méi)有必要指定吻育。
l???????? 沒(méi)有必要在針對(duì)一個(gè)表的視圖上創(chuàng)建INSTEAD OF觸發(fā)器，只要?jiǎng)?chuàng)建DML觸發(fā)器就可以了淤井。
?
8.2.3 創(chuàng)建系統(tǒng)事件觸發(fā)器
??? ORACLE10G提供的系統(tǒng)事件觸發(fā)器可以在DDL或數(shù)據(jù)庫(kù)系統(tǒng)上被觸發(fā)布疼。DDL指的是數(shù)據(jù)定義語(yǔ)言摊趾，如CREATE 、ALTER及DROP 等游两。而數(shù)據(jù)庫(kù)系統(tǒng)事件包括數(shù)據(jù)庫(kù)服務(wù)器的啟動(dòng)或關(guān)閉砾层，用戶的登錄與退出、數(shù)據(jù)庫(kù)服務(wù)錯(cuò)誤等贱案。創(chuàng)建系統(tǒng)觸發(fā)器的語(yǔ)法如下：?
創(chuàng)建觸發(fā)器的一般語(yǔ)法是:
CREATE?OR?REPLACE?TRIGGER?[sachema.]trigger_name
{BEFORE|AFTER}
{ddl_event_list?|?database_event_list}
ON?{?DATABASE?|?[schema.]SCHEMA?}
[WHEN?condition]
PL/SQL_block?|?CALL?procedure_name;
其中: ddl_event_list：一個(gè)或多個(gè)DDL 事件肛炮，事件間用 OR 分開(kāi)；
????????database_event_list：一個(gè)或多個(gè)數(shù)據(jù)庫(kù)事件宝踪，事件間用 OR 分開(kāi)侨糟；
???????????系統(tǒng)事件觸發(fā)器既可以建立在一個(gè)模式上，又可以建立在整個(gè)數(shù)據(jù)庫(kù)上瘩燥。當(dāng)建立在模式(SCHEMA)之上時(shí)秕重，只有模式所指定用戶的DDL操作和它們所導(dǎo)致的錯(cuò)誤才激活觸發(fā)器, 默認(rèn)時(shí)為當(dāng)前用戶模式。當(dāng)建立在數(shù)據(jù)庫(kù)(DATABASE)之上時(shí)厉膀，該數(shù)據(jù)庫(kù)所有用戶的DDL操作和他們所導(dǎo)致的錯(cuò)誤溶耘，以及數(shù)據(jù)庫(kù)的啟動(dòng)和關(guān)閉均可激活觸發(fā)器。要在數(shù)據(jù)庫(kù)之上建立觸發(fā)器時(shí)服鹅，要求用戶具有ADMINISTER DATABASE TRIGGER權(quán)限凳兵。
下面給出系統(tǒng)觸發(fā)器的種類和事件出現(xiàn)的時(shí)機(jī)（前或后）：
事件允許的時(shí)機(jī)說(shuō)明
STARTUPAFTER啟動(dòng)數(shù)據(jù)庫(kù)實(shí)例之后觸發(fā)
SHUTDOWNBEFORE關(guān)閉數(shù)據(jù)庫(kù)實(shí)例之前觸發(fā)（非正常關(guān)閉不觸發(fā)）
SERVERERRORAFTER數(shù)據(jù)庫(kù)服務(wù)器發(fā)生錯(cuò)誤之后觸發(fā)
LOGONAFTER成功登錄連接到數(shù)據(jù)庫(kù)后觸發(fā)
LOGOFFBEFORE開(kāi)始斷開(kāi)數(shù)據(jù)庫(kù)連接之前觸發(fā)
CREATEBEFORE，AFTER在執(zhí)行CREATE語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象之前企软、之后觸發(fā)
DROPBEFORE庐扫，AFTER在執(zhí)行DROP語(yǔ)句刪除數(shù)據(jù)庫(kù)對(duì)象之前、之后觸發(fā)
ALTERBEFORE澜倦，AFTER在執(zhí)行ALTER語(yǔ)句更新數(shù)據(jù)庫(kù)對(duì)象之前聚蝶、之后觸發(fā)
DDLBEFORE，AFTER在執(zhí)行大多數(shù)DDL語(yǔ)句之前藻治、之后觸發(fā)
GRANTBEFORE碘勉，AFTER執(zhí)行GRANT語(yǔ)句授予權(quán)限之前、之后觸發(fā)
REVOKEBEFORE桩卵，AFTER執(zhí)行REVOKE語(yǔ)句收權(quán)限之前验靡、之后觸犯發(fā)
RENAMEBEFORE，AFTER執(zhí)行RENAME語(yǔ)句更改數(shù)據(jù)庫(kù)對(duì)象名稱之前雏节、之后觸犯發(fā)
AUDIT/?NOAUDITBEFORE胜嗓，AFTER執(zhí)行AUDIT或NOAUDIT進(jìn)行審計(jì)或停止審計(jì)之前、之后觸發(fā)
8.2.4 系統(tǒng)觸發(fā)器事件屬性
事件屬性\事件Startup/ShutdownServererrorLogon/LogoffDDLDML
事件名稱ü＊ü＊ü＊ü＊＊
數(shù)據(jù)庫(kù)名稱ü＊????
數(shù)據(jù)庫(kù)實(shí)例號(hào)ü＊????
錯(cuò)誤號(hào)?ü＊???
用戶名??ü＊＊?
模式對(duì)象類型???ü＊＊
模式對(duì)象名稱???ü＊＊
列????ü＊
除DML語(yǔ)句的列屬性外钩乍，其余事件屬性值可通過(guò)調(diào)用ORACLE定義的事件屬性函數(shù)來(lái)讀取辞州。
函數(shù)名稱數(shù)據(jù)類型說(shuō)??? 明
Ora_syseventVARCHAR2（20）激活觸發(fā)器的事件名稱
Instance_numNUMBER數(shù)據(jù)庫(kù)實(shí)例名
Ora_database_nameVARCHAR2（50）數(shù)據(jù)庫(kù)名稱
Server_error(posi)NUMBER錯(cuò)誤信息棧中posi指定位置中的錯(cuò)誤號(hào)
Is_servererror(err_number)
BOOLEAN
檢查err_number指定的錯(cuò)誤號(hào)是否在錯(cuò)誤信息棧中，如果在則返回TRUE寥粹，否則返回FALSE变过。在觸發(fā)器內(nèi)調(diào)用此函數(shù)可以判斷是否發(fā)生指定的錯(cuò)誤埃元。
Login_userVARCHAR2(30)登陸或注銷的用戶名稱
Dictionary_obj_typeVARCHAR2(20)DDL語(yǔ)句所操作的數(shù)據(jù)庫(kù)對(duì)象類型
Dictionary_obj_nameVARCHAR2(30)DDL語(yǔ)句所操作的數(shù)據(jù)庫(kù)對(duì)象名稱
Dictionary_obj_ownerVARCHAR2(30)DDL語(yǔ)句所操作的數(shù)據(jù)庫(kù)對(duì)象所有者名稱
Des_encrypted_passwordVARCHAR2(2)正在創(chuàng)建或修改的經(jīng)過(guò)DES算法加密的用戶口令
?
例1：創(chuàng)建觸發(fā)器，存放有關(guān)事件信息媚狰。
DESC?ora_sysevent
DESC?ora_login_user
--創(chuàng)建用于記錄事件用的表
CREATE?TABLE?ddl_event
(crt_date?timestamp?PRIMARY?KEY,
event_name?VARCHAR2(20),
user_name?VARCHAR2(10),
obj_type?VARCHAR2(20),
obj_name?VARCHAR2(20));
--創(chuàng)建觸犯發(fā)器
CREATE?OR?REPLACE?TRIGGER?tr_ddl
AFTER?DDL?ON?SCHEMA
BEGIN
INSERT?INTO?ddl_event?VALUES
(systimestamp,ora_sysevent,?ora_login_user,
ora_dict_obj_type,?ora_dict_obj_name);
END?tr_ddl;
例2：創(chuàng)建登錄岛杀、退出觸發(fā)器。
CREATE?TABLE?log_event
(user_name?VARCHAR2(10),
address?VARCHAR2(20),
logon_date?timestamp,
logoff_date?timestamp);
--創(chuàng)建登錄觸發(fā)器
CREATE?OR?REPLACE?TRIGGER?tr_logon
AFTER?LOGON?ON?DATABASE
BEGIN
INSERT?INTO?log_event?(user_name,?address,?logon_date)
VALUES?(ora_login_user,?ora_client_ip_address,?systimestamp);
END?tr_logon;
--創(chuàng)建退出觸發(fā)器
CREATE?OR?REPLACE?TRIGGER?tr_logoff
BEFORE?LOGOFF?ON?DATABASE
BEGIN
INSERT?INTO?log_event?(user_name,?address,?logoff_date)
VALUES?(ora_login_user,?ora_client_ip_address,?systimestamp);
END?tr_logoff;
8.2.5 使用觸發(fā)器謂詞
??? ORACLE 提供三個(gè)參數(shù)INSERTING, UPDATING,DELETING 用于判斷觸發(fā)了哪些操作崭孤。
謂詞行為
INSERTING如果觸發(fā)語(yǔ)句是 INSERT 語(yǔ)句类嗤，則為T(mén)RUE,否則為FALSE
UPDATING如果觸發(fā)語(yǔ)句是 UPDATE語(yǔ)句，則為T(mén)RUE,否則為FALSE
DELETING如果觸發(fā)語(yǔ)句是 DELETE 語(yǔ)句辨宠，則為T(mén)RUE,否則為FALSE
?
8.2.6 重新編譯觸發(fā)器
如果在觸發(fā)器內(nèi)調(diào)用其它函數(shù)或過(guò)程遗锣，當(dāng)這些函數(shù)或過(guò)程被刪除或修改后，觸發(fā)器的狀態(tài)將被標(biāo)識(shí)為無(wú)效彭羹。當(dāng)DML語(yǔ)句激活一個(gè)無(wú)效觸發(fā)器時(shí)黄伊，ORACLE將重新編譯觸發(fā)器代碼，如果編譯時(shí)發(fā)現(xiàn)錯(cuò)誤派殷，這將導(dǎo)致DML語(yǔ)句執(zhí)行失敗。
在PL/SQL程序中可以調(diào)用ALTER TRIGGER語(yǔ)句重新編譯已經(jīng)創(chuàng)建的觸發(fā)器墓阀，格式為：???????????
ALTER?TRIGGER?[schema.]?trigger_name?COMPILE?[?DEBUG]
?????? 其中：DEBUG 選項(xiàng)要器編譯器生成PL/SQL 程序條使其所使用的調(diào)試代碼毡惜。
8.3?刪除和使能觸發(fā)器
l刪除觸發(fā)器：
DROP?TRIGGER?trigger_name;
當(dāng)刪除其他用戶模式中的觸發(fā)器名稱，需要具有DROP ANY TRIGGER系統(tǒng)權(quán)限斯撮，當(dāng)刪除建立在數(shù)據(jù)庫(kù)上的觸發(fā)器時(shí)经伙，用戶需要具有ADMINISTER DATABASE TRIGGER系統(tǒng)權(quán)限。
此外勿锅，當(dāng)刪除表或視圖時(shí)帕膜，建立在這些對(duì)象上的觸發(fā)器也隨之刪除。?
l禁用或啟用觸發(fā)器
數(shù)據(jù)庫(kù)TRIGGER 的狀態(tài)：
有效狀態(tài)(ENABLE)：當(dāng)觸發(fā)事件發(fā)生時(shí)溢十，處于有效狀態(tài)的數(shù)據(jù)庫(kù)觸發(fā)器TRIGGER 將被觸發(fā)垮刹。
無(wú)效狀態(tài)(DISABLE)：當(dāng)觸發(fā)事件發(fā)生時(shí)，處于無(wú)效狀態(tài)的數(shù)據(jù)庫(kù)觸發(fā)器TRIGGER 將不會(huì)被觸發(fā)张弛，此時(shí)就跟沒(méi)有這個(gè)數(shù)據(jù)庫(kù)觸發(fā)器(TRIGGER) 一樣荒典。
數(shù)據(jù)庫(kù)TRIGGER的這兩種狀態(tài)可以互相轉(zhuǎn)換。格式為：
ALTER?TIGGER?trigger_name?[DISABLE?|?ENABLE?];
--例：ALTER?TRIGGER?emp_view_delete?DISABLE;
???????????ALTER TRIGGER語(yǔ)句一次只能改變一個(gè)觸發(fā)器的狀態(tài)吞鸭，而ALTER TABLE語(yǔ)句則一次能夠改變與指定表相關(guān)的所有觸發(fā)器的使用狀態(tài)寺董。格式為：?????????????
ALTER?TABLE?[schema.]table_name?{ENABLE|DISABLE}?ALL?TRIGGERS;
--例：使表EMP?上的所有TRIGGER?失效：
ALTER?TABLE?emp?DISABLE?ALL?TRIGGERS;
8.4?觸發(fā)器和數(shù)據(jù)字典
相關(guān)數(shù)據(jù)字典：USER_TRIGGERS、ALL_TRIGGERS刻剥、DBA_TRIGGERS?
SELECT?TRIGGER_NAME,?TRIGGER_TYPE,?TRIGGERING_EVENT,
TABLE_OWNER,?BASE_OBJECT_TYPE,?REFERENCING_NAMES,
STATUS,?ACTION_TYPE
FROM?user_triggers;
8.5?? 數(shù)據(jù)庫(kù)觸發(fā)器的應(yīng)用舉例
例1：創(chuàng)建一個(gè)DML語(yǔ)句級(jí)觸發(fā)器遮咖，當(dāng)對(duì)emp表執(zhí)行INSERT, UPDATE, DELETE 操作時(shí)，它自動(dòng)更新dept_summary 表中的數(shù)據(jù)造虏。由于在PL/SQL塊中不能直接調(diào)用DDL語(yǔ)句御吞，所以麦箍，利用ORACLE內(nèi)置包DBMS_UTILITY中的EXEC_DDL_STATEMENT過(guò)程，由它執(zhí)行DDL語(yǔ)句創(chuàng)建觸發(fā)器魄藕。
CREATE?TABLE?dept_summary(
Deptno?NUMBER(2),
Sal_sum?NUMBER(9,2),
Emp_count?NUMBER);
INSERT?INTO?dept_summary(deptno,?sal_sum,?emp_count)
SELECT?deptno,?SUM(sal),?COUNT(*)
FROM?emp
GROUP?BY?deptno;
--創(chuàng)建一個(gè)PL/SQL過(guò)程disp_dept_summary
--在觸發(fā)器中調(diào)用該過(guò)程顯示dept_summary標(biāo)中的數(shù)據(jù)内列。
CREATE?OR?REPLACE?PROCEDURE?disp_dept_summary
IS
Rec?dept_summary%ROWTYPE;
CURSOR?c1?IS?SELECT?*?FROM?dept_summary;
BEGIN
OPEN?c1;
FETCH?c1?INTO?REC;
DBMS_OUTPUT.PUT_LINE('deptno????sal_sum????emp_count');
DBMS_OUTPUT.PUT_LINE('-------------------------------------');
WHILE?c1%FOUND?LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(rec.deptno,6)||
To_char(rec.sal_sum,?'$999,999.99')||
LPAD(rec.emp_count,13));
FETCH?c1?INTO?rec;
END?LOOP;
CLOSE?c1;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('插入前');
Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE?OR?REPLACE?TRIGGER?trig1
AFTER?INSERT?OR?DELETE?OR?UPDATE?OF?sal?ON?emp
BEGIN
DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig1?觸發(fā)器…'');
DELETE?FROM?dept_summary;
INSERT?INTO?dept_summary(deptno,?sal_sum,?emp_count)
SELECT?deptno,?SUM(sal),?COUNT(*)
FROM?emp?GROUP?BY?deptno;
END;
');
INSERT?INTO?dept(deptno,?dname,?loc)
VALUES(90,?‘demo_dept’,?‘none_loc’);
INSERT?INTO?emp(ename,?deptno,?empno,?sal)
VALUES(USER,90,9999,3000);
DBMS_OUTPUT.PUT_LINE('插入后');
Disp_dept_summary();
UPDATE?emp?SET?sal=1000WHERE?empno=9999;
DBMS_OUTPUT.PUT_LINE('修改后');
Disp_dept_summary();
DELETE?FROM?emp?WHERE?empno=9999;
DELETE?FROM?dept?WHERE?deptno=90;
DBMS_OUTPUT.PUT_LINE('刪除后');
Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT(‘DROP?TRIGGER?trig1’);
EXCEPTION
WHEN?OTHERS?THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
例2：創(chuàng)建DML語(yǔ)句行級(jí)觸發(fā)器。當(dāng)對(duì)emp表執(zhí)行INSERT, UPDATE, DELETE 操作時(shí)背率，它自動(dòng)更新dept_summary 表中的數(shù)據(jù)话瞧。由于在PL/SQL塊中不能直接調(diào)用DDL語(yǔ)句，所以寝姿，利用ORACLE內(nèi)置包DBMS_UTILITY中的EXEC_DDL_STATEMENT過(guò)程交排，由它執(zhí)行DDL語(yǔ)句創(chuàng)建觸發(fā)器。
BEGIN
DBMS_OUTPUT.PUT_LINE('插入前');
Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT(
'CREATE?OR?REPLACE?TRIGGER?trig2_update
AFTER?UPDATE?OF?sal?ON?emp
REFERENCING?OLD?AS?old_emp?NEW?AS?new_emp
FOR?EACH?ROW
WHEN?(old_emp.sal?!=?new_emp.sal)
BEGIN
DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2_update?觸發(fā)器…'');
DBMS_OUTPUT.PUT_LINE(''sal?舊值：''||?:old_emp.sal);
DBMS_OUTPUT.PUT_LINE(''sal?新值：''||?:new_emp.sal);
UPDATE?dept_summary
SET?sal_sum=sal_sum?+?:new_emp.sal?-?:old_emp.sal
WHERE?deptno?=?:new_emp.deptno;
END;'
);
DBMS_UTILITY.EXEC_DDL_STATEMENT(
'CREATE?OR?REPLACE?TRIGGER?trig2_insert
AFTER?INSERT?ON?emp
REFERENCING?NEW?AS?new_emp
FOR?EACH?ROW
DECLARE
I?NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2_insert?觸發(fā)器…'');
SELECT?COUNT(*)?INTO?I
FROM?dept_summary?WHERE?deptno?=?:new_emp.deptno;
IF?I?>?0?THEN
UPDATE?dept_summary
SET?sal_sum=sal_sum+:new_emp.sal,
Emp_count=emp_count+1
WHERE?deptno?=?:new_emp.deptno;
ELSE
INSERT?INTO?dept_summary
VALUES?(:new_emp.deptno,?:new_emp.sal,?1);
END?IF;
END;'
);
DBMS_UTILITY.EXEC_DDL_STATEMENT(
'CREATE?OR?REPLACE?TRIGGER?trig2_delete
AFTER?DELETE?ON?emp
REFERENCING?OLD?AS?old_emp
FOR?EACH?ROW
DECLARE
I?NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2_delete?觸發(fā)器…'');
SELECT?emp_count?INTO?I
FROM?dept_summary?WHERE?deptno?=?:old_emp.deptno;
IF?I?>1?THEN
UPDATE?dept_summary
SET?sal_sum=sal_sum?-?:old_emp.sal,
Emp_count=emp_count?-?1
WHERE?deptno?=?:old_emp.deptno;
ELSE
DELETE?FROM?dept_summary?WHERE?deptno?=?:old_emp.deptno;
END?IF;
END;'
);
INSERT?INTO?dept(deptno,?dname,?loc)
VALUES(90,?'demo_dept',?'none_loc');
INSERT?INTO?emp(ename,?deptno,?empno,?sal)
VALUES(USER,90,9999,3000);
INSERT?INTO?emp(ename,?deptno,?empno,?sal)
VALUES(USER,90,9998,2000);
DBMS_OUTPUT.PUT_LINE('插入后');
Disp_dept_summary();
UPDATE?emp?SET?sal?=?sal*1.1WHERE?deptno=90;
DBMS_OUTPUT.PUT_LINE('修改后');
Disp_dept_summary();
DELETE?FROM?emp?WHERE?deptno=90;
DELETE?FROM?dept?WHERE?deptno=90;
DBMS_OUTPUT.PUT_LINE('刪除后');
Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP?TRIGGER?trig2_update');
DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP?TRIGGER?trig2_insert');
DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP?TRIGGER?trig2_delete');
EXCEPTION
WHEN?OTHERS?THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
例3：利用ORACLE提供的條件謂詞INSERTING饵筑、UPDATING和DELETING創(chuàng)建與例2具有相同功能的觸發(fā)器埃篓。
BEGIN
DBMS_OUTPUT.PUT_LINE('插入前');
Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT(
'CREATE?OR?REPLACE?TRIGGER?trig2
AFTER?INSERT?OR?DELETE?OR?UPDATE?OF?sal
ON?emp
REFERENCING?OLD?AS?old_emp?NEW?AS?new_emp
FOR?EACH?ROW
DECLARE
I?NUMBER;
BEGIN
IF?UPDATING?AND?:old_emp.sal?!=?:new_emp.sal?THEN
DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2?觸發(fā)器…'');
DBMS_OUTPUT.PUT_LINE(''sal?舊值：''||?:old_emp.sal);
DBMS_OUTPUT.PUT_LINE(''sal?新值：''||?:new_emp.sal);
UPDATE?dept_summary
SET?sal_sum=sal_sum?+?:new_emp.sal?-?:old_emp.sal
WHERE?deptno?=?:new_emp.deptno;
ELSIF?INSERTING?THEN
DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2觸發(fā)器…'');
SELECT?COUNT(*)?INTO?I
FROM?dept_summary
WHERE?deptno?=?:new_emp.deptno;
IF?I?>?0?THEN
UPDATE?dept_summary
SET?sal_sum=sal_sum+:new_emp.sal,
Emp_count=emp_count+1
WHERE?deptno?=?:new_emp.deptno;
ELSE
INSERT?INTO?dept_summary
VALUES?(:new_emp.deptno,?:new_emp.sal,?1);
END?IF;
ELSE
DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2觸發(fā)器…'');
SELECT?emp_count?INTO?I
FROM?dept_summary?WHERE?deptno?=?:old_emp.deptno;
IF?I?>?1?THEN
UPDATE?dept_summary
SET?sal_sum=sal_sum?-?:old_emp.sal,
Emp_count=emp_count?-?1
WHERE?deptno?=?:old_emp.deptno;
ELSE
DELETE?FROM?dept_summary
WHERE?deptno?=?:old_emp.deptno;
END?IF;
END?IF;
END;'
);
INSERT?INTO?dept(deptno,?dname,?loc)
VALUES(90,?'demo_dept',?'none_loc');
INSERT?INTO?emp(ename,?deptno,?empno,?sal)
VALUES(USER,90,9999,3000);
INSERT?INTO?emp(ename,?deptno,?empno,?sal)
VALUES(USER,90,9998,2000);
DBMS_OUTPUT.PUT_LINE('插入后');
Disp_dept_summary();
UPDATE?emp?SET?sal?=?sal*1.1WHERE?deptno=90;
DBMS_OUTPUT.PUT_LINE('修改后');
Disp_dept_summary();
DELETE?FROM?emp?WHERE?deptno=90;
DELETE?FROM?dept?WHERE?deptno=90;
DBMS_OUTPUT.PUT_LINE('刪除后');
Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP?TRIGGER?trig2');
EXCEPTION
WHEN?OTHERS?THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
例4：創(chuàng)建INSTEAD OF 觸發(fā)器。首先創(chuàng)建一個(gè)視圖myview,由于該視圖是復(fù)合查詢所產(chǎn)生的視圖根资，所以不能執(zhí)行DML語(yǔ)句架专。根據(jù)用戶對(duì)視圖所插入的數(shù)據(jù)判斷需要將數(shù)據(jù)插入到哪個(gè)視圖基表中，然后對(duì)該基表執(zhí)行插入操作玄帕。
DECLARE
No?NUMBER;
Name?VARCHAR2(20);
BEGIN
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE?OR?REPLACE?VIEW?myview?AS
SELECT?empno,?ename,?''E''?type?FROM?emp
UNION
SELECT?dept.deptno,?dname,?''D''?FROM?dept
');
--?創(chuàng)建INSTEAD?OF?觸發(fā)器trigger3;
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE?OR?REPLACE?TRIGGER?trig3
INSTEAD?OF?INSERT?ON?myview
REFERENCING?NEW?n
FOR?EACH?ROW
DECLARE
Rows?INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig3觸發(fā)器…'');
IF?:n.type?=?''D''?THEN
SELECT?COUNT(*)?INTO?rows
FROM?dept?WHERE?deptno?=?:n.empno;
IF?rows?=?0?THEN
DBMS_OUTPUT.PUT_LINE(''向dept表中插入數(shù)據(jù)…'');
INSERT?INTO?dept(deptno,?dname,?loc)
VALUES?(:n.empno,?:n.ename,?''none’’);
ELSE
DBMS_OUTPUT.PUT_LINE(''編號(hào)為''||?:n.empno||
''的部門(mén)已存在部脚，插入操作失敗裤纹！'');
END?IF;
ELSE
SELECT?COUNT(*)?INTO?rows
FROM?emp?WHERE?empno?=?:n.empno;
IF?rows?=?0?THEN
DBMS_OUTPUT.PUT_LINE('’向emp表中插入數(shù)據(jù)…’’);
INSERT?INTO?emp(empno,?ename)
VALUES(:n.empno,?:n.ename);
ELSE
DBMS_OUTPUT.PUT_LINE(''編號(hào)為''||?:n.empno||
''的人員已存在委刘，插入操作失敗!'');
END?IF;
END?IF;
END;
');
INSERT?INTO?myview?VALUES?(70,?'demo',?'D');
INSERT?INTO?myview?VALUES?(9999,?USER,?'E');
SELECT?deptno,?dname?INTO?no,?name?FROM?dept?WHERE?deptno=70;
DBMS_OUTPUT.PUT_LINE('員工編號(hào)：'||TO_CHAR(no)||'姓名：'||name);
SELECT?empno,?ename?INTO?no,?name?FROM?emp?WHERE?empno=9999;
DBMS_OUTPUT.PUT_LINE('部門(mén)編號(hào)：'||TO_CHAR(no)||'姓名：'||name);
DELETE?FROM?emp?WHERE?empno=9999;
DELETE?FROM?dept?WHERE?deptno=70;
DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP?TRIGGER?trig3');
END;
例5：利用ORACLE事件屬性函數(shù)，創(chuàng)建一個(gè)系統(tǒng)事件觸發(fā)器鹰椒。首先創(chuàng)建一個(gè)事件日志表eventlog锡移，由它存儲(chǔ)用戶在當(dāng)前數(shù)據(jù)庫(kù)中所創(chuàng)建的數(shù)據(jù)庫(kù)對(duì)象，以及用戶的登陸和注銷漆际、數(shù)據(jù)庫(kù)的啟動(dòng)和關(guān)閉等事件淆珊，之后創(chuàng)建trig4_ddl、trig4_before和trig4_after觸發(fā)器灿椅，它們調(diào)用事件屬性函數(shù)將各個(gè)事件記錄到eventlog數(shù)據(jù)表中套蒂。
?
BEGIN
--?創(chuàng)建用于記錄事件日志的數(shù)據(jù)表
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE?TABLE?eventlog(
Eventname?VARCHAR2(20)?NOT?NULL,
Eventdate?date?default?sysdate,
Inst_num?NUMBER?NULL,
Db_name?VARCHAR2(50)?NULL,
Srv_error?NUMBER?NULL,
Username?VARCHAR2(30)?NULL,
Obj_type?VARCHAR2(20)?NULL,
Obj_name?VARCHAR2(30)?NULL,
Obj_owner?VARCHAR2(30)?NULL
)
');
--?創(chuàng)建DDL觸發(fā)器trig4_ddl
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE?OR?REPLACE?TRIGGER?trig4_ddl
AFTER?CREATE?OR?ALTER?OR?DROP
ON?DATABASE
DECLARE
Event?VARCHAR2(20);
Typ?VARCHAR2(20);
Name?VARCHAR2(30);
Owner?VARCHAR2(30);
BEGIN
--?讀取DDL事件屬性
Event?:=?SYSEVENT;
Typ?:=?DICTIONARY_OBJ_TYPE;
Name?:=?DICTIONARY_OBJ_NAME;
Owner?:=?DICTIONARY_OBJ_OWNER;
--將事件屬性插入到事件日志表中
INSERT?INTO?scott.eventlog(eventname,?obj_type,?obj_name,?obj_owner)
VALUES(event,?typ,?name,?owner);
END;
');
--?創(chuàng)建LOGON、STARTUP和SERVERERROR?事件觸發(fā)器
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE?OR?REPLACE?TRIGGER?trig4_after
AFTER?LOGON?OR?STARTUP?OR?SERVERERROR
ON?DATABASE
DECLARE
Event?VARCHAR2(20);
Instance?NUMBER;
Err_num?NUMBER;
Dbname?VARCHAR2(50);
User?VARCHAR2(30);
BEGIN
Event?:=?SYSEVENT;
IF?event?=?''LOGON''?THEN
User?:=?LOGIN_USER;
INSERT?INTO?eventlog(eventname,?username)
VALUES(event,?user);
ELSIF?event?=?''SERVERERROR''?THEN
Err_num?:=?SERVER_ERROR(1);
INSERT?INTO?eventlog(eventname,?srv_error)
VALUES(event,?err_num);
ELSE
Instance?:=?INSTANCE_NUM;
Dbname?:=?DATABASE_NAME;
INSERT?INTO?eventlog(eventname,?inst_num,?db_name)
VALUES(event,?instance,?dbname);
END?IF;
END;
');
--?創(chuàng)建LOGOFF和SHUTDOWN?事件觸發(fā)器
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE?OR?REPLACE?TRIGGER?trig4_before
BEFORE?LOGOFF?OR?SHUTDOWN
ON?DATABASE
DECLARE
Event?VARCHAR2(20);
Instance?NUMBER;
Dbname?VARCHAR2(50);
User?VARCHAR2(30);
BEGIN
Event?:=?SYSEVENT;
IF?event?=?''LOGOFF''?THEN
User?:=?LOGIN_USER;
INSERT?INTO?eventlog(eventname,?username)
VALUES(event,?user);
ELSE
Instance?:=?INSTANCE_NUM;
Dbname?:=?DATABASE_NAME;
INSERT?INTO?eventlog(eventname,?inst_num,?db_name)
VALUES(event,?instance,?dbname);
END?IF;
END;
');
END;
CREATE?TABLE?mydata(mydate?NUMBER);
CONNECT?SCOTT/TIGER
COL?eventname?FORMAT?A10
COL?eventdate?FORMAT?A12
COL?username?FORMAT?A10
COL?obj_type?FORMAT?A15
COL?obj_name?FORMAT?A15
COL?obj_owner?FORMAT?A10
SELECT?eventname,?eventdate,?obj_type,?obj_name,?obj_owner,?username,?Srv_error
FROM?eventlog;
DROP?TRIGGER?trig4_ddl;
DROP?TRIGGER?trig4_before;
DROP?TRIGGER?trig4_after;
DROP?TABLE?eventlog;
DROP?TABLE?mydata;
8.6?? 數(shù)據(jù)庫(kù)觸發(fā)器的應(yīng)用實(shí)例
用戶可以使用數(shù)據(jù)庫(kù)觸發(fā)器實(shí)現(xiàn)各種功能：
l???????? 復(fù)雜的審計(jì)功能茫蛹；
例：將EMP 表的變化情況記錄到AUDIT_TABLE和AUDIT_TABLE_VALUES中操刀。
CREATE?TABLE?audit_table(
Audit_id?????NUMBER,
User_name?VARCHAR2(20),
Now_time?DATE,
Terminal_name?VARCHAR2(10),
Table_name?VARCHAR2(10),
Action_name?VARCHAR2(10),
Emp_id?NUMBER(4));
CREATE?TABLE?audit_table_val(
Audit_id?NUMBER,
Column_name?VARCHAR2(10),
Old_val?NUMBER(7,2),
New_val?NUMBER(7,2));
CREATE?SEQUENCE?audit_seq
START?WITH1000
INCREMENT?BY1
NOMAXVALUE
NOCYCLE?NOCACHE;
CREATE?OR?REPLACE?TRIGGER?audit_emp
AFTER?INSERT?OR?UPDATE?OR?DELETE?ON?emp
FOR?EACH?ROW
DECLARE
Time_now?DATE;
Terminal?CHAR(10);
BEGIN
Time_now:=sysdate;
Terminal:=USERENV('TERMINAL');
IF?INSERTING?THEN
INSERT?INTO?audit_table
VALUES(audit_seq.NEXTVAL,?user,?time_now,
terminal,?'EMP',?'INSERT',?:new.empno);
ELSIF?DELETING?THEN
INSERT?INTO?audit_table
VALUES(audit_seq.NEXTVAL,?user,?time_now,
terminal,?'EMP',?'DELETE',?:old.empno);
ELSE
INSERT?INTO?audit_table
VALUES(audit_seq.NEXTVAL,?user,?time_now,
terminal,?'EMP',?'UPDATE',?:old.empno);
IF?UPDATING('SAL')?THEN
INSERT?INTO?audit_table_val
VALUES(audit_seq.CURRVAL,?'SAL',?:old.sal,?:new.sal);
ELSE?UPDATING('DEPTNO')
INSERT?INTO?audit_table_val
VALUES(audit_seq.CURRVAL,?'DEPTNO',?:old.deptno,?:new.deptno);
END?IF;
END?IF;
END;
l???????? 增強(qiáng)數(shù)據(jù)的完整性管理；
例：修改DEPT表的DEPTNO列時(shí)婴洼，同時(shí)把EMP表中相應(yīng)的DEPTNO也作相應(yīng)的修改骨坑；
CREATE?SEQUENCE?update_sequence
INCREMENT?BY1
START?WITH1000
MAXVALUE5000CYCLE;
ALTER?TABLE?emp
ADD?update_id?NUMBER;
CREATE?OR?REPLACE?PACKAGE?integritypackage?AS
Updateseq?NUMBER;
END?integritypackage;
CREATE?OR?REPLACE?PACKAGE?BODY?integritypackage?AS
END?integritypackage;
CREATE?OR?REPLACE?TRIGGER?dept_cascade1
BEFORE?UPDATE?OF?deptno?ON?dept
DECLARE
Dummy?NUMBER;
BEGIN
SELECT?update_sequence.NEXTVAL?INTO?dummy?FROM?dual;
Integritypackage.updateseq:=dummy;
END;
CREATE?OR?REPLACE?TRIGGER?dept_cascade2
AFTER?DELETE?OR?UPDATE?OF?deptno?ON?dept
FOR?EACH?ROW
BEGIN
IF?UPDATING?THEN
UPDATE?emp?SET?deptno=:new.deptno,
update_id=integritypackage.updateseq
WHERE?emp.deptno=:old.deptno?AND?update_id?IS?NULL;
END?IF;
IF?DELETING?THEN
DELETE?FROM?emp
WHERE?emp.deptno=:old.deptno;
END?IF;
END;
CREATE?OR?REPLACE?TRIGGER?dept_cascade3
AFTER?UPDATE?OF?deptno?ON?dept
BEGIN
UPDATE?emp?SET?update_id=NULL
WHERE?update_id=integritypackage.updateseq;
END;
SELECT?*?FROM?EMP?ORDER?BY?DEPTNO;
UPDATE?dept?SET?deptno=25WHERE?deptno=20;
l???????? 幫助實(shí)現(xiàn)安全控制；
例：保證對(duì)EMP表的修改僅在工作日的工作時(shí)間；
CREATE?TABLE?company_holidays(day?DATE);
INSERT?INTO?company_holidays
VALUES(sysdate);
INSERT?INTO?company_holidays
VALUES(TO_DATE('21-10月-01',?'DD-MON-YY'));
CREATE?OR?REPLACE?TRIGGER?emp_permit_change
BEFORE?INSERT?OR?DELETE?OR?UPDATE?ON?emp
DECLARE
Dummy?NUMBER;
Not_on_weekends?EXCEPTION;
Not_on_holidays?EXCEPTION;
Not_working_hours?EXCEPTION;
BEGIN
/*?check?for?weekends?*/
IF?TO_CHAR(SYSDATE,?'DAY')?IN?('星期六',?'星期日')?THEN
RAISE?not_on_weekends;
END?IF;
/*?check?for?company?holidays?*/
SELECT?COUNT(*)?INTO?dummy?FROM?company_holidays
WHERE?TRUNC(day)=TRUNC(SYSDATE);
IF?dummy?>0THEN
RAISE?not_on_holidays;
END?IF;
/*?check?for?work?hours(8:00?AM?to?18:00?PM?*/
IF?(TO_CHAR(SYSDATE,'HH24')<8OR?TO_CHAR(SYSDATE,?'HH24')>18)?THEN
RAISE?not_working_hours;
END?IF;
EXCEPTION
WHEN?not_on_weekends?THEN
RAISE_APPLICATION_ERROR(-20324,
'May?not?change?employee?table?during?the?weekends');
WHEN?not_on_holidays?THEN
RAISE_APPLICATION_ERROR(-20325,
'May?not?change?employee?table?during?a?holiday');
WHEN?not_working_hours?THEN
RAISE_APPLICATION_ERROR(-20326,
'May?not?change?employee?table?during?no_working?hours');
END;
