ORACLE PL/SQL 編程之八： ?

把觸發器說透 ?

?

本篇主要內容例如以下：

8.1 觸發器類型

8.1.1 DML觸發器

8.1.2 替代觸發器

8.1.3 系統觸發器

8.2?創建觸發器

8.2.1 觸發器觸發次序

8.2.2 創建DML觸發器

8.2.3 創建替代(INSTEAD OF)觸發器

8.2.3 創建系統事件觸發器

8.2.4 系統觸發器事件屬性

8.2.5 使用觸發器謂詞

8.2.6 又一次編譯觸發器

8.3?刪除和使能觸發器

8.4?觸發器和數據字典

8.5?? 數據庫觸發器的應用舉例

?

?

觸發器是很多關系數據庫系統都提供的一項技術。在ORACLE系統里，觸發器類似過程和函數，都有聲明，運行和異常處理過程的PL/SQL塊。

8.1?觸發器類型

??? 觸發器在數據庫里以獨立的對象存儲，它與存儲過程和函數不同的是，存儲過程與函數須要用戶顯示調用才執行，而觸發器是由一個事件來啟動執行。即觸發器是當某個事件發生時 自己主動地隱式執行 。而且，觸發器 不能接收參數 。所以執行觸發器就叫觸發或點火（firing）。ORACLE事件指的是對數據庫的表進行的INSERT、UPDATE及DELETE操作或對視圖進行類似的操作。ORACLE將觸發器的功能擴展到了觸發ORACLE，如數據庫的啟動與關閉等。所以觸發器經常使用來完畢由數據庫的完整性約束難以完畢的復雜業務規則的約束，或用來監視對數據庫的各種操作，實現審計的功能。

?

8.1.1 DML觸發器

??? ORACLE能夠在DML語句進行觸發，能夠在DML操作前或操作后進行觸發，而且能夠對每一個行或語句操作上進行觸發。

?

8.1.2 替代觸發器

??? 因為在ORACLE里，不能直接對由兩個以上的表建立的視圖進行操作。所以給出了替代觸發器。它就是ORACLE 8專門為進行視圖操作的一種處理方法。

?

8.1.3 系統觸發器

ORACLE 8i 提供了第三種類型的觸發器叫系統觸發器。它能夠在ORACLE數據庫系統的事件中進行觸發，如ORACLE系統的啟動與關閉等。

?

觸發器組成:?

l???????? 觸發事件： 引起觸發器被觸發的事件。 比如：DML語句(INSERT, UPDATE, DELETE語句對表或視圖運行數據處理操作)、DDL語句（如CREATE、ALTER、DROP語句在數據庫中創建、改動、刪除模式對象）、數據庫系統事件（如系統啟動或退出、異常錯誤）、用戶事件（如登錄或退出數據庫）。

l???????? 觸發時間 ：即該TRIGGER 是在觸發事件發生之前（BEFORE）還是之后(AFTER)觸發，也就是觸發事件和該TRIGGER 的操作順序。

l???????? 觸發操作： 即該TRIGGER 被觸發之后的目的和意圖，正是觸發器本身要做的事情。 比如：PL/SQL 塊。

l???????? 觸發對象： 包含表、視圖、模式、數據庫。僅僅有在這些對象上發生了符合觸發條件的觸發事件，才會運行觸發操作。

l???????? 觸發條件： 由WHEN子句指定一個邏輯表達式。僅僅有當該表達式的值為TRUE時，遇到觸發事件才會自己主動運行觸發器，使其運行觸發操作。

l???????? 觸發頻率 ：說明觸發器內定義的動作被運行的次數。即語句級(STATEMENT)觸發器和行級(ROW)觸發器。

語句級(STATEMENT)觸發器：是指當某觸發事件發生時，該觸發器僅僅運行一次；

行級(ROW)觸發器：是指當某觸發事件發生時，對受到該操作影響的每一行數據，觸發器都單獨運行一次。

編寫觸發器時，須要注意下面幾點：

l???????? 觸發器不接受參數。

l???????? 一個表上最多可有12個觸發器，但同一時間、同一事件、同一類型的觸發器僅僅能有一個。并各觸發器之間不能有矛盾。

l???????? 在一個表上的觸發器越多，對在該表上的DML操作的性能影響就越大。

