ORACLE PL/SQL 編程之八： ?

把觸發(fā)器說透 ?

?

本篇主要內(nèi)容例如以下：

8.1 觸發(fā)器類型

8.1.1 DML觸發(fā)器

8.1.2 替代觸發(fā)器

8.1.3 系統(tǒng)觸發(fā)器

8.2?創(chuàng)建觸發(fā)器

8.2.1 觸發(fā)器觸發(fā)次序

8.2.2 創(chuàng)建DML觸發(fā)器

8.2.3 創(chuàng)建替代(INSTEAD OF)觸發(fā)器

8.2.3 創(chuàng)建系統(tǒng)事件觸發(fā)器

8.2.4 系統(tǒng)觸發(fā)器事件屬性

8.2.5 使用觸發(fā)器謂詞

8.2.6 又一次編譯觸發(fā)器

8.3?刪除和使能觸發(fā)器

8.4?觸發(fā)器和數(shù)據(jù)字典

8.5?? 數(shù)據(jù)庫(kù)觸發(fā)器的應(yīng)用舉例

?

?

觸發(fā)器是很多關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)都提供的一項(xiàng)技術(shù)。在ORACLE系統(tǒng)里，觸發(fā)器類似過程和函數(shù)，都有聲明，運(yùn)行和異常處理過程的PL/SQL塊。

8.1?觸發(fā)器類型

??? 觸發(fā)器在數(shù)據(jù)庫(kù)里以獨(dú)立的對(duì)象存儲(chǔ)，它與存儲(chǔ)過程和函數(shù)不同的是，存儲(chǔ)過程與函數(shù)須要用戶顯示調(diào)用才執(zhí)行，而觸發(fā)器是由一個(gè)事件來啟動(dòng)執(zhí)行。即觸發(fā)器是當(dāng)某個(gè)事件發(fā)生時(shí) 自己主動(dòng)地隱式執(zhí)行 。而且，觸發(fā)器 不能接收參數(shù) 。所以執(zhí)行觸發(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ā)器經(jīng)常使用來完畢由數(shù)據(jù)庫(kù)的完整性約束難以完畢的復(fù)雜業(yè)務(wù)規(guī)則的約束，或用來監(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ā)，而且能夠?qū)γ恳粋€(gè)行或語(yǔ)句操作上進(jìn)行觸發(fā)。

?

8.1.2 替代觸發(fā)器

??? 因?yàn)樵贠RACLE里，不能直接對(duì)由兩個(gè)以上的表建立的視圖進(jìn)行操作。所以給出了替代觸發(fā)器。它就是ORACLE 8專門為進(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ì)表或視圖運(yùn)行數(shù)據(jù)處理操作)、DDL語(yǔ)句（如CREATE、ALTER、DROP語(yǔ)句在數(shù)據(jù)庫(kù)中創(chuàng)建、改動(dò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ì)運(yùn)行觸發(fā)操作。

l???????? 觸發(fā)條件： 由WHEN子句指定一個(gè)邏輯表達(dá)式。僅僅有當(dāng)該表達(dá)式的值為TRUE時(shí)，遇到觸發(fā)事件才會(huì)自己主動(dòng)運(yùn)行觸發(fā)器，使其運(yùn)行觸發(fā)操作。

l???????? 觸發(fā)頻率 ：說明觸發(fā)器內(nèi)定義的動(dòng)作被運(yùn)行的次數(shù)。即語(yǔ)句級(jí)(STATEMENT)觸發(fā)器和行級(jí)(ROW)觸發(fā)器。

語(yǔ)句級(jí)(STATEMENT)觸發(fā)器：是指當(dāng)某觸發(fā)事件發(fā)生時(shí)，該觸發(fā)器僅僅運(yùn)行一次；

行級(jí)(ROW)觸發(fā)器：是指當(dāng)某觸發(fā)事件發(fā)生時(shí)，對(duì)受到該操作影響的每一行數(shù)據(jù)，觸發(fā)器都單獨(dú)運(yùn)行一次。

編寫觸發(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í)須要，能夠先建立過程，然后在觸發(fā)器中用CALL語(yǔ)句進(jìn)行調(diào)用。

l???????? 在觸發(fā)器的運(yùn)行部分僅僅能用DML 語(yǔ)句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL語(yǔ)句（CREATE、ALTER、DROP） 。

l???????? 觸發(fā)器中不能包括事務(wù)控制語(yǔ)句(COMMIT，ROLLBACK，SAVEPOINT)。由于觸發(fā)器是觸發(fā)語(yǔ)句的一部分，觸發(fā)語(yǔ)句被提交、回退時(shí)，觸發(fā)器也被提交、回退了。

