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

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

維護在表創(chuàng)建階段通過聲明限制無法實現(xiàn)的復雜完整性限制。

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

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

total_creditsNUMBER,

total_studentsNUMBER);為了保持表major_stats中的數(shù)據(jù)處于更新狀態(tài)，創(chuàng)建一個每次表students被修改時自動更新表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）只能定義在視圖上（可以是關系或?qū)ο螅?。與DML觸發(fā)器不同，DML觸發(fā)器是在DML操作之外運行的，而替代觸發(fā)器則代替激發(fā)它的DML語句運行。替代觸發(fā)器是行一級的。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ù)庫啟動或關閉等系統(tǒng)事件時激發(fā)，而不是在執(zhí)行DML語句時激發(fā)。系統(tǒng)觸發(fā)器也可以在DDL操作時，如表的創(chuàng)建中激發(fā)。例如，假設我們要記錄對象創(chuàng)建的時間，我們可以通過創(chuàng)建下面的表來實現(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ā)器的通用語法：

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語法；可以用WHEN語法；可以用new.column_name、old.column_name語法；Createorreplacetriggertemp_air

AfterinsertontempForeachrowWhen(new.n=0)Begin

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

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

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

其中，Trigger_name

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

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

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

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

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