數(shù)據(jù)庫(kù)觸發(fā)器和內(nèi)置程序包

數(shù)據(jù)庫(kù)觸發(fā)器講師：林強(qiáng)目標(biāo)本章將討論：數(shù)據(jù)庫(kù)觸發(fā)器內(nèi)置程序包2簡(jiǎn)介觸發(fā)器類(lèi)似于函數(shù)和過(guò)程，它們都是具有聲明部分、執(zhí)行部分和異常處理部分的命名PL/SQL塊。像包一樣，觸發(fā)器必須在數(shù)據(jù)庫(kù)中以獨(dú)立對(duì)象的身份存儲(chǔ)，并且不能與包和塊具有本地關(guān)系。我們?cè)谇皟烧轮幸呀?jīng)講過(guò)，過(guò)程是顯式地通過(guò)過(guò)程調(diào)用從其他塊中執(zhí)行的，同時(shí)，過(guò)程調(diào)用可以傳遞參數(shù)。與之相反,觸發(fā)器是在事件發(fā)生時(shí)隱式地運(yùn)行的，并且觸發(fā)器不能接收參數(shù)。運(yùn)行觸發(fā)器的方式叫做激發(fā)（firing）觸發(fā)器，觸發(fā)事件可以是對(duì)數(shù)據(jù)庫(kù)表的DML（INSERT、UPDATE或DELETE）操作或某種視圖的操作(View)。Oracle8i把觸發(fā)器功能擴(kuò)展到了可以激發(fā)系統(tǒng)事件，如數(shù)據(jù)庫(kù)的啟動(dòng)和關(guān)閉，以及某種DDL操作。3觸發(fā)器可以用于下列情況：

維護(hù)在表創(chuàng)建階段通過(guò)聲明限制無(wú)法實(shí)現(xiàn)的復(fù)雜完整性限制。

通過(guò)記錄修改內(nèi)容和修改者來(lái)審計(jì)表中的信息。在表內(nèi)容發(fā)生變更時(shí)，自動(dòng)通知其他程序采取相應(yīng)的處理。在訂閱發(fā)布環(huán)境下，發(fā)布有關(guān)各種事件的信息。4數(shù)據(jù)庫(kù)觸發(fā)器數(shù)據(jù)庫(kù)觸發(fā)器是PL/SQL塊或存儲(chǔ)過(guò)程是在對(duì)關(guān)聯(lián)表執(zhí)行DML操作時(shí)觸發(fā)的是隱式執(zhí)行的還可能具有聲明部分和異常處理部分5createtableSTUDENTS(

IDINTEGERnotnull,MAJORVARCHAR2(30),CURRENT_CREDITSNUMBER,)CREATETABLEmajor_stats(majorVARCHAR2(30),

total_creditsNUMBER,

total_studentsNUMBER);為了保持表major_stats中的數(shù)據(jù)處于更新?tīng)顟B(tài)，創(chuàng)建一個(gè)每次表students被修改時(shí)自動(dòng)更新表major_stats的觸發(fā)器。例子2/1DML觸發(fā)6例子2/2DML觸發(fā)CREATEORREPLACETRIGGERUpdateMajorStatsAFTERINSERTORDELETEORUPDATEONstudentsDECLARECURSORc_StatisticsISSELECTmajor,COUNT(*)total_students,SUM(current_credits)total_creditsFROMstudentsGROUPBYmajor;BEGINDELETEFROMmajor_stats;FORv_StatsRecordinc_StatisticsLOOPINSERTINTOmajor_stats(major,total_credits,total_students)VALUES(v_StatsRecord.major,v_StatsRecord.total_credits,v_StatsRecord.total_students);ENDLOOP;ENDUpdateMajorStats;7替代觸發(fā)器Oracle8提供的這種替代觸發(fā)器（Instead-oftrigger）只能定義在視圖上（可以是關(guān)系或?qū)ο螅?。與DML觸發(fā)器不同，DML觸發(fā)器是在DML操作之外運(yùn)行的，而替代觸發(fā)器則代替激發(fā)它的DML語(yǔ)句運(yùn)行。替代觸發(fā)器是行一級(jí)的。8例子替代觸發(fā)器createtableROOMS(ROOM_IDINTEGERnotnull,DEPARTMENTVARCHAR2(22),BUILDINGVARCHAR2(22))createtableCLASS(

CLASS_IDINTEGER,

COURSEINTEGER,

ROOM_IDINTEGER

)9視圖：CREATEORREPLACEVIEWCLASSES_ROOMSAS

SELECTr.department,r.building,c.room_id

FROMroomsr,classc

WHEREr.room_id=c.room_id

CREATEorreplaceTRIGGERClassesRoomsInsert

INSTEADOFINSERTONclasses_rooms

DECLARE