l????????觸發器最大為32KB。若確實須要，能夠先建立過程，然后在觸發器中用CALL語句進行調用。

l???????? 在觸發器的運行部分僅僅能用DML 語句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL語句（CREATE、ALTER、DROP） 。

l???????? 觸發器中不能包括事務控制語句(COMMIT，ROLLBACK，SAVEPOINT)。由于觸發器是觸發語句的一部分，觸發語句被提交、回退時，觸發器也被提交、回退了。

l???????? 在觸發器主體中調用的不論什么過程、函數，都不能使用事務控制語句。

l???????? 在觸發器主體中不能申明不論什么Long和blob變量。新值new和舊值old也不能是表中的不論什么long和blob列。

l???????? 不同類型的觸發器(如DML觸發器、INSTEAD OF觸發器、系統觸發器)的語法格式和作用有較大差別。
?

8.2?創建觸發器

創建觸發器的一般語法是:

?

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指出觸發器的觸發時序分別為前觸發和后觸發方式，前觸發是在運行觸發事件之前觸發當前所創建的觸發器，后觸發是在運行觸發事件之后觸發當前所創建的觸發器。

?????? FOR EACH ROW選項說明觸發器為 行觸發器 。行觸發器和語句觸發器的差別表如今：行觸發器要求當一個DML語句操作影響數據庫中的多行數據時，對于當中的每一個數據行，僅僅要它們符合觸發約束條件，均激活一次觸發器；而 語句觸發器 將整個語句操作作為觸發事件，當它符合約束條件時，激活一次觸發器。當省略FOR EACH ROW 選項時，BEFORE 和AFTER 觸發器為語句觸發器，而 INSTEAD OF 觸發器則僅僅能為行觸發器 。

???????????REFERENCING 子句說明相關名稱，在行觸發器的PL/SQL塊和WHEN 子句中能夠使用相關名稱參照當前的新、舊列值，默認的相關名稱分別為OLD和NEW。觸發器的PL/SQL塊中應用相關名稱時，必須在它們之前加冒號(:)，但在WHEN子句中則不能加冒號。

WHEN 子句說明觸發約束條件。Condition 為一個邏輯表達時，當中必須包括相關名稱，而不能包括查詢語句，也不能調用PL/SQL 函數。WHEN 子句指定的觸發約束條件僅僅能用在BEFORE 和AFTER 行觸發器中，不能用在INSTEAD OF 行觸發器和其他類型的觸發器中。

??? 當一個基表被修改( INSERT, UPDATE, DELETE)時要運行的存儲過程，運行時依據其所依附的基表修改而自己主動觸發，因此與應用程序無關，用數據庫觸發器能夠保證數據的一致性和完整性。

?

每張表最多可建立12 種類型的觸發器，它們是:

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 觸發器觸發次序

1.???????運行 BEFORE語句級觸發器;

2.???????對與受語句影響的每一行：

l???????? 運行 BEFORE行級觸發器

l???????? 運行 DML語句

l???????? 運行 AFTER行級觸發器?

3.???????運行 AFTER語句級觸發器

?

8.2.2 創建DML觸發器

??? 觸發器名與過程名和包的名字不一樣，它是單獨的名字空間，因而觸發器名能夠和表或過程有同樣的名字，但在一個模式中觸發器名不能同樣。

?

DML觸發器的限制

l???????? CREATE TRIGGER語句文本的字符長度不能超過32KB；

l???????? 觸發器體內的SELECT 語句僅僅能為SELECT … INTO …結構，或者為定義游標所使用的SELECT 語句。

l???????? 觸發器中不能使用數據庫事務控制語句 COMMIT; ROLLBACK, SVAEPOINT 語句；

l???????? 由觸發器所調用的過程或函數也不能使用數據庫事務控制語句；

l???????? 觸發器中不能使用LONG, LONG RAW 類型；

l???????? 觸發器內能夠參照LOB 類型列的列值，但不能通過 :NEW 改動LOB列中的數據；

?

DML觸發器基本要點

l???????? 觸發時機： 指定觸發器的觸發時間。假設指定為BEFORE，則表示在運行DML操作之前觸發，以便防止某些錯誤操作發生或實現某些業務規則；假設指定為AFTER，則表示在運行DML操作之后觸發，以便記錄該操作或做某些事后處理。

