第8章

觸

發

器

8.1觸發器簡介

8.2觸發器的格式

8.3觸發器的種類

8.4管理觸發器

8.5小結

習題八

上機實驗八

8.1觸發器簡介

觸發器是一種特殊的存儲過程，當特定對象上的特定事件出現時，將自動觸發執行的代碼塊。觸發器比數據庫有更精細和更復雜的數據控制能力。觸發器與過程的區別在于：過程要由用戶或應用程序顯式調用，而觸發器是滿足特定事件時在數據庫后臺自動執行。數據庫觸發器具有以下功能：

(1)實現復雜的數據完整性規則。

(2)自動生成派生數據。

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

(4)啟用復雜的業務邏輯。

(5)實施更復雜的安全性檢查。

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

8.2觸發器的格式

所有的觸發器，不管其類型如何，都可以使用相同的語法創建。下面先簡單了解一下Oracle產生數據庫觸發器的基本語法：

create[orreplace]trigger觸發器名

觸發時間

觸發事件

on對象名

[foreachrow]

pl/sql

語句

說明：觸發器名：觸發器對象的名稱。由于觸發器是數據庫自動執行的，因此該名稱只是一個名稱，沒有實質的用途。

觸發時間：指明觸發器何時執行，取值有before和after。

before表示在數據庫動作之前觸發器執行；

after表示在數據庫動作之后觸發器執行。

觸發事件：指明哪些數據庫動作會觸發此觸發器，比如，

insert表示數據庫插入會觸發此觸發器；

update表示數據庫修改會觸發此觸發器；

delete表示數據庫刪除會觸發此觸發器。

對象名：數據庫觸發器所在的表名、數據庫名或模式用戶名。

foreachrow：對表的每一行觸發器執行一次。如果沒有這一選項，則只對整個表執行一次。

【例8.1】

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

SQL>createtriggerstudent_secure

2

beforeinsertorupdateordelete

--對整表在插入、更新、刪除前觸發

3

onstudent

4

begin

5

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

6

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

7

endif;

8

end；

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

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行指明創建的觸發器名稱，第2～6行為觸發器語句，第7行為觸發器限制，第8行為觸發器操作。

說明：

trigger_name是觸發器的名稱。

觸發器語句是指在相應數據庫對象上觸發的時間及導致觸發器執行的事件等。tiggering_event表示觸發事件，比如表或視圖上的dml語句、ddl語句、數據庫關閉或啟動等。其中，dml_event_list是一個或多個DML事件，包括INSERT、UPDATE、DELETE語句，事件之間用“OR”分隔；ddl_event_list是一個或多個DDL事件，包括CREATE、ALTER或DROP語句；database_event_list是一個或多個數據庫事件，包括服務器的啟動或關閉、用戶的登錄或退出以及服務器錯誤等。referencing_clause用來引用正在處于修改狀態下的行中的數據。

WHEN子句代表觸發器限制條件，包含一個布爾表達式，即在

WHEN子句中如果指定trigger_condition，則首先對該條件求值。只有在該條件為真值時才運行。

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

【例8.2】

