Orcale觸發(fā)器詳解

1、ORACLE PL/SQL編程之八： 把觸發(fā)器說(shuō)透 本篇主要內(nèi)容如下：8.1 觸發(fā)器類(lèi)型8.1.1 DML觸發(fā)器8.1.2 替代觸發(fā)器8.1.3 系統(tǒng)觸發(fā)器8.2 創(chuàng)建觸發(fā)器8.2.1 觸發(fā)器觸發(fā)次序8.2.2 創(chuàng)建DML觸發(fā)器8.2.3 創(chuàng)建替代(INSTEAD OF)觸發(fā)器8.2.3 創(chuàng)建系統(tǒng)事件觸發(fā)器8.2.4 系統(tǒng)觸發(fā)器事件屬性8.2.5 使用觸發(fā)器謂詞8.2.6 重新編譯觸發(fā)器8.3 刪除和使能觸發(fā)器8.4 觸發(fā)器和數(shù)據(jù)字典8.5 數(shù)據(jù)庫(kù)觸發(fā)器的應(yīng)用舉例 觸發(fā)器是許多關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)都提供的一項(xiàng)技術(shù)。在ORACLE系統(tǒng)里，觸發(fā)器類(lèi)似過(guò)程和函數(shù)，都有聲明，執(zhí)行和異常處理過(guò)程的PL/SQL

2、塊。8.1 觸發(fā)器類(lèi)型 觸發(fā)器在數(shù)據(jù)庫(kù)里以獨(dú)立的對(duì)象存儲(chǔ)，它與存儲(chǔ)過(guò)程和函數(shù)不同的是，存儲(chǔ)過(guò)程與函數(shù)需要用戶(hù)顯示調(diào)用才執(zhí)行，而觸發(fā)器是由一個(gè)事件來(lái)啟動(dòng)運(yùn)行。即觸發(fā)器是當(dāng)某個(gè)事件發(fā)生時(shí)自動(dòng)地隱式運(yùn)行。并且，觸發(fā)器不能接收參數(shù)。所以運(yùn)行觸發(fā)器就叫觸發(fā)或點(diǎn)火（firing）。ORACLE事件指的是對(duì)數(shù)據(jù)庫(kù)的表進(jìn)行的INSERT、UPDATE及DELETE操作或?qū)σ晥D進(jìn)行類(lèi)似的操作。ORACLE將觸發(fā)器的功能擴(kuò)展到了觸發(fā)ORACLE，如數(shù)據(jù)庫(kù)的啟動(dòng)與關(guān)閉等。所以觸發(fā)器常用來(lái)完成由數(shù)據(jù)庫(kù)的完整性約束難以完成的復(fù)雜業(yè)務(wù)規(guī)則的約束，或用來(lái)監(jiān)視對(duì)數(shù)據(jù)庫(kù)的各種操作，實(shí)現(xiàn)審計(jì)的功能。 8.1.1 DML觸發(fā)器 O

3、RACLE可以在DML語(yǔ)句進(jìn)行觸發(fā)，可以在DML操作前或操作后進(jìn)行觸發(fā)，并且可以對(duì)每個(gè)行或語(yǔ)句操作上進(jìn)行觸發(fā)。 8.1.2 替代觸發(fā)器 由于在ORACLE里，不能直接對(duì)由兩個(gè)以上的表建立的視圖進(jìn)行操作。所以給出了替代觸發(fā)器。它就是ORACLE 8專(zhuān)門(mén)為進(jìn)行視圖操作的一種處理方法。 8.1.3 系統(tǒng)觸發(fā)器ORACLE 8i 提供了第三種類(lèi)型的觸發(fā)器叫系統(tǒng)觸發(fā)器。它可以在ORACLE數(shù)據(jù)庫(kù)系統(tǒng)的事件中進(jìn)行觸發(fā)，如ORACLE系統(tǒng)的啟動(dòng)與關(guān)閉等。 觸發(fā)器組成: l 觸發(fā)事件：引起觸發(fā)器被觸發(fā)的事件。 例如：DML語(yǔ)句(INSERT, UPDATE, DELETE語(yǔ)句對(duì)表或視圖執(zhí)行數(shù)據(jù)處理操作)、DD

4、L語(yǔ)句（如CREATE、ALTER、DROP語(yǔ)句在數(shù)據(jù)庫(kù)中創(chuàng)建、修改、刪除模式對(duì)象）、數(shù)據(jù)庫(kù)系統(tǒng)事件（如系統(tǒng)啟動(dòng)或退出、異常錯(cuò)誤）、用戶(hù)事件（如登錄或退出數(shù)據(jù)庫(kù)）。l 觸發(fā)時(shí)間：即該TRIGGER 是在觸發(fā)事件發(fā)生之前（BEFORE）還是之后(AFTER)觸發(fā)，也就是觸發(fā)事件和該TRIGGER 的操作順序。l 觸發(fā)操作：即該TRIGGER 被觸發(fā)之后的目的和意圖，正是觸發(fā)器本身要做的事情。 例如：PL/SQL 塊。l 觸發(fā)對(duì)象：包括表、視圖、模式、數(shù)據(jù)庫(kù)。只有在這些對(duì)象上發(fā)生了符合觸發(fā)條件的觸發(fā)事件，才會(huì)執(zhí)行觸發(fā)操作。l 觸發(fā)條件：由WHEN子句指定一個(gè)邏輯表達(dá)式。只有當(dāng)該表達(dá)式的值為T(mén)RUE

