一 創建DML 觸發器
DML觸發器基本要點：
觸發時機：指定觸發器的觸發時間。如果指定為BEFORE，則表示在執行DML操作之前觸發，以便防止某些錯誤操作發生或實現某些業務規則；如果指定為AFTER，則表示在執行DML操作之后觸發，以便記錄該操作或做某些事后處理。
觸發事件：引起觸發器被觸發的事件，即DML操作(INSERT、UPDATE、DELETE)。既可以是單個觸發事件，也可以是多個觸發事件的組合(只能使用OR邏輯組合，不能使用AND邏輯組合)。
條件謂詞：當在觸發器中包含多個觸發事件(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。
解發對象：指定觸發器是創建在哪個表、視圖上。
觸發類型：是語句級還是行級觸發器。
觸發條件：由WHEN子句指定一個邏輯表達式，只允許在行級觸發器上指定觸發條件，指定UPDATING后面的列的列表。
當觸發器被觸發時，要使用被插入、更新或刪除的記錄中的列值，有時要使用操作前、 后列的值。可以使用：NEW和 ：OLD 。
：NEW 修飾符表示操作完成后列的值
：OLD 修飾符表示操作完成前列的值
：NEW和：OLD的具體使用見表9-1。
表9-1 ：NEW和：OLD的值
特性 INSERT UPDATE DELETE
OLD NULL 實際值 實際值
NEW 實際值 實際值 NULL

【例9-1】建立職工表emp的日志表EMPPLOYEES_LOG。當職工表進行DML操作時候，把職工表具體的操作名稱，時間等信息插入到日志表中。
具體代碼如下：
–第九章\sfq.sql

–建立職工表
create table emp
(empno NUMBER(4)， --職工號
job VARCHAR2(10)， --崗位
sal NUMBER(7，2) --薪水
);
–往職工表中插入數據
insert into EMP
select ‘1105’，‘工程師’，6500 from dual;

Insert into EMP
select ‘1135’，‘質檢員’，3000 from dual;

Commit;

–建立日志表

create table EMPLOYEES_LOG
(
WHO VARCHAR2(30)，
WHEN DATE，
ACTION VARCHAR2(50)，
TALENME VARCHAR2(30)
)
CREATE OR REPLACE Trigger biud_employee_copy
Before insert or update or delete
On emp
Declare
tn VARCHAR2(10);
Begin
tn:=‘員工表’;
– :GLOBAL.USERNAME:=‘hzm’
if inserting then
Insert into employees_log(
Who，when，TALENME，action)
Values(user， sysdate，tn，‘插入新數據’);
END IF;
if updating then
Insert into employees_log(
Who，when，TALENME，action)
Values( user， sysdate，tn，‘更新數據’);
END IF;
if deleting then
Insert into employees_log(
Who，when，TALENME，action)
Values( user， sysdate，tn，‘刪除數據’);
END IF;
End;

–插入數據
insert into EMP
select ‘1237’，‘項目經理’，8000 from dual;

–刪除數據
Delete from EMP where empno=‘1135’;
Commit;

Select * from emp;

Select * from EMPLOYEES_LOG;

查詢emp 表數據，結果如圖9-1所示。

圖9-1查詢職工表的數據。

查詢日志表數據，結果如圖9-2所示。

圖9-2查詢日志表的數據。

【例9-2】建立職工表emp和審查表audit_emp_values，在職工表上建立一個觸發器，當該表進行DML 操作時候，記錄表中的舊值和新值，然后插入審查表。
具體代碼如下：
– 第九章\sfq.sql

–建立職工表
create table emp
(empno NUMBER(4)，
job VARCHAR2(10)，
sal NUMBER(7，2)
)
–建立審查表
CREATE TABLE audit_emp_values
(user_name VARCHAR2(50)，
timestamp DATE，
id NUMBER(4)，
old_last_name VARCHAR2(10)，
new_last_name VARCHAR2(10)，
old_title VARCHAR2(10)，
new_title VARCHAR2(10)，
old_salary NUMBER(7，2)，
new_salary NUMBER(7，2));

–建立行級觸發器
CREATE OR REPLACE TRIGGER audit_emp_values
AFTER
DELETE OR INSERT OR UPDATE ON emp
FOR EACH ROW
BEGIN
INSERT INTO audit_emp_values (user_name，
timestamp， id，
old_title， new_title， old_salary， new_salary)
VALUES (USER， SYSDATE， :old.empno，
:old.job， :new.job， :old.sal， :new.sal);
END;

----職工表執行dml 操作
insert into emp (empno，job，sal) values(9，‘worker’，3000);
insert into emp (empno，job，sal) values(8，‘hunter’，100);
Commit;
Update emp set sal=4500 where empno=‘8’ ;
Commit;
delete from emp where job=‘hunter’;

Select * from emp;

Select * from audit_emp_values;

查詢職工表，結果如圖9-3所示。

圖9-3執行DML操作后查詢職工表的數據。

查詢審查表，結果如圖9-4所示。

圖9-4執行DML操作后查詢職工表的數據
三創建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 觸發器。
【例9-3】建立視圖emp_view，取表emp記錄數和總工資數。建立INSTEAD OF
觸發器，當刪除表的數據后，可以刪除視圖的的數據，最后查詢刪除數據后視圖的數據。
建立觸發器，代碼如下：
– 第九章\sfq.sql

–建 emp表，插入記錄

create table emp
(empno NUMBER(4)，
job VARCHAR2(10)，
sal NUMBER(7，2)
);
insert into emp (empno，job，sal) values(9，‘worker’，3000);
Commit;

–建立視圖，統計職工表的總職工數和總工資
CREATE OR REPLACE VIEW emp_view AS
SELECT empno， count(*) total_employeer， sum(sal) total_salary
FROM emp GROUP BY empno;

–建立觸發器，當刪除職工表數據時候觸發
CREATE OR REPLACE TRIGGER emp_view_delete
INSTEAD OF DELETE ON emp_view FOR EACH ROW
BEGIN
DELETE FROM emp WHERE empno= :old.empno;
END emp_view_delete;

–刪除視圖數據
DELETE FROM emp_view WHERE empno=9;
Commit;
–查詢視圖的數據
Select * from emp_view

查詢視圖，結果如圖9-5所示

圖9-5查詢視圖emp_view的數據
四 創建系統事件觸發器
Oracle 10G提供的系統事件觸發器可以在DDL或數據庫系統上被觸發。DDL指的是數據定義語言，如CREATE 、ALTER及DROP 等。而數據庫系統事件包括數據庫服務器的啟動或關閉、用戶的登錄與退出、數據庫服務錯誤等。創建系統觸發器的語法如下：
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;
dl_event_list：一個或多個DDL 事件，多個事件中間用 OR 分開。
database_event_list：一個或多個數據庫事件，多個事件中間間用 OR 分開。
系統事件觸發器既可以建立在一個模式上，又可以建立在整個數據庫上。當建立在模式(SCHEMA)之上時，只有模式所指定用戶的DDL操作和它們所導致的錯誤才激活觸發器， 默認時為當前用戶模式。當建立在數據庫(DATABASE)之上時，該數據庫所有用戶的DDL操作和他們所導致的錯誤，以及數據庫的啟動和關閉均可激活觸發器。要在數據庫之上建立觸發器時，要求用戶具有ADMINISTER DATABASE TRIGGER權限。
系統觸發器的種類和事件出現的時機(前或后)，見表9-2。
表9-2 系統時間允許的時機
事件 允許的時機 說明
STARTUP AFTER 啟動數據庫實例之后觸發
SHUTDOWN BEFORE 關閉數據庫實例之前觸發(非正常關閉不觸發)
SERVERERROR AFTER 數據庫服務器發生錯誤之后觸發
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進行審計或停止審計之前、之后觸發

【例9-4】建立事件表ddl_event ，當數據庫有DML操作，把具體事件名稱和時間插入事件表中。
建立事件表，代碼如下：
–第九章\sfq.sql

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));
建立觸發器，當發生ddl操作，把時間名稱等插入事件表，代碼如下：
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;