l???????? 在觸發(fā)器主體中調(diào)用的不論什么過程、函數(shù)，都不能使用事務(wù)控制語(yǔ)句。

l???????? 在觸發(fā)器主體中不能申明不論什么Long和blob變量。新值new和舊值old也不能是表中的不論什么long和blob列。

l???????? 不同類型的觸發(fā)器(如DML觸發(fā)器、INSTEAD OF觸發(fā)器、系統(tǒng)觸發(fā)器)的語(yǔ)法格式和作用有較大差別。
?

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;

?

?

當(dāng)中：

BEFORE 和AFTER指出觸發(fā)器的觸發(fā)時(shí)序分別為前觸發(fā)和后觸發(fā)方式，前觸發(fā)是在運(yùn)行觸發(fā)事件之前觸發(fā)當(dāng)前所創(chuàng)建的觸發(fā)器，后觸發(fā)是在運(yùn)行觸發(fā)事件之后觸發(fā)當(dāng)前所創(chuàng)建的觸發(fā)器。

?????? FOR EACH ROW選項(xiàng)說明觸發(fā)器為 行觸發(fā)器 。行觸發(fā)器和語(yǔ)句觸發(fā)器的差別表如今：行觸發(fā)器要求當(dāng)一個(gè)DML語(yǔ)句操作影響數(shù)據(jù)庫(kù)中的多行數(shù)據(jù)時(shí)，對(duì)于當(dāng)中的每一個(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 子句說明相關(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 子句說明觸發(fā)約束條件。Condition 為一個(gè)邏輯表達(dá)時(shí)，當(dāng)中必須包括相關(guān)名稱，而不能包括查詢語(yǔ)句，也不能調(diào)用PL/SQL 函數(shù)。WHEN 子句指定的觸發(fā)約束條件僅僅能用在BEFORE 和AFTER 行觸發(fā)器中，不能用在INSTEAD OF 行觸發(fā)器和其他類型的觸發(fā)器中。

??? 當(dāng)一個(gè)基表被修改( INSERT, UPDATE, DELETE)時(shí)要運(yùn)行的存儲(chǔ)過程，運(yùn)行時(shí)依據(jù)其所依附的基表修改而自己主動(dòng)觸發(fā)，因此與應(yīng)用程序無關(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.???????運(yùn)行 BEFORE語(yǔ)句級(jí)觸發(fā)器;

2.???????對(duì)與受語(yǔ)句影響的每一行：

l???????? 運(yùn)行 BEFORE行級(jí)觸發(fā)器

l???????? 運(yùn)行 DML語(yǔ)句

l???????? 運(yùn)行 AFTER行級(jí)觸發(fā)器?

3.???????運(yùn)行 AFTER語(yǔ)句級(jí)觸發(fā)器

?

8.2.2 創(chuàng)建DML觸發(fā)器

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

?

DML觸發(fā)器的限制

l???????? CREATE TRIGGER語(yǔ)句文本的字符長(zhǎng)度不能超過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)用的過程或函數(shù)也不能使用數(shù)據(jù)庫(kù)事務(wù)控制語(yǔ)句；

l???????? 觸發(fā)器中不能使用LONG, LONG RAW 類型；

l???????? 觸發(fā)器內(nèi)能夠參照LOB 類型列的列值，但不能通過 :NEW 改動(dòng)LOB列中的數(shù)據(jù)；

?

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

l???????? 觸發(fā)時(shí)機(jī)： 指定觸發(fā)器的觸發(fā)時(shí)間。假設(shè)指定為BEFORE，則表示在運(yùn)行DML操作之前觸發(fā)，以便防止某些錯(cuò)誤操作發(fā)生或?qū)崿F(xiàn)某些業(yè)務(wù)規(guī)則；假設(shè)指定為AFTER，則表示在運(yùn)行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í)，取值為TRUE，否則為FALSE。

2）。 UPDATING [（column_1,column_2,…,column_x）]： 當(dāng)觸發(fā)事件是UPDATE????? 時(shí)，假設(shè)改動(dòng)了column_x列，則取值為TRUE，否則為FALSE。當(dāng)中column_x是可選的。

3）。 DELETING： 當(dāng)觸發(fā)事件是DELETE時(shí)，則取值為TRUE，否則為FALSE。

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

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