5、時(shí)，遇到觸發(fā)事件才會(huì)自動(dòng)執(zhí)行觸發(fā)器，使其執(zhí)行觸發(fā)操作。l 觸發(fā)頻率：說(shuō)明觸發(fā)器內(nèi)定義的動(dòng)作被執(zhí)行的次數(shù)。即語(yǔ)句級(jí)(STATEMENT)觸發(fā)器和行級(jí)(ROW)觸發(fā)器。語(yǔ)句級(jí)(STATEMENT)觸發(fā)器：是指當(dāng)某觸發(fā)事件發(fā)生時(shí)，該觸發(fā)器只執(zhí)行一次；行級(jí)(ROW)觸發(fā)器：是指當(dāng)某觸發(fā)事件發(fā)生時(shí)，對(duì)受到該操作影響的每一行數(shù)據(jù)，觸發(fā)器都單獨(dú)執(zhí)行一次。編寫(xiě)觸發(fā)器時(shí)，需要注意以下幾點(diǎn)：l 觸發(fā)器不接受參數(shù)。l 一個(gè)表上最多可有12個(gè)觸發(fā)器，但同一時(shí)間、同一事件、同一類(lèi)型的觸發(fā)器只能有一個(gè)。并各觸發(fā)器之間不能有矛盾。l 在一個(gè)表上的觸發(fā)器越多，對(duì)在該表上的DML操作的性能影響就越大。l 觸發(fā)器最大為32KB。

6、若確實(shí)需要，可以先建立過(guò)程，然后在觸發(fā)器中用CALL語(yǔ)句進(jìn)行調(diào)用。l 在觸發(fā)器的執(zhí)行部分只能用DML語(yǔ)句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL語(yǔ)句（CREATE、ALTER、DROP）。l 觸發(fā)器中不能包含事務(wù)控制語(yǔ)句(COMMIT，ROLLBACK，SAVEPOINT)。因?yàn)橛|發(fā)器是觸發(fā)語(yǔ)句的一部分，觸發(fā)語(yǔ)句被提交、回退時(shí)，觸發(fā)器也被提交、回退了。l 在觸發(fā)器主體中調(diào)用的任何過(guò)程、函數(shù)，都不能使用事務(wù)控制語(yǔ)句。l 在觸發(fā)器主體中不能申明任何Long和blob變量。新值new和舊值old也不能是表中的任何long和blob列。l 不同類(lèi)型的觸發(fā)器(如DML觸

7、發(fā)器、INSTEAD OF觸發(fā)器、系統(tǒng)觸發(fā)器)的語(yǔ)法格式和作用有較大區(qū)別。 8.2 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器的一般語(yǔ)法是: CREATE OR REPLACE TRIGGER trigger_nameBEFORE | AFTER INSERT | DELETE | UPDATE OF column , column OR INSERT | DELETE | UPDATE OF column , column .ON schema.table_name | schema.view_name REFERENCING OLD AS old | NEW AS new| PARENT as parentFO

8、R EACH ROW WHEN conditionPL/SQL_BLOCK | CALL procedure_name; 其中：BEFORE 和AFTER指出觸發(fā)器的觸發(fā)時(shí)序分別為前觸發(fā)和后觸發(fā)方式，前觸發(fā)是在執(zhí)行觸發(fā)事件之前觸發(fā)當(dāng)前所創(chuàng)建的觸發(fā)器，后觸發(fā)是在執(zhí)行觸發(fā)事件之后觸發(fā)當(dāng)前所創(chuàng)建的觸發(fā)器。 FOR EACH ROW選項(xiàng)說(shuō)明觸發(fā)器為行觸發(fā)器。行觸發(fā)器和語(yǔ)句觸發(fā)器的區(qū)別表現(xiàn)在：行觸發(fā)器要求當(dāng)一個(gè)DML語(yǔ)句操作影響數(shù)據(jù)庫(kù)中的多行數(shù)據(jù)時(shí)，對(duì)于其中的每個(gè)數(shù)據(jù)行，只要它們符合觸發(fā)約束條件，均激活一次觸發(fā)器；而語(yǔ)句觸發(fā)器將整個(gè)語(yǔ)句操作作為觸發(fā)事件，當(dāng)它符合約束條件時(shí)，激活一次觸發(fā)器。當(dāng)省略FOR