【例9-5】建立登錄事件表log_event ，當數據庫有登錄或退出操作，把具體事件名稱和用戶等信息插入時間表中。
具體代碼如下：
–第九章\sfq.sql

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;
9.5觸發器的編譯和刪除
1)重新編譯觸發器
如果在觸發器內調用其它函數或過程，當這些函數或過程被刪除或修改后，觸發器的狀態將被標識為無效。當DML語句激活一個無效觸發器時，Oracle將重新編譯觸發器代碼，如果編譯時發現錯誤，這將導致DML語句執行失敗。
在PL/SQL程序中可以調用ALTER TRIGGER語句重新編譯已經創建的觸發器，格式如下：
ALTER TRIGGER [schema.] trigger_name COMPILE [ DEBUG]
其中：DEBUG 選項要器編譯器生成PL/SQL 程序條使其所使用的調試代碼。
2)　啟用觸發器的格式如下：
ALTER TRIGGER trigger_name ENABLE;
3)禁用觸發器的格式如下：
alter trigger trigger_name disable;
4)刪除觸發器格式如下：
　　DROP TRIGGER trigger_name;
當刪除其他用戶模式中的觸發器名稱，需要具有DROP ANY TRIGGER系統權限，當刪除建立在數據庫上的觸發器時，用戶需要具有ADMINISTER DATABASE TRIGGER系統權限。此外，當刪除表或視圖時，建立在這些對象上的觸發器也隨之刪除。
觸發器優點：
強化約束：強制復雜業務的規則和要求，能實現比check語句更為復雜的約束。
跟蹤變化：觸發器可以偵測數據庫內的操作，從而禁止數據庫中未經許可的更新和變化。
級聯運行：偵測數據庫內的操作時，可自動地級聯影響整個數據庫的各項內容。
嵌套調用：觸發器可以調用一個或多個存儲過程。觸發器最多可以嵌套32層。
缺點：性能較低。因為在運行觸發器時，系統處理的大部分時間花費在參照其他表的處理上，這些表既不在內存中也不在數據庫設備上，而刪除表和插入表總是位于內存中。