《Oracle數(shù)據(jù)庫(kù)應(yīng)用教程》課件第8章

第8章

觸

發(fā)

器

8.1觸發(fā)器簡(jiǎn)介

8.2觸發(fā)器的格式

8.3觸發(fā)器的種類

8.4管理觸發(fā)器

8.5小結(jié)

習(xí)題八

上機(jī)實(shí)驗(yàn)八

8.1觸發(fā)器簡(jiǎn)介

觸發(fā)器是一種特殊的存儲(chǔ)過程，當(dāng)特定對(duì)象上的特定事件出現(xiàn)時(shí)，將自動(dòng)觸發(fā)執(zhí)行的代碼塊。觸發(fā)器比數(shù)據(jù)庫(kù)有更精細(xì)和更復(fù)雜的數(shù)據(jù)控制能力。觸發(fā)器與過程的區(qū)別在于：過程要由用戶或應(yīng)用程序顯式調(diào)用，而觸發(fā)器是滿足特定事件時(shí)在數(shù)據(jù)庫(kù)后臺(tái)自動(dòng)執(zhí)行。數(shù)據(jù)庫(kù)觸發(fā)器具有以下功能：

(1)實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)完整性規(guī)則。

(2)自動(dòng)生成派生數(shù)據(jù)。

(3)提供審計(jì)和日志記錄。

(4)啟用復(fù)雜的業(yè)務(wù)邏輯。

(5)實(shí)施更復(fù)雜的安全性檢查。

(6)防止無效的事務(wù)處理。

8.2觸發(fā)器的格式

所有的觸發(fā)器，不管其類型如何，都可以使用相同的語法創(chuàng)建。下面先簡(jiǎn)單了解一下Oracle產(chǎn)生數(shù)據(jù)庫(kù)觸發(fā)器的基本語法：

create[orreplace]trigger觸發(fā)器名

觸發(fā)時(shí)間

觸發(fā)事件

on對(duì)象名

[foreachrow]

pl/sql

語句

說明：觸發(fā)器名：觸發(fā)器對(duì)象的名稱。由于觸發(fā)器是數(shù)據(jù)庫(kù)自動(dòng)執(zhí)行的，因此該名稱只是一個(gè)名稱，沒有實(shí)質(zhì)的用途。

觸發(fā)時(shí)間：指明觸發(fā)器何時(shí)執(zhí)行，取值有before和after。

before表示在數(shù)據(jù)庫(kù)動(dòng)作之前觸發(fā)器執(zhí)行；

after表示在數(shù)據(jù)庫(kù)動(dòng)作之后觸發(fā)器執(zhí)行。

觸發(fā)事件：指明哪些數(shù)據(jù)庫(kù)動(dòng)作會(huì)觸發(fā)此觸發(fā)器，比如，

insert表示數(shù)據(jù)庫(kù)插入會(huì)觸發(fā)此觸發(fā)器；

update表示數(shù)據(jù)庫(kù)修改會(huì)觸發(fā)此觸發(fā)器；

delete表示數(shù)據(jù)庫(kù)刪除會(huì)觸發(fā)此觸發(fā)器。

對(duì)象名：數(shù)據(jù)庫(kù)觸發(fā)器所在的表名、數(shù)據(jù)庫(kù)名或模式用戶名。

foreachrow：對(duì)表的每一行觸發(fā)器執(zhí)行一次。如果沒有這一選項(xiàng)，則只對(duì)整個(gè)表執(zhí)行一次。

【例8.1】

在student表上建立觸發(fā)器。在更新表student之前觸發(fā)，目的是不允許在周末修改表。

SQL>createtriggerstudent_secure

2

beforeinsertorupdateordelete

--對(duì)整表在插入、更新、刪除前觸發(fā)

3

onstudent

4

begin

5

if(to_char(sysdate,'DY')='SUN')then

6

RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表student');

7

endif;

8

end；

9/一般來說，觸發(fā)器由觸發(fā)器名稱、觸發(fā)器語句、觸發(fā)器限制和觸發(fā)器操作幾部分組成。下面是創(chuàng)建觸發(fā)器的通用語法：

1CREATE[ORREPLACE]TRIGGERtrigger_name

2{BEFORE|AFTER|INSTEADOF}

3triggering_event{dml_event_list|ddl_event_list|database_event_list}

4ONtrigger_object{[database]|[schema.][table_or_view_name]}

5referencing_clause

6[FOREACHROW]

7[WHENtrigger_condition]

8trigger_body其中，第1行指明創(chuàng)建的觸發(fā)器名稱，第2～6行為觸發(fā)器語句，第7行為觸發(fā)器限制，第8行為觸發(fā)器操作。

說明：

trigger_name是觸發(fā)器的名稱。