9、EACH ROW 選項(xiàng)時(shí)，BEFORE 和AFTER 觸發(fā)器為語(yǔ)句觸發(fā)器，而INSTEAD OF 觸發(fā)器則只能為行觸發(fā)器。 REFERENCING 子句說(shuō)明相關(guān)名稱(chēng)，在行觸發(fā)器的PL/SQL塊和WHEN 子句中可以使用相關(guān)名稱(chēng)參照當(dāng)前的新、舊列值，默認(rèn)的相關(guān)名稱(chēng)分別為OLD和NEW。觸發(fā)器的PL/SQL塊中應(yīng)用相關(guān)名稱(chēng)時(shí)，必須在它們之前加冒號(hào)(:)，但在WHEN子句中則不能加冒號(hào)。WHEN 子句說(shuō)明觸發(fā)約束條件。Condition 為一個(gè)邏輯表達(dá)時(shí)，其中必須包含相關(guān)名稱(chēng)，而不能包含查詢(xún)語(yǔ)句，也不能調(diào)用PL/SQL 函數(shù)。WHEN 子句指定的觸發(fā)約束條件只能用在BEFORE 和AFTER 行觸發(fā)

10、器中，不能用在INSTEAD OF 行觸發(fā)器和其它類(lèi)型的觸發(fā)器中。 當(dāng)一個(gè)基表被修改( INSERT, UPDATE, DELETE)時(shí)要執(zhí)行的存儲(chǔ)過(guò)程，執(zhí)行時(shí)根據(jù)其所依附的基表改動(dòng)而自動(dòng)觸發(fā)，因此與應(yīng)用程序無(wú)關(guān)，用數(shù)據(jù)庫(kù)觸發(fā)器可以保證數(shù)據(jù)的一致性和完整性。 每張表最多可建立12 種類(lèi)型的觸發(fā)器，它們是:BEFORE INSERTBEFORE INSERT FOR EACH ROWAFTER INSERTAFTER INSERT FOR EACH ROW BEFORE UPDATEBEFORE UPDATE FOR EACH ROWAFTER UPDATEAFTER UPDATE FOR EA

11、CH ROW BEFORE DELETEBEFORE DELETE FOR EACH ROWAFTER DELETEAFTER DELETE FOR EACH ROW 8.2.1 觸發(fā)器觸發(fā)次序1. 執(zhí)行 BEFORE語(yǔ)句級(jí)觸發(fā)器;2. 對(duì)與受語(yǔ)句影響的每一行：l 執(zhí)行 BEFORE行級(jí)觸發(fā)器l 執(zhí)行 DML語(yǔ)句l 執(zhí)行 AFTER行級(jí)觸發(fā)器 3. 執(zhí)行 AFTER語(yǔ)句級(jí)觸發(fā)器 8.2.2 創(chuàng)建DML觸發(fā)器 觸發(fā)器名與過(guò)程名和包的名字不一樣，它是單獨(dú)的名字空間，因而觸發(fā)器名可以和表或過(guò)程有相同的名字，但在一個(gè)模式中觸發(fā)器名不能相同。 DML觸發(fā)器的限制l CREATE TRIGGER語(yǔ)句文本的

12、字符長(zhǎng)度不能超過(guò)32KB；l 觸發(fā)器體內(nèi)的SELECT 語(yǔ)句只能為SELECT INTO 結(jié)構(gòu)，或者為定義游標(biāo)所使用的SELECT 語(yǔ)句。l 觸發(fā)器中不能使用數(shù)據(jù)庫(kù)事務(wù)控制語(yǔ)句 COMMIT; ROLLBACK, SVAEPOINT 語(yǔ)句；l 由觸發(fā)器所調(diào)用的過(guò)程或函數(shù)也不能使用數(shù)據(jù)庫(kù)事務(wù)控制語(yǔ)句；l 觸發(fā)器中不能使用LONG, LONG RAW 類(lèi)型；l 觸發(fā)器內(nèi)可以參照LOB 類(lèi)型列的列值，但不能通過(guò) :NEW 修改LOB列中的數(shù)據(jù)； DML觸發(fā)器基本要點(diǎn)l 觸發(fā)時(shí)機(jī)：指定觸發(fā)器的觸發(fā)時(shí)間。如果指定為BEFORE，則表示在執(zhí)行DML操作之前觸發(fā)，以便防止某些錯(cuò)誤操作發(fā)生或?qū)崿F(xiàn)某些業(yè)務(wù)規(guī)則；

