轉(zhuǎn)載自http://blog.csdn.net/indexman/article/details/8023740/
觸發(fā)器是許多關(guān)系數(shù)據(jù)庫系統(tǒng)都提供的一項技術(shù)。在oracle系統(tǒng)里，觸發(fā)器類似過程和函數(shù)，都有聲明，執(zhí)行和異常處理過程的PL/SQL塊。
8.1?觸發(fā)器類型
??? 觸發(fā)器在數(shù)據(jù)庫里以獨立的對象存儲，它與存儲過程和函數(shù)不同的是，存儲過程與函數(shù)需要用戶顯示調(diào)用才執(zhí)行，而觸發(fā)器是由一個事件來啟動運行。即觸發(fā)器是當某個事件發(fā)生時自動地隱式運行。并且，觸發(fā)器不能接收參數(shù)。所以運行觸發(fā)器就叫觸發(fā)或點火（firing）。ORACLE事件指的是對數(shù)據(jù)庫的表進行的INSERT、UPDATE及DELETE操作或?qū)σ晥D進行類似的操作。ORACLE將觸發(fā)器的功能擴展到了觸發(fā)ORACLE，如數(shù)據(jù)庫的啟動與關(guān)閉等。所以觸發(fā)器常用來完成由數(shù)據(jù)庫的完整性約束難以完成的復(fù)雜業(yè)務(wù)規(guī)則的約束，或用來監(jiān)視對數(shù)據(jù)庫的各種操作，實現(xiàn)審計的功能。
8.1.1 DML觸發(fā)器
??? ORACLE可以在DML語句進行觸發(fā)，可以在DML操作前或操作后進行觸發(fā)，并且可以對每個行或語句操作上進行觸發(fā)。
8.1.2 替代觸發(fā)器
??? 由于在ORACLE里，不能直接對由兩個以上的表建立的視圖進行操作。所以給出了替代觸發(fā)器。它就是ORACLE 8專門為進行視圖操作的一種處理方法。
8.1.3 系統(tǒng)觸發(fā)器
ORACLE 8i 提供了第三種類型的觸發(fā)器叫系統(tǒng)觸發(fā)器。它可以在ORACLE數(shù)據(jù)庫系統(tǒng)的事件中進行觸發(fā)，如ORACLE系統(tǒng)的啟動與關(guān)閉等。
觸發(fā)器組成:?
l?????????觸發(fā)事件：引起觸發(fā)器被觸發(fā)的事件。 例如：DML語句(INSERT, UPDATE, DELETE語句對表或視圖執(zhí)行數(shù)據(jù)處理操作)、DDL語句（如CREATE、ALTER、DROP語句在數(shù)據(jù)庫中創(chuàng)建、修改、刪除模式對象）、數(shù)據(jù)庫系統(tǒng)事件（如系統(tǒng)啟動或退出、異常錯誤）、用戶事件（如登錄或退出數(shù)據(jù)庫）。
l?????????觸發(fā)時間：即該TRIGGER 是在觸發(fā)事件發(fā)生之前（BEFORE）還是之后(AFTER)觸發(fā)，也就是觸發(fā)事件和該TRIGGER 的操作順序。
l?????????觸發(fā)操作：即該TRIGGER 被觸發(fā)之后的目的和意圖，正是觸發(fā)器本身要做的事情。 例如：PL/SQL 塊。
l?????????觸發(fā)對象：包括表、視圖、模式、數(shù)據(jù)庫。只有在這些對象上發(fā)生了符合觸發(fā)條件的觸發(fā)事件，才會執(zhí)行觸發(fā)操作。
l?????????觸發(fā)條件：由WHEN子句指定一個邏輯表達式。只有當該表達式的值為TRUE時，遇到觸發(fā)事件才會自動執(zhí)行觸發(fā)器，使其執(zhí)行觸發(fā)操作。
l?????????觸發(fā)頻率：說明觸發(fā)器內(nèi)定義的動作被執(zhí)行的次數(shù)。即語句級(STATEMENT)觸發(fā)器和行級(ROW)觸發(fā)器。
語句級(STATEMENT)觸發(fā)器：是指當某觸發(fā)事件發(fā)生時，該觸發(fā)器只執(zhí)行一次；
行級(ROW)觸發(fā)器：是指當某觸發(fā)事件發(fā)生時，對受到該操作影響的每一行數(shù)據(jù)，觸發(fā)器都單獨執(zhí)行一次。
編寫觸發(fā)器時，需要注意以下幾點：
l???????? 觸發(fā)器不接受參數(shù)。
l???????? 一個表上最多可有12個觸發(fā)器，但同一時間、同一事件、同一類型的觸發(fā)器只能有一個。并各觸發(fā)器之間不能有矛盾。
l???????? 在一個表上的觸發(fā)器越多，對在該表上的DML操作的性能影響就越大。
l????????觸發(fā)器最大為32KB。若確實需要，可以先建立過程，然后在觸發(fā)器中用CALL語句進行調(diào)用。
l在觸發(fā)器的執(zhí)行部分只能用DML語句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL語句（CREATE、ALTER、DROP）。
l???????? 觸發(fā)器中不能包含事務(wù)控制語句(COMMIT，ROLLBACK，SAVEPOINT)。因為觸發(fā)器是觸發(fā)語句的一部分，觸發(fā)語句被提交、回退時，觸發(fā)器也被提交、回退了。
l???????? 在觸發(fā)器主體中調(diào)用的任何過程、函數(shù)，都不能使用事務(wù)控制語句。
l???????? 在觸發(fā)器主體中不能申明任何Long和blob變量。新值new和舊值old也不能是表中的任何long和blob列。
l???????? 不同類型的觸發(fā)器(如DML觸發(fā)器、INSTEAD OF觸發(fā)器、系統(tǒng)觸發(fā)器)的語法格式和作用有較大區(qū)別。
8.2?創(chuàng)建觸發(fā)器
創(chuàng)建觸發(fā)器的一般語法是:
CREATE?[OR?REPLACE]?TRIGGER?trigger_name
{BEFORE?|?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?parent}]
[FOR?EACH?ROW?]
[WHEN?condition]
PL/SQL_BLOCK?|?CALL?procedure_name;
其中：
BEFORE 和AFTER指出觸發(fā)器的觸發(fā)時序分別為前觸發(fā)和后觸發(fā)方式，前觸發(fā)是在執(zhí)行觸發(fā)事件之前觸發(fā)當前所創(chuàng)建的觸發(fā)器，后觸發(fā)是在執(zhí)行觸發(fā)事件之后觸發(fā)當前所創(chuàng)建的觸發(fā)器。
?????? FOR EACH ROW選項說明觸發(fā)器為行觸發(fā)器。行觸發(fā)器和語句觸發(fā)器的區(qū)別表現(xiàn)在：行觸發(fā)器要求當一個DML語句操作影響數(shù)據(jù)庫中的多行數(shù)據(jù)時，對于其中的每個數(shù)據(jù)行，只要它們符合觸發(fā)約束條件，均激活一次觸發(fā)器；而語句觸發(fā)器將整個語句操作作為觸發(fā)事件，當它符合約束條件時，激活一次觸發(fā)器。當省略FOR EACH ROW 選項時，BEFORE 和AFTER 觸發(fā)器為語句觸發(fā)器，而INSTEAD OF?觸發(fā)器則只能為行觸發(fā)器。
???????????REFERENCING 子句說明相關(guān)名稱，在行觸發(fā)器的PL/SQL塊和WHEN 子句中可以使用相關(guān)名稱參照當前的新、舊列值，默認的相關(guān)名稱分別為OLD和NEW。觸發(fā)器的PL/SQL塊中應(yīng)用相關(guān)名稱時，必須在它們之前加冒號(:)，但在WHEN子句中則不能加冒號。
WHEN 子句說明觸發(fā)約束條件。Condition 為一個邏輯表達時，其中必須包含相關(guān)名稱，而不能包含查詢語句，也不能調(diào)用PL/SQL 函數(shù)。WHEN 子句指定的觸發(fā)約束條件只能用在BEFORE 和AFTER 行觸發(fā)器中，不能用在INSTEAD OF 行觸發(fā)器和其它類型的觸發(fā)器中。
??? 當一個基表被修改( INSERT, UPDATE, DELETE)時要執(zhí)行的存儲過程，執(zhí)行時根據(jù)其所依附的基表改動而自動觸發(fā)，因此與應(yīng)用程序無關(guān)，用數(shù)據(jù)庫觸發(fā)器可以保證數(shù)據(jù)的一致性和完整性。
?
每張表最多可建立12 種類型的觸發(fā)器，它們是:
BEFORE INSERT
BEFORE INSERT FOR EACH ROW
AFTER INSERT
AFTER INSERT FOR EACH ROW
BEFORE UPDATE
BEFORE UPDATE FOR EACH ROW
AFTER UPDATE
AFTER UPDATE FOR EACH ROW
BEFORE DELETE
BEFORE DELETE FOR EACH ROW
AFTER DELETE
AFTER DELETE FOR EACH ROW
8.2.1 觸發(fā)器觸發(fā)次序
1.???????執(zhí)行 BEFORE語句級觸發(fā)器;
2.???????對與受語句影響的每一行：
l???????? 執(zhí)行 BEFORE行級觸發(fā)器
l???????? 執(zhí)行 DML語句
l???????? 執(zhí)行 AFTER行級觸發(fā)器?
3.???????執(zhí)行 AFTER語句級觸發(fā)器
8.2.2 創(chuàng)建DML觸發(fā)器
??? 觸發(fā)器名與過程名和包的名字不一樣，它是單獨的名字空間，因而觸發(fā)器名可以和表或過程有相同的名字，但在一個模式中觸發(fā)器名不能相同。
?
DML觸發(fā)器的限制
l???????? CREATE TRIGGER語句文本的字符長度不能超過32KB；
l???????? 觸發(fā)器體內(nèi)的SELECT 語句只能為SELECT … INTO …結(jié)構(gòu)，或者為定義游標所使用的SELECT 語句。
l???????? 觸發(fā)器中不能使用數(shù)據(jù)庫事務(wù)控制語句 COMMIT; ROLLBACK, SVAEPOINT 語句；
l???????? 由觸發(fā)器所調(diào)用的過程或函數(shù)也不能使用數(shù)據(jù)庫事務(wù)控制語句；
l???????? 觸發(fā)器中不能使用LONG, LONG RAW 類型；
l???????? 觸發(fā)器內(nèi)可以參照LOB 類型列的列值，但不能通過 :NEW 修改LOB列中的數(shù)據(jù)；
?
DML觸發(fā)器基本要點
l觸發(fā)時機：指定觸發(fā)器的觸發(fā)時間。如果指定為BEFORE，則表示在執(zhí)行DML操作之前觸發(fā)，以便防止某些錯誤操作發(fā)生或?qū)崿F(xiàn)某些業(yè)務(wù)規(guī)則；如果指定為AFTER，則表示在執(zhí)行DML操作之后觸發(fā)，以便記錄該操作或做某些事后處理。
l觸發(fā)事件：引起觸發(fā)器被觸發(fā)的事件，即DML操作（INSERT、UPDATE、DELETE）。既可以是單個觸發(fā)事件，也可以是多個觸發(fā)事件的組合（只能使用OR邏輯組合，不能使用AND邏輯組合）。
l條件謂詞：當在觸發(fā)器中包含多個觸發(fā)事件（INSERT、UPDATE、DELETE）的組合時，為了分別針對不同的事件進行不同的處理，需要使用ORACLE提供的如下條件謂詞。
1）。INSERTING：當觸發(fā)事件是INSERT時，取值為TRUE，否則為FALSE。
2）。UPDATING [（column_1,column_2,…,column_x）]：當觸發(fā)事件是UPDATE????? 時，如果修改了column_x列，則取值為TRUE，否則為FALSE。其中column_x是可選的。
3）。DELETING：當觸發(fā)事件是DELETE時，則取值為TRUE，否則為FALSE。
解發(fā)對象：指定觸發(fā)器是創(chuàng)建在哪個表、視圖上。
l觸發(fā)類型：是語句級還是行級觸發(fā)器。
l觸發(fā)條件：由WHEN子句指定一個邏輯表達式，只允許在行級觸發(fā)器上指定觸發(fā)條件，指定UPDATING后面的列的列表。
?
問題：當觸發(fā)器被觸發(fā)時，要使用被插入、更新或刪除的記錄中的列值，有時要使用操作前、??????? 后列的值.
實現(xiàn):??:NEW?修飾符訪問操作完成后列的值
???????:OLD?修飾符訪問操作完成前列的值
特性INSERTUPDATEDELETE
OLDNULL實際值實際值
NEW實際值實際值NULL
例1:建立一個觸發(fā)器, 當職工表 emp 表被刪除一條記錄時，把被刪除記錄寫到職工表刪除日志表中去。
CREATE?TABLE?emp_his?AS?SELECT?*?FROM?EMP?WHERE1=2;
CREATE?OR?REPLACE?TRIGGER?tr_del_emp
BEFORE?DELETE?--指定觸發(fā)時機為刪除操作前觸發(fā)
ON?scott.emp
FOR?EACH?ROW???--說明創(chuàng)建的是行級觸發(fā)器
BEGIN
--將修改前數(shù)據(jù)插入到日志記錄表?del_emp?,以供監(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,?:old.comm,?:old.hiredate?);
END;
DELETE?emp?WHERE?empno=7788;
DROP?TABLE?emp_his;
DROP?TRIGGER?del_emp;
例2：限制對Departments表修改（包括INSERT,DELETE,UPDATE）的時間范圍，即不允許在非工作時間修改departments表。
CREATE?OR?REPLACE?TRIGGER?tr_dept_time
BEFORE?INSERT?OR?DELETE?OR?UPDATE
ON?departments
BEGIN
IF?(TO_CHAR(sysdate,'DAY')?IN?('星期六',?'星期日'))?OR?(TO_CHAR(sysdate,?'HH24:MI')?NOT?BETWEEN?'08:30'?AND?'18:00')?THEN
RAISE_APPLICATION_ERROR(-20001,?'不是上班時間，不能修改departments表');
END?IF;
END;
例3：限定只對部門號為80的記錄進行行觸發(fā)器操作。
CREATE?OR?REPLACE?TRIGGER?tr_emp_sal_comm
BEFORE?UPDATE?OF?salary,?commission_pct
OR?DELETE
ON?HR.employees
FOR?EACH?ROW
WHEN?(old.department_id?=80)
BEGIN
CASE
WHEN?UPDATING?('salary')?THEN
IF?:NEW.salary?<?:old.salary?THEN
RAISE_APPLICATION_ERROR(-20001,?'部門80的人員的工資不能降');
END?IF;
WHEN?UPDATING?('commission_pct')?THEN
IF?:NEW.commission_pct?<?:old.commission_pct?THEN
RAISE_APPLICATION_ERROR(-20002,?'部門80的人員的獎金不能降');
END?IF;
WHEN?DELETING?THEN
RAISE_APPLICATION_ERROR(-20003,?'不能刪除部門80的人員記錄');
END?CASE;
END;
/*
實例：
UPDATE?employees?SET?salary?=?8000?WHERE?employee_id?=?177;
DELETE?FROM?employees?WHERE?employee_id?in?(177,170);
*/
例4：利用行觸發(fā)器實現(xiàn)級聯(lián)更新。在修改了主表regions中的region_id之后（AFTER），級聯(lián)的、自動的更新子表countries表中原來在該地區(qū)的國家的region_id。
CREATE?OR?REPLACE?TRIGGER?tr_reg_cou
AFTER?update?OF?region_id
ON?regions
FOR?EACH?ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('舊的region_id值是'||:old.region_id
||'、新的region_id值是'||:new.region_id);
UPDATE?countries?SET?region_id?=?:new.region_id
WHERE?region_id?=?:old.region_id;
END;
例5：在觸發(fā)器中調(diào)用過程。
CREATE?OR?REPLACE?PROCEDURE?add_job_history
(?p_emp_id??????????job_history.employee_id%type
,?p_start_date??????job_history.start_date%type
,?p_end_date????????job_history.end_date%type
,?p_job_id??????????job_history.job_id%type
,?p_department_id???job_history.department_id%type
)
IS
BEGIN
INSERT?INTO?job_history?(employee_id,?start_date,?end_date,
job_id,?department_id)
VALUES(p_emp_id,?p_start_date,?p_end_date,?p_job_id,?p_department_id);
END?add_job_history;
--創(chuàng)建觸發(fā)器調(diào)用存儲過程...
CREATE?OR?REPLACE?TRIGGER?update_job_history
AFTER?UPDATE?OF?job_id,?department_id?ON?employees
FOR?EACH?ROW
BEGIN
add_job_history(:old.employee_id,?:old.hire_date,?sysdate,
:old.job_id,?:old.department_id);
END;
8.2.3 創(chuàng)建替代(INSTEAD OF)觸發(fā)器
創(chuàng)建觸發(fā)器的一般語法是:
CREATE?[OR?REPLACE]?TRIGGER?trigger_name
INSTEAD?OF
{INSERT?|?DELETE?|?UPDATE?[OF?column?[,?column?…]]}
[OR?{INSERT?|?DELETE?|?UPDATE?[OF?column?[,?column?…]]}...]
ON?[schema.]?view_name?--只能定義在視圖上
[REFERENCING?{OLD?[AS]?old?|?NEW?[AS]?new|?PARENT?as?parent}]
[FOR?EACH?ROW?]?--因為INSTEAD?OF觸發(fā)器只能在行級上觸發(fā),所以沒有必要指定
[WHEN?condition]
PL/SQL_block?|?CALL?procedure_name;
其中：
INSTEAD OF 選項使ORACLE激活觸發(fā)器，而不執(zhí)行觸發(fā)事件。只能對視圖和對象視圖建立INSTEAD OF觸發(fā)器，而不能對表、模式和數(shù)據(jù)庫建立INSTEAD OF 觸發(fā)器。
???????????FOR EACH ROW選項說明觸發(fā)器為行觸發(fā)器。行觸發(fā)器和語句觸發(fā)器的區(qū)別表現(xiàn)在：行觸發(fā)器要求當一個DML語句操走影響數(shù)據(jù)庫中的多行數(shù)據(jù)時，對于其中的每個數(shù)據(jù)行，只要它們符合觸發(fā)約束條件，均激活一次觸發(fā)器；而語句觸發(fā)器將整個語句操作作為觸發(fā)事件，當它符合約束條件時，激活一次觸發(fā)器。當省略FOR EACH ROW 選項時，BEFORE 和AFTER 觸發(fā)器為語句觸發(fā)器，而INSTEAD OF 觸發(fā)器則為行觸發(fā)器。
???????????REFERENCING 子句說明相關(guān)名稱，在行觸發(fā)器的PL/SQL塊和WHEN 子句中可以使用相關(guān)名稱參照當前的新、舊列值，默認的相關(guān)名稱分別為OLD和NEW。觸發(fā)器的PL/SQL塊中應(yīng)用相關(guān)名稱時，必須在它們之前加冒號(:)，但在WHEN子句中則不能加冒號。
WHEN 子句說明觸發(fā)約束條件。Condition 為一個邏輯表達時，其中必須包含相關(guān)名稱，而不能包含查詢語句，也不能調(diào)用PL/SQL 函數(shù)。WHEN 子句指定的觸發(fā)約束條件只能用在BEFORE 和AFTER 行觸發(fā)器中，不能用在INSTEAD OF 行觸發(fā)器和其它類型的觸發(fā)器中。
??? INSTEAD_OF 用于對視圖的DML觸發(fā)，由于視圖有可能是由多個表進行聯(lián)結(jié)(join)而成，因而并非是所有的聯(lián)結(jié)都是可更新的。但可以按照所需的方式執(zhí)行更新，例如下面情況：
例1：
CREATE?OR?REPLACE?VIEW?emp_view?AS
SELECT?deptno,?count(*)?total_employeer,?sum(sal)?total_salary
FROM?emp?GROUP?BY?deptno;
在此視圖中直接刪除是非法：
SQL>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ā)器來為 DELETE 操作執(zhí)行所需的處理，即刪除EMP表中所有基準行：
?
CREATE?OR?REPLACE?TRIGGER?emp_view_delete
INSTEAD?OF?DELETE?ON?emp_view?FOR?EACH?ROW
BEGIN
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ù)雜視圖，針對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")
AS
SELECT?r.region_id,
r.region_name,
c.country_id,
c.country_name
FROM?regions?r,
countries?c
WHERE?r.region_id?=?c.region_id;
l????????創(chuàng)建觸發(fā)器：
?
CREATE?OR?REPLACE?TRIGGER?"HR"."TR_I_O_REG_COU"?INSTEAD?OF
INSERT?ON?v_reg_cou?FOR?EACH?ROW?DECLARE?v_count?NUMBER;
BEGIN
SELECT?COUNT(*)?INTO?v_count?FROM?regions?WHERE?region_id?=?:new.r_id;
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_id
);
END?IF;
END;
?
創(chuàng)建INSTEAD OF觸發(fā)器需要注意以下幾點：
l???????? 只能被創(chuàng)建在視圖上，并且該視圖沒有指定WITH CHECK OPTION選項。
l???????? 不能指定BEFORE 或 AFTER選項。
l???????? FOR EACH ROW子可是可選的，即INSTEAD OF觸發(fā)器只能在行級上觸發(fā)、或只能是行級觸發(fā)器，沒有必要指定。
l???????? 沒有必要在針對一個表的視圖上創(chuàng)建INSTEAD OF觸發(fā)器，只要創(chuàng)建DML觸發(fā)器就可以了。
?
8.2.3 創(chuàng)建系統(tǒng)事件觸發(fā)器
??? ORACLE10G提供的系統(tǒng)事件觸發(fā)器可以在DDL或數(shù)據(jù)庫系統(tǒng)上被觸發(fā)。DDL指的是數(shù)據(jù)定義語言，如CREATE 、ALTER及DROP 等。而數(shù)據(jù)庫系統(tǒng)事件包括數(shù)據(jù)庫服務(wù)器的啟動或關(guān)閉，用戶的登錄與退出、數(shù)據(jù)庫服務(wù)錯誤等。創(chuàng)建系統(tǒng)觸發(fā)器的語法如下：?
創(chuàng)建觸發(fā)器的一般語法是:
CREATE?OR?REPLACE?TRIGGER?[sachema.]trigger_name
{BEFORE|AFTER}
{ddl_event_list?|?database_event_list}
ON?{?DATABASE?|?[schema.]SCHEMA?}
[WHEN?condition]
PL/SQL_block?|?CALL?procedure_name;
其中: ddl_event_list：一個或多個DDL 事件，事件間用 OR 分開；
????????database_event_list：一個或多個數(shù)據(jù)庫事件，事件間用 OR 分開；
???????????系統(tǒng)事件觸發(fā)器既可以建立在一個模式上，又可以建立在整個數(shù)據(jù)庫上。當建立在模式(SCHEMA)之上時，只有模式所指定用戶的DDL操作和它們所導(dǎo)致的錯誤才激活觸發(fā)器, 默認時為當前用戶模式。當建立在數(shù)據(jù)庫(DATABASE)之上時，該數(shù)據(jù)庫所有用戶的DDL操作和他們所導(dǎo)致的錯誤，以及數(shù)據(jù)庫的啟動和關(guān)閉均可激活觸發(fā)器。要在數(shù)據(jù)庫之上建立觸發(fā)器時，要求用戶具有ADMINISTER DATABASE TRIGGER權(quán)限。
下面給出系統(tǒng)觸發(fā)器的種類和事件出現(xiàn)的時機（前或后）：
事件允許的時機說明
STARTUPAFTER啟動數(shù)據(jù)庫實例之后觸發(fā)
SHUTDOWNBEFORE關(guān)閉數(shù)據(jù)庫實例之前觸發(fā)（非正常關(guān)閉不觸發(fā)）
SERVERERRORAFTER數(shù)據(jù)庫服務(wù)器發(fā)生錯誤之后觸發(fā)
LOGONAFTER成功登錄連接到數(shù)據(jù)庫后觸發(fā)
LOGOFFBEFORE開始斷開數(shù)據(jù)庫連接之前觸發(fā)
CREATEBEFORE，AFTER在執(zhí)行CREATE語句創(chuàng)建數(shù)據(jù)庫對象之前、之后觸發(fā)
DROPBEFORE，AFTER在執(zhí)行DROP語句刪除數(shù)據(jù)庫對象之前、之后觸發(fā)
ALTERBEFORE，AFTER在執(zhí)行ALTER語句更新數(shù)據(jù)庫對象之前、之后觸發(fā)
DDLBEFORE，AFTER在執(zhí)行大多數(shù)DDL語句之前、之后觸發(fā)
GRANTBEFORE，AFTER執(zhí)行GRANT語句授予權(quán)限之前、之后觸發(fā)
REVOKEBEFORE，AFTER執(zhí)行REVOKE語句收權(quán)限之前、之后觸犯發(fā)
RENAMEBEFORE，AFTER執(zhí)行RENAME語句更改數(shù)據(jù)庫對象名稱之前、之后觸犯發(fā)
AUDIT/?NOAUDITBEFORE，AFTER執(zhí)行AUDIT或NOAUDIT進行審計或停止審計之前、之后觸發(fā)
8.2.4 系統(tǒng)觸發(fā)器事件屬性
事件屬性\事件Startup/ShutdownServererrorLogon/LogoffDDLDML
事件名稱ü＊ü＊ü＊ü＊＊
數(shù)據(jù)庫名稱ü＊????
數(shù)據(jù)庫實例號ü＊????
錯誤號?ü＊???
用戶名??ü＊＊?
模式對象類型???ü＊＊
模式對象名稱???ü＊＊
列????ü＊
除DML語句的列屬性外，其余事件屬性值可通過調(diào)用ORACLE定義的事件屬性函數(shù)來讀取。
函數(shù)名稱數(shù)據(jù)類型說??? 明
Ora_syseventVARCHAR2（20）激活觸發(fā)器的事件名稱
Instance_numNUMBER數(shù)據(jù)庫實例名
Ora_database_nameVARCHAR2（50）數(shù)據(jù)庫名稱
Server_error(posi)NUMBER錯誤信息棧中posi指定位置中的錯誤號
Is_servererror(err_number)
BOOLEAN
檢查err_number指定的錯誤號是否在錯誤信息棧中，如果在則返回TRUE，否則返回FALSE。在觸發(fā)器內(nèi)調(diào)用此函數(shù)可以判斷是否發(fā)生指定的錯誤。
Login_userVARCHAR2(30)登陸或注銷的用戶名稱
Dictionary_obj_typeVARCHAR2(20)DDL語句所操作的數(shù)據(jù)庫對象類型
Dictionary_obj_nameVARCHAR2(30)DDL語句所操作的數(shù)據(jù)庫對象名稱
Dictionary_obj_ownerVARCHAR2(30)DDL語句所操作的數(shù)據(jù)庫對象所有者名稱
Des_encrypted_passwordVARCHAR2(2)正在創(chuàng)建或修改的經(jīng)過DES算法加密的用戶口令
?
例1：創(chuàng)建觸發(fā)器，存放有關(guān)事件信息。
DESC?ora_sysevent
DESC?ora_login_user
--創(chuàng)建用于記錄事件用的表
CREATE?TABLE?ddl_event
(crt_date?timestamp?PRIMARY?KEY,
event_name?VARCHAR2(20),
user_name?VARCHAR2(10),
obj_type?VARCHAR2(20),
obj_name?VARCHAR2(20));
--創(chuàng)建觸犯發(fā)器
CREATE?OR?REPLACE?TRIGGER?tr_ddl
AFTER?DDL?ON?SCHEMA
BEGIN
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ā)器。
CREATE?TABLE?log_event
(user_name?VARCHAR2(10),
address?VARCHAR2(20),
logon_date?timestamp,
logoff_date?timestamp);
--創(chuàng)建登錄觸發(fā)器
CREATE?OR?REPLACE?TRIGGER?tr_logon
AFTER?LOGON?ON?DATABASE
BEGIN
INSERT?INTO?log_event?(user_name,?address,?logon_date)
VALUES?(ora_login_user,?ora_client_ip_address,?systimestamp);
END?tr_logon;
--創(chuàng)建退出觸發(fā)器
CREATE?OR?REPLACE?TRIGGER?tr_logoff
BEFORE?LOGOFF?ON?DATABASE
BEGIN
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 提供三個參數(shù)INSERTING, UPDATING,DELETING 用于判斷觸發(fā)了哪些操作。
謂詞行為
INSERTING如果觸發(fā)語句是 INSERT 語句，則為TRUE,否則為FALSE
UPDATING如果觸發(fā)語句是 UPDATE語句，則為TRUE,否則為FALSE
DELETING如果觸發(fā)語句是 DELETE 語句，則為TRUE,否則為FALSE
?
8.2.6 重新編譯觸發(fā)器
如果在觸發(fā)器內(nèi)調(diào)用其它函數(shù)或過程，當這些函數(shù)或過程被刪除或修改后，觸發(fā)器的狀態(tài)將被標識為無效。當DML語句激活一個無效觸發(fā)器時，ORACLE將重新編譯觸發(fā)器代碼，如果編譯時發(fā)現(xiàn)錯誤，這將導(dǎo)致DML語句執(zhí)行失敗。
在PL/SQL程序中可以調(diào)用ALTER TRIGGER語句重新編譯已經(jīng)創(chuàng)建的觸發(fā)器，格式為：???????????
ALTER?TRIGGER?[schema.]?trigger_name?COMPILE?[?DEBUG]
?????? 其中：DEBUG 選項要器編譯器生成PL/SQL 程序條使其所使用的調(diào)試代碼。
8.3?刪除和使能觸發(fā)器
l刪除觸發(fā)器：
DROP?TRIGGER?trigger_name;
當刪除其他用戶模式中的觸發(fā)器名稱，需要具有DROP ANY TRIGGER系統(tǒng)權(quán)限，當刪除建立在數(shù)據(jù)庫上的觸發(fā)器時，用戶需要具有ADMINISTER DATABASE TRIGGER系統(tǒng)權(quán)限。
此外，當刪除表或視圖時，建立在這些對象上的觸發(fā)器也隨之刪除。?
l禁用或啟用觸發(fā)器
數(shù)據(jù)庫TRIGGER 的狀態(tài)：
有效狀態(tài)(ENABLE)：當觸發(fā)事件發(fā)生時，處于有效狀態(tài)的數(shù)據(jù)庫觸發(fā)器TRIGGER 將被觸發(fā)。
無效狀態(tài)(DISABLE)：當觸發(fā)事件發(fā)生時，處于無效狀態(tài)的數(shù)據(jù)庫觸發(fā)器TRIGGER 將不會被觸發(fā)，此時就跟沒有這個數(shù)據(jù)庫觸發(fā)器(TRIGGER) 一樣。
數(shù)據(jù)庫TRIGGER的這兩種狀態(tài)可以互相轉(zhuǎn)換。格式為：
ALTER?TIGGER?trigger_name?[DISABLE?|?ENABLE?];
--例：ALTER?TRIGGER?emp_view_delete?DISABLE;
???????????ALTER TRIGGER語句一次只能改變一個觸發(fā)器的狀態(tài)，而ALTER TABLE語句則一次能夠改變與指定表相關(guān)的所有觸發(fā)器的使用狀態(tài)。格式為：?????????????
ALTER?TABLE?[schema.]table_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_TYPE
FROM?user_triggers;
8.5?? 數(shù)據(jù)庫觸發(fā)器的應(yīng)用舉例
例1：創(chuàng)建一個DML語句級觸發(fā)器，當對emp表執(zhí)行INSERT, UPDATE, DELETE 操作時，它自動更新dept_summary 表中的數(shù)據(jù)。由于在PL/SQL塊中不能直接調(diào)用DDL語句，所以，利用ORACLE內(nèi)置包DBMS_UTILITY中的EXEC_DDL_STATEMENT過程，由它執(zhí)行DDL語句創(chuàng)建觸發(fā)器。
CREATE?TABLE?dept_summary(
Deptno?NUMBER(2),
Sal_sum?NUMBER(9,2),
Emp_count?NUMBER);
INSERT?INTO?dept_summary(deptno,?sal_sum,?emp_count)
SELECT?deptno,?SUM(sal),?COUNT(*)
FROM?emp
GROUP?BY?deptno;
--創(chuàng)建一個PL/SQL過程disp_dept_summary
--在觸發(fā)器中調(diào)用該過程顯示dept_summary標中的數(shù)據(jù)。
CREATE?OR?REPLACE?PROCEDURE?disp_dept_summary
IS
Rec?dept_summary%ROWTYPE;
CURSOR?c1?IS?SELECT?*?FROM?dept_summary;
BEGIN
OPEN?c1;
FETCH?c1?INTO?REC;
DBMS_OUTPUT.PUT_LINE('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_STATEMENT('
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,?dname,?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=1000WHERE?empno=9999;
DBMS_OUTPUT.PUT_LINE('修改后');
Disp_dept_summary();
DELETE?FROM?emp?WHERE?empno=9999;
DELETE?FROM?dept?WHERE?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語句行級觸發(fā)器。當對emp表執(zhí)行INSERT, UPDATE, DELETE 操作時，它自動更新dept_summary 表中的數(shù)據(jù)。由于在PL/SQL塊中不能直接調(diào)用DDL語句，所以，利用ORACLE內(nèi)置包DBMS_UTILITY中的EXEC_DDL_STATEMENT過程，由它執(zhí)行DDL語句創(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
DBMS_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.PUT_LINE(''正在執(zhí)行trig2_insert?觸發(fā)器…'');
SELECT?COUNT(*)?INTO?I
FROM?dept_summary?WHERE?deptno?=?:new_emp.deptno;
IF?I?>?0?THEN
UPDATE?dept_summary
SET?sal_sum=sal_sum+:new_emp.sal,
Emp_count=emp_count+1
WHERE?deptno?=?:new_emp.deptno;
ELSE
INSERT?INTO?dept_summary
VALUES?(:new_emp.deptno,?:new_emp.sal,?1);
END?IF;
END;'
);
DBMS_UTILITY.EXEC_DDL_STATEMENT(
'CREATE?OR?REPLACE?TRIGGER?trig2_delete
AFTER?DELETE?ON?emp
REFERENCING?OLD?AS?old_emp
FOR?EACH?ROW
DECLARE
I?NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2_delete?觸發(fā)器…'');
SELECT?emp_count?INTO?I
FROM?dept_summary?WHERE?deptno?=?:old_emp.deptno;
IF?I?>1?THEN
UPDATE?dept_summary
SET?sal_sum=sal_sum?-?:old_emp.sal,
Emp_count=emp_count?-?1
WHERE?deptno?=?:old_emp.deptno;
ELSE
DELETE?FROM?dept_summary?WHERE?deptno?=?:old_emp.deptno;
END?IF;
END;'
);
INSERT?INTO?dept(deptno,?dname,?loc)
VALUES(90,?'demo_dept',?'none_loc');
INSERT?INTO?emp(ename,?deptno,?empno,?sal)
VALUES(USER,90,9999,3000);
INSERT?INTO?emp(ename,?deptno,?empno,?sal)
VALUES(USER,90,9998,2000);
DBMS_OUTPUT.PUT_LINE('插入后');
Disp_dept_summary();
UPDATE?emp?SET?sal?=?sal*1.1WHERE?deptno=90;
DBMS_OUTPUT.PUT_LINE('修改后');
Disp_dept_summary();
DELETE?FROM?emp?WHERE?deptno=90;
DELETE?FROM?dept?WHERE?deptno=90;
DBMS_OUTPUT.PUT_LINE('刪除后');
Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP?TRIGGER?trig2_update');
DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP?TRIGGER?trig2_insert');
DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP?TRIGGER?trig2_delete');
EXCEPTION
WHEN?OTHERS?THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
例3：利用ORACLE提供的條件謂詞INSERTING、UPDATING和DELETING創(chuàng)建與例2具有相同功能的觸發(fā)器。
BEGIN
DBMS_OUTPUT.PUT_LINE('插入前');
Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT(
'CREATE?OR?REPLACE?TRIGGER?trig2
AFTER?INSERT?OR?DELETE?OR?UPDATE?OF?sal
ON?emp
REFERENCING?OLD?AS?old_emp?NEW?AS?new_emp
FOR?EACH?ROW
DECLARE
I?NUMBER;
BEGIN
IF?UPDATING?AND?:old_emp.sal?!=?:new_emp.sal?THEN
DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2?觸發(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;
ELSIF?INSERTING?THEN
DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2觸發(fā)器…'');
SELECT?COUNT(*)?INTO?I
FROM?dept_summary
WHERE?deptno?=?:new_emp.deptno;
IF?I?>?0?THEN
UPDATE?dept_summary
SET?sal_sum=sal_sum+:new_emp.sal,
Emp_count=emp_count+1
WHERE?deptno?=?:new_emp.deptno;
ELSE
INSERT?INTO?dept_summary
VALUES?(:new_emp.deptno,?:new_emp.sal,?1);
END?IF;
ELSE
DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2觸發(fā)器…'');
SELECT?emp_count?INTO?I
FROM?dept_summary?WHERE?deptno?=?:old_emp.deptno;
IF?I?>?1?THEN
UPDATE?dept_summary
SET?sal_sum=sal_sum?-?:old_emp.sal,
Emp_count=emp_count?-?1
WHERE?deptno?=?:old_emp.deptno;
ELSE
DELETE?FROM?dept_summary
WHERE?deptno?=?:old_emp.deptno;
END?IF;
END?IF;
END;'
);
INSERT?INTO?dept(deptno,?dname,?loc)
VALUES(90,?'demo_dept',?'none_loc');
INSERT?INTO?emp(ename,?deptno,?empno,?sal)
VALUES(USER,90,9999,3000);
INSERT?INTO?emp(ename,?deptno,?empno,?sal)
VALUES(USER,90,9998,2000);
DBMS_OUTPUT.PUT_LINE('插入后');
Disp_dept_summary();
UPDATE?emp?SET?sal?=?sal*1.1WHERE?deptno=90;
DBMS_OUTPUT.PUT_LINE('修改后');
Disp_dept_summary();
DELETE?FROM?emp?WHERE?deptno=90;
DELETE?FROM?dept?WHERE?deptno=90;
DBMS_OUTPUT.PUT_LINE('刪除后');
Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP?TRIGGER?trig2');
EXCEPTION
WHEN?OTHERS?THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
例4：創(chuàng)建INSTEAD OF 觸發(fā)器。首先創(chuàng)建一個視圖myview,由于該視圖是復(fù)合查詢所產(chǎn)生的視圖，所以不能執(zhí)行DML語句。根據(jù)用戶對視圖所插入的數(shù)據(jù)判斷需要將數(shù)據(jù)插入到哪個視圖基表中，然后對該基表執(zhí)行插入操作。
DECLARE
No?NUMBER;
Name?VARCHAR2(20);
BEGIN
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE?OR?REPLACE?VIEW?myview?AS
SELECT?empno,?ename,?''E''?type?FROM?emp
UNION
SELECT?dept.deptno,?dname,?''D''?FROM?dept
');
--?創(chuàng)建INSTEAD?OF?觸發(fā)器trigger3;
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE?OR?REPLACE?TRIGGER?trig3
INSTEAD?OF?INSERT?ON?myview
REFERENCING?NEW?n
FOR?EACH?ROW
DECLARE
Rows?INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig3觸發(fā)器…'');
IF?:n.type?=?''D''?THEN
SELECT?COUNT(*)?INTO?rows
FROM?dept?WHERE?deptno?=?:n.empno;
IF?rows?=?0?THEN
DBMS_OUTPUT.PUT_LINE(''向dept表中插入數(shù)據(jù)…'');
INSERT?INTO?dept(deptno,?dname,?loc)
VALUES?(:n.empno,?:n.ename,?''none’’);
ELSE
DBMS_OUTPUT.PUT_LINE(''編號為''||?:n.empno||
''的部門已存在，插入操作失??！'');
END?IF;
ELSE
SELECT?COUNT(*)?INTO?rows
FROM?emp?WHERE?empno?=?:n.empno;
IF?rows?=?0?THEN
DBMS_OUTPUT.PUT_LINE('’向emp表中插入數(shù)據(jù)…’’);
INSERT?INTO?emp(empno,?ename)
VALUES(:n.empno,?:n.ename);
ELSE
DBMS_OUTPUT.PUT_LINE(''編號為''||?:n.empno||
''的人員已存在，插入操作失敗!'');
END?IF;
END?IF;
END;
');
INSERT?INTO?myview?VALUES?(70,?'demo',?'D');
INSERT?INTO?myview?VALUES?(9999,?USER,?'E');
SELECT?deptno,?dname?INTO?no,?name?FROM?dept?WHERE?deptno=70;
DBMS_OUTPUT.PUT_LINE('員工編號：'||TO_CHAR(no)||'姓名：'||name);
SELECT?empno,?ename?INTO?no,?name?FROM?emp?WHERE?empno=9999;
DBMS_OUTPUT.PUT_LINE('部門編號：'||TO_CHAR(no)||'姓名：'||name);
DELETE?FROM?emp?WHERE?empno=9999;
DELETE?FROM?dept?WHERE?deptno=70;
DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP?TRIGGER?trig3');
END;
例5：利用ORACLE事件屬性函數(shù)，創(chuàng)建一個系統(tǒng)事件觸發(fā)器。首先創(chuàng)建一個事件日志表eventlog，由它存儲用戶在當前數(shù)據(jù)庫中所創(chuàng)建的數(shù)據(jù)庫對象，以及用戶的登陸和注銷、數(shù)據(jù)庫的啟動和關(guān)閉等事件，之后創(chuàng)建trig4_ddl、trig4_before和trig4_after觸發(fā)器，它們調(diào)用事件屬性函數(shù)將各個事件記錄到eventlog數(shù)據(jù)表中。
?
BEGIN
--?創(chuàng)建用于記錄事件日志的數(shù)據(jù)表
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE?TABLE?eventlog(
Eventname?VARCHAR2(20)?NOT?NULL,
Eventdate?date?default?sysdate,
Inst_num?NUMBER?NULL,
Db_name?VARCHAR2(50)?NULL,
Srv_error?NUMBER?NULL,
Username?VARCHAR2(30)?NULL,
Obj_type?VARCHAR2(20)?NULL,
Obj_name?VARCHAR2(30)?NULL,
Obj_owner?VARCHAR2(30)?NULL
)
');
--?創(chuàng)建DDL觸發(fā)器trig4_ddl
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE?OR?REPLACE?TRIGGER?trig4_ddl
AFTER?CREATE?OR?ALTER?OR?DROP
ON?DATABASE
DECLARE
Event?VARCHAR2(20);
Typ?VARCHAR2(20);
Name?VARCHAR2(30);
Owner?VARCHAR2(30);
BEGIN
--?讀取DDL事件屬性
Event?:=?SYSEVENT;
Typ?:=?DICTIONARY_OBJ_TYPE;
Name?:=?DICTIONARY_OBJ_NAME;
Owner?:=?DICTIONARY_OBJ_OWNER;
--將事件屬性插入到事件日志表中
INSERT?INTO?scott.eventlog(eventname,?obj_type,?obj_name,?obj_owner)
VALUES(event,?typ,?name,?owner);
END;
');
--?創(chuàng)建LOGON、STARTUP和SERVERERROR?事件觸發(fā)器
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE?OR?REPLACE?TRIGGER?trig4_after
AFTER?LOGON?OR?STARTUP?OR?SERVERERROR
ON?DATABASE
DECLARE
Event?VARCHAR2(20);
Instance?NUMBER;
Err_num?NUMBER;
Dbname?VARCHAR2(50);
User?VARCHAR2(30);
BEGIN
Event?:=?SYSEVENT;
IF?event?=?''LOGON''?THEN
User?:=?LOGIN_USER;
INSERT?INTO?eventlog(eventname,?username)
VALUES(event,?user);
ELSIF?event?=?''SERVERERROR''?THEN
Err_num?:=?SERVER_ERROR(1);
INSERT?INTO?eventlog(eventname,?srv_error)
VALUES(event,?err_num);
ELSE
Instance?:=?INSTANCE_NUM;
Dbname?:=?DATABASE_NAME;
INSERT?INTO?eventlog(eventname,?inst_num,?db_name)
VALUES(event,?instance,?dbname);
END?IF;
END;
');
--?創(chuàng)建LOGOFF和SHUTDOWN?事件觸發(fā)器
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE?OR?REPLACE?TRIGGER?trig4_before
BEFORE?LOGOFF?OR?SHUTDOWN
ON?DATABASE
DECLARE
Event?VARCHAR2(20);
Instance?NUMBER;
Dbname?VARCHAR2(50);
User?VARCHAR2(30);
BEGIN
Event?:=?SYSEVENT;
IF?event?=?''LOGOFF''?THEN
User?:=?LOGIN_USER;
INSERT?INTO?eventlog(eventname,?username)
VALUES(event,?user);
ELSE
Instance?:=?INSTANCE_NUM;
Dbname?:=?DATABASE_NAME;
INSERT?INTO?eventlog(eventname,?inst_num,?db_name)
VALUES(event,?instance,?dbname);
END?IF;
END;
');
END;
CREATE?TABLE?mydata(mydate?NUMBER);
CONNECT?SCOTT/TIGER
COL?eventname?FORMAT?A10
COL?eventdate?FORMAT?A12
COL?username?FORMAT?A10
COL?obj_type?FORMAT?A15
COL?obj_name?FORMAT?A15
COL?obj_owner?FORMAT?A10
SELECT?eventname,?eventdate,?obj_type,?obj_name,?obj_owner,?username,?Srv_error
FROM?eventlog;
DROP?TRIGGER?trig4_ddl;
DROP?TRIGGER?trig4_before;
DROP?TRIGGER?trig4_after;
DROP?TABLE?eventlog;
DROP?TABLE?mydata;
8.6?? 數(shù)據(jù)庫觸發(fā)器的應(yīng)用實例
用戶可以使用數(shù)據(jù)庫觸發(fā)器實現(xiàn)各種功能：
l???????? 復(fù)雜的審計功能；
例：將EMP 表的變化情況記錄到AUDIT_TABLE和AUDIT_TABLE_VALUES中。
CREATE?TABLE?audit_table(
Audit_id?????NUMBER,
User_name?VARCHAR2(20),
Now_time?DATE,
Terminal_name?VARCHAR2(10),
Table_name?VARCHAR2(10),
Action_name?VARCHAR2(10),
Emp_id?NUMBER(4));
CREATE?TABLE?audit_table_val(
Audit_id?NUMBER,
Column_name?VARCHAR2(10),
Old_val?NUMBER(7,2),
New_val?NUMBER(7,2));
CREATE?SEQUENCE?audit_seq
START?WITH1000
INCREMENT?BY1
NOMAXVALUE
NOCYCLE?NOCACHE;
CREATE?OR?REPLACE?TRIGGER?audit_emp
AFTER?INSERT?OR?UPDATE?OR?DELETE?ON?emp
FOR?EACH?ROW
DECLARE
Time_now?DATE;
Terminal?CHAR(10);
BEGIN
Time_now:=sysdate;
Terminal:=USERENV('TERMINAL');
IF?INSERTING?THEN
INSERT?INTO?audit_table
VALUES(audit_seq.NEXTVAL,?user,?time_now,
terminal,?'EMP',?'INSERT',?:new.empno);
ELSIF?DELETING?THEN
INSERT?INTO?audit_table
VALUES(audit_seq.NEXTVAL,?user,?time_now,
terminal,?'EMP',?'DELETE',?:old.empno);
ELSE
INSERT?INTO?audit_table
VALUES(audit_seq.NEXTVAL,?user,?time_now,
terminal,?'EMP',?'UPDATE',?:old.empno);
IF?UPDATING('SAL')?THEN
INSERT?INTO?audit_table_val
VALUES(audit_seq.CURRVAL,?'SAL',?:old.sal,?:new.sal);
ELSE?UPDATING('DEPTNO')
INSERT?INTO?audit_table_val
VALUES(audit_seq.CURRVAL,?'DEPTNO',?:old.deptno,?:new.deptno);
END?IF;
END?IF;
END;
l???????? 增強數(shù)據(jù)的完整性管理；
例：修改DEPT表的DEPTNO列時，同時把EMP表中相應(yīng)的DEPTNO也作相應(yīng)的修改；
CREATE?SEQUENCE?update_sequence
INCREMENT?BY1
START?WITH1000
MAXVALUE5000CYCLE;
ALTER?TABLE?emp
ADD?update_id?NUMBER;
CREATE?OR?REPLACE?PACKAGE?integritypackage?AS
Updateseq?NUMBER;
END?integritypackage;
CREATE?OR?REPLACE?PACKAGE?BODY?integritypackage?AS
END?integritypackage;
CREATE?OR?REPLACE?TRIGGER?dept_cascade1
BEFORE?UPDATE?OF?deptno?ON?dept
DECLARE
Dummy?NUMBER;
BEGIN
SELECT?update_sequence.NEXTVAL?INTO?dummy?FROM?dual;
Integritypackage.updateseq:=dummy;
END;
CREATE?OR?REPLACE?TRIGGER?dept_cascade2
AFTER?DELETE?OR?UPDATE?OF?deptno?ON?dept
FOR?EACH?ROW
BEGIN
IF?UPDATING?THEN
UPDATE?emp?SET?deptno=:new.deptno,
update_id=integritypackage.updateseq
WHERE?emp.deptno=:old.deptno?AND?update_id?IS?NULL;
END?IF;
IF?DELETING?THEN
DELETE?FROM?emp
WHERE?emp.deptno=:old.deptno;
END?IF;
END;
CREATE?OR?REPLACE?TRIGGER?dept_cascade3
AFTER?UPDATE?OF?deptno?ON?dept
BEGIN
UPDATE?emp?SET?update_id=NULL
WHERE?update_id=integritypackage.updateseq;
END;
SELECT?*?FROM?EMP?ORDER?BY?DEPTNO;
UPDATE?dept?SET?deptno=25WHERE?deptno=20;
l???????? 幫助實現(xiàn)安全控制；
例：保證對EMP表的修改僅在工作日的工作時間；
CREATE?TABLE?company_holidays(day?DATE);
INSERT?INTO?company_holidays
VALUES(sysdate);
INSERT?INTO?company_holidays
VALUES(TO_DATE('21-10月-01',?'DD-MON-YY'));
CREATE?OR?REPLACE?TRIGGER?emp_permit_change
BEFORE?INSERT?OR?DELETE?OR?UPDATE?ON?emp
DECLARE
Dummy?NUMBER;
Not_on_weekends?EXCEPTION;
Not_on_holidays?EXCEPTION;
Not_working_hours?EXCEPTION;
BEGIN
/*?check?for?weekends?*/
IF?TO_CHAR(SYSDATE,?'DAY')?IN?('星期六',?'星期日')?THEN
RAISE?not_on_weekends;
END?IF;
/*?check?for?company?holidays?*/
SELECT?COUNT(*)?INTO?dummy?FROM?company_holidays
WHERE?TRUNC(day)=TRUNC(SYSDATE);
IF?dummy?>0THEN
RAISE?not_on_holidays;
END?IF;
/*?check?for?work?hours(8:00?AM?to?18:00?PM?*/
IF?(TO_CHAR(SYSDATE,'HH24')<8OR?TO_CHAR(SYSDATE,?'HH24')>18)?THEN
RAISE?not_working_hours;
END?IF;
EXCEPTION
WHEN?not_on_weekends?THEN
RAISE_APPLICATION_ERROR(-20324,
'May?not?change?employee?table?during?the?weekends');
WHEN?not_on_holidays?THEN
RAISE_APPLICATION_ERROR(-20325,
'May?not?change?employee?table?during?a?holiday');
WHEN?not_working_hours?THEN
RAISE_APPLICATION_ERROR(-20326,
'May?not?change?employee?table?during?no_working?hours');
END;