l???????? 觸發(fā)條件： 由WHEN子句指定一個(gè)邏輯表達(dá)式， 僅僅同意在行級(jí)觸發(fā)器上指定觸發(fā)條件，指定 UPDATING 后面的列的列表 。

?

問題：當(dāng)觸發(fā)器被觸發(fā)時(shí)，要使用被插入、更新或刪除的記錄中的列值，有時(shí)要使用操作前、??????? 后列的值.

實(shí)現(xiàn):??:NEW?修飾符訪問操作完畢后列的值

???????:OLD?修飾符訪問操作完畢前列的值

?

特性	INSERT	UPDATE	DELETE
OLD	NULL	實(shí)際值	實(shí)際值
NEW	實(shí)際值	實(shí)際值	NULL

?

例1: 建立一個(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??? -- 說明創(chuàng)建的是行級(jí)觸發(fā)器 ?
BEGIN
??? -- 將改動(dòng)前數(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表改動(dòng)（包含INSERT,DELETE,UPDATE）的時(shí)間范圍，即不同意在非工作時(shí)間改動(dòng)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í)間，不能改動(dòng) departments 表 ' );
? END ? IF ;
END ;

?

例3： 限定僅僅對(duì)部門號(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 ,? ' 部門 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);
*/

?

例4： 利用行觸發(fā)器實(shí)現(xiàn)級(jí)聯(lián)更新。在改動(dòng)了主表regions中的region_id之后（AFTER），級(jí)聯(lián)的、自己主動(dòng)的更新子表countries表中原來在該地區(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)用過程。

?

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ǔ)過程 ...
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?] ? -- 由于 INSTEAD?OF 觸發(fā)器僅僅能在行級(jí)上觸發(fā) , 所以沒有必要指定
[WHEN?condition]
PL / SQL_block? | ?CALL?procedure_name;

?

當(dāng)中：

???????????INSTEAD OF 選項(xiàng)使ORACLE激活觸發(fā)器，而不運(yùn)行觸發(fā)事件。 僅僅能對(duì)視圖和對(duì)象視圖建立INSTEAD OF觸發(fā)器，而不能對(duì)表、模式和數(shù)據(jù)庫(kù)建立INSTEAD OF 觸發(fā)器。

???????????FOR EACH ROW選項(xiàng)說明觸發(fā)器為行觸發(fā)器。行觸發(fā)器和語(yǔ)句觸發(fā)器的差別表如今：行觸發(fā)器要求當(dāng)一個(gè)DML語(yǔ)句操走影響數(shù)據(jù)庫(kù)中的多行數(shù)據(jù)時(shí)，對(duì)于當(dāng)中的每一個(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 子句說明相關(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 子句說明觸發(fā)約束條件。Condition 為一個(gè)邏輯表達(dá)時(shí)，當(dāng)中必須包括相關(guān)名稱，而不能包括查詢語(yǔ)句，也不能調(diào)用PL/SQL 函數(shù)。WHEN 子句指定的觸發(fā)約束條件僅僅能用在BEFORE 和AFTER 行觸發(fā)器中，不能用在INSTEAD OF 行觸發(fā)器和其他類型的觸發(fā)器中。

?

??? INSTEAD_OF 用于對(duì)視圖的DML觸發(fā)，因?yàn)橐晥D有可能是由多個(gè)表進(jìn)行聯(lián)結(jié)(join)而成，因而并不是是全部的聯(lián)結(jié)都是可更新的。但能夠依照所需的方式運(yùn)行更新，比如以下情況：

例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ā)器來為 DELETE 操作運(yùn)行所需的處理，即刪除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)建在視圖上，而且該視圖沒有指定WITH CHECK OPTION選項(xiàng)。

l???????? 不能指定BEFORE 或 AFTER選項(xiàng)。

l???????? FOR EACH ROW子但是可選的，即INSTEAD OF觸發(fā)器僅僅能在行級(jí)上觸發(fā)、或僅僅能是行級(jí)觸發(fā)器，沒有必要指定。