觸發(fā)器語句是指在相應(yīng)數(shù)據(jù)庫(kù)對(duì)象上觸發(fā)的時(shí)間及導(dǎo)致觸發(fā)器執(zhí)行的事件等。tiggering_event表示觸發(fā)事件，比如表或視圖上的dml語句、ddl語句、數(shù)據(jù)庫(kù)關(guān)閉或啟動(dòng)等。其中，dml_event_list是一個(gè)或多個(gè)DML事件，包括INSERT、UPDATE、DELETE語句，事件之間用“OR”分隔；ddl_event_list是一個(gè)或多個(gè)DDL事件，包括CREATE、ALTER或DROP語句；database_event_list是一個(gè)或多個(gè)數(shù)據(jù)庫(kù)事件，包括服務(wù)器的啟動(dòng)或關(guān)閉、用戶的登錄或退出以及服務(wù)器錯(cuò)誤等。referencing_clause用來引用正在處于修改狀態(tài)下的行中的數(shù)據(jù)。

WHEN子句代表觸發(fā)器限制條件，包含一個(gè)布爾表達(dá)式，即在

WHEN子句中如果指定trigger_condition，則首先對(duì)該條件求值。只有在該條件為真值時(shí)才運(yùn)行。

觸發(fā)器操作即觸發(fā)器主體包含一些SQL語句和代碼。

【例8.2】

在employees表上構(gòu)建一個(gè)觸發(fā)器，在插入或修改部門號(hào)時(shí)觸發(fā)，如果該雇員部門號(hào)不是“80”，則commission_pct列值置為0。(注：這里使用的為HR用戶下的employees表。缺省狀態(tài)下，HR用戶被鎖定，可以通過管理員解除鎖定并向其提供口令，下同。)

SQL>createtriggerbiufer_employees_department_id

2beforeinsertorupdateofdepartment_id

3

onemployees

4referencingoldasold_value

5

newasnew_value

6foreachrow

7when(new_value.department_id<>80)

8begin

9:new_mission_pct:=0;

10end;

/

觸發(fā)器已創(chuàng)建。

在這個(gè)例子中，觸發(fā)器名稱是第1行biufer_employees_department_id，第2行至第6行構(gòu)成了觸發(fā)器語句。在這個(gè)例子中，無論是否規(guī)定了department_id，對(duì)employees表進(jìn)行insert或?qū)mployees表的department_id列進(jìn)行update時(shí)，觸發(fā)器都會(huì)在每一受影響的行上執(zhí)行一次。第7行為觸發(fā)器限制，限制不是必需的。此例表示如果列department_id不等于80，則觸發(fā)器就會(huì)執(zhí)行。其中的new_value代表更新之后的值。第8行至第10行構(gòu)成了觸發(fā)器的主體。本例中，主體很簡(jiǎn)單，就是將更新后的commission_pct列置為0。

下面做一個(gè)觸發(fā)動(dòng)作，以測(cè)試觸發(fā)器是否有效。

SQL>insertintoemployees

(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct)

values(12345,'chen','donny',sysdate,'AD_PRES','donny@',60,10000,.25);

通過查看下面語句查看結(jié)果。

SQL>selectcommission_pctfromemployeeswhereemployee_id=12345;

COMMISSION_PCT

----------

0

查詢結(jié)果說明觸發(fā)器生效，觸發(fā)器已經(jīng)自動(dòng)改變了用戶的輸入值。

8.3觸發(fā)器的種類

8.3.1DML觸發(fā)器

在實(shí)際應(yīng)用中，DML觸發(fā)器是使用最多的觸發(fā)器。DML觸發(fā)器可以由

DML語句激發(fā)，并且由該語句的類型決定

DML觸發(fā)器的類型?？梢远xDML觸發(fā)器進(jìn)行INSERT、UPDATE、DELETE操作。這類觸發(fā)器可以在上述操作之前或之后激發(fā)，也可以按每個(gè)變更行激發(fā)一次，或每個(gè)語句激發(fā)一次。這些條件的組合形成了觸發(fā)器的類型，3種語句×2種定時(shí)×2種級(jí)別總共有

12種可能的觸發(fā)類型。例如，在插入行之前、更新語句之后等都是合法的

DML觸發(fā)器類型。

表8-1DML觸發(fā)器

1．觸發(fā)器時(shí)機(jī)

在DML觸發(fā)器中，根據(jù)其觸發(fā)時(shí)機(jī)的不同(觸發(fā)時(shí)機(jī)可分為：BEFORE和AFTER)，觸發(fā)器可分為兩類：BEFORE觸發(fā)器和AFTER觸發(fā)器。它們?cè)谟|發(fā)過程中各自執(zhí)行的順序不同。DML觸發(fā)器觸發(fā)時(shí)機(jī)及執(zhí)行順序是：BEFORE觸發(fā)器、約束檢查、更新表和AFTER觸發(fā)器。

BEFORE觸發(fā)器在約束之前執(zhí)行，通常用于：

(1)設(shè)置或修改被更新或插入的列值。

(2)檢查復(fù)雜的安全規(guī)則，如限制時(shí)間等。

(3)增強(qiáng)商業(yè)應(yīng)用規(guī)則。

(4)通過觸發(fā)器的邏輯潛在地引發(fā)一個(gè)異常來拒絕觸發(fā)語句，這是相當(dāng)有效的，因?yàn)橛|發(fā)器是在約束之前執(zhí)行的。

