Oracle第章觸發(fā)器操作

第9章觸發(fā)器操作主編：劉志成本章學(xué)習(xí)導(dǎo)航本章學(xué)習(xí)導(dǎo)航本章學(xué)習(xí)要點(diǎn)（1）觸發(fā)器的基礎(chǔ)知識(shí)。（2）:OLD和:NEW變量。（3）SQLDeveloper創(chuàng)建、修改、查看、編譯和刪除觸發(fā)器。（4）PL/SQL創(chuàng)建、修改、查看、編譯和刪除觸發(fā)器。（5）觸發(fā)器的功能及使用。（6）eBuy電子商城中的典型觸發(fā)器

建議課時(shí)：8課時(shí)9.1觸發(fā)器概述

觸發(fā)器簡(jiǎn)介觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，它與數(shù)據(jù)表緊密聯(lián)系，用于保護(hù)表中的數(shù)據(jù)，當(dāng)一個(gè)定義了特定類型觸發(fā)器的基表執(zhí)行插入、修改或刪除表中數(shù)據(jù)的操作時(shí)，將自動(dòng)觸發(fā)觸發(fā)器中定義的操作，以實(shí)現(xiàn)數(shù)據(jù)的一致性和完整性。觸發(fā)器擁有比數(shù)據(jù)庫(kù)本身標(biāo)準(zhǔn)的功能更精細(xì)和更復(fù)雜的數(shù)據(jù)控制能力。

觸發(fā)器具有以下的作用：（1）在安全性方面，觸發(fā)器可以基于數(shù)據(jù)庫(kù)的值使用戶具有操作數(shù)據(jù)庫(kù)的某種權(quán)利。

（2）在審計(jì)方面，觸發(fā)器可以跟蹤用戶對(duì)數(shù)據(jù)庫(kù)的操作。

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

（4）實(shí)現(xiàn)復(fù)雜的非標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)相關(guān)完整性規(guī)則。觸發(fā)器可以對(duì)數(shù)據(jù)庫(kù)中相關(guān)的表進(jìn)行連環(huán)更新。

（5）同步實(shí)時(shí)地復(fù)制表中的數(shù)據(jù)。（6）自動(dòng)計(jì)算數(shù)據(jù)值，如果數(shù)據(jù)的值達(dá)到了一定的要求，則進(jìn)行特定的處理。例如，如果商品的數(shù)量低于5，則立即給管理人員發(fā)送庫(kù)存報(bào)警信息。9.1觸發(fā)器概述

觸發(fā)器類型1．觸發(fā)器的功能（1）DML觸發(fā)器當(dāng)對(duì)表進(jìn)行DML操作時(shí)觸發(fā)，可以在DML操作前或操作后觸發(fā)。（2）替代觸發(fā)器是Oracle用來(lái)替換所使用的實(shí)際語(yǔ)句而執(zhí)行的觸發(fā)器。（3）系統(tǒng)觸發(fā)器在Oracle數(shù)據(jù)庫(kù)系統(tǒng)的事件（Oracle系統(tǒng)的啟動(dòng)與關(guān)閉等）中進(jìn)行觸發(fā)。（4）用戶事件觸發(fā)器指與數(shù)據(jù)庫(kù)定義語(yǔ)句或用戶的登錄/注銷等事件相關(guān)的觸發(fā)器。2．觸發(fā)事件按觸發(fā)事件的不同，觸發(fā)器可以分為插入型（INSERT）、更新型（UPDATE）和刪除型（DELETE）觸發(fā)器。對(duì)于插入型觸發(fā)器，當(dāng)觸發(fā)器所在的表發(fā)生插入操作時(shí)，觸發(fā)器將自動(dòng)觸發(fā)執(zhí)行。對(duì)于更新型觸發(fā)器，當(dāng)觸發(fā)器所在的表發(fā)生更新操作時(shí)，觸發(fā)器將自動(dòng)觸發(fā)執(zhí)行。同樣，對(duì)于刪除型觸發(fā)器，當(dāng)觸發(fā)器所在的表發(fā)生刪除操作時(shí)，觸發(fā)器也將自動(dòng)觸發(fā)執(zhí)行。

9.1觸發(fā)器概述