l???????? 觸發事件： 引起觸發器被觸發的事件，即DML操作（INSERT、UPDATE、DELETE）。既能夠是單個觸發事件，也能夠是多個觸發事件的組合（僅僅能使用OR邏輯組合，不能使用AND邏輯組合）。

l???????? 條件謂詞： 當在觸發器中包括多個觸發事件（INSERT、UPDATE、DELETE）的組合時，為了分別針對不同的事件進行不同的處理，須要使用ORACLE提供的例如以下條件謂詞。

1）。 INSERTING： 當觸發事件是INSERT時，取值為TRUE，否則為FALSE。

2）。 UPDATING [（column_1,column_2,…,column_x）]： 當觸發事件是UPDATE????? 時，假設改動了column_x列，則取值為TRUE，否則為FALSE。當中column_x是可選的。

3）。 DELETING： 當觸發事件是DELETE時，則取值為TRUE，否則為FALSE。

解發對象： 指定觸發器是創建在哪個表、視圖上。

l???????? 觸發類型： 是語句級還是行級觸發器。

l???????? 觸發條件： 由WHEN子句指定一個邏輯表達式， 僅僅同意在行級觸發器上指定觸發條件，指定 UPDATING 后面的列的列表 。

?

問題：當觸發器被觸發時，要使用被插入、更新或刪除的記錄中的列值，有時要使用操作前、??????? 后列的值.

實現:??:NEW?修飾符訪問操作完畢后列的值

???????:OLD?修飾符訪問操作完畢前列的值

?

特性	INSERT	UPDATE	DELETE
OLD	NULL	實際值	實際值
NEW	實際值	實際值	NULL

?

例1: 建立一個觸發器, 當職工表 emp 表被刪除一條記錄時，把被刪除記錄寫到職工表刪除日志表中去。

?

CREATE ? TABLE ?emp_his? AS ? SELECT ? * ? FROM ?EMP? WHERE ? 1 = 2 ;?
CREATE ? OR ? REPLACE ? TRIGGER ?tr_del_emp?
???BEFORE? DELETE ? -- 指定觸發時機為刪除操作前觸發
??? ON ?scott.emp?
??? FOR ?EACH?ROW??? -- 說明創建的是行級觸發器 ?
BEGIN
??? -- 將改動前數據插入到日志記錄表 ?del_emp?, 以供監督使用。
??? 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的記錄進行行觸發器操作。

?

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： 利用行觸發器實現級聯更新。在改動了主表regions中的region_id之后（AFTER），級聯的、自己主動的更新子表countries表中原來在該地區的國家的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： 在觸發器中調用過程。

?

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;

-- 創建觸發器調用存儲過程 ...
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 創建替代(INSTEAD OF)觸發器

?

創建觸發器的一般語法是:

?

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 觸發器僅僅能在行級上觸發 , 所以沒有必要指定
[WHEN?condition]
PL / SQL_block? | ?CALL?procedure_name;

?

當中：

???????????INSTEAD OF 選項使ORACLE激活觸發器，而不運行觸發事件。 僅僅能對視圖和對象視圖建立INSTEAD OF觸發器，而不能對表、模式和數據庫建立INSTEAD OF 觸發器。

???????????FOR EACH ROW選項說明觸發器為行觸發器。行觸發器和語句觸發器的差別表如今：行觸發器要求當一個DML語句操走影響數據庫中的多行數據時，對于當中的每一個數據行，僅僅要它們符合觸發約束條件，均激活一次觸發器；而語句觸發器將整個語句操作作為觸發事件，當它符合約束條件時，激活一次觸發器。當省略FOR EACH ROW 選項時，BEFORE 和AFTER 觸發器為語句觸發器，而INSTEAD OF 觸發器則為行觸發器。

???????????REFERENCING 子句說明相關名稱，在行觸發器的PL/SQL塊和WHEN 子句中能夠使用相關名稱參照當前的新、舊列值，默認的相關名稱分別為OLD和NEW。觸發器的PL/SQL塊中應用相關名稱時，必須在它們之前加冒號(:)，但在WHEN子句中則不能加冒號。

WHEN 子句說明觸發約束條件。Condition 為一個邏輯表達時，當中必須包括相關名稱，而不能包括查詢語句，也不能調用PL/SQL 函數。WHEN 子句指定的觸發約束條件僅僅能用在BEFORE 和AFTER 行觸發器中，不能用在INSTEAD OF 行觸發器和其他類型的觸發器中。