AFTER觸發(fā)器在BEFORE觸發(fā)器、約束檢查以及更新表后才執(zhí)行。AFTER觸發(fā)器一般用于：

(1)用戶信息的審計(jì)。

(2)導(dǎo)出數(shù)據(jù)的生成。如果導(dǎo)出數(shù)據(jù)存儲(chǔ)在其他表中，而不是觸發(fā)器所依賴的表，則使用AFTER；如果導(dǎo)出數(shù)據(jù)存儲(chǔ)在當(dāng)前觸發(fā)器依賴的表中，則觸發(fā)器必須定義成BEFORE觸發(fā)器。

(3)遠(yuǎn)程數(shù)據(jù)的復(fù)制。

2．語句級(jí)觸發(fā)器和行級(jí)觸發(fā)器

根據(jù)觸發(fā)器所依賴的表對(duì)象不同，可將DML觸發(fā)器進(jìn)一步分為語句級(jí)(statement)和行級(jí)(row)觸發(fā)器。這兩類觸發(fā)器指定了觸發(fā)器語句執(zhí)行的頻率。若創(chuàng)建觸發(fā)器的語句中添加了子句foreachrow，則為行級(jí)觸發(fā)器，否則為語句級(jí)觸發(fā)器。默認(rèn)是語句級(jí)觸發(fā)器。

語句級(jí)觸發(fā)器是在表或者視圖上執(zhí)行的特定語句(或者語句組)的觸發(fā)器，能夠與INSERT、UPDATE、DELETE及其組合進(jìn)行關(guān)聯(lián)。無論使用什么樣的組合，各個(gè)語句觸發(fā)器都只針對(duì)指定語句激活一次。比如，無論UPDATE有多少行，都只會(huì)調(diào)用一次UPDATE語句觸發(fā)器。

【例8.3】

創(chuàng)建一個(gè)語句級(jí)觸發(fā)器，以對(duì)修改表的時(shí)間、人員進(jìn)行日志記錄。

(1)建立實(shí)驗(yàn)表。

SQL>createtableemployees_copyasselect*fromhr.employees;

說明：缺省狀態(tài)下，HR用戶被鎖定，可以通過管理員解除鎖定并向其提供口令。在另一用戶下做測(cè)試時(shí)，可以將HR用戶employees的增、刪、改的權(quán)限暫時(shí)授予用戶。

(2)建立日志表。

SQL>createtableemployees_log(

whovarchar2(30),

whendate);

(3)在employees_copy表上建立語句觸發(fā)器，在觸發(fā)器中填充employees_log表。

SQL>createorreplacetriggerbiud_employee_copy

2beforeinsertorUPDATEorDELETE

3onemployees_copy

4begin

5INSERTintoemployees_log(who,when)

6

values(user,sysdate);

7end;

/

(4)測(cè)試。

SQL>UPDATEemployees_copysetsalary=salary*1.1;

SQL>select*fromemployees_log;

(5)確定是哪個(gè)語句起作用，即確定INSERT、UPDATE、DELETE中哪一個(gè)觸發(fā)了觸發(fā)器。

行級(jí)觸發(fā)器是指被受到影響的各個(gè)行激活的觸發(fā)器，即每行變動(dòng)一次就觸發(fā)一次。在觸發(fā)器內(nèi)部，我們可以訪問正在處理的行的數(shù)據(jù)。這種訪問是通過兩個(gè)相關(guān)的標(biāo)識(shí)符(:?old和

:?new)實(shí)現(xiàn)的。

:old和

:new相關(guān)標(biāo)識(shí)符在不同的DML語句中代表的值的含義見表8-2。相關(guān)標(biāo)識(shí)符是一種特殊的PL/SQL連接變量(bindvariable)。該標(biāo)識(shí)符前面的冒號(hào)說明它們是使用在嵌套

PL/SQL中的宿主變量意義上的連接變量，而不是一般的PL/SQL變量。referencing子句只是將new和old重命名為new_value和old_value，目的是避免混淆，比如操作一個(gè)名為new的表。

表8-2:old和

:new相關(guān)標(biāo)識(shí)符

【例8.4】

重新修改上述觸發(fā)器。在上述語句級(jí)觸發(fā)器示例的第(3)步的第3行和第4行之間加一條語句foreachrow，重新執(zhí)行第(3)步和第(4)步，觀看效果。結(jié)果發(fā)現(xiàn)語句級(jí)觸發(fā)器在執(zhí)行過程中每行觸發(fā)一次。

【例8.5】

創(chuàng)建一行級(jí)觸發(fā)器，為主鍵生成自增序列號(hào)。

(1)創(chuàng)建一個(gè)實(shí)驗(yàn)表和一個(gè)序列。

SQL>droptablefoo;

SQL>createtablefoo(idnumber,datavarchar2(20));

SQL>createsequencefoo_seq;

(2)創(chuàng)建觸發(fā)器。

SQL>createorreplacetriggerbifer_foo_id_pk

beforeINSERTonfoo

foreachrow

begin