13、如果指定為AFTER，則表示在執(zhí)行DML操作之后觸發(fā)，以便記錄該操作或做某些事后處理。l 觸發(fā)事件：引起觸發(fā)器被觸發(fā)的事件，即DML操作（INSERT、UPDATE、DELETE）。既可以是單個(gè)觸發(fā)事件，也可以是多個(gè)觸發(fā)事件的組合（只能使用OR邏輯組合，不能使用AND邏輯組合）。l 條件謂詞：當(dāng)在觸發(fā)器中包含多個(gè)觸發(fā)事件（INSERT、UPDATE、DELETE）的組合時(shí)，為了分別針對(duì)不同的事件進(jìn)行不同的處理，需要使用ORACLE提供的如下條件謂詞。1）。INSERTING：當(dāng)觸發(fā)事件是INSERT時(shí)，取值為T(mén)RUE，否則為FALSE。2）。UPDATING （column_1,column_

14、2,column_x）：當(dāng)觸發(fā)事件是UPDATE 時(shí)，如果修改了column_x列，則取值為T(mén)RUE，否則為FALSE。其中column_x是可選的。3）。DELETING：當(dāng)觸發(fā)事件是DELETE時(shí)，則取值為T(mén)RUE，否則為FALSE。解發(fā)對(duì)象：指定觸發(fā)器是創(chuàng)建在哪個(gè)表、視圖上。l 觸發(fā)類(lèi)型：是語(yǔ)句級(jí)還是行級(jí)觸發(fā)器。l 觸發(fā)條件：由WHEN子句指定一個(gè)邏輯表達(dá)式，只允許在行級(jí)觸發(fā)器上指定觸發(fā)條件，指定UPDATING后面的列的列表。 問(wèn)題：當(dāng)觸發(fā)器被觸發(fā)時(shí)，要使用被插入、更新或刪除的記錄中的列值，有時(shí)要使用操作前、 后列的值.實(shí)現(xiàn): :NEW 修飾符訪(fǎng)問(wèn)操作完成后列的值 :OLD 修飾符訪(fǎng)問(wèn)

15、操作完成前列的值 特性INSERTUPDATEDELETEOLDNULL實(shí)際值實(shí)際值NEW實(shí)際值實(shí)際值NULL 例1: 建立一個(gè)觸發(fā)器, 當(dāng)職工表 emp 表被刪除一條記錄時(shí)，把被刪除記錄寫(xiě)到職工表刪除日志表中去。 CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2; CREATE OR REPLACE TRIGGER tr_del_emp BEFORE DELETE -指定觸發(fā)時(shí)機(jī)為刪除操作前觸發(fā) ON scott.emp FOR EACH ROW -說(shuō)明創(chuàng)建的是行級(jí)觸發(fā)器 BEGIN -將修改前數(shù)據(jù)插入到日志記錄表 del_emp ,以供

16、監(jiān)督使用。 INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate ) VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :m, :old.hiredate );END;DELETE emp WHERE empno=7788;DROP TABLE emp_his;DROP TRIGGER del_emp; 例2：限制對(duì)Departments表修改（包括INSERT,DELETE,UPDATE）的時(shí)間