?

??? INSTEAD_OF 用于對視圖的DML觸發，因為視圖有可能是由多個表進行聯結(join)而成，因而并不是是全部的聯結都是可更新的。但能夠依照所需的方式運行更新，比如以下情況：

例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: 此視圖的數據操縱操作非法

?

可是我們能夠創建INSTEAD_OF觸發器來為 DELETE 操作運行所需的處理，即刪除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： 創建復雜視圖，針對INSERT操作創建INSTEAD OF觸發器，向復雜視圖插入數據。

l???????? 創建視圖：

?

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????????創建觸發器：

?

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 ;

?

創建INSTEAD OF觸發器須要注意下面幾點：

l???????? 僅僅能被創建在視圖上，而且該視圖沒有指定WITH CHECK OPTION選項。

l???????? 不能指定BEFORE 或 AFTER選項。

l???????? FOR EACH ROW子但是可選的，即INSTEAD OF觸發器僅僅能在行級上觸發、或僅僅能是行級觸發器，沒有必要指定。

l???????? 沒有必要在針對一個表的視圖上創建INSTEAD OF觸發器，僅僅要創建DML觸發器就能夠了。

?

8.2.3 創建系統事件觸發器

??? ORACLE10G提供的系統事件觸發器能夠在DDL或數據庫系統上被觸發。DDL指的是數據定義語言，如CREATE 、ALTER及DROP 等。而數據庫系統事件包含數據庫server的啟動或關閉，用戶的登錄與退出、數據庫服務錯誤等。創建系統觸發器的語法例如以下：?

創建觸發器的一般語法是:

?

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：一個或多個數據庫事件，事件間用 OR 分開；

?

???????????系統事件觸發器既能夠建立在一個模式上，又能夠建立在整個數據庫上。當建立在模式(SCHEMA)之上時，僅僅有模式所指定用戶的DDL操作和它們所導致的錯誤才激活觸發器, 默認時為當前用戶模式。當建立在數據庫(DATABASE)之上時，該數據庫全部用戶的DDL操作和他們所導致的錯誤，以及數據庫的啟動和關閉均可激活觸發器。要在數據庫之上建立觸發器時，要求用戶具有ADMINISTER DATABASE TRIGGER權限。

?

以下給出系統觸發器的種類和事件出現的時機（前或后）：

事件	同意的時機	說明
STARTUP	AFTER	啟動數據庫實例之后觸發
SHUTDOWN	BEFORE	關閉數據庫實例之前觸發（非正常關閉不觸發）
SERVERERROR	AFTER	數據庫server錯誤發生之后觸發
LOGON	AFTER	成功登錄連接到數據庫后觸發
LOGOFF	BEFORE	開始斷開數據庫連接之前觸發
CREATE	BEFORE，AFTER	在運行CREATE語句創建數據庫對象之前、之后觸發
DROP	BEFORE，AFTER	在運行DROP語句刪除數據庫對象之前、之后觸發
ALTER	BEFORE，AFTER	在運行ALTER語句更新數據庫對象之前、之后觸發
DDL	BEFORE，AFTER	在運行大多數DDL語句之前、之后觸發
GRANT	BEFORE，AFTER	運行GRANT語句授予權限之前、之后觸發
REVOKE	BEFORE，AFTER	運行REVOKE語句收權限之前、之后觸犯發
RENAME	BEFORE，AFTER	運行RENAME語句更改數據庫對象名稱之前、之后觸犯發
AUDIT / NOAUDIT	BEFORE，AFTER	運行AUDIT 或 NOAUDIT進行審計或停止審計之前、之后觸發

?

?

8.2.4 系統觸發器事件屬性

?

事件屬性\事件	Startup/Shutdown	Servererror	Logon/Logoff	DDL	DML
事件名稱	ü＊	ü＊	ü＊	ü＊	＊
數據庫名稱	ü＊	?	?	?	?
數據庫實例號	ü＊	?	?	?	?
錯誤號	?	ü＊	?	?	?
username	?	?	ü＊	＊	?
模式對象類型	?	?	?	ü＊	＊
模式對象名稱	?	?	?	ü＊	＊
列	?	?	?	?	ü＊

?

除DML語句的列屬性外，其余事件屬性值可通過調用ORACLE定義的事件屬性函數來讀取。