selectfoo_seq.nextvalinto:new.idfromdual;

end;

(3)插入數(shù)據(jù)進(jìn)行測(cè)試。

SQL>INSERTintofoo(data)values('donny');

SQL>INSERTintofoovalues(5,'chen');

(4)查詢結(jié)果，測(cè)試觸發(fā)器是否生效。

SQL>select*fromfoo;

3．DML觸發(fā)器WHEN子句

WHEN子句只適用于行級(jí)觸發(fā)器。如果使用該子句，則觸發(fā)器體將只對(duì)滿足WHEN子句說明條件的行執(zhí)行。WHEN子句的語法如下：

WHENtrigger_condition

其中，trigger_condition是邏輯表達(dá)式。該表達(dá)式將為每行求值。:?new和

:?old記錄可以在trigger_condition內(nèi)部引用，但不需使用冒號(hào)，該冒號(hào)只在觸發(fā)器體內(nèi)有效。

【例8.6】

觸發(fā)器CheckCredits只在當(dāng)前學(xué)生得到的學(xué)分超出

20時(shí)才運(yùn)行。

SQL>CREATEORREPLACETRIGGERCheckCredits

BEFOREINSERTORUPDATEOFcurrent_creditsONstudents

FOREACHROW

WHEN(new.current_credits>20)

BEGIN

/*Triggerbodygoeshere.*/

END;上述觸發(fā)器CheckCredits的實(shí)現(xiàn)也可寫為下列代碼：

SQL>CREATEORREPLACETRIGGERCheckCredits

BEFOREINSERTORUPDATEOFcurrent_creditsONstudents

FOREACHROW

BEGIN

IF:new.current_credits>20THEN

/*Triggerbodygoeshere.*/

ENDIF;

END;

4．觸發(fā)器謂詞：INSERTING、UPDATING和DELETING

這種觸發(fā)器的內(nèi)部(為不同的DML語句激發(fā)的觸發(fā)器)有三個(gè)可用來確認(rèn)執(zhí)行何種操作的邏輯表達(dá)式。這些表達(dá)式的謂詞是

INSERTING、UPDATING和DELETING。表8-3給出了表達(dá)式謂詞與對(duì)應(yīng)執(zhí)行DML語句的屬性值。

表8-3表達(dá)式謂詞與對(duì)應(yīng)執(zhí)行DML語句的屬性值

可以在觸發(fā)器中使用INSERTING、UPDATING或DELETING條件謂詞來進(jìn)行判斷。例如下面的示范代碼：

SQL>Begin

ifINSERTINGthen

elsifUPDATINGthen

elsifDELETINGthen

endif;

end;………

或者修改某一列數(shù)據(jù)，例如：

SQL>ifUPDATING('col1')orUPDATING('col2')then

endif;

【例8.7】

建立一觸發(fā)器，用于審計(jì)對(duì)employees_copy表所作的操作行為。

(1)修改日志表。

SQL>altertableemployees_log

add(actionvarchar2(20));…

(2)修改觸發(fā)器，以便記錄語句類型。

SQL>createorreplacetriggerbiud_employee_copy

beforeinsertorupdateordelete

onemployees_copy

declare

l_actionemployees_log.action%type;

begin

ifINSERTINGthen

l_action:='insert';

elsifUPDATINGthen

l_action:='update';

elsifDELETINGthen

l_action:='delete';

else

raise_application_error(-20001,'youshouldneverevergetthiserror.');

endif;

insertintoemployees_log(who,when,action)

values(user,sysdate,l_action);

end;

/

(3)測(cè)試。為了測(cè)試本觸發(fā)器的效果，首先刪掉記錄日志表employees_log中的數(shù)據(jù)。

SQL>DELETEfromemployees_log;

SQL>insertintoemployees_copy(employee_id,last_name,email,hire_date,job_id)

values(666,'chen','donny@hotmail',sysdate,'AC_MGR');

SQL>select*fromemployees_log;

然后執(zhí)行更新操作，查看觸發(fā)器的效果。

SQL>UPDATEemployees_copysetsalary=50000whereemployee_id=666;

SQL>select*fromemployees_log;8.3.2INSTEADOF觸發(fā)器

替代觸發(fā)器(Insteadoftrigger)只能定義在視圖上。替代觸發(fā)器是行觸發(fā)器。與DML觸發(fā)器不同，DML觸發(fā)器是在DML操作之外運(yùn)行的，而替代觸發(fā)器則用INSTEADOF來規(guī)定,它執(zhí)行一個(gè)替代操作來代替觸發(fā)觸發(fā)器的操作。例如，如果對(duì)某個(gè)視圖建立了一個(gè)INSTEADOF觸發(fā)器，它由INSERT語句觸發(fā)，則在對(duì)此表執(zhí)行INSERT操作時(shí)觸發(fā)此觸發(fā)器，但并不對(duì)視圖實(shí)際執(zhí)行INSERT操作，這與DML觸發(fā)器完全不同，DML觸發(fā)器不影響DML語句對(duì)表的實(shí)際操作。那么為什么要用替代觸發(fā)器呢？

