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

1、講師：林強(qiáng)講師：林強(qiáng) 本章將討論：本章將討論： 數(shù)據(jù)庫(kù)觸發(fā)器數(shù)據(jù)庫(kù)觸發(fā)器 內(nèi)置程序包內(nèi)置程序包 觸發(fā)器類似于函數(shù)和過(guò)程，它們都是具有聲明部分、執(zhí)行觸發(fā)器類似于函數(shù)和過(guò)程，它們都是具有聲明部分、執(zhí)行部分和異常處理部分的命名部分和異常處理部分的命名PL/SQLPL/SQL塊。像包一樣，觸發(fā)器塊。像包一樣，觸發(fā)器必須在數(shù)據(jù)庫(kù)中以獨(dú)立對(duì)象的身份存儲(chǔ)，并且不能與包和必須在數(shù)據(jù)庫(kù)中以獨(dú)立對(duì)象的身份存儲(chǔ)，并且不能與包和塊具有本地關(guān)系。我們?cè)谇皟烧轮幸呀?jīng)講過(guò)，過(guò)程是顯式塊具有本地關(guān)系。我們?cè)谇皟烧轮幸呀?jīng)講過(guò)，過(guò)程是顯式地通過(guò)過(guò)程調(diào)用從其他塊中執(zhí)行的，同時(shí)，過(guò)程調(diào)用可以地通過(guò)過(guò)程調(diào)用從其他塊中執(zhí)行的，同時(shí)，過(guò)程

2、調(diào)用可以傳遞參數(shù)。與之相反傳遞參數(shù)。與之相反, ,觸發(fā)器是在事件發(fā)生時(shí)隱式地運(yùn)行觸發(fā)器是在事件發(fā)生時(shí)隱式地運(yùn)行的，并且觸發(fā)器不能接收參數(shù)。運(yùn)行觸發(fā)器的方式叫做激的，并且觸發(fā)器不能接收參數(shù)。運(yùn)行觸發(fā)器的方式叫做激發(fā)（發(fā)（firingfiring）觸發(fā)器，觸發(fā)事件可以是對(duì)數(shù)據(jù)庫(kù)表的）觸發(fā)器，觸發(fā)事件可以是對(duì)數(shù)據(jù)庫(kù)表的DMLDML（INSERTINSERT、UPDATEUPDATE或或DELETEDELETE）操作或某種視圖的操作）操作或某種視圖的操作( Vi e ( Vi e w )w )。Oracle8iOracle8i把觸發(fā)器功能擴(kuò)展到了可以激發(fā)系統(tǒng)事件，把觸發(fā)器功能擴(kuò)展到了可以激發(fā)系統(tǒng)事件，

3、如數(shù)據(jù)庫(kù)的啟動(dòng)和關(guān)閉，以及某種如數(shù)據(jù)庫(kù)的啟動(dòng)和關(guān)閉，以及某種DDLDDL操作。操作。 維護(hù)在表創(chuàng)建階段通過(guò)聲明限制無(wú)法實(shí)現(xiàn)的復(fù)雜維護(hù)在表創(chuàng)建階段通過(guò)聲明限制無(wú)法實(shí)現(xiàn)的復(fù)雜完整性限制。完整性限制。 通過(guò)記錄修改內(nèi)容和修改者來(lái)審計(jì)表中的信息。通過(guò)記錄修改內(nèi)容和修改者來(lái)審計(jì)表中的信息。 在表內(nèi)容發(fā)生變更時(shí)，自動(dòng)通知其他程序采取相在表內(nèi)容發(fā)生變更時(shí)，自動(dòng)通知其他程序采取相應(yīng)的處理。應(yīng)的處理。 在訂閱發(fā)布環(huán)境下，發(fā)布有關(guān)各種事件的信息。在訂閱發(fā)布環(huán)境下，發(fā)布有關(guān)各種事件的信息。 數(shù)據(jù)庫(kù)觸發(fā)器數(shù)據(jù)庫(kù)觸發(fā)器 是是 PL/SQL 塊或存儲(chǔ)過(guò)程塊或存儲(chǔ)過(guò)程 是在對(duì)關(guān)聯(lián)表執(zhí)行是在對(duì)關(guān)聯(lián)表執(zhí)行 DML 操作時(shí)觸發(fā)的

