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