假如有一個(gè)視圖是基于多個(gè)表的字段連接查詢得到的，現(xiàn)在如果想直接對(duì)這個(gè)視圖進(jìn)行插入操作，那么對(duì)視圖的插入操作如何來反映到組成這個(gè)視圖的各個(gè)表中呢？事實(shí)上，除了定義一個(gè)觸發(fā)器來綁定對(duì)視圖的插入動(dòng)作外，沒有別的辦法通過系統(tǒng)的報(bào)錯(cuò)而直接向視圖中插入數(shù)據(jù)，這就是用替代觸發(fā)器的原因。替換的意思實(shí)際上是觸發(fā)器的主體部分把對(duì)視圖的插入操作轉(zhuǎn)換成詳細(xì)的對(duì)各個(gè)表的插入。

例如，直接執(zhí)行對(duì)該視圖的插入操作是非法的。這是因?yàn)樵撘晥D是兩個(gè)表的聯(lián)合，而插入操作要求對(duì)兩個(gè)現(xiàn)行表進(jìn)行修改。下面的

SQL*Plus會(huì)話顯示了插入操作過程。

【例8.8】

演示INSTEADOF觸發(fā)器的應(yīng)用案例。

(1)創(chuàng)建一個(gè)視圖company_phone_book，其中，name列的定義來自hr.employees表中兩個(gè)字段的聯(lián)合。

SQL>createorreplaceviewcompany_phone_bookas

selectfirst_name||','||last_namename,email,phone_number,

employee_idemp_id

fromhr.employees;

更新視圖的語句如下：

SQL>updatecompany_phone_book

setname='chen1,donny1'

whereemp_id=100;

此時(shí)出現(xiàn)如下的錯(cuò)誤提示：

ERROR位于第

2行:

ORA-01733:此處不允許虛擬列

(4)測(cè)試。執(zhí)行步驟(2)中的更新視圖語句，然后在其對(duì)應(yīng)的表和視圖中查看效果。重新更新視圖的語句如下：

SQL>updatecompany_phone_book

setname='chen1,donny1'

whereemp_id=100;

已更新1行。

8.3.3DDL觸發(fā)器

DDL觸發(fā)器是指在執(zhí)行DDL操作(如CREATE、ALTER、DROP等語句)時(shí)激發(fā)的觸發(fā)器。例如，用戶可以創(chuàng)建觸發(fā)器來記錄對(duì)象創(chuàng)建的時(shí)間，以防止用戶刪除自己創(chuàng)建的表。這種觸發(fā)器主要用來防止DDL操作引起的破壞或提供相應(yīng)的安全監(jiān)控。表8-4給出了DDL事件的種類以及這些事件出現(xiàn)的時(shí)機(jī)。

表8-4DDL事件的種類及其出現(xiàn)時(shí)機(jī)

系統(tǒng)觸發(fā)器有幾個(gè)內(nèi)部的屬性函數(shù)可供使用。這些參數(shù)允許觸發(fā)器體獲得有關(guān)觸發(fā)事件的信息。表

8-5對(duì)這些事件屬性函數(shù)做了說明。與觸發(fā)器參數(shù)不同，事件屬性函數(shù)是

SYS擁有的獨(dú)立

PL/SQL函數(shù)。系統(tǒng)沒有為這些函數(shù)指定默認(rèn)的替代名稱，所以為了識(shí)別這些函數(shù)，程序中必須在它們的前面加上前綴SYS。

表

8-5DDL事件用到的屬性函數(shù)

【例8.9】

建立DDL觸發(fā)器，用于記錄所刪除的對(duì)象情況(環(huán)境：在scott用戶模式下)。

(1)建立一個(gè)日志表。

SQL>connectscott/tiger;

已連接。

SQL>createtabledroped_objects(

object_namevarchar2(30),

object_typevarchar2(30),

dropped_ondate);

表已創(chuàng)建。

(2)創(chuàng)建觸發(fā)器。

SQL>createorreplacetriggerlog_drop_trigger

beforedroponscott.schema

begin

insertintodroped_objectsvalues(

sys.dictionary_obj_name,

--與觸發(fā)器相關(guān)的函數(shù)

sys.dictionary_obj_type,

sysdate);

end;

/

觸發(fā)器已創(chuàng)建。

(3)進(jìn)行測(cè)試。用如下命令創(chuàng)建一個(gè)表drop_me，創(chuàng)建一個(gè)視圖drop_me_view，然后將這兩個(gè)對(duì)象刪除。

SQL>createtabledrop_me(anumber);

表已創(chuàng)建。

SQL>createviewdrop_me_viewasselect*fromdrop_me;

視圖已建立。

SQL>dropviewdrop_me_view;

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

系統(tǒng)觸發(fā)器在發(fā)生如數(shù)據(jù)庫(kù)啟動(dòng)或關(guān)閉等系統(tǒng)事件時(shí)激發(fā)，而不是在執(zhí)行