l???????? 沒有必要在針對(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ù)server的啟動(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;

?

當(dāng)中: ddl_event_list：一個(gè)或多個(gè)DDL 事件，事件間用 OR 分開；

????????database_event_list：一個(gè)或多個(gè)數(shù)據(jù)庫(kù)事件，事件間用 OR 分開；

?

???????????系統(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ī)	說明
STARTUP	AFTER	啟動(dòng)數(shù)據(jù)庫(kù)實(shí)例之后觸發(fā)
SHUTDOWN	BEFORE	關(guān)閉數(shù)據(jù)庫(kù)實(shí)例之前觸發(fā)（非正常關(guān)閉不觸發(fā)）
SERVERERROR	AFTER	數(shù)據(jù)庫(kù)server錯(cuò)誤發(fā)生之后觸發(fā)
LOGON	AFTER	成功登錄連接到數(shù)據(jù)庫(kù)后觸發(fā)
LOGOFF	BEFORE	開始斷開數(shù)據(jù)庫(kù)連接之前觸發(fā)
CREATE	BEFORE，AFTER	在運(yùn)行CREATE語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象之前、之后觸發(fā)
DROP	BEFORE，AFTER	在運(yùn)行DROP語(yǔ)句刪除數(shù)據(jù)庫(kù)對(duì)象之前、之后觸發(fā)
ALTER	BEFORE，AFTER	在運(yùn)行ALTER語(yǔ)句更新數(shù)據(jù)庫(kù)對(duì)象之前、之后觸發(fā)
DDL	BEFORE，AFTER	在運(yùn)行大多數(shù)DDL語(yǔ)句之前、之后觸發(fā)
GRANT	BEFORE，AFTER	運(yùn)行GRANT語(yǔ)句授予權(quán)限之前、之后觸發(fā)
REVOKE	BEFORE，AFTER	運(yùn)行REVOKE語(yǔ)句收權(quán)限之前、之后觸犯發(fā)
RENAME	BEFORE，AFTER	運(yùn)行RENAME語(yǔ)句更改數(shù)據(jù)庫(kù)對(duì)象名稱之前、之后觸犯發(fā)
AUDIT / NOAUDIT	BEFORE，AFTER	運(yùn)行AUDIT 或 NOAUDIT進(jìn)行審計(jì)或停止審計(jì)之前、之后觸發(fā)

?

?

8.2.4 系統(tǒng)觸發(fā)器事件屬性

?

事件屬性\事件	Startup/Shutdown	Servererror	Logon/Logoff	DDL	DML
事件名稱	ü＊	ü＊	ü＊	ü＊	＊
數(shù)據(jù)庫(kù)名稱	ü＊	?	?	?	?
數(shù)據(jù)庫(kù)實(shí)例號(hào)	ü＊	?	?	?	?
錯(cuò)誤號(hào)	?	ü＊	?	?	?
username	?	?	ü＊	＊	?
模式對(duì)象類型	?	?	?	ü＊	＊
模式對(duì)象名稱	?	?	?	ü＊	＊
列	?	?	?	?	ü＊

?

除DML語(yǔ)句的列屬性外，其余事件屬性值可通過調(diào)用ORACLE定義的事件屬性函數(shù)來讀取。

函數(shù)名稱	數(shù)據(jù)類型	說??? 明
Ora_sysevent	VARCHAR2（20）	激活觸發(fā)器的事件名稱
Instance_num	NUMBER	數(shù)據(jù)庫(kù)實(shí)例名
Ora_database_name	VARCHAR2（50）	數(shù)據(jù)庫(kù)名稱
Server_error(posi)	NUMBER	錯(cuò)誤信息棧中posi指定位置中的錯(cuò)誤號(hào)
? ? Is_servererror(err_number)	? ? BOOLEAN	檢查err_number指定的錯(cuò)誤號(hào)是否在錯(cuò)誤信息棧中，假設(shè)在則返回TRUE，否則返回FALSE。在觸發(fā)器內(nèi)調(diào)用此函數(shù)能夠推斷是否發(fā)生指定的錯(cuò)誤。
Login_user	VARCHAR2(30)	登陸或注銷的username稱
Dictionary_obj_type	VARCHAR2(20)	DDL語(yǔ)句所操作的數(shù)據(jù)庫(kù)對(duì)象類型
Dictionary_obj_name	VARCHAR2(30)	DDL語(yǔ)句所操作的數(shù)據(jù)庫(kù)對(duì)象名稱
Dictionary_obj_owner	VARCHAR2(30)	DDL語(yǔ)句所操作的數(shù)據(jù)庫(kù)對(duì)象全部者名稱
Des_encrypted_password	VARCHAR2(2)	正在創(chuàng)建或改動(dòng)的經(jīng)過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	假設(shè)觸發(fā)語(yǔ)句是 INSERT 語(yǔ)句，則為TRUE,否則為FALSE
UPDATING	假設(shè)觸發(fā)語(yǔ)句是 UPDATE語(yǔ)句，則為TRUE,否則為FALSE
DELETING	假設(shè)觸發(fā)語(yǔ)句是 DELETE 語(yǔ)句，則為TRUE,否則為FALSE

?

8.2.6 又一次編譯觸發(fā)器

假設(shè)在觸發(fā)器內(nèi)調(diào)用其他函數(shù)或過程，當(dāng)這些函數(shù)或過程被刪除或改動(dòng)后，觸發(fā)器的狀態(tài)將被標(biāo)識(shí)為無效。當(dāng)DML語(yǔ)句激活一個(gè)無效觸發(fā)器時(shí)，ORACLE將又一次編譯觸發(fā)器代碼，假設(shè)編譯時(shí)發(fā)現(xiàn)錯(cuò)誤，這將導(dǎo)致DML語(yǔ)句運(yùn)行失敗。

在PL/SQL程序中能夠調(diào)用ALTER TRIGGER語(yǔ)句又一次編譯已經(jīng)創(chuàng)建的觸發(fā)器，格式為：???????????

ALTER ? TRIGGER ? [schema.] ?trigger_name?COMPILE? [?DEBUG]

?????? 當(dāng)中：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ā)。