v_roomID

rooms.room_id%TYPE;

BEGIN

--FirstdeterminetheroomID

SELECTroom_id

INTOv_roomID

FROMrooms

WHEREbuilding=:new.building

ANDroom_id=:new.room_id;

--Andnowupdatetheclass

UPDATECLASSES

SETroom_id=v_roomID

WHEREdepartment=:new.department

ANDcourse=:new.course;

ENDClassesRoomsInsert;

10系統(tǒng)觸發(fā)器Oracle8i提供了第三種觸發(fā)器，這種系統(tǒng)觸發(fā)器在發(fā)生如數(shù)據(jù)庫(kù)啟動(dòng)或關(guān)閉等系統(tǒng)事件時(shí)激發(fā)，而不是在執(zhí)行DML語(yǔ)句時(shí)激發(fā)。系統(tǒng)觸發(fā)器也可以在DDL操作時(shí)，如表的創(chuàng)建中激發(fā)。例如，假設(shè)我們要記錄對(duì)象創(chuàng)建的時(shí)間，我們可以通過(guò)創(chuàng)建下面的表來(lái)實(shí)現(xiàn)上述記錄功能：11系統(tǒng)觸發(fā)器CREATE

TABLE

ddl_creations(

user_id

VARCHAR2(30),

object_type

VARCHAR2(20),

object_name

VARCHAR2(30),

object_owner

VARCHAR2(30),

creation_date

DATE)CREATE

OR

REPLACE

TRIGGER

LogCreations

AFTER

CREATE

ON

SCHEMA

BEGIN

INSERT

INTO

ddl_creations(user_id,object_type,object_name,

object_owner,creation_date)

VALUES(USER,SYS.DICTIONARY_OBJ_TYPE,SYS.DICTIONARY_OBJ_NAME,

SYS.DICTIONARY_OBJ_OWNER,SYSDATE);

END

LogCreations;12創(chuàng)建觸發(fā)器的通用語(yǔ)法：

13createtableTEMP_TABLE

(

NUM_COLNUMBER,

CHAR_COLVARCHAR2(100)

)CREATESEQUENCEtrig_seqSTARTWITH1INCREMENTBY1;CREATEORREPLACEPACKAGETrigPackageAS

--Globalcounterforuseinthetriggers

v_CounterNUMBER;ENDTrigPackage;

14CREATEORREPLACETRIGGERClassesBStatement

BEFOREUPDATEONclass

BEGIN

--Resetthecounterfirst.

TrigPackage.v_Counter:=0;

INSERTINTOtemp_table(num_col,char_col)

VALUES(trig_seq.NEXTVAL,

'BeforeStatement:counter='||TrigPackage.v_Counter);

TrigPackage.v_Counter:=TrigPackage.v_Counter+1;

ENDClassesBStatement;CREATEORREPLACETRIGGERClassesAStatement1AFTERUPDATEONclassBEGININSERTINTOtemp_table(num_col,char_col)VALUES(trig_seq.NEXTVAL,'AfterStatement1:counter='||TrigPackage.v_Counter);--Incrementforthenexttrigger.TrigPackage.v_Counter:=TrigPackage.v_Counter+1;ENDClassesAStatement1;15CREATEORREPLACETRIGGERClassesBRow1BEFOREUPDATEONclassFOREACHROWBEGININSERTINTOtemp_table(num_col,char_col)VALUES(trig_seq.NEXTVAL,'BeforeRow1:counter='||TrigPackage.v_Counter);--Incrementforthenexttrigger.TrigPackage.v_Counter:=TrigPackage.v_Counter+1;ENDClassesBRow1;CREATEORREPLACETRIGGERClassesBRow2

BEFOREUPDATEONclass

FOREACHROW

BEGIN

INSERTINTOtemp_table(num_col,char_col)

VALUES(trig_seq.NEXTVAL,

'BeforeRow2:counter='||TrigPackage.v_Counter);

--Incrementforthenexttrigger.

TrigPackage.v_Counter:=TrigPackage.v_Counter+1;

ENDClassesBRow2;16行觸發(fā)器用FOREACHROW語(yǔ)法；可以用WHEN語(yǔ)法；可以用new.column_name、old.column_name語(yǔ)法；Createorreplacetriggertemp_air

AfterinsertontempForeachrowWhen(new.n=0)Begin