在employees表上構建一個觸發器，在插入或修改部門號時觸發，如果該雇員部門號不是“80”，則commission_pct列值置為0。(注：這里使用的為HR用戶下的employees表。缺省狀態下，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;

/

觸發器已創建。

在這個例子中，觸發器名稱是第1行biufer_employees_department_id，第2行至第6行構成了觸發器語句。在這個例子中，無論是否規定了department_id，對employees表進行insert或對employees表的department_id列進行update時，觸發器都會在每一受影響的行上執行一次。第7行為觸發器限制，限制不是必需的。此例表示如果列department_id不等于80，則觸發器就會執行。其中的new_value代表更新之后的值。第8行至第10行構成了觸發器的主體。本例中，主體很簡單，就是將更新后的commission_pct列置為0。

下面做一個觸發動作，以測試觸發器是否有效。

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

通過查看下面語句查看結果。

SQL>selectcommission_pctfromemployeeswhereemployee_id=12345;

COMMISSION_PCT

----------

0

查詢結果說明觸發器生效，觸發器已經自動改變了用戶的輸入值。

8.3觸發器的種類

8.3.1DML觸發器

在實際應用中，DML觸發器是使用最多的觸發器。DML觸發器可以由

DML語句激發，并且由該語句的類型決定

DML觸發器的類型。可以定義DML觸發器進行INSERT、UPDATE、DELETE操作。這類觸發器可以在上述操作之前或之后激發，也可以按每個變更行激發一次，或每個語句激發一次。這些條件的組合形成了觸發器的類型，3種語句×2種定時×2種級別總共有

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

DML觸發器類型。

表8-1DML觸發器

1．觸發器時機

在DML觸發器中，根據其觸發時機的不同(觸發時機可分為：BEFORE和AFTER)，觸發器可分為兩類：BEFORE觸發器和AFTER觸發器。它們在觸發過程中各自執行的順序不同。DML觸發器觸發時機及執行順序是：BEFORE觸發器、約束檢查、更新表和AFTER觸發器。

BEFORE觸發器在約束之前執行，通常用于：

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

(2)檢查復雜的安全規則，如限制時間等。

(3)增強商業應用規則。

(4)通過觸發器的邏輯潛在地引發一個異常來拒絕觸發語句，這是相當有效的，因為觸發器是在約束之前執行的。

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

(1)用戶信息的審計。

(2)導出數據的生成。如果導出數據存儲在其他表中，而不是觸發器所依賴的表，則使用AFTER；如果導出數據存儲在當前觸發器依賴的表中，則觸發器必須定義成BEFORE觸發器。

(3)遠程數據的復制。

2．語句級觸發器和行級觸發器

根據觸發器所依賴的表對象不同，可將DML觸發器進一步分為語句級(statement)和行級(row)觸發器。這兩類觸發器指定了觸發器語句執行的頻率。若創建觸發器的語句中添加了子句foreachrow，則為行級觸發器，否則為語句級觸發器。默認是語句級觸發器。

語句級觸發器是在表或者視圖上執行的特定語句(或者語句組)的觸發器，能夠與INSERT、UPDATE、DELETE及其組合進行關聯。無論使用什么樣的組合，各個語句觸發器都只針對指定語句激活一次。比如，無論UPDATE有多少行，都只會調用一次UPDATE語句觸發器。

【例8.3】

創建一個語句級觸發器，以對修改表的時間、人員進行日志記錄。

(1)建立實驗表。

SQL>createtableemployees_copyasselect*fromhr.employees;

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

(2)建立日志表。

SQL>createtableemployees_log(

whovarchar2(30),

whendate);

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

SQL>createorreplacetriggerbiud_employee_copy

2beforeinsertorUPDATEorDELETE

3onemployees_copy

4begin

5INSERTintoemployees_log(who,when)

6

values(user,sysdate);

7end;

/

(4)測試。

SQL>UPDATEemployees_copysetsalary=salary*1.1;

SQL>select*fromemployees_log;

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

行級觸發器是指被受到影響的各個行激活的觸發器，即每行變動一次就觸發一次。在觸發器內部，我們可以訪問正在處理的行的數據。這種訪問是通過兩個相關的標識符(:?old和

:?new)實現的。

:old和

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

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

表8-2:old和

:new相關標識符

【例8.4】

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

【例8.5】

創建一行級觸發器，為主鍵生成自增序列號。

(1)創建一個實驗表和一個序列。

SQL>droptablefoo;

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

SQL>createsequencefoo_seq;

(2)創建觸發器。

SQL>createorreplacetriggerbifer_foo_id_pk

beforeINSERTonfoo

foreachrow

begin

selectfoo_seq.nextvalinto:new.idfromdual;

end;

(3)插入數據進行測試。

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

SQL>INSERTintofoovalues(5,'chen');

(4)查詢結果，測試觸發器是否生效。

SQL>select*fromfoo;

3．DML觸發器WHEN子句

WHEN子句只適用于行級觸發器。如果使用該子句，則觸發器體將只對滿足WHEN子句說明條件的行執行。WHEN子句的語法如下：

WHENtrigger_condition

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

:?old記錄可以在trigger_condition內部引用，但不需使用冒號，該冒號只在觸發器體內有效。

【例8.6】

觸發器CheckCredits只在當前學生得到的學分超出

20時才運行。

SQL>CREATEORREPLACETRIGGERCheckCredits

BEFOREINSERTORUPDATEOFcurrent_creditsONstudents

FOREACHROW

WHEN(new.current_credits>20)

BEGIN

/*Triggerbodygoeshere.*/

END;上述觸發器CheckCredits的實現也可寫為下列代碼：

SQL>CREATEORREPLACETRIGGERCheckCredits

BEFOREINSERTORUPDATEOFcurrent_creditsONstudents

FOREACHROW

BEGIN

IF:new.current_credits>20THEN

/*Triggerbodygoeshere.*/

ENDIF;

END;

4．觸發器謂詞：INSERTING、UPDATING和DELETING

這種觸發器的內部(為不同的DML語句激發的觸發器)有三個可用來確認執行何種操作的邏輯表達式。這些表達式的謂詞是

INSERTING、UPDATING和DELETING。表8-3給出了表達式謂詞與對應執行DML語句的屬性值。

表8-3表達式謂詞與對應執行DML語句的屬性值

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

SQL>Begin

ifINSERTINGthen

elsifUPDATINGthen

elsifDELETINGthen

endif;

end;………

或者修改某一列數據，例如：

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

endif;

【例8.7】

建立一觸發器，用于審計對employees_copy表所作的操作行為。

(1)修改日志表。

SQL>altertableemployees_log

add(actionvarchar2(20));…

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

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)測試。為了測試本觸發器的效果，首先刪掉記錄日志表employees_log中的數據。

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;

