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

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

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

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

4、操作時觸發(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 CREATE TABLE major_statsmajor_stats ( ( major VARCHAR2(30), m

5、ajor VARCHAR2(30), total_creditstotal_credits NUMBER, NUMBER, total_studentstotal_students NUMBER); NUMBER); 為了保持表為了保持表major_statsmajor_stats中的數(shù)據(jù)處于更新狀態(tài)，創(chuàng)建一中的數(shù)據(jù)處于更新狀態(tài)，創(chuàng)建一個每次表個每次表studentsstudents被修改時自動更新表被修改時自動更新表major_statsmajor_stats的觸的觸發(fā)器。發(fā)器。CREATE OR REPLACE TRIGGER CREATE OR REPLACE TRIGGER Updat

6、eMajorStatsUpdateMajorStatsAFTER INSERT OR DELETE OR UPDATE ON studentsAFTER INSERT OR DELETE OR UPDATE ON studentsDECLAREDECLARECURSOR CURSOR c_Statisticsc_Statistics IS ISSELECT major, COUNT(SELECT major, COUNT(* *) ) total_studentstotal_students, ,SUM(current_creditsSUM(current_credits) ) total_c

7、reditstotal_creditsFROM studentsFROM studentsGROUP BY major;GROUP BY major;BEGINBEGINDELETE FROM DELETE FROM major_statsmajor_stats; ;FOR FOR v_StatsRecordv_StatsRecord in in c_Statisticsc_Statistics LOOP LOOPINSERT INTO INSERT INTO major_statsmajor_stats (major, (major, total_creditstotal_credits,

8、, total_studentstotal_students) )VALUES (VALUES (v_StatsRecord.majorv_StatsRecord.major, , v_StatsRecord.total_creditsv_StatsRecord.total_credits, ,v_StatsRecord.total_studentsv_StatsRecord.total_students););END LOOP;END LOOP;END END UpdateMajorStatsUpdateMajorStats; ; Oracle8提供的這種替代觸發(fā)器（提供的這種替代觸發(fā)器（

9、Instead-of trigger）只能定義在視圖上（可以是關(guān)系或）只能定義在視圖上（可以是關(guān)系或?qū)ο螅Ｅc對象）。與DML觸發(fā)器不同，觸發(fā)器不同， DML觸發(fā)器是觸發(fā)器是在在DML操作之外運行的，而替代觸發(fā)器則代操作之外運行的，而替代觸發(fā)器則代替激發(fā)它的替激發(fā)它的DML語句運行。替代觸發(fā)器是行語句運行。替代觸發(fā)器是行一級的。一級的。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 SELECT r.departmentr.departme

11、nt, , r.building,c.room_idr.building,c.room_idFROM rooms r, class cFROM rooms r, class cWHERE WHERE r.room_idr.room_id = = c.room_idc.room_idCREATE or replace TRIGGER CREATE or replace TRIGGER ClassesRoomsInsertClassesRoomsInsertINSTEAD OF INSERT ON INSTEAD OF INSERT ON classes_roomsclasses_roomsDEC

12、LAREDECLAREv_roomIDv_roomID rooms.room_id%TYPErooms.room_id%TYPE; ;BEGINBEGIN- First determine the room ID- First determine the room IDSELECT SELECT room_idroom_idINTO INTO v_roomIDv_roomIDFROM roomsFROM roomsWHERE building = :WHERE building = :new.buildingnew.buildingAND AND room_idroom_id = : = :n

13、ew.room_idnew.room_id; ;- And now update the class- And now update the classUPDATE CLASSESUPDATE CLASSESSET SET room_idroom_id = = v_roomIDv_roomIDWHERE department = :WHERE department = :new.departmentnew.departmentAND course = :AND course = :new.coursenew.course; ;END END ClassesRoomsInsertClassesR

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

15、creations (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.D

16、ICTIONARY_OBJ_TYPE, SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_OWNER, SYSDATE);END LogCreations;創(chuàng)建觸發(fā)器的通用語法：create table TEMP_TABLEcreate table TEMP_TABLE( ( NUM_COL NUMBER, NUM_COL NUMBER, CHAR_COL VARCHAR2(100) CHAR_COL VARCHAR2(100) )CREATE SEQUENCE CREATE SEQUENCE trig_seqtrig_seqSTART WITH 1STAR

17、T WITH 1INCREMENT BY 1;INCREMENT BY 1;CREATE OR REPLACE PACKAGE CREATE OR REPLACE PACKAGE TrigPackageTrigPackage AS AS- Global counter for use in the triggers- Global counter for use in the triggersv_Counterv_Counter NUMBER; NUMBER;END END TrigPackageTrigPackage; ;CREATE OR REPLACE TRIGGER CREATE OR

18、 REPLACE TRIGGER ClassesBStatementClassesBStatementBEFORE UPDATE ON classBEFORE UPDATE ON classBEGINBEGIN- Reset the counter first.- Reset the counter first.TrigPackage.v_CounterTrigPackage.v_Counter := 0; := 0;INSERT INTO INSERT INTO temp_tabletemp_table ( (num_colnum_col, , char_colchar_col) )VALU

19、ES (VALUES (trig_seq.NEXTVALtrig_seq.NEXTVAL, ,Before Statement: counter = | Before Statement: counter = | TrigPackage.v_CounterTrigPackage.v_Counter););TrigPackage.v_CounterTrigPackage.v_Counter := := TrigPackage.v_CounterTrigPackage.v_Counter + 1; + 1;END END ClassesBStatementClassesBStatement; ;C

20、REATE OR REPLACE TRIGGER ClassesAStatement1CREATE OR REPLACE TRIGGER ClassesAStatement1AFTER UPDATE ON classAFTER UPDATE ON classBEGINBEGININSERT INTO INSERT INTO temp_tabletemp_table ( (num_colnum_col, , char_colchar_col) )VALUES (VALUES (trig_seq.NEXTVALtrig_seq.NEXTVAL, ,After Statement 1: counte

21、r = | After Statement 1: counter = | TrigPackage.v_CounterTrigPackage.v_Counter););- Increment for the next trigger.- Increment for the next trigger.TrigPackage.v_CounterTrigPackage.v_Counter := := TrigPackage.v_CounterTrigPackage.v_Counter + 1; + 1;END ClassesAStatement1;END ClassesAStatement1;CREA

22、TE OR REPLACE TRIGGER 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

23、REPLACE TRIGGER ClassesBRow2BEFORE 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 F

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

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

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

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

28、列子句直接修改視圖中的嵌套列 通過通過 INSTEAD OF 觸發(fā)器進行修改觸發(fā)器進行修改 嵌套表上的觸發(fā)器因嵌套表上的觸發(fā)器因 DML 操作而觸發(fā)操作而觸發(fā) 啟用和禁用觸發(fā)器啟用和禁用觸發(fā)器 語法如下：語法如下：alter trigger enable;alter trigger enable;alter trigger disable;alter trigger disable; 其中，其中，Trigger_nameTrigger_name 是觸發(fā)器名稱。是觸發(fā)器名稱。 刪除觸發(fā)器刪除觸發(fā)器 使用以下語法：使用以下語法：drop trigger ;drop trigger ;其中，其中，t

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