4、操作時(shí)觸發(fā)的 是隱式執(zhí)行的是隱式執(zhí)行的 還可能具有聲明部分和異常處理部分還可能具有聲明部分和異常處理部分create table STUDENTS(create table STUDENTS(ID INTEGER not null,ID INTEGER not null, MAJOR VARCHAR2(30), MAJOR VARCHAR2(30), CURRENT_CREDITS NUMBER,) CURRENT_CREDITS NUMBER,)CREATE TABLE major_stats (CREATE TABLE major_stats ( major VARCHAR2(30), m

5、ajor VARCHAR2(30), total_credits NUMBER, total_credits NUMBER, total_students NUMBER); total_students NUMBER); 為了保持表為了保持表major_statsmajor_stats中的數(shù)據(jù)處于更新?tīng)顟B(tài)，創(chuàng)建一中的數(shù)據(jù)處于更新?tīng)顟B(tài)，創(chuàng)建一個(gè)每次表個(gè)每次表studentsstudents被修改時(shí)自動(dòng)更新表被修改時(shí)自動(dòng)更新表major_statsmajor_stats的觸的觸發(fā)器。發(fā)器。CREATE OR REPLACE TRIGGER UpdateMajorStatsCREATE OR REP

6、LACE TRIGGER UpdateMajorStatsAFTER INSERT OR DELETE OR UPDATE ON studentsAFTER INSERT OR DELETE OR UPDATE ON studentsDECLAREDECLARECURSOR c_Statistics ISCURSOR c_Statistics ISSELECT major, COUNT(SELECT major, COUNT(* *) total_students,) total_students,SUM(current_credits) total_creditsSUM(current_cr

7、edits) total_creditsFROM studentsFROM studentsGROUP BY major;GROUP BY major;BEGINBEGINDELETE FROM major_stats;DELETE FROM major_stats;FOR v_StatsRecord in c_Statistics LOOPFOR v_StatsRecord in c_Statistics LOOPINSERT INTO major_stats (major, total_credits, total_students)INSERT INTO major_stats (maj

8、or, total_credits, total_students)VALUES (v_StatsRecord.major, v_StatsRecord.total_credits,VALUES (v_StatsRecord.major, v_StatsRecord.total_credits,v_StatsRecord.total_students);v_StatsRecord.total_students);END LOOP;END LOOP;END UpdateMajorStats;END UpdateMajorStats; Oracle8提供的這種替代觸發(fā)器（提供的這種替代觸發(fā)器（ I

9、nstead-of trigger）只能定義在視圖上（可以是關(guān)系或）只能定義在視圖上（可以是關(guān)系或?qū)ο螅?。與對(duì)象）。與DML觸發(fā)器不同，觸發(fā)器不同， DML觸發(fā)器是觸發(fā)器是在在DML操作之外運(yùn)行的，而替代觸發(fā)器則代操作之外運(yùn)行的，而替代觸發(fā)器則代替激發(fā)它的替激發(fā)它的DML語(yǔ)句運(yùn)行。替代觸發(fā)器是行語(yǔ)句運(yùn)行。替代觸發(fā)器是行一級(jí)的。一級(jí)的。create table ROOMS(create table ROOMS(ROOM_ID INTEGER not null,ROOM_ID INTEGER not null,DEPARTMENT VARCHAR2(22),DEPARTMENT VARCHAR2(

10、22),BUILDING VARCHAR2(22)BUILDING VARCHAR2(22) create table CLASS( create table CLASS(CLASS_ID INTEGER,CLASS_ID INTEGER,COURSE INTEGER,COURSE INTEGER,ROOM_ID INTEGER ROOM_ID INTEGER ) )CREATE OR REPLACE VIEW CLASSES_ROOMS ASCREATE OR REPLACE VIEW CLASSES_ROOMS ASSELECT r.department, r.building,c.roo

11、m_idSELECT r.department, r.building,c.room_idFROM rooms r, class cFROM rooms r, class cWHERE r.room_id = c.room_idWHERE r.room_id = c.room_idCREATE or replace TRIGGER ClassesRoomsInsertCREATE or replace TRIGGER ClassesRoomsInsertINSTEAD OF INSERT ON classes_roomsINSTEAD OF INSERT ON classes_roomsDEC

12、LAREDECLAREv_roomID rooms.room_id%TYPE;v_roomID rooms.room_id%TYPE;BEGINBEGIN- First determine the room ID- First determine the room IDSELECT room_idSELECT room_idINTO v_roomIDINTO v_roomIDFROM roomsFROM roomsWHERE building = :new.buildingWHERE building = :new.buildingAND room_id = :new.room_id;AND

13、room_id = :new.room_id;- And now update the class- And now update the classUPDATE CLASSESUPDATE CLASSESSET room_id = v_roomIDSET room_id = v_roomIDWHERE department = :new.departmentWHERE department = :new.departmentAND course = :new.course;AND course = :new.course;END ClassesRoomsInsert;END ClassesR

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

15、ns (user_id VARCHAR2(30),object_type VARCHAR2(20),object_name VARCHAR2(30),object_owner VARCHAR2(30),creation_date DATE)CREATE OR REPLACE TRIGGER LogCreationsAFTER CREATE ON SCHEMABEGININSERT INTO ddl_creations (user_id, object_type, object_name,object_owner, creation_date)VALUES (USER, SYS.DICTIONA

16、RY_OBJ_TYPE, SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_OWNER, SYSDATE);END LogCreations;create table TEMP_TABLEcreate table TEMP_TABLE( ( NUM_COL NUMBER, NUM_COL NUMBER, CHAR_COL VARCHAR2(100) CHAR_COL VARCHAR2(100) )CREATE SEQUENCE trig_seqCREATE SEQUENCE trig_seqSTART WITH 1START WITH 1INCREMENT

17、BY 1;INCREMENT BY 1;CREATE OR REPLACE PACKAGE TrigPackage ASCREATE OR REPLACE PACKAGE TrigPackage AS- Global counter for use in the triggers- Global counter for use in the triggersv_Counter NUMBER;v_Counter NUMBER;END TrigPackage;END TrigPackage;CREATE OR REPLACE TRIGGER ClassesBStatementCREATE OR R

18、EPLACE TRIGGER ClassesBStatementBEFORE UPDATE ON classBEFORE UPDATE ON classBEGINBEGIN- Reset the counter first.- Reset the counter first.TrigPackage.v_Counter := 0;TrigPackage.v_Counter := 0;INSERT INTO temp_table (num_col, char_col)INSERT INTO temp_table (num_col, char_col)VALUES (trig_seq.NEXTVAL

19、,VALUES (trig_seq.NEXTVAL,Before Statement: counter = | TrigPackage.v_Counter);Before Statement: counter = | TrigPackage.v_Counter);TrigPackage.v_Counter := TrigPackage.v_Counter + 1;TrigPackage.v_Counter := TrigPackage.v_Counter + 1;END ClassesBStatement;END ClassesBStatement;CREATE OR REPLACE TRIG

20、GER ClassesAStatement1CREATE OR REPLACE TRIGGER ClassesAStatement1AFTER UPDATE ON classAFTER UPDATE ON classBEGINBEGININSERT INTO temp_table (num_col, char_col)INSERT INTO temp_table (num_col, char_col)VALUES (trig_seq.NEXTVAL,VALUES (trig_seq.NEXTVAL,After Statement 1: counter = | TrigPackage.v_Cou

21、nter);After Statement 1: counter = | TrigPackage.v_Counter);- Increment for the next trigger.- Increment for the next trigger.TrigPackage.v_Counter := TrigPackage.v_Counter + 1;TrigPackage.v_Counter := TrigPackage.v_Counter + 1;END ClassesAStatement1;END ClassesAStatement1;CREATE OR REPLACE TRIGGER

22、ClassesBRow1BEFORE UPDATE ON classFOR EACH ROWBEGININSERT INTO temp_table (num_col, char_col)VALUES (trig_seq.NEXTVAL,Before Row 1: counter = | TrigPackage.v_Counter);- Increment for the next trigger.TrigPackage.v_Counter := TrigPackage.v_Counter + 1;END ClassesBRow1;CREATE OR REPLACE TRIGGER Classe

23、sBRow2BEFORE UPDATE ON classFOR EACH ROWBEGININSERT INTO temp_table (num_col, char_col)VALUES (trig_seq.NEXTVAL,Before Row 2: counter = | TrigPackage.v_Counter);- Increment for the next trigger.TrigPackage.v_Counter := TrigPackage.v_Counter + 1;END ClassesBRow2;用用FOR EACH ROW FOR EACH ROW 語(yǔ)法；語(yǔ)法；可以用可

24、以用WHENWHEN語(yǔ)法；語(yǔ)法；可以用可以用new.column_namenew.column_name、old.column_nameold.column_name語(yǔ)法；語(yǔ)法；Create or replace trigger temp_air After insert on tempFor each rowWhen (new.n=0)Begin dbms_output.put_line( execute trigger);end 無(wú)無(wú)FOR EACH ROW 語(yǔ)法；語(yǔ)法； 不可以用不可以用WHEN語(yǔ)法；語(yǔ)法； 不可以用不可以用new.column_name、old.column_name語(yǔ)

25、法；語(yǔ)法； 觸發(fā)器的組成部分觸發(fā)器的組成部分 觸發(fā)器語(yǔ)句觸發(fā)器語(yǔ)句 觸發(fā)器主體觸發(fā)器主體 觸發(fā)器限制觸發(fā)器限制 觸發(fā)器語(yǔ)句觸發(fā)器語(yǔ)句 指定觸發(fā)器定時(shí)、事件、表名及類型指定觸發(fā)器定時(shí)、事件、表名及類型 觸發(fā)器主體觸發(fā)器主體 是是 PL/SQL 塊或?qū)^(guò)程的調(diào)用塊或?qū)^(guò)程的調(diào)用 觸發(fā)器限制觸發(fā)器限制 可以通過(guò)可以通過(guò) WHEN 子句實(shí)現(xiàn)子句實(shí)現(xiàn) 觸發(fā)器的類型觸發(fā)器的類型 應(yīng)用程序觸發(fā)器應(yīng)用程序觸發(fā)器 數(shù)據(jù)庫(kù)觸發(fā)器數(shù)據(jù)庫(kù)觸發(fā)器 應(yīng)用程序觸發(fā)器應(yīng)用程序觸發(fā)器 在應(yīng)用程序內(nèi)發(fā)生特定在應(yīng)用程序內(nèi)發(fā)生特定 DML 事件時(shí)，將隱式事件時(shí)，將隱式觸發(fā)它觸發(fā)它 數(shù)據(jù)庫(kù)觸發(fā)器數(shù)據(jù)庫(kù)觸發(fā)器 在表、視圖或數(shù)據(jù)庫(kù)上發(fā)生特

26、定在表、視圖或數(shù)據(jù)庫(kù)上發(fā)生特定 DML 事件時(shí)，事件時(shí)，將隱式觸發(fā)它將隱式觸發(fā)它 在表和視圖上定義在表和視圖上定義 在數(shù)據(jù)庫(kù)或方案上，它也可以充當(dāng)系統(tǒng)在數(shù)據(jù)庫(kù)或方案上，它也可以充當(dāng)系統(tǒng)觸發(fā)器觸發(fā)器 DML 觸發(fā)器組件觸發(fā)器組件 觸發(fā)器定時(shí)觸發(fā)器定時(shí) 觸發(fā)器事件觸發(fā)器事件 表名表名 觸發(fā)器類型觸發(fā)器類型 WHEN 子句子句 觸發(fā)器主體觸發(fā)器主體 變異表變異表 當(dāng)前正由當(dāng)前正由 DML 語(yǔ)句修改語(yǔ)句修改 聲明刪除級(jí)聯(lián)（引用完整性約束條件）的影聲明刪除級(jí)聯(lián)（引用完整性約束條件）的影響響 約束表約束表 對(duì)于對(duì)于 SQL 語(yǔ)句，觸發(fā)語(yǔ)句直接讀取語(yǔ)句，觸發(fā)語(yǔ)句直接讀取 對(duì)于聲明的引用完整性約束條件，間接讀

27、取對(duì)于聲明的引用完整性約束條件，間接讀取 表的變異或約束只是對(duì)會(huì)話而言的表的變異或約束只是對(duì)會(huì)話而言的 INSTEAD OF 觸發(fā)器觸發(fā)器 是在視圖上而不是在表上定義的是在視圖上而不是在表上定義的 提供修改視圖的透明方法提供修改視圖的透明方法 可以針對(duì)視圖編寫可以針對(duì)視圖編寫 INSERT、UPDATE 及及 DELETE 語(yǔ)句語(yǔ)句 它以不可見(jiàn)方式在后臺(tái)運(yùn)行它以不可見(jiàn)方式在后臺(tái)運(yùn)行 執(zhí)行在觸發(fā)器主體中編寫的操作執(zhí)行在觸發(fā)器主體中編寫的操作 嵌套表上的嵌套表上的 INSTEAD OF 觸發(fā)器觸發(fā)器 不能使用不能使用 TABLE 子句直接修改視圖中的嵌套列子句直接修改視圖中的嵌套列 通過(guò)通過(guò) IN

28、STEAD OF 觸發(fā)器進(jìn)行修改觸發(fā)器進(jìn)行修改 嵌套表上的觸發(fā)器因嵌套表上的觸發(fā)器因 DML 操作而觸發(fā)操作而觸發(fā) 啟用和禁用觸發(fā)器啟用和禁用觸發(fā)器 語(yǔ)法如下：語(yǔ)法如下：alter trigger enable;alter trigger enable;alter trigger disable;alter trigger disable; 其中，其中，Trigger_nameTrigger_name 是觸發(fā)器名稱。是觸發(fā)器名稱。 刪除觸發(fā)器刪除觸發(fā)器 使用以下語(yǔ)法：使用以下語(yǔ)法：drop trigger ;drop trigger ;其中，其中，trigger_nametrigger_nam

29、e 是觸發(fā)器名稱。是觸發(fā)器名稱。 查看有關(guān)觸發(fā)器的信息查看有關(guān)觸發(fā)器的信息 使用下列數(shù)據(jù)字典視圖：使用下列數(shù)據(jù)字典視圖：USER_TRIGGERSALL_TRIGGERSDBA_TRIGGERS 擴(kuò)展數(shù)據(jù)庫(kù)的功能擴(kuò)展數(shù)據(jù)庫(kù)的功能 為為 PL/SQL 提供對(duì)提供對(duì) SQL 功能的訪問(wèn)功能的訪問(wèn) 用戶用戶 SYS 擁有所有程序包擁有所有程序包 是公共同義詞是公共同義詞 可以由任何用戶訪問(wèn)可以由任何用戶訪問(wèn) 用戶必須具有用戶必須具有 EXECUTE 權(quán)限，才能訪問(wèn)權(quán)限，才能訪問(wèn)過(guò)程和函數(shù)過(guò)程和函數(shù) 其中的一些內(nèi)置程序包如下：其中的一些內(nèi)置程序包如下： DBMS_STANDARD 提供語(yǔ)言工具提供語(yǔ)言工具 DBMS_ALERT 支持?jǐn)?shù)據(jù)庫(kù)事件的異步通知支持?jǐn)?shù)據(jù)庫(kù)事件的異步通知 DBMS_OUTPUT 允許顯示輸出結(jié)果允許顯示輸出結(jié)果 如果禁用它，則忽略對(duì)此程序包的調(diào)用如果禁用它，則忽略對(duì)此程序包的調(diào)用 DBMS_LOB 添加用于處理大型對(duì)象的實(shí)用過(guò)程和函數(shù)添加用于處理大型對(duì)象的實(shí)用過(guò)程和函數(shù) DBMS_OUTPUT 中的一些過(guò)程中的一些過(guò)程 Enable Disable Put Put_line New_line Get_line Get_lines DBMS_LOB append compare copy erase fileclos