然后執行更新操作，查看觸發器的效果。

SQL>UPDATEemployees_copysetsalary=50000whereemployee_id=666;

SQL>select*fromemployees_log;8.3.2INSTEADOF觸發器

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

假如有一個視圖是基于多個表的字段連接查詢得到的，現在如果想直接對這個視圖進行插入操作，那么對視圖的插入操作如何來反映到組成這個視圖的各個表中呢？事實上，除了定義一個觸發器來綁定對視圖的插入動作外，沒有別的辦法通過系統的報錯而直接向視圖中插入數據，這就是用替代觸發器的原因。替換的意思實際上是觸發器的主體部分把對視圖的插入操作轉換成詳細的對各個表的插入。

例如，直接執行對該視圖的插入操作是非法的。這是因為該視圖是兩個表的聯合，而插入操作要求對兩個現行表進行修改。下面的

SQL*Plus會話顯示了插入操作過程。

【例8.8】

演示INSTEADOF觸發器的應用案例。

(1)創建一個視圖company_phone_book，其中，name列的定義來自hr.employees表中兩個字段的聯合。

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;

此時出現如下的錯誤提示：

ERROR位于第

2行:

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

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

SQL>updatecompany_phone_book

setname='chen1,donny1'

whereemp_id=100;

已更新1行。

8.3.3DDL觸發器

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

表8-4DDL事件的種類及其出現時機

系統觸發器有幾個內部的屬性函數可供使用。這些參數允許觸發器體獲得有關觸發事件的信息。表

8-5對這些事件屬性函數做了說明。與觸發器參數不同，事件屬性函數是

SYS擁有的獨立

PL/SQL函數。系統沒有為這些函數指定默認的替代名稱，所以為了識別這些函數，程序中必須在它們的前面加上前綴SYS。

表

8-5DDL事件用到的屬性函數

【例8.9】

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

(1)建立一個日志表。

SQL>connectscott/tiger;

已連接。

SQL>createtabledroped_objects(

object_namevarchar2(30),

object_typevarchar2(30),

dropped_ondate);

表已創建。

(2)創建觸發器。

SQL>createorreplacetriggerlog_drop_trigger

beforedroponscott.schema

begin

insertintodroped_objectsvalues(

sys.dictionary_obj_name,

--與觸發器相關的函數

sys.dictionary_obj_type,

sysdate);

end;

/

觸發器已創建。

(3)進行測試。用如下命令創建一個表drop_me，創建一個視圖drop_me_view，然后將這兩個對象刪除。

SQL>createtabledrop_me(anumber);

表已創建。

SQL>createviewdrop_me_viewasselect*fromdrop_me;

視圖已建立。

SQL>dropviewdrop_me_view;

8.3.4系統觸發器

系統觸發器在發生如數據庫啟動或關閉等系統事件時激發，而不是在執行

DML語句時激發。數據庫事件包括服務器的啟動或關閉、用戶的登錄或退出以及服務器錯誤。創建系統觸發器的語法如下：

CREATE[ORREPLACE]TRIGGER[schema.]trigger_name

{BEFORE|AFTER}

{ddl_event_list|database_event_list}

ON{DATABASE|[schema.]SCHEMA}

[when_clause]

表8-6數據庫事件的種類及出現時機

【例8.10】