17、范圍，即不允許在非工作時(shí)間修改departments表。 CREATE OR REPLACE TRIGGER tr_dept_timeBEFORE INSERT OR DELETE OR UPDATE ON departmentsBEGINIF (TO_CHAR(sysdate,DAY) IN (星期六, 星期日) OR (TO_CHAR(sysdate, HH24:MI) NOT BETWEEN 08:30 AND 18:00) THEN RAISE_APPLICATION_ERROR(-20001, 不是上班時(shí)間，不能修改departments表);END IF;END; 例3：限定只對(duì)部

18、門(mén)號(hào)為80的記錄進(jìn)行行觸發(fā)器操作。 CREATE OR REPLACE TRIGGER tr_emp_sal_commBEFORE UPDATE OF salary, commission_pct OR DELETEON HR.employeesFOR EACH ROWWHEN (old.department_id = 80)BEGINCASE WHEN UPDATING (salary) THEN IF :NEW.salary :old.salary THEN RAISE_APPLICATION_ERROR(-20001, 部門(mén)80的人員的工資不能降); END IF; WHEN UPDAT

19、ING (commission_pct) THEN IF :NEW.commission_pct DELETE FROM emp_view WHERE deptno=10;DELETE FROM emp_view WHERE deptno=10 ERROR 位于第 1 行:ORA-01732: 此視圖的數(shù)據(jù)操縱操作非法 但是我們可以創(chuàng)建INSTEAD_OF觸發(fā)器來(lái)為 DELETE 操作執(zhí)行所需的處理，即刪除EMP表中所有基準(zhǔn)行： CREATE OR REPLACE TRIGGER emp_view_delete INSTEAD OF DELETE ON emp_view FOR EACH RO

20、WBEGIN DELETE FROM emp WHERE deptno= :old.deptno;END emp_view_delete; DELETE FROM emp_view WHERE deptno=10; DROP TRIGGER emp_view_delete;DROP VIEW emp_view; 例2：創(chuàng)建復(fù)雜視圖，針對(duì)INSERT操作創(chuàng)建INSTEAD OF觸發(fā)器，向復(fù)雜視圖插入數(shù)據(jù)。l 創(chuàng)建視圖： CREATE OR REPLACE FORCE VIEW HR.V_REG_COU (R_ID, R_NAME, C_ID, C_NAME)ASSELECT r.region_i

21、d, r.region_name, c.country_id, c.country_nameFROM regions r, countries cWHERE r.region_id = c.region_id; l 創(chuàng)建觸發(fā)器： CREATE OR REPLACE TRIGGER HR.TR_I_O_REG_COU INSTEAD OFINSERT ON v_reg_cou FOR EACH ROW DECLARE v_count NUMBER;BEGINSELECT COUNT(*) INTO v_count FROM regions WHERE region_id = :new.r_id;

22、IF v_count = 0 THEN INSERT INTO regions (region_id, region_name ) VALUES (:new.r_id, :new.r_name );END IF;SELECT COUNT(*) INTO v_count FROM countries WHERE country_id = :new.c_id;IF v_count = 0 THEN INSERT INTO countries ( country_id, country_name, region_id ) VALUES ( :new.c_id, :new.c_name, :new.r

23、_id );END IF;END; 創(chuàng)建INSTEAD OF觸發(fā)器需要注意以下幾點(diǎn)：l 只能被創(chuàng)建在視圖上，并且該視圖沒(méi)有指定WITH CHECK OPTION選項(xiàng)。l 不能指定BEFORE 或 AFTER選項(xiàng)。l FOR EACH ROW子可是可選的，即INSTEAD OF觸發(fā)器只能在行級(jí)上觸發(fā)、或只能是行級(jí)觸發(fā)器，沒(méi)有必要指定。l 沒(méi)有必要在針對(duì)一個(gè)表的視圖上創(chuàng)建INSTEAD OF觸發(fā)器，只要?jiǎng)?chuàng)建DML觸發(fā)器就可以了。 8.2.3 創(chuàng)建系統(tǒng)事件觸發(fā)器 ORACLE10G提供的系統(tǒng)事件觸發(fā)器可以在DDL或數(shù)據(jù)庫(kù)系統(tǒng)上被觸發(fā)。DDL指的是數(shù)據(jù)定義語(yǔ)言，如CREATE 、ALTER及DROP

24、等。而數(shù)據(jù)庫(kù)系統(tǒng)事件包括數(shù)據(jù)庫(kù)服務(wù)器的啟動(dòng)或關(guān)閉，用戶(hù)的登錄與退出、數(shù)據(jù)庫(kù)服務(wù)錯(cuò)誤等。創(chuàng)建系統(tǒng)觸發(fā)器的語(yǔ)法如下： 創(chuàng)建觸發(fā)器的一般語(yǔ)法是: CREATE OR REPLACE TRIGGER sachema.trigger_nameBEFORE|AFTER ddl_event_list | database_event_listON DATABASE | schema.SCHEMA WHEN conditionPL/SQL_block | CALL procedure_name; 其中: ddl_event_list：一個(gè)或多個(gè)DDL 事件，事件間用 OR 分開(kāi)； database_event

25、_list：一個(gè)或多個(gè)數(shù)據(jù)庫(kù)事件，事件間用 OR 分開(kāi)； 系統(tǒng)事件觸發(fā)器既可以建立在一個(gè)模式上，又可以建立在整個(gè)數(shù)據(jù)庫(kù)上。當(dāng)建立在模式(SCHEMA)之上時(shí)，只有模式所指定用戶(hù)的DDL操作和它們所導(dǎo)致的錯(cuò)誤才激活觸發(fā)器, 默認(rèn)時(shí)為當(dāng)前用戶(hù)模式。當(dāng)建立在數(shù)據(jù)庫(kù)(DATABASE)之上時(shí)，該數(shù)據(jù)庫(kù)所有用戶(hù)的DDL操作和他們所導(dǎo)致的錯(cuò)誤，以及數(shù)據(jù)庫(kù)的啟動(dòng)和關(guān)閉均可激活觸發(fā)器。要在數(shù)據(jù)庫(kù)之上建立觸發(fā)器時(shí)，要求用戶(hù)具有ADMINISTER DATABASE TRIGGER權(quán)限。 下面給出系統(tǒng)觸發(fā)器的種類(lèi)和事件出現(xiàn)的時(shí)機(jī)（前或后）：事件允許的時(shí)機(jī)說(shuō)明STARTUPAFTER啟動(dòng)數(shù)據(jù)庫(kù)實(shí)例之后觸發(fā)SHUTD