DML語句時(shí)激發(fā)。數(shù)據(jù)庫(kù)事件包括服務(wù)器的啟動(dòng)或關(guān)閉、用戶的登錄或退出以及服務(wù)器錯(cuò)誤。創(chuàng)建系統(tǒng)觸發(fā)器的語法如下：

CREATE[ORREPLACE]TRIGGER[schema.]trigger_name

{BEFORE|AFTER}

{ddl_event_list|database_event_list}

ON{DATABASE|[schema.]SCHEMA}

[when_clause]

表8-6數(shù)據(jù)庫(kù)事件的種類及出現(xiàn)時(shí)機(jī)

【例8.10】

創(chuàng)建當(dāng)數(shù)據(jù)庫(kù)啟動(dòng)時(shí)的系統(tǒng)觸發(fā)器。

SQL>createtriggerad_startup

afterstartup

ondatabase

begin

--dosomestuff--比如可以進(jìn)行數(shù)據(jù)的初始化工作，記錄數(shù)據(jù)庫(kù)的啟動(dòng)時(shí)間等。

end;

系統(tǒng)觸發(fā)器也有一些內(nèi)部的屬性函數(shù)可供使用。這些參數(shù)允許觸發(fā)器體獲得有關(guān)觸發(fā)事件的信息。表8-7對(duì)這些事件屬性函數(shù)做了說明。

在本章的開始部分介紹的觸發(fā)器

LogCreations中使用了這些屬性函數(shù)。與觸發(fā)器參數(shù)不同，事件屬性函數(shù)是

SYS擁有的獨(dú)立

PL/SQL函數(shù)。系統(tǒng)沒有為這些函數(shù)指定默認(rèn)的替代名稱，所以為了識(shí)別這些函數(shù)，程序中必須在它們的前面加上前綴

SYS。

表

8-7系統(tǒng)觸發(fā)器的屬性函數(shù)

【例8.11】

創(chuàng)建系統(tǒng)觸發(fā)器，記錄本次啟動(dòng)數(shù)據(jù)庫(kù)以來所有登錄的用戶。

(1)創(chuàng)建一個(gè)用戶登錄的日志記錄表，包含登錄用戶名、登錄時(shí)間、數(shù)據(jù)庫(kù)名字和實(shí)例號(hào)。

SQL>createtableuserlog(usernamevarchar2(10),logon_timedate,db_name

varchar2(20),instance_numbernumber);

表已創(chuàng)建。

(2)授予創(chuàng)建觸發(fā)器的用戶administerdatabasetrigger權(quán)限。

SQL>connsystem/manager;

已連接。

SQL>grantadministerdatabasetriggertoscott;

授權(quán)成功。

(3)創(chuàng)建系統(tǒng)啟動(dòng)的觸發(fā)器，根據(jù)題目要求，記錄本次啟動(dòng)數(shù)據(jù)庫(kù)以來登錄的用戶日志，因此該觸發(fā)器在啟動(dòng)時(shí)，清空以往的用戶日志表。

SQL>createorreplacetriggerinit_logonafterstartupondatabase

2begin

3deletefromuserlog;

4end;

5/

觸發(fā)器已創(chuàng)建。

(4)創(chuàng)建登錄系統(tǒng)的觸發(fā)器，用于記錄用戶登錄日志。

SQL>createorreplacetriggerdatabase_logon

2after

3logon

4ondatabase

5begin

6insertintouserlog

7values(sys.login_user,sysdate,sys.database_name,sys.instance_num);

8end;

9/

觸發(fā)器已創(chuàng)建。

(5)測(cè)試。

用不同的用戶登錄查詢用戶日志表。

SQL>connecthr/hr;

已連接。

SQL>connectscott/tiger;

已連接。

8.4管

理

觸

發(fā)

器

1．利用數(shù)據(jù)字典視圖查看觸發(fā)器的有關(guān)信息

與存儲(chǔ)子程序類似，數(shù)據(jù)字典視圖包括有關(guān)觸發(fā)器及其執(zhí)行狀態(tài)的信息。這些視圖必須在觸發(fā)器創(chuàng)建或撤消時(shí)進(jìn)行更新。當(dāng)創(chuàng)建了一個(gè)觸發(fā)器時(shí)，其源程序代碼存儲(chǔ)在數(shù)據(jù)庫(kù)視圖USER_TRIGGERS中。該視圖包括觸發(fā)器體、WHEN子句、觸發(fā)表和觸發(fā)器類型。

例如，下面的查詢返回有關(guān)

BIUD_EMPLOYEE_COPY的信息。

2．刪除觸發(fā)器

與過程和包類似，觸發(fā)器也可以被刪除。實(shí)現(xiàn)刪除功能的命令如下：

DROPTRIGGERtriggername;

其中，triggername是觸發(fā)器的名稱。該命令可把指定的觸發(fā)器從數(shù)據(jù)字典中永久性地刪除。類似于子程序，子句ORREPLACE可用在觸發(fā)器的

CREATE語句中。在這種情況下，如果要?jiǎng)?chuàng)建的觸發(fā)器已存在，則先將其刪除。

3．啟用和禁止觸發(fā)器