dbms_output.put_line(‘executetrigger’);end17語(yǔ)句觸發(fā)器無(wú)FOREACHROW語(yǔ)法；不可以用WHEN語(yǔ)法；不可以用new.column_name、old.column_name語(yǔ)法；18數(shù)據(jù)庫(kù)觸發(fā)器10-2觸發(fā)器的組成部分觸發(fā)器語(yǔ)句觸發(fā)器主體觸發(fā)器限制19數(shù)據(jù)庫(kù)觸發(fā)器10-3觸發(fā)器語(yǔ)句指定觸發(fā)器定時(shí)、事件、表名及類(lèi)型觸發(fā)器主體是PL/SQL塊或?qū)^(guò)程的調(diào)用觸發(fā)器限制可以通過(guò)WHEN子句實(shí)現(xiàn)20數(shù)據(jù)庫(kù)觸發(fā)器10-4觸發(fā)器的類(lèi)型應(yīng)用程序觸發(fā)器數(shù)據(jù)庫(kù)觸發(fā)器21數(shù)據(jù)庫(kù)觸發(fā)器10-5應(yīng)用程序觸發(fā)器在應(yīng)用程序內(nèi)發(fā)生特定DML事件時(shí)，將隱式

觸發(fā)它數(shù)據(jù)庫(kù)觸發(fā)器在表、視圖或數(shù)據(jù)庫(kù)上發(fā)生特定DML事件時(shí)，將隱式觸發(fā)它在表和視圖上定義在數(shù)據(jù)庫(kù)或方案上，它也可以充當(dāng)系統(tǒng)

觸發(fā)器22數(shù)據(jù)庫(kù)觸發(fā)器10-6DML觸發(fā)器組件觸發(fā)器定時(shí)觸發(fā)器事件表名觸發(fā)器類(lèi)型WHEN子句觸發(fā)器主體23數(shù)據(jù)庫(kù)觸發(fā)器10-7變異表當(dāng)前正由DML語(yǔ)句修改聲明刪除級(jí)聯(lián)（引用完整性約束條件）的影響約束表對(duì)于SQL語(yǔ)句，觸發(fā)語(yǔ)句直接讀取對(duì)于聲明的引用完整性約束條件，間接讀取表的變異或約束只是對(duì)會(huì)話(huà)而言的24INSTEADOF觸發(fā)器是在視圖上而不是在表上定義的提供修改視圖的透明方法可以針對(duì)視圖編寫(xiě)INSERT、UPDATE及DELETE語(yǔ)句它以不可見(jiàn)方式在后臺(tái)運(yùn)行執(zhí)行在觸發(fā)器主體中編寫(xiě)的操作數(shù)據(jù)庫(kù)觸發(fā)器10-825嵌套表上的INSTEADOF觸發(fā)器不能使用TABLE子句直接修改視圖中的嵌套列通過(guò)INSTEADOF觸發(fā)器進(jìn)行修改嵌套表上的觸發(fā)器因DML操作而觸發(fā)啟用和禁用觸發(fā)器語(yǔ)法如下：altertrigger<Trigger_name>enable;altertrigger<Trigger_name>disable;

其中，Trigger_name

是觸發(fā)器名稱(chēng)。數(shù)據(jù)庫(kù)觸發(fā)器10-926刪除觸發(fā)器使用以下語(yǔ)法：droptrigger<trigger_name>;其中，trigger_name

是觸發(fā)器名稱(chēng)。查看有關(guān)觸發(fā)器的信息使用下列數(shù)據(jù)字典視圖：USER_TRIGGERSALL_TRIGGERSDBA_TRIGGERS數(shù)據(jù)庫(kù)觸發(fā)器10-1027擴(kuò)展數(shù)據(jù)庫(kù)的功能為PL/SQL提供對(duì)SQL功能的訪(fǎng)問(wèn)用戶(hù)SYS擁有所有程序包是公共同義詞可以由任何用戶(hù)訪(fǎng)問(wèn)用戶(hù)必須具有EXECUTE權(quán)限，才能訪(fǎng)問(wèn)過(guò)程和函數(shù)內(nèi)置程序包6-128其中的一些內(nèi)置程序包如下：

內(nèi)置程序包6-229DBMS_STANDARD提供語(yǔ)言工具DBMS_ALERT支持?jǐn)?shù)據(jù)庫(kù)事件的異步通知DBMS_OUTPUT允許顯示輸出結(jié)果如果禁用它，則忽略對(duì)此程序包的調(diào)用DBMS_LOB添加用于處理大型對(duì)象的實(shí)用過(guò)程和函數(shù)內(nèi)置程序包6-330DBMS_OUTPUT中的一些過(guò)程EnableDisablePutPut_lineNew_lineGet_lineGet_lines

內(nèi)置程序包6-431DBMS_LOBappendcomparecopyerasefileclosefileexistsfilegetnamegetlength內(nèi)置程序包6-532Oracle9i中新增程序包的部分列表內(nèi)置程序包6-6程序包名稱(chēng)描述DBMS_AQELM提供過(guò)程管理AdvancedQueuing異步通知的配置（通過(guò)電子郵件和HTTP）

。DBMS_FGA提供細(xì)粒度安全函數(shù)。DB