26、OWNBEFORE關(guān)閉數(shù)據(jù)庫(kù)實(shí)例之前觸發(fā)（非正常關(guān)閉不觸發(fā)）SERVERERRORAFTER數(shù)據(jù)庫(kù)服務(wù)器發(fā)生錯(cuò)誤之后觸發(fā)LOGONAFTER成功登錄連接到數(shù)據(jù)庫(kù)后觸發(fā)LOGOFFBEFORE開(kāi)始斷開(kāi)數(shù)據(jù)庫(kù)連接之前觸發(fā)CREATEBEFORE，AFTER在執(zhí)行CREATE語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象之前、之后觸發(fā)DROPBEFORE，AFTER在執(zhí)行DROP語(yǔ)句刪除數(shù)據(jù)庫(kù)對(duì)象之前、之后觸發(fā)ALTERBEFORE，AFTER在執(zhí)行ALTER語(yǔ)句更新數(shù)據(jù)庫(kù)對(duì)象之前、之后觸發(fā)DDLBEFORE，AFTER在執(zhí)行大多數(shù)DDL語(yǔ)句之前、之后觸發(fā)GRANTBEFORE，AFTER執(zhí)行GRANT語(yǔ)句授予權(quán)限之前、之后觸

27、發(fā)REVOKEBEFORE，AFTER執(zhí)行REVOKE語(yǔ)句收權(quán)限之前、之后觸犯發(fā)RENAMEBEFORE，AFTER執(zhí)行RENAME語(yǔ)句更改數(shù)據(jù)庫(kù)對(duì)象名稱(chēng)之前、之后觸犯發(fā)AUDIT / NOAUDITBEFORE，AFTER執(zhí)行AUDIT或NOAUDIT進(jìn)行審計(jì)或停止審計(jì)之前、之后觸發(fā) 8.2.4 系統(tǒng)觸發(fā)器事件屬性 事件屬性事件Startup/ShutdownServererrorLogon/LogoffDDLDML事件名稱(chēng)數(shù)據(jù)庫(kù)名稱(chēng) 數(shù)據(jù)庫(kù)實(shí)例號(hào) 錯(cuò)誤號(hào) 用戶(hù)名 模式對(duì)象類(lèi)型 模式對(duì)象名稱(chēng) 列 除DML語(yǔ)句的列屬性外，其余事件屬性值可通過(guò)調(diào)用ORACLE定義的事件屬性函數(shù)來(lái)讀取。函數(shù)名稱(chēng)數(shù)

28、據(jù)類(lèi)型說(shuō) 明Ora_syseventVARCHAR2（20）激活觸發(fā)器的事件名稱(chēng)Instance_numNUMBER數(shù)據(jù)庫(kù)實(shí)例名Ora_database_nameVARCHAR2（50）數(shù)據(jù)庫(kù)名稱(chēng)Server_error(posi)NUMBER錯(cuò)誤信息棧中posi指定位置中的錯(cuò)誤號(hào) Is_servererror(err_number) BOOLEAN檢查err_number指定的錯(cuò)誤號(hào)是否在錯(cuò)誤信息棧中，如果在則返回TRUE，否則返回FALSE。在觸發(fā)器內(nèi)調(diào)用此函數(shù)可以判斷是否發(fā)生指定的錯(cuò)誤。Login_userVARCHAR2(30)登陸或注銷(xiāo)的用戶(hù)名稱(chēng)Dictionary_obj_type