函數名稱	數據類型	說??? 明
Ora_sysevent	VARCHAR2（20）	激活觸發器的事件名稱
Instance_num	NUMBER	數據庫實例名
Ora_database_name	VARCHAR2（50）	數據庫名稱
Server_error(posi)	NUMBER	錯誤信息棧中posi指定位置中的錯誤號
? ? Is_servererror(err_number)	? ? BOOLEAN	檢查err_number指定的錯誤號是否在錯誤信息棧中，假設在則返回TRUE，否則返回FALSE。在觸發器內調用此函數能夠推斷是否發生指定的錯誤。
Login_user	VARCHAR2(30)	登陸或注銷的username稱
Dictionary_obj_type	VARCHAR2(20)	DDL語句所操作的數據庫對象類型
Dictionary_obj_name	VARCHAR2(30)	DDL語句所操作的數據庫對象名稱
Dictionary_obj_owner	VARCHAR2(30)	DDL語句所操作的數據庫對象全部者名稱
Des_encrypted_password	VARCHAR2(2)	正在創建或改動的經過DES算法加密的用戶口令

?

例1： 創建觸發器，存放有關事件信息。

DESC ?ora_sysevent
DESC ?ora_login_user

-- 創建用于記錄事件用的表

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 ));

-- 創建觸犯發器
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： 創建登錄、退出觸發器。

?

CREATE ? TABLE ?log_event
( user_name ? VARCHAR2 ( 10 ),
?address? VARCHAR2 ( 20 ),?
?logon_date? timestamp ,
?logoff_date? timestamp );?

-- 創建登錄觸發器
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;
-- 創建退出觸發器
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 使用觸發器謂詞

??? ORACLE 提供三個參數INSERTING, UPDATING,DELETING 用于推斷觸發了哪些操作。

謂詞	行為
INSERTING	假設觸發語句是 INSERT 語句，則為TRUE,否則為FALSE
UPDATING	假設觸發語句是 UPDATE語句，則為TRUE,否則為FALSE
DELETING	假設觸發語句是 DELETE 語句，則為TRUE,否則為FALSE

?

8.2.6 又一次編譯觸發器

假設在觸發器內調用其他函數或過程，當這些函數或過程被刪除或改動后，觸發器的狀態將被標識為無效。當DML語句激活一個無效觸發器時，ORACLE將又一次編譯觸發器代碼，假設編譯時發現錯誤，這將導致DML語句運行失敗。

在PL/SQL程序中能夠調用ALTER TRIGGER語句又一次編譯已經創建的觸發器，格式為：???????????

ALTER ? TRIGGER ? [schema.] ?trigger_name?COMPILE? [?DEBUG]

?????? 當中：DEBUG 選項要器編譯器生成PL/SQL 程序條使其所使用的調試代碼。

8.3?刪除和使能觸發器

l???????? 刪除觸發器：

DROP ? TRIGGER ?trigger_name;

當刪除其它用戶模式中的觸發器名稱，須要具有DROP ANY TRIGGER系統權限，當刪除建立在數據庫上的觸發器時，用戶須要具有ADMINISTER DATABASE TRIGGER系統權限。

此外，當刪除表或視圖時，建立在這些對象上的觸發器也隨之刪除。?

l???????? 禁用或啟用觸發器

數據庫TRIGGER 的狀態：

有效狀態(ENABLE)：當觸發事件發生時，處于有效狀態的數據庫觸發器TRIGGER 將被觸發。

無效狀態(DISABLE)：當觸發事件發生時，處于無效狀態的數據庫觸發器TRIGGER 將不會被觸發，此時就跟沒有這個數據庫觸發器(TRIGGER) 一樣。

數據庫TRIGGER的這兩種狀態能夠互相轉換。格式為：

ALTER ?TIGGER?trigger_name? [DISABLE?|?ENABLE?] ;

-- 例： ALTER?TRIGGER?emp_view_delete?DISABLE;

???????????

???????????ALTER TRIGGER語句一次僅僅能改變一個觸發器的狀態，而 ALTER TABLE 語句則一次可以改變與指定表相關的全部觸發器的使用狀態 。格式為：?????????????

ALTER ? TABLE ? [schema.] table_name?{ENABLE | DISABLE}? ALL ?TRIGGERS;