創建當數據庫啟動時的系統觸發器。

SQL>createtriggerad_startup

afterstartup

ondatabase

begin

--dosomestuff--比如可以進行數據的初始化工作，記錄數據庫的啟動時間等。

end;

系統觸發器也有一些內部的屬性函數可供使用。這些參數允許觸發器體獲得有關觸發事件的信息。表8-7對這些事件屬性函數做了說明。

在本章的開始部分介紹的觸發器

LogCreations中使用了這些屬性函數。與觸發器參數不同，事件屬性函數是

SYS擁有的獨立

PL/SQL函數。系統沒有為這些函數指定默認的替代名稱，所以為了識別這些函數，程序中必須在它們的前面加上前綴

SYS。

表

8-7系統觸發器的屬性函數

【例8.11】

創建系統觸發器，記錄本次啟動數據庫以來所有登錄的用戶。

(1)創建一個用戶登錄的日志記錄表，包含登錄用戶名、登錄時間、數據庫名字和實例號。

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

varchar2(20),instance_numbernumber);

表已創建。

(2)授予創建觸發器的用戶administerdatabasetrigger權限。

SQL>connsystem/manager;

已連接。

SQL>grantadministerdatabasetriggertoscott;

授權成功。

(3)創建系統啟動的觸發器，根據題目要求，記錄本次啟動數據庫以來登錄的用戶日志，因此該觸發器在啟動時，清空以往的用戶日志表。

SQL>createorreplacetriggerinit_logonafterstartupondatabase

2begin

3deletefromuserlog;

4end;

5/

觸發器已創建。

(4)創建登錄系統的觸發器，用于記錄用戶登錄日志。

SQL>createorreplacetriggerdatabase_logon

2after

3logon

4ondatabase

5begin

6insertintouserlog

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

8end;

9/

觸發器已創建。

(5)測試。

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

SQL>connecthr/hr;

已連接。

SQL>connectscott/tiger;

已連接。

8.4管

理

觸

發

器

1．利用數據字典視圖查看觸發器的有關信息

與存儲子程序類似，數據字典視圖包括有關觸發器及其執行狀態的信息。這些視圖必須在觸發器創建或撤消時進行更新。當創建了一個觸發器時，其源程序代碼存儲在數據庫視圖USER_TRIGGERS中。該視圖包括觸發器體、WHEN子句、觸發表和觸發器類型。

例如，下面的查詢返回有關

BIUD_EMPLOYEE_COPY的信息。

2．刪除觸發器

與過程和包類似，觸發器也可以被刪除。實現刪除功能的命令如下：

DROPTRIGGERtriggername;

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

CREATE語句中。在這種情況下，如果要創建的觸發器已存在，則先將其刪除。

3．啟用和禁止觸發器

與過程和包不同的是，觸發器可以被禁止使用。在數據維護或初始化過程中，特別是當大批量數據導入時，并不需要觸發器語句體的執行，也不需要刪除觸發器，待數據維護或初始化過程完成后，繼續使觸發器生效。對此，可通過改變觸發器的狀態啟用或禁止觸發器命令(ENABLE或DISABLE)來完成。當觸發器被禁止時，它仍存儲在數據字典中，但不再激活。禁止觸發器的語句如下：

ALTERTRIGGERtriggername{DISABLE|ENABLE};

其中，triggername是觸發器的名稱。當創建觸發器時，所有觸發器的默認值都是允許狀態(ENABLED)。語句ALTERTRIGGER可以禁止或再啟用任何觸發器。

【例8.12】

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

BIUD_EMPLOYEE_COPY。

SQL>ALTERTRIGGERBIUD_EMPLOYEE_COPYDISABLE;

Triggeraltered.

SQL>ALTERTRIGGERBIUD_EMPLOYEE_COPYENABLE;

Triggeraltered.

在使用命令ALTERTABLE的同時加入ENABLEALLTRIGGERS或DISABLEALLtriggers子句可以將指定表的所有觸發器禁止或允許。

【例8.13】

指定students表的所有觸發器為禁止或允許狀態。

SQL>ALTERTABLEstudentsENABLEALLTRIGGERS;

Tablealtered.

SQL>ALTERTABLEstudentsDISABLEALLTRIGGERS;

Tablealtered.

視圖user_triggers的status列包括ENABLED或DISABLED兩個字符串，用來指示觸發器的當前狀態。禁止一個觸發器將不從其數據字典中刪除。