與過程和包不同的是，觸發(fā)器可以被禁止使用。在數(shù)據(jù)維護(hù)或初始化過程中，特別是當(dāng)大批量數(shù)據(jù)導(dǎo)入時(shí)，并不需要觸發(fā)器語句體的執(zhí)行，也不需要?jiǎng)h除觸發(fā)器，待數(shù)據(jù)維護(hù)或初始化過程完成后，繼續(xù)使觸發(fā)器生效。對(duì)此，可通過改變觸發(fā)器的狀態(tài)啟用或禁止觸發(fā)器命令(ENABLE或DISABLE)來完成。當(dāng)觸發(fā)器被禁止時(shí)，它仍存儲(chǔ)在數(shù)據(jù)字典中，但不再激活。禁止觸發(fā)器的語句如下：

ALTERTRIGGERtriggername{DISABLE|ENABLE};

其中，triggername是觸發(fā)器的名稱。當(dāng)創(chuàng)建觸發(fā)器時(shí)，所有觸發(fā)器的默認(rèn)值都是允許狀態(tài)(ENABLED)。語句ALTERTRIGGER可以禁止或再啟用任何觸發(fā)器。

【例8.12】

下面的代碼先禁止再允許激活觸發(fā)器

BIUD_EMPLOYEE_COPY。

SQL>ALTERTRIGGERBIUD_EMPLOYEE_COPYDISABLE;

Triggeraltered.

SQL>ALTERTRIGGERBIUD_EMPLOYEE_COPYENABLE;

Triggeraltered.

在使用命令A(yù)LTERTABLE的同時(shí)加入ENABLEALLTRIGGERS或DISABLEALLtriggers子句可以將指定表的所有觸發(fā)器禁止或允許。

【例8.13】

指定students表的所有觸發(fā)器為禁止或允許狀態(tài)。

SQL>ALTERTABLEstudentsENABLEALLTRIGGERS;

Tablealtered.

SQL>ALTERTABLEstudentsDISABLEALLTRIGGERS;

Tablealtered.

視圖user_triggers的status列包括ENABLED或DISABLED兩個(gè)字符串，用來指示觸發(fā)器的當(dāng)前狀態(tài)。禁止一個(gè)觸發(fā)器將不從其數(shù)據(jù)字典中刪除。

注意：在觸發(fā)器中不能使用commit/rollback，因?yàn)閐dl語句具有隱式的commit，所以ddl語句也不允許使用。

8.5小

結(jié)

觸發(fā)器是當(dāng)滿足特定事件時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過程。觸發(fā)器由觸發(fā)器名稱、觸發(fā)語句、觸發(fā)器限制和觸發(fā)操作幾部分組成。

按照觸發(fā)事件和觸發(fā)對(duì)象的不同，觸發(fā)器一般分為以下幾種：

DML觸發(fā)器、INSTEADOF觸發(fā)器、DDL觸發(fā)器和系統(tǒng)觸發(fā)器。DML觸發(fā)器是使用最多的觸發(fā)器。INSTEADOF觸發(fā)器定義在視圖上。替代觸發(fā)器是行觸發(fā)器。DDL觸發(fā)器是指在執(zhí)行DDL操作時(shí)激發(fā)的觸發(fā)器，這種觸發(fā)器主要用來防止DDL操作引起的破壞或提供相應(yīng)的安全監(jiān)控。系統(tǒng)觸發(fā)器在當(dāng)發(fā)生數(shù)據(jù)庫(kù)事件(如服務(wù)器的啟動(dòng)或關(guān)閉，用戶的登錄或退出)以及服務(wù)器錯(cuò)誤時(shí)觸發(fā)。

習(xí)題八

一、選擇題

1.下列有關(guān)觸發(fā)器和存儲(chǔ)過程的描述，正確的是()。

A．兩者都可以傳遞參數(shù)

B．兩者都可以被其他程序調(diào)用

C．兩種模塊中都可以包含數(shù)據(jù)庫(kù)事務(wù)語言

D．創(chuàng)建的系統(tǒng)權(quán)限不同

2.下列事件屬于DDL事件的是()。

A．INSERT B．LOGON

C．DROP D．SERVERERROR

3．假定在一個(gè)表上同時(shí)定義了行級(jí)和語句觸發(fā)器，在一次觸發(fā)當(dāng)中，下列說法正確的是()。

A．語句觸發(fā)器只執(zhí)行一次

B．語句觸發(fā)器先行于行級(jí)觸發(fā)器執(zhí)行

C．行級(jí)觸發(fā)器先于語句觸發(fā)器執(zhí)行

D．行級(jí)觸發(fā)器對(duì)表的每一行都會(huì)執(zhí)行一次

4．有關(guān)行級(jí)觸發(fā)器的偽記錄，下列說法正確的是()。

A.INSERT事件觸發(fā)器中，可以使用:old偽記錄

B.DELETE事件觸發(fā)器中，可以使用:new偽記錄

C.UPDATE事件觸發(fā)器中，可以使用:new偽記錄

D.UPDATE事件觸發(fā)器中，可以使用:old偽記錄