-- 例：使表 EMP? 上的全部 TRIGGER? 失效：
ALTER ? TABLE ?emp?DISABLE? ALL ?TRIGGERS;?

?

8.4?觸發器和數據字典

相關數據字典： 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?? 數據庫觸發器的應用舉例

例1： 創建一個DML語句級觸發器，當對emp表運行INSERT, UPDATE, DELETE 操作時，它自己主動更新dept_summary 表中的數據。因為在PL/SQL塊中不能直接調用DDL語句，所以，利用ORACLE內置包DBMS_UTILITY中的EXEC_DDL_STATEMENT過程，由它運行DDL語句創建觸發器。

?

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;

-- 創建一個 PL/SQL 過程 disp_dept_summary
-- 在觸發器中調用該過程顯示 dept_summary 標中的數據。
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('' 正在運行 trig1? 觸發器 …'');
??????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 = 1000 ? WHERE ?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： 創建DML語句行級觸發器。當對emp表運行INSERT, UPDATE, DELETE 操作時，它自己主動更新dept_summary 表中的數據。因為在PL/SQL塊中不能直接調用DDL語句，所以，利用ORACLE內置包DBMS_UTILITY中的EXEC_DDL_STATEMENT過程，由它運行DDL語句創建觸發器。

?

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('' 正在運行 trig2_update? 觸發器 …'');
??????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('' 正在運行 trig2_insert? 觸發器 …'');
??????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('' 正在運行 trig2_delete? 觸發器 …'');
??????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.1 ? WHERE ?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創建與例2具有同樣功能的觸發器。

?

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('' 正在運行 trig2? 觸發器 …'');
????????????????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('' 正在運行 trig2 觸發器 …'');
????????????????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('' 正在運行 trig2 觸發器 …'');
????????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.1 ? WHERE ?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： 創建INSTEAD OF 觸發器。首先創建一個視圖myview,因為該視圖是復合查詢所產生的視圖，所以不能運行DML語句。依據用戶對視圖所插入的數據推斷須要將數據插入到哪個視圖基表中，然后對該基表運行插入操作。

?

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
????' );
???? --? 創建 INSTEAD?OF? 觸發器 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('' 正在運行 trig3 觸發器 …'');
????????????IF?:n.type?=?''D''?THEN
????????????????SELECT?COUNT(*)?INTO?rows
????????????????????FROM?dept?WHERE?deptno?=?:n.empno;
????????????????IF?rows?=?0?THEN
????????????????????DBMS_OUTPUT.PUT_LINE('' 向 dept 表中插入數據 …'');
????????????????????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 表中插入數據 …’’);
???????????????????? 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事件屬性函數，創建一個系統事件觸發器。首先創建一個事件日志表eventlog，由它存儲用戶在當前數據庫中所創建的數據庫對象，以及用戶的登陸和注銷、數據庫的啟動和關閉等事件，之后創建trig4_ddl、trig4_before和trig4_after觸發器，它們調用事件屬性函數將各個事件記錄到eventlog數據表中。

?

BEGIN
???? --? 創建用于記錄事件日志的數據表
????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
????????)
????' );

???? --? 創建 DDL 觸發器 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;
????' );

???? --? 創建 LOGON 、 STARTUP 和 SERVERERROR? 事件觸發器
????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;
??' );

?? --? 創建 LOGOFF 和 SHUTDOWN? 事件觸發器
??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?? 數據庫觸發器的應用實例

用戶能夠使用數據庫觸發器實現各種功能：

l???????? 復雜的審計功能；

例：將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? WITH ? 1000
????INCREMENT? BY ? 1
????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???????? 增強數據的完整性管理；

例：改動DEPT表的DEPTNO列時，同一時候把EMP表中對應的DEPTNO也作對應的改動；

?

CREATE ?SEQUENCE?update_sequence?
????INCREMENT? BY ? 1
????START? WITH ? 1000
????MAXVALUE? 5000 ?CYCLE;

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 = 25 ? WHERE ?deptno = 20 ;

?

l???????? 幫助實現安全控制；

例：保證對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 ? > 0 ? THEN
????RAISE?not_on_holidays;
END ? IF ;
???? /*?check?for?work?hours(8:00?AM?to?18:00?PM?*/
IF ?(TO_CHAR(SYSDATE, 'HH24' ) < 8 ? OR ?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 ;

?