注意：在觸發器中不能使用commit/rollback，因為ddl語句具有隱式的commit，所以ddl語句也不允許使用。

8.5小

結

觸發器是當滿足特定事件時自動執行的存儲過程。觸發器由觸發器名稱、觸發語句、觸發器限制和觸發操作幾部分組成。

按照觸發事件和觸發對象的不同，觸發器一般分為以下幾種：

DML觸發器、INSTEADOF觸發器、DDL觸發器和系統觸發器。DML觸發器是使用最多的觸發器。INSTEADOF觸發器定義在視圖上。替代觸發器是行觸發器。DDL觸發器是指在執行DDL操作時激發的觸發器，這種觸發器主要用來防止DDL操作引起的破壞或提供相應的安全監控。系統觸發器在當發生數據庫事件(如服務器的啟動或關閉，用戶的登錄或退出)以及服務器錯誤時觸發。

習題八

一、選擇題

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

A．兩者都可以傳遞參數

B．兩者都可以被其他程序調用

C．兩種模塊中都可以包含數據庫事務語言

D．創建的系統權限不同

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

A．INSERT B．LOGON

C．DROP D．SERVERERROR

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

A．語句觸發器只執行一次

B．語句觸發器先行于行級觸發器執行

C．行級觸發器先于語句觸發器執行

D．行級觸發器對表的每一行都會執行一次

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

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

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

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

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

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

A．行級

B．語句級

C．模式

D．數據庫級

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

A.替代觸發器創建在表上

B.替代觸發器創建在數據庫上

C.通過替代觸發器可以向基表插入數據

D.通過替代觸發器可以向視圖插入數據

7.要審計用戶執行的CREATE、DROP和ALTER等DDL語句，應該創建()觸發器。

A．行級

B．語句級

C．INSTEADOF

D．模式

E．數據庫級

二、

簡答題

1．創建一個觸發器，無論用戶插入新記錄，還是修改EMP表的JOB列，都將用戶指定的JOB列的值轉換成大寫。

2．創建一個觸發器，禁止用戶刪除DEPT表中的記錄。(提示：創建語句級觸發器。)

3．創建一個emp表的觸發器emp--_total，每次向雇員表插入、刪除或更新雇員信息時，將新的統計信息存入統計表emptotal中，使統計表總能反映最新的統計信息。

統計表是記錄各部門雇員總人數、總工資的統計表，結構如下：

部門編號

number(2)，

總人數

number(5)，

總工資

number(10,2)

上機實驗八

實驗1語句級觸發器

目的和要求：

1．掌握語句級觸發器的原理。

2．掌握語句級觸發器的編寫方法。

3．測試語句級觸發器是否生效。

實驗內容：

1．創建語句級觸發器，需要對teacher用戶的foo表上進行DML操作的用戶進行安全檢查。如果不是teacher用戶，則不能夠做增、刪、改的動作。

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

SQL>createtablefoo(anumber);

(2)建立觸發器。

SQL>createtriggerbiud_foo

beforeinsertorupdateordeleteonfoo

begin

ifusernotin('teacher')then

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

endif;

end;

/

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

2．創建語句級觸發器，需要對scott用戶的emp表上進行DML操作的用戶進行安全檢查。如果不是scott用戶，則不能夠做增、刪、改的動作。

實驗2行級觸發器

目的和要求：

1．掌握行級觸發器的原理。

2．掌握行級觸發器的編寫方法。

3．測試行級觸發器是否生效。

實驗內容：

1．創建行級觸發器，對SCOTT用戶的EMP表插入數據。當DEPTNO<>30時，將COMM值置為0。

步驟提示：

(1)建立觸發器。

(2)測試觸發器。

插入deptno<>30和

deptno=30的數據，進行查看測試。

實驗3替代觸發器

目的和要求：

1．掌握替代觸發器的原理。

2．創建DDL觸發器。

3．替代觸發器的測試方法。

實驗內容：

1．創建一個視圖view_emp_dept，數據來源于emp表的字段empno、ename、job、emp.deptno，條件是emp.deptno=dept.deptno。然后對視圖view_emp_dept進行插入數據操作。

(1)創建視圖。

SQL>createorreplaceviewview_emp_deptasselectempno,ename,job,emp.deptnodepno

fromemp,deptwhereemp.deptno=dept.deptno

(2)對視圖進行插入操作。

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