5.()觸發(fā)器允許觸發(fā)操作中的語句訪問行的值。

A．行級(jí)

B．語句級(jí)

C．模式

D．?dāng)?shù)據(jù)庫(kù)級(jí)

6.下列有關(guān)替代觸發(fā)器的描述，正確的是()。

A.替代觸發(fā)器創(chuàng)建在表上

B.替代觸發(fā)器創(chuàng)建在數(shù)據(jù)庫(kù)上

C.通過替代觸發(fā)器可以向基表插入數(shù)據(jù)

D.通過替代觸發(fā)器可以向視圖插入數(shù)據(jù)

7.要審計(jì)用戶執(zhí)行的CREATE、DROP和ALTER等DDL語句，應(yīng)該創(chuàng)建()觸發(fā)器。

A．行級(jí)

B．語句級(jí)

C．INSTEADOF

D．模式

E．?dāng)?shù)據(jù)庫(kù)級(jí)

二、

簡(jiǎn)答題

1．創(chuàng)建一個(gè)觸發(fā)器，無論用戶插入新記錄，還是修改EMP表的JOB列，都將用戶指定的JOB列的值轉(zhuǎn)換成大寫。

2．創(chuàng)建一個(gè)觸發(fā)器，禁止用戶刪除DEPT表中的記錄。(提示：創(chuàng)建語句級(jí)觸發(fā)器。)

3．創(chuàng)建一個(gè)emp表的觸發(fā)器emp--_total，每次向雇員表插入、刪除或更新雇員信息時(shí)，將新的統(tǒng)計(jì)信息存入統(tǒng)計(jì)表emptotal中，使統(tǒng)計(jì)表總能反映最新的統(tǒng)計(jì)信息。

統(tǒng)計(jì)表是記錄各部門雇員總?cè)藬?shù)、總工資的統(tǒng)計(jì)表，結(jié)構(gòu)如下：

部門編號(hào)

number(2)，

總?cè)藬?shù)

number(5)，

總工資

number(10,2)

上機(jī)實(shí)驗(yàn)八

實(shí)驗(yàn)1語句級(jí)觸發(fā)器

目的和要求：

1．掌握語句級(jí)觸發(fā)器的原理。

2．掌握語句級(jí)觸發(fā)器的編寫方法。

3．測(cè)試語句級(jí)觸發(fā)器是否生效。

實(shí)驗(yàn)內(nèi)容：

1．創(chuàng)建語句級(jí)觸發(fā)器，需要對(duì)teacher用戶的foo表上進(jìn)行DML操作的用戶進(jìn)行安全檢查。如果不是teacher用戶，則不能夠做增、刪、改的動(dòng)作。

(1)連接teacher用戶，建表。

SQL>createtablefoo(anumber);

(2)建立觸發(fā)器。

SQL>createtriggerbiud_foo

beforeinsertorupdateordeleteonfoo

begin

ifusernotin('teacher')then

raise_application_error(-20001,'youdon'thaveaccesstomodifythistable.');

endif;

end;

/

(3)測(cè)試觸發(fā)器。即使sys、system用戶也不能修改foo表。

2．創(chuàng)建語句級(jí)觸發(fā)器，需要對(duì)scott用戶的emp表上進(jìn)行DML操作的用戶進(jìn)行安全檢查。如果不是scott用戶，則不能夠做增、刪、改的動(dòng)作。

實(shí)驗(yàn)2行級(jí)觸發(fā)器

目的和要求：

1．掌握行級(jí)觸發(fā)器的原理。

2．掌握行級(jí)觸發(fā)器的編寫方法。

3．測(cè)試行級(jí)觸發(fā)器是否生效。

實(shí)驗(yàn)內(nèi)容：

1．創(chuàng)建行級(jí)觸發(fā)器，對(duì)SCOTT用戶的EMP表插入數(shù)據(jù)。當(dāng)DEPTNO<>30時(shí)，將COMM值置為0。

步驟提示：

(1)建立觸發(fā)器。

(2)測(cè)試觸發(fā)器。

插入deptno<>30和

deptno=30的數(shù)據(jù)，進(jìn)行查看測(cè)試。

實(shí)驗(yàn)3替代觸發(fā)器

目的和要求：

1．掌握替代觸發(fā)器的原理。

2．創(chuàng)建DDL觸發(fā)器。

3．替代觸發(fā)器的測(cè)試方法。

實(shí)驗(yàn)內(nèi)容：

1．創(chuàng)建一個(gè)視圖view_emp_dept，數(shù)據(jù)來源于emp表的字段empno、ename、job、emp.deptno，條件是emp.deptno=dept.deptno。然后對(duì)視圖view_emp_dept進(jìn)行插入數(shù)據(jù)操作。

(1)創(chuàng)建視圖。

SQL>createorreplaceviewview_emp_deptasselectempno,ename,job,emp.deptnodepno

fromemp,deptwhereemp.deptno=dept.deptno

(2)對(duì)視圖進(jìn)行插入操作。

insertintoview_emp_deptvalues(7805,'david1','CLERK',50);