無效狀態(tài)(DISABLE)：當(dāng)觸發(fā)事件發(fā)生時(shí)，處于無效狀態(tài)的數(shù)據(jù)庫(kù)觸發(fā)器TRIGGER 將不會(huì)被觸發(fā)，此時(shí)就跟沒有這個(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表運(yùn)行INSERT, UPDATE, DELETE 操作時(shí)，它自己主動(dòng)更新dept_summary 表中的數(shù)據(jù)。因?yàn)樵赑L/SQL塊中不能直接調(diào)用DDL語(yǔ)句，所以，利用ORACLE內(nèi)置包DBMS_UTILITY中的EXEC_DDL_STATEMENT過程，由它運(yùn)行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 過程 disp_dept_summary
-- 在觸發(fā)器中調(diào)用該過程顯示 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('' 正在運(yùn)行 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 = 1000 ? WHERE ?empno = 9999 ;
?DBMS_OUTPUT.PUT_LINE( ' 改動(dòng)后 ' );
?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表運(yùn)行INSERT, UPDATE, DELETE 操作時(shí)，它自己主動(dòng)更新dept_summary 表中的數(shù)據(jù)。因?yàn)樵赑L/SQL塊中不能直接調(diào)用DDL語(yǔ)句，所以，利用ORACLE內(nèi)置包DBMS_UTILITY中的EXEC_DDL_STATEMENT過程，由它運(yùn)行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('' 正在運(yùn)行 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('' 正在運(yùn)行 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('' 正在運(yùn)行 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.1 ? WHERE ?deptno = 90 ;
??DBMS_OUTPUT.PUT_LINE( ' 改動(dòng)后 ' );
??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('' 正在運(yùn)行 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('' 正在運(yùn)行 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('' 正在運(yùn)行 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.1 ? WHERE ?deptno = 90 ;
??DBMS_OUTPUT.PUT_LINE( ' 改動(dòng)后 ' );
??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,因?yàn)樵撘晥D是復(fù)合查詢所產(chǎn)生的視圖，所以不能運(yùn)行DML語(yǔ)句。依據(jù)用戶對(duì)視圖所插入的數(shù)據(jù)推斷須要將數(shù)據(jù)插入到哪個(gè)視圖基表中，然后對(duì)該基表運(yùn)行插入操作。

?

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('' 正在運(yùn)行 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||
?????????????????????'' 的部門已存在，插入操作失??！ '');
?????????????????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(' 部門編號(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? WITH ? 1000
????INCREMENT? BY ? 1
????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ù)的完整性管理；

例：改動(dòng)DEPT表的DEPTNO列時(shí)，同一時(shí)候把EMP表中對(duì)應(yīng)的DEPTNO也作對(duì)應(yīng)的改動(dòng)；

?

CREATE ?SEQUENCE?update_sequence?
????INCREMENT? BY ? 1
????START? WITH ? 1000
????MAXVALUE? 5000 ?CYCLE;

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 = 25 ? WHERE ?deptno = 20 ;

?

l???????? 幫助實(shí)現(xiàn)安全控制；

例：保證對(duì)EMP表的改動(dòng)僅在工作日的工作時(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 ? > 0 ? THEN
????RAISE?not_on_holidays;
END ? IF ;
???? /*?check?for?work?hours(8:00?AM?to?18:00?PM?*/
IF ?(TO_CHAR(SYSDATE, 'HH24' ) < 8 ? OR ?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 ;

?