觸發(fā)器類型3．觸發(fā)時(shí)間根據(jù)指定的事件和觸發(fā)器執(zhí)行的先后次序，觸發(fā)器可以分為BEFORE型和AFTER型觸發(fā)器。如果在指定的事件（INSERT、UPDATE或者DELETE）之前執(zhí)行觸發(fā)器，這類觸發(fā)器稱為BEFORE觸發(fā)器；若在指定的事件之后執(zhí)行觸發(fā)器，則稱這類觸發(fā)器為AFTER觸發(fā)器。4．觸發(fā)級(jí)別根據(jù)觸發(fā)級(jí)別的不同，觸發(fā)器可以分為行觸發(fā)器和語(yǔ)句觸發(fā)器。對(duì)于行觸發(fā)器，受觸發(fā)事件影響的每一行都將引發(fā)觸發(fā)器的執(zhí)行；而對(duì)于語(yǔ)句觸發(fā)器，觸發(fā)事件只觸發(fā)一次，即使有若干行受觸發(fā)事件的影響，也只執(zhí)行一次觸發(fā)操作。

9.1觸發(fā)器概述

:OLD變量和:NEW變量在Oracle系統(tǒng)中，每個(gè)觸發(fā)器被DML操作觸發(fā)時(shí)，會(huì)產(chǎn)生兩個(gè)特殊的變量:OLD和:NEW，分別代表某數(shù)據(jù)記錄行在修改前和修改后的值。這兩個(gè)變量都是系統(tǒng)變量，由Oracle系統(tǒng)管理，存儲(chǔ)在內(nèi)存中，不允許用戶直接對(duì)其進(jìn)行修改。:OLD和:NEW變量的結(jié)構(gòu)總是與執(zhí)行DML操作的表的結(jié)構(gòu)相同。當(dāng)觸發(fā)器工作完成以后，這兩個(gè)變量也隨之消失。這兩個(gè)變量的值是只讀的，即用戶不能向這兩個(gè)變量寫入內(nèi)容，但可以引用變量中的數(shù)據(jù)。:OLD變量用于存儲(chǔ)DELETE和UPDATE操作所影響的行的副本。當(dāng)執(zhí)行DELETE或UPDATE操作時(shí)，行從觸發(fā)表中被刪除，并傳輸?shù)?OLD變量中。:NEW變量用于存儲(chǔ)INSERT和UPDATE操作所影響的行的副本。當(dāng)執(zhí)行INSERT或UPDATE操作時(shí)，新行被同時(shí)添加到:NEW變量和觸發(fā)表中，:NEW變量中的行即為觸發(fā)表中新行的副本。

9.1觸發(fā)器概述

:OLD變量和:NEW變量課堂案例1—使用SQLDeveloper管理觸發(fā)器

學(xué)習(xí)使用SQLDeveloper創(chuàng)建觸發(fā)器的方法和基本步驟，掌握觸發(fā)器的執(zhí)行時(shí)機(jī)

。案例學(xué)習(xí)目標(biāo)

SQLDeveloper創(chuàng)建觸發(fā)器、驗(yàn)證觸發(fā)器的作用。案例知識(shí)要點(diǎn)課堂案例1—使用SQLDeveloper管理觸發(fā)器案例完成步驟-創(chuàng)建觸發(fā)器添加標(biāo)題文字1．創(chuàng)建觸發(fā)器

（1）在SQLDeveloper中右擊Triggers選項(xiàng)，從快捷菜單中選擇“NewTrigger”項(xiàng)，將開始創(chuàng)建觸發(fā)器。（2）在打開的“CreateTrigger”對(duì)話框中，輸入TR_ADDUSER作為新建觸發(fā)器的名稱；選擇觸發(fā)器類型（TriggerType）為TABLE，并選擇觸發(fā)表為客戶表USERS。然后選擇觸發(fā)器類別信息為After、語(yǔ)句級(jí)別（StatementLevel）和插入型（Insert）；其余采用默認(rèn)值。教師演示講解課堂案例例1—使用SQLDeveloper管理觸發(fā)發(fā)器案例完成成步驟-創(chuàng)建觸發(fā)發(fā)器添加標(biāo)題題文字（3）觸發(fā)器器設(shè)置完完成后，，單擊““確定””按鈕，，在PL/SQL編輯窗口口中補(bǔ)充充創(chuàng)建觸觸發(fā)器的的PL/SQL代碼。（4）觸發(fā)器器編譯成成功后，，在PL/SQL編輯窗口口中輸入入以下PL/SQL代碼：INSERTINTOUsersVALUES('00','trigger','普通','trigger');（5）按F9鍵執(zhí)行該該P(yáng)L/SQL語(yǔ)句，激激發(fā)觸發(fā)發(fā)器的執(zhí)執(zhí)行教師演示示講解課堂案例例1—使用SQLDeveloper管理觸發(fā)發(fā)器案例完成成步驟-查看觸發(fā)發(fā)器添加標(biāo)題題文字在SQLDeveloper的左邊的的樹型結(jié)結(jié)構(gòu)中，，展開Triggers節(jié)點(diǎn)后選選擇需要要查看的的觸發(fā)器器tr_AddUser，將在右右邊欄內(nèi)內(nèi)顯示tr_AddUser觸發(fā)器的的信息，，如觸發(fā)發(fā)器的定定義語(yǔ)句句和詳細(xì)細(xì)描述等等教師演示示講解課堂案例例1—使用SQLDeveloper管理觸發(fā)發(fā)器案例完成成步驟-修改觸發(fā)發(fā)器添加標(biāo)題題文字（1）在SQLDeveloper中，右鍵鍵單擊Triggers選項(xiàng)中的的觸發(fā)器器tr_AddUser，從快捷捷菜單中中選擇““Edit”項(xiàng)。（2）在打開開的觸發(fā)發(fā)器編輯輯窗口中中，修改改觸發(fā)器器的定義義PL/SQL語(yǔ)句教師演示示講解課堂案例例1—使用SQLDeveloper管理觸發(fā)發(fā)器案例完成成步驟-刪除觸發(fā)發(fā)器添加標(biāo)題題文字（1）在SQLDeveloper中，右擊擊Triggers選項(xiàng)中的的待刪除除觸發(fā)器器tr_AddUser，從快捷捷菜單中中選擇““DropTrigger””項(xiàng)。（2）在打開開的“DropTrigger””對(duì)話框中中，在Prompts（提示））選項(xiàng)卡卡內(nèi)顯示示了待刪刪除觸發(fā)發(fā)器的所所有者和和名稱，，并提示示用戶是是否需要要?jiǎng)h除該該觸發(fā)器器。（3）單擊““應(yīng)用””按鈕，，將打開開Confirmation對(duì)話框，，以提示示用戶該該觸發(fā)器器已經(jīng)被被刪除。。教師演示示講解課堂案例例2—使用PL/SQL管理觸發(fā)發(fā)器學(xué)習(xí)使用用PL/SQL語(yǔ)句中的的CREATETRIGGER語(yǔ)句的用用法。案例學(xué)習(xí)習(xí)目標(biāo)CREATETRIGGER創(chuàng)建觸發(fā)發(fā)器、驗(yàn)驗(yàn)證觸發(fā)發(fā)器的作作用。案例知識(shí)識(shí)要點(diǎn)課堂案例例2—使用PL/SQL管理觸發(fā)發(fā)器案例完成成步驟-創(chuàng)建觸發(fā)發(fā)器添加標(biāo)題題文字1．創(chuàng)建觸觸發(fā)器【例2-1】】為用戶方方案SCOTT的客戶表表CUSTOMERS創(chuàng)建插入入型觸發(fā)發(fā)器，當(dāng)當(dāng)添加新新客戶信信息時(shí)，，顯示““歡迎新新會(huì)員注注冊(cè)！””。INSERTINTOSCOTT.CustomersVALUES('C2002','trigger','觸發(fā)器','女湖南長(zhǎng)沙沙市你出生在在哪里','湖南長(zhǎng)沙沙','普通');教師演示示講解課堂案例例2—使使用PL/SQL管理理觸發(fā)器器案例完成成步驟-創(chuàng)建觸發(fā)發(fā)器添加標(biāo)題題文字【例2-2】】為用戶方方案SCOTT的客戶表表CUSTOMERS創(chuàng)建刪除除型觸發(fā)發(fā)器，當(dāng)當(dāng)刪除會(huì)會(huì)員信息息時(shí)，顯顯示“×××用戶已被被刪除！！”的信信息。CREATEORREPLACETRIGGERtr_DelCUSTBEFOREDELETEONSCOTT.CUSTOMERSFOREACHROWBEGINDBMS_OUTPUT.PUT_LINE('待刪除的的記錄為為:');DBMS_OUTPUT.PUT_LINE(:OLD.c_NAME||'用戶已被刪除除!');ENDtr_DelCUST;DELETECUSTOMERS WHEREc_D='C2002';教師演示講解解課堂案例2—使用PL/SQL管理觸發(fā)器案例完成步驟驟-創(chuàng)建觸發(fā)器添加標(biāo)題文字字【例2-3】實(shí)現(xiàn)eBuy電子商城系統(tǒng)統(tǒng)的日志操作作功能（語(yǔ)句句級(jí)觸發(fā)器））。（1）創(chuàng)建語(yǔ)句級(jí)級(jí)觸發(fā)器的測(cè)測(cè)試表(該語(yǔ)句應(yīng)該在在創(chuàng)建觸發(fā)器器的語(yǔ)句之前前運(yùn)行)CREATETABLESYSLOG(WHOVARCHAR2(30),OPER_DATEDATE);該表用來(lái)保存存對(duì)eBuy系統(tǒng)進(jìn)行操作作的日志。（2）創(chuàng)建語(yǔ)句級(jí)級(jí)觸發(fā)器CREATEORREPLACETRIGGERtr_DeleteUserAFTERDELETEONUsersBEGININSERTINTOSYSLOG(WHO,OPER_DATE)VALUES(USER,SYSDATE);END;教師演示講解解課堂案例2—使用PL/SQL管理觸發(fā)器案例完成步驟驟-創(chuàng)建觸發(fā)器添加標(biāo)題文字字該觸發(fā)器用來(lái)來(lái)實(shí)現(xiàn)在Users表中刪除一條條記錄時(shí)，在在SYSLOG表中添加一條條包含當(dāng)前操操作用戶（USER表示當(dāng)前用戶戶）和當(dāng)前操操作日期（SYSDATE表示當(dāng)前日期期）的記錄。。（3）測(cè)試觸發(fā)器器tr_DeleteUser通過(guò)刪除Users表中的一條記記錄（用戶名名稱為“存儲(chǔ)儲(chǔ)過(guò)程”）來(lái)來(lái)測(cè)試觸發(fā)器器的功能。DELETEFROMUsersWHEREu_Name='存儲(chǔ)過(guò)程';SELECT*FROMSyslog;教師演示講解解課堂案例2—使用PL/SQL管理觸發(fā)器案例完成步驟驟-創(chuàng)建觸發(fā)器添加標(biāo)題文字字【例2-4】通過(guò)臨時(shí)表將將Users表中刪除的記記錄進(jìn)行臨時(shí)時(shí)保存（行級(jí)級(jí)觸發(fā)器）。。（1）創(chuàng)建行級(jí)觸觸發(fā)器的測(cè)試試表CREATETABLEuserdelASSELECT*FROMUsersWHEREu_Name='amy';（2）創(chuàng)建行級(jí)觸觸發(fā)器CREATEORREPLACETRIGGERtr_DeleteUser2AFTERDELETEONUsersFOREACHROWBEGININSERTINTOuserdelALUES(:OLD.u_ID,:OLD.u_Name,:OLD.u_Type,:OLD.u_Password);END;（3）測(cè)試觸發(fā)器器tr_DeleteUser和tr_DeleteUser2DELETEFROMUsersWHEREu_Name='amy';SELECT*FROMuserdel;SELECT*FROMsyslog;教師演示講解解課堂案例2—使用PL/SQL管理觸發(fā)器案例完成步驟驟-查看觸發(fā)器添加標(biāo)題文字字【例2-5】查看用戶方案案SCOTT中所有觸發(fā)器器的名稱、觸觸發(fā)類型、觸觸發(fā)事件、所所有者和觸發(fā)發(fā)表等信息。。SELECT TRIGGER_NAME觸發(fā)器名,TRIGGER_TYPE觸發(fā)類型,TRIGGERING_EVENT觸發(fā)事件,TABLE_NAME觸發(fā)表FROMUSER_TRIGGERS;教師演示講解解課堂案例2—使用PL/SQL管理觸發(fā)器案例完成步驟驟-修改和刪除觸觸發(fā)器添加標(biāo)題文字字3．修改觸發(fā)器器修改觸發(fā)器和和修改視圖類類似，雖然Oracle也提供ALTERTRIGGER命令，但它只只用于重新編編譯或者驗(yàn)證證現(xiàn)有觸發(fā)器器。如果需要要修改觸發(fā)器器的定義，則則仍然使用CREATEORREPLACETRIGGER命令。4．刪除觸發(fā)器器Oracle的PL/SQL語(yǔ)句提供了DROPTRIGGER命令來(lái)刪除觸觸發(fā)器，其基基本語(yǔ)法格式式為：DROPTRIGGER[用戶方案.]觸發(fā)器名;【例2-6】刪除用戶方案案SCOTT的觸發(fā)器tr_ADDCUST。實(shí)現(xiàn)該刪除任任務(wù)的DROPTRIGGER命令如下：DROPTRIGGERSCOTT.tr_ADDCUST;教師演示講解解9.3其他類型觸發(fā)發(fā)器替代觸發(fā)器添加標(biāo)題文字字INSTEADOF觸發(fā)器主要用用來(lái)對(duì)另一個(gè)個(gè)表或者視圖圖進(jìn)行DML操作?！纠?-1】已經(jīng)創(chuàng)建了查查詢商品信息息的視圖VW_GOODSINFO，視圖結(jié)構(gòu)包包含商品編號(hào)號(hào)、商品名稱稱、商品類別別和詳細(xì)描述述。對(duì)于視圖圖VWGOODSINFO，考慮使用更更新語(yǔ)句，以以期更新視圖圖，達(dá)到更新新基表中數(shù)據(jù)據(jù)的目的。（1）定義視圖CREATEORREPLACEVIEWSCOTT.VW_GOODSINFOASSELECTg_ID,g_NAME,t_NAME,g_PRICEFROMSCOTT.GOODSGJOINSCOTT.TYPESTONG.t_ID=T.t_ID;教師演示講講解9.3其其他類型觸觸發(fā)器替代觸發(fā)器器添加標(biāo)題文文字（2）更新視圖圖數(shù)據(jù)對(duì)于視圖VWGOODSINFO，考慮使用用更新語(yǔ)句句，以期更更新視圖，，達(dá)到更新新基表中數(shù)數(shù)據(jù)的目的的。相應(yīng)的的PL/SQL語(yǔ)句如下所所示：UPDATESCOTT.VW_GOODSINFOSETg_NAME='三星SGH-C888',t_NAME='電腦產(chǎn)品'WHERE g_ID='010006';教師演示講講解9.3其其他類型觸觸發(fā)器替代觸發(fā)器器添加標(biāo)題文文字（3）創(chuàng)建INSTEADOF觸發(fā)器教師演示講講解9.3其其他類型觸觸發(fā)器系統(tǒng)事件觸觸發(fā)器添加標(biāo)題文文字系統(tǒng)事件觸觸發(fā)器是指指由數(shù)據(jù)庫(kù)庫(kù)系統(tǒng)事件件觸發(fā)的數(shù)數(shù)據(jù)庫(kù)觸發(fā)發(fā)器。數(shù)據(jù)據(jù)庫(kù)系統(tǒng)事事件通常包包括以下幾幾種：數(shù)據(jù)庫(kù)的啟啟動(dòng)（STARTUP）；數(shù)據(jù)庫(kù)的關(guān)關(guān)閉（SHUTDOWN）；數(shù)據(jù)庫(kù)服務(wù)務(wù)器出錯(cuò)（（SERVERERROR）?！纠?-2】創(chuàng)建數(shù)據(jù)庫(kù)庫(kù)啟動(dòng)后記記錄啟動(dòng)時(shí)時(shí)間的系統(tǒng)統(tǒng)事件觸發(fā)發(fā)器。（1）創(chuàng)建系統(tǒng)統(tǒng)事件觸發(fā)發(fā)器測(cè)試表表CREATETABLEdblog(op_datetimestamp);（2）創(chuàng)建系統(tǒng)統(tǒng)事件觸發(fā)發(fā)器CREATEORREPLACETRIGGERtr_StartDBAFTERSTARTUPONDATABASEBEGININSERTINTODBLOGVALUES(SYSDATE);END;教師演示講講解9.3其其他類型觸觸發(fā)器用戶事件觸觸發(fā)器添加標(biāo)題文文字用戶事件觸觸發(fā)器指與與數(shù)據(jù)庫(kù)定定義語(yǔ)句或或用戶的登登錄/注銷等事件件相關(guān)的觸觸發(fā)器。這這些事件包包括以下語(yǔ)語(yǔ)句，并且且可以規(guī)定定觸發(fā)時(shí)間間BEFORE或AFTER?！纠?-3】創(chuàng)建保存系系統(tǒng)操作日日志的用戶戶事件觸發(fā)發(fā)器。createorreplaceTRIGGERSCOTT.TR_DDLAFTERLOGONONSCHEMABEGININSERTINTOSCOTT.SYSLOGVALUES(USER,SYSDATE);END;教師演示講講解9.4eBuy中的典型觸觸發(fā)器插入型觸發(fā)發(fā)器添加標(biāo)題文文字【例4-1】在用戶方案案SCOTT的商品表GOODS中插入數(shù)據(jù)據(jù)記錄時(shí)需需要進(jìn)行如如下檢查：：當(dāng)試圖往往GOODS表中插入在在商品類別別表TYPES中并不存在在的商品類類別編號(hào)t_ID時(shí)，返回一一條類別錯(cuò)錯(cuò)誤的消息息；否則顯顯示插入成成功的消息息。教師演示講講解9.4eBuy中中的典型觸觸發(fā)器刪除型觸發(fā)發(fā)器添加標(biāo)題文文字【例4-2】當(dāng)需要要?jiǎng)h除