29、VARCHAR2(20)DDL語(yǔ)句所操作的數(shù)據(jù)庫(kù)對(duì)象類(lèi)型Dictionary_obj_nameVARCHAR2(30)DDL語(yǔ)句所操作的數(shù)據(jù)庫(kù)對(duì)象名稱(chēng)Dictionary_obj_ownerVARCHAR2(30)DDL語(yǔ)句所操作的數(shù)據(jù)庫(kù)對(duì)象所有者名稱(chēng)Des_encrypted_passwordVARCHAR2(2)正在創(chuàng)建或修改的經(jīng)過(guò)DES算法加密的用戶(hù)口令 例1：創(chuàng)建觸發(fā)器，存放有關(guān)事件信息。DESC ora_syseventDESC ora_login_user-創(chuàng)建用于記錄事件用的表CREATE TABLE ddl_event(crt_date timestamp PRIMARY KE

30、Y, event_name VARCHAR2(20), user_name VARCHAR2(10), obj_type VARCHAR2(20), obj_name VARCHAR2(20);-創(chuàng)建觸犯發(fā)器CREATE OR REPLACE TRIGGER tr_ddlAFTER DDL ON SCHEMABEGIN INSERT INTO ddl_event VALUES (systimestamp,ora_sysevent, ora_login_user, ora_dict_obj_type, ora_dict_obj_name);END tr_ddl; 例2：創(chuàng)建登錄、退出觸發(fā)器。 C

31、REATE TABLE log_event(user_name VARCHAR2(10), address VARCHAR2(20), logon_date timestamp, logoff_date timestamp); -創(chuàng)建登錄觸發(fā)器CREATE OR REPLACE TRIGGER tr_logonAFTER LOGON ON DATABASEBEGIN INSERT INTO log_event (user_name, address, logon_date) VALUES (ora_login_user, ora_client_ip_address, systimestamp)

32、;END tr_logon;-創(chuàng)建退出觸發(fā)器CREATE OR REPLACE TRIGGER tr_logoffBEFORE LOGOFF ON DATABASEBEGIN INSERT INTO log_event (user_name, address, logoff_date) VALUES (ora_login_user, ora_client_ip_address, systimestamp);END tr_logoff; 8.2.5 使用觸發(fā)器謂詞 ORACLE 提供三個(gè)參數(shù)INSERTING, UPDATING,DELETING 用于判斷觸發(fā)了哪些操作。謂詞行為INSERTIN

33、G如果觸發(fā)語(yǔ)句是 INSERT 語(yǔ)句，則為T(mén)RUE,否則為FALSEUPDATING如果觸發(fā)語(yǔ)句是 UPDATE語(yǔ)句，則為T(mén)RUE,否則為FALSEDELETING如果觸發(fā)語(yǔ)句是 DELETE 語(yǔ)句，則為T(mén)RUE,否則為FALSE 8.2.6 重新編譯觸發(fā)器如果在觸發(fā)器內(nèi)調(diào)用其它函數(shù)或過(guò)程，當(dāng)這些函數(shù)或過(guò)程被刪除或修改后，觸發(fā)器的狀態(tài)將被標(biāo)識(shí)為無(wú)效。當(dāng)DML語(yǔ)句激活一個(gè)無(wú)效觸發(fā)器時(shí)，ORACLE將重新編譯觸發(fā)器代碼，如果編譯時(shí)發(fā)現(xiàn)錯(cuò)誤，這將導(dǎo)致DML語(yǔ)句執(zhí)行失敗。在PL/SQL程序中可以調(diào)用ALTER TRIGGER語(yǔ)句重新編譯已經(jīng)創(chuàng)建的觸發(fā)器，格式為： ALTER TRIGGER schem

34、a. trigger_name COMPILE DEBUG 其中：DEBUG 選項(xiàng)要器編譯器生成PL/SQL 程序條使其所使用的調(diào)試代碼。8.3 刪除和使能觸發(fā)器l 刪除觸發(fā)器：DROP TRIGGER trigger_name;當(dāng)刪除其他用戶(hù)模式中的觸發(fā)器名稱(chēng)，需要具有DROP ANY TRIGGER系統(tǒng)權(quán)限，當(dāng)刪除建立在數(shù)據(jù)庫(kù)上的觸發(fā)器時(shí)，用戶(hù)需要具有ADMINISTER DATABASE TRIGGER系統(tǒng)權(quán)限。此外，當(dāng)刪除表或視圖時(shí)，建立在這些對(duì)象上的觸發(fā)器也隨之刪除。 l 禁用或啟用觸發(fā)器數(shù)據(jù)庫(kù)TRIGGER 的狀態(tài)：有效狀態(tài)(ENABLE)：當(dāng)觸發(fā)事件發(fā)生時(shí)，處于有效狀態(tài)的數(shù)據(jù)庫(kù)觸

35、發(fā)器TRIGGER 將被觸發(fā)。無(wú)效狀態(tài)(DISABLE)：當(dāng)觸發(fā)事件發(fā)生時(shí)，處于無(wú)效狀態(tài)的數(shù)據(jù)庫(kù)觸發(fā)器TRIGGER 將不會(huì)被觸發(fā)，此時(shí)就跟沒(méi)有這個(gè)數(shù)據(jù)庫(kù)觸發(fā)器(TRIGGER) 一樣。數(shù)據(jù)庫(kù)TRIGGER的這兩種狀態(tài)可以互相轉(zhuǎn)換。格式為：ALTER TIGGER trigger_name DISABLE | ENABLE ;-例：ALTER TRIGGER emp_view_delete DISABLE; ALTER TRIGGER語(yǔ)句一次只能改變一個(gè)觸發(fā)器的狀態(tài)，而ALTER TABLE語(yǔ)句則一次能夠改變與指定表相關(guān)的所有觸發(fā)器的使用狀態(tài)。格式為： ALTER TABLE schema.t

36、able_name ENABLE|DISABLE ALL TRIGGERS;-例：使表EMP 上的所有TRIGGER 失效：ALTER TABLE emp DISABLE ALL TRIGGERS; 8.4 觸發(fā)器和數(shù)據(jù)字典相關(guān)數(shù)據(jù)字典：USER_TRIGGERS、ALL_TRIGGERS、DBA_TRIGGERS SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, BASE_OBJECT_TYPE, REFERENCING_NAMES, STATUS, ACTION_TYPEFROM user_triggers;

37、 8.5 數(shù)據(jù)庫(kù)觸發(fā)器的應(yīng)用舉例例1：創(chuàng)建一個(gè)DML語(yǔ)句級(jí)觸發(fā)器，當(dāng)對(duì)emp表執(zhí)行INSERT, UPDATE, DELETE 操作時(shí)，它自動(dòng)更新dept_summary 表中的數(shù)據(jù)。由于在PL/SQL塊中不能直接調(diào)用DDL語(yǔ)句，所以，利用ORACLE內(nèi)置包DBMS_UTILITY中的EXEC_DDL_STATEMENT過(guò)程，由它執(zhí)行DDL語(yǔ)句創(chuàng)建觸發(fā)器。 CREATE TABLE dept_summary( Deptno NUMBER(2), Sal_sum NUMBER(9, 2), Emp_count NUMBER); INSERT INTO dept_summary(deptno, s

38、al_sum, emp_count)SELECT deptno, SUM(sal), COUNT(*) FROM emp GROUP BY deptno;-創(chuàng)建一個(gè)PL/SQL過(guò)程disp_dept_summary-在觸發(fā)器中調(diào)用該過(guò)程顯示dept_summary標(biāo)中的數(shù)據(jù)。CREATE OR REPLACE PROCEDURE disp_dept_summaryIS Rec dept_summary%ROWTYPE;CURSOR c1 IS SELECT * FROM dept_summary;BEGINOPEN c1;FETCH c1 INTO REC; DBMS_OUTPUT.PUT_L

39、INE(deptno sal_sum emp_count); DBMS_OUTPUT.PUT_LINE(-);WHILE c1%FOUND LOOP DBMS_OUTPUT.PUT_LINE(RPAD(rec.deptno, 6)| To_char(rec.sal_sum, $999,999.99)| LPAD(rec.emp_count, 13); FETCH c1 INTO rec;END LOOP;CLOSE c1;END;BEGIN DBMS_OUTPUT.PUT_LINE(插入前); Disp_dept_summary(); DBMS_UTILITY.EXEC_DDL_STATEME

40、NT( CREATE OR REPLACE TRIGGER trig1 AFTER INSERT OR DELETE OR UPDATE OF sal ON emp BEGIN DBMS_OUTPUT.PUT_LINE(正在執(zhí)行trig1 觸發(fā)器); DELETE FROM dept_summary; INSERT INTO dept_summary(deptno, sal_sum, emp_count) SELECT deptno, SUM(sal), COUNT(*) FROM emp GROUP BY deptno; END; );INSERT INTO dept(deptno, dna

41、me, loc) VALUES(90, demo_dept, none_loc);INSERT INTO emp(ename, deptno, empno, sal)VALUES(USER, 90, 9999, 3000); DBMS_OUTPUT.PUT_LINE(插入后); Disp_dept_summary();UPDATE emp SET sal=1000 WHERE empno=9999; DBMS_OUTPUT.PUT_LINE(修改后); Disp_dept_summary();DELETE FROM emp WHERE empno=9999;DELETE FROM dept W

42、HERE deptno=90; DBMS_OUTPUT.PUT_LINE(刪除后); Disp_dept_summary(); DBMS_UTILITY.EXEC_DDL_STATEMENT(DROP TRIGGER trig1);EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE|-|SQLERRM);END; 例2：創(chuàng)建DML語(yǔ)句行級(jí)觸發(fā)器。當(dāng)對(duì)emp表執(zhí)行INSERT, UPDATE, DELETE 操作時(shí)，它自動(dòng)更新dept_summary 表中的數(shù)據(jù)。由于在PL/SQL塊中不能直接調(diào)用DDL語(yǔ)句，所以，利用ORACLE內(nèi)置包

43、DBMS_UTILITY中的EXEC_DDL_STATEMENT過(guò)程，由它執(zhí)行DDL語(yǔ)句創(chuàng)建觸發(fā)器。 BEGIN DBMS_OUTPUT.PUT_LINE(插入前); Disp_dept_summary(); DBMS_UTILITY.EXEC_DDL_STATEMENT( CREATE OR REPLACE TRIGGER trig2_update AFTER UPDATE OF sal ON emp REFERENCING OLD AS old_emp NEW AS new_emp FOR EACH ROW WHEN (old_emp.sal != new_emp.sal) BEGIN D

44、BMS_OUTPUT.PUT_LINE(正在執(zhí)行trig2_update 觸發(fā)器); DBMS_OUTPUT.PUT_LINE(sal 舊值：| :old_emp.sal); DBMS_OUTPUT.PUT_LINE(sal 新值：| :new_emp.sal); UPDATE dept_summary SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal WHERE deptno = :new_emp.deptno; END; ); DBMS_UTILITY.EXEC_DDL_STATEMENT( CREATE OR REPLACE TRIGGER trig2_insert AFTER INSERT ON emp REFERENCING NEW AS new_emp FOR EACH ROW DECLARE I NUMBER; BEGIN DBMS_OUTPUT.