PL/SQL編程_觸發器

觸發器是一種特殊的存儲過程，它在創建後就存儲在資料庫中。
觸發器的特殊性在於它是建立在某個具體的表之上的，而且是自動激發執行的，如果用戶在這個表上執行了某個DML操作（ UPDATE 、INSERT 、DELETE ），觸發器就被激發執行。
觸發器常用於自動完成一些資料庫的維護工作。
例如，觸發器可以具有以下功能:
·可以對錶自動進行複雜的安全性、完整性檢查。
·可以在對錶進行DML操作之前或之後進行其他處理。
·進行審計，可以對錶上的操作進行跟蹤。
·實現不同節點間資料庫的同步更新。

觸發器的使用
觸發器是依附於某個具體的表的特殊存儲過程，它在某個DML操作的激發下自動執行。
在創建觸發器時應該仔細考慮如它的相關信息。
具體地說，應該考慮以下幾個方面的問題：
1 ）觸發器應該建立在哪個表之上。
2 ）觸發器應該對什麼樣的DML操作進行相應。
3 ）觸發器在指定的DML操作之前激發還是在之後撤發。
4 ）對每次DML相應一次，還是對受DML操作影響的每一行數據都響應一次。
在確定了觸發器的實現細節後，現在，就可以創建觸發器了。
創建觸發器的語法格式為：
CREATE [OR REPLACE] TRIGGER 觸發器名
BEFORE | AFTER | INSTEAD OF
DELETE | INSERT | UPDATE [OF 列名]
ON 表名
[FOR EACH ROW [WHEN 條件]]
BEGIN
PL/SQL語句;
END;
在創建觸發器的語法結構中，用方括弧限定的部分是可選的，可以根據需要選用。
創建觸發器的命令是CREATE TRIGGER ，根據指定的名字創建一個觸發器。
OR REPLACE子句的作用是如果已經存在同名的觸發器，則刪除它，並重新創建。
觸發器可以是前激發的（ BEFORE ），也可以是後激發的（ AFTER ）。
如果是前激發的，則觸發器在DML語句執行之前激發。
如果是後激發的，則觸發器在DML語句執行之後激發。
用BEFORE關鍵字創建的觸發器是前激發的，用AFTER關鍵字創建的觸發器是後激發的，這兩個關鍵字只能使用其一。
INSTEAD OF子句僅用於視圖上的觸發器。
觸發器可以被任何DML命令激發，包括INSERT 、DELETE和UPDATE 。
如果希望其中的一種、兩種或三種命令能夠激發該觸發器，則可以指定它們之間的任意組合，兩種不同命令之間用OR分開。
如果指定了UPDATE命令，還可以進一步指定當表中的哪個列受到UPDATE命令的影響時激發該觸發器。
當在指定的表上執行指定的DML命令時，將會激發觸發器，觸發器將對這樣的操作進行必要的響應。
觸發器可能對每次單獨的DML操作響應一次，也可能對每次DML操作所影響的每一行數據響應一次。
如果對每次單獨的DML操作響應一次，觸發器執行的次數與受影晌的行數無關，這樣的觸發器叫做語句級觸發器。
如果對受影響的每一行數據都響應一次，那麼觸發器執行的次數等於受影響的行數，這樣的觸發器叫做行觸發器。
FOR EACH ROW子句的作用是指定創建的觸發器為行觸發器。
如果沒有這樣的子句，則創建的觸發器為語句級觸發器。

由關鍵字BEGIN和END限定的部分是觸發器的代碼，也就是觸發器被激發時所執行的代碼。
代碼的編寫方法與普通PL/SQL塊的編寫方法相同。

在觸發器中可以定義變數，也可以進行異常處理，如果發生異常，就執行相應的異常處理程式。

例如，下麵創建的觸發器是為了監視用戶對錶EMP中的數據所進行的刪除操作。

如果有這樣的訪問，則列印相應的信息。

CREATE OR REPLACE TRIGGER tri_emp
AFTER DELETE
ON emp
BEGIN
DBMS_OUTPUT.PUT_LINE('正在對錶emp進行刪除操作');
END;

從觸發器的執行情況可以看出，無論用戶通過DELETE命令刪除0行、1行或者多行數據，這個觸發器只對每次DELETE操作激發一次，所以這是一個典型的語句級觸發器。
如果一個觸發器不再使用，那麼可以刪除它。

刪除觸發器的語法為：

DROP TRIGGER 觸發器名;

例如，要刪除剛纔創建的觸發器tri_emp，使用的語句為：

DROP TRIGGER tri_emp;

觸發器的創建者和資料庫管理員可以使觸發器失效。

觸發器失效後將暫時不起作用，直到再次使它有效。
使觸發器失效的命令格式為：

ALTER TRIGGER 觸發器名 DISABLE;

觸發器失效後只是暫時不起作用，它仍然存在於資料庫中，使用命令可以使它再次起作用。
使觸發器再次有效的命令格式為：

ALTER TRIGGER 觸發器名ENABLE;

例如，下麵的兩條命令先使觸發器tri_emp 失效，然後使其再次有效：

ALTER TRIGGER tri_emp DISABLE;

ALTER TRIGGER tri_emp ENABLE;

語句級觸發器

如果一個觸發器在用戶每次進行DML操作時被激發而且執行一次，而不管這個DML操作影響了多少行數據，這個觸發器就是語句級觸發器。
語句級觸發器有前激發和後激發兩種形式。

前激發觸發器是在DML操作執行之前被激發執行，後激發觸發器是在DML操作執行之後被激發執行。
無論是哪種形式，觸發器都將執行一次。
例如，我們創建一個前激發觸發器tri_emp，當用戶對錶EMP的DEPTNO列進行UPDATE操
作時該觸發器將被激發。

創建該觸發器的語句為：

CREATE OR REPLACE TRIGGER tri_emp
BEFORE UPDATE OF deptno
ON emp
BEGIN
DBMS_OUTPUT.PUT_LINE('正在對錶emp進行修改DEPTNO列操作');
END;

從UPDATE語句的執行結果可以看出，實際受影響的有6行數據，但是觸發器只執行了一次。
實際上不管這條語句影響了一行、兩行還是多行，或者沒有影響任何行，這個觸發器都將在UPDATE語句執行之前被激發執行一次。
再考慮下麵的UPDATE語句，相信會對語句級觸發器的執行有更深的理解。
這次我們修改一個根本不存在的部門編號3 。

update emp set deptno=10 WHERE deptno=3;

如果更關心用戶對錶所實施的訪問本身，而不是該次訪問影響的數據行數，這時可以在表上創建語句級觸發器。
在觸發器中可以使用三個條件謂詞，這三個謂詞用來判斷當前所執行的操作。

它們是：
•INSERTING ：如果激發觸發器的操作是INSERT ，則結果為真，否則為假
•UPDATING ： 如果激發觸發器的操作是UPDATE ，則結果為真，否則為假
•DELETING ：如果激發觸發器的操作是DELETE ，則結果為真，否則為假
這三個條件謂詞通常作為IF語句的條件，用來判斷用戶當前所進行的操作。
如果要對用戶在表emp上進行的所有DML操作進行監視，可以在這個表上創建一個觸發器，將用戶的所有DML操作作為日誌記錄下來。
為此，我們先創建一個表emp_log ，它的結構及各列的意義如下所示。

列　　　　　　類型　　　　 意義

oper_user　　char(10)　　執行DML操作的用戶

oper_type　　char(10)　　DML操作的類型

oper_time　　char(20)　　執行DML操作的時間

然後，在表emp上創建一個後激發的觸發器emp_dml_trg。

只要用戶在表上進行DML操作，這個觸發器就會將執行這個操作的用戶以及操作類型和操作時間記錄在表emp_log 中，而不管這樣的操作影響了多少行數據。

CREATE OR REPLACE TRIGGER emp_dml_trg
AFTER INSERT OR UPDATE OR DELETE
ON emp
DECLARE
dml_type char(10);
BEGIN
if INSERTING then
dml_type := 'INSERT';
elsif UPDATING then
dml_type:='UPDATE';
elsif DELETING then
dml_type:='DELETE';
END if;
INSERT INTO emp_log
VALUES(user,dml_type, to_char(sysdate , 'yyyy-mm-dd hh:mi:ss' ));
END;

如果用戶在表emp上進行了以下DML操作：

UPDATE emp set sal=sal+100;

DELETE FROM emp WHERE deptno=20;

那麼這二個操作的信息都將被記錄在表emp_log 中。

行觸發器 

如果在某個表上創建了一個觸發器，在對這個表進行DML操作時，每影響一行數據，該觸發器都將被激發執行一次，那麼這個觸發器就是行觸發器。
首先看一個非常簡單的觸發器的例子：

CREATE OR REPLACE TRIGGER tri_emp
AFTER DELETE
ON emp
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('一行已被刪除');
END;

這個觸發器是後觸發的。

如果對錶emp進行DELETE操作，將激發這個觸發器的執行。

在創建觸發器時使用了FOR EACH ROW子句，因此這個觸發器是行觸發器，例如，如果執行了以下的DELETE語句：

DELETE FROM emp WHERE deptno=20;

執行結果中的最後一行是SQL*Plus的統計信息，表明有5行受到DELETE語句的影響。

從觸發器執行的輸出信息可以看出，觸發器確實執行了5次。
也就是說，觸發器執行的次數等於受DML操作影響的數據行數。
如果DML操作沒有影響到任何一行數據，那麼觸發器將不執行。

例如，如果要根據一個不存在的deptno列的值從emp表中刪除行：

DELETE FROM emp WHERE deptno=200;

可見，如果DML語句沒有影響到任何行，觸發器將不會執行。
如果更關心DML語句對每行數據的訪問情況，而不是DML操作本身的信息，那麼在表上創建行觸發器更合適。
在創建行觸發器時，可以指定一些條件，這樣只有當特定的數據受到DML語句影響時，觸發器才被激發執行。
創建觸發器時，可以在FOR EACH ROW子句之後使用WHEN子句指定條件。

例如，重新考慮上面的簡單的行觸發器。

如果只對部門30進行監視，只有當從表emp 中刪除deptno列值為30的行時，才激發觸發器。
這個觸發器可以這樣創建：

CREATE OR REPLACE TRIGGER tri_emp
AFTER DELETE
ON emp
FOR EACH ROW WHEN (old.deptno=30)
BEGIN
DBMS_OUTPUT.PUT_LINE('一行已被刪除');
END;

如果執行兩次DELETE操作，分別刪除deptno 為30和20的行:

DELETE FROM emp WHERE deptno=30;

DELETE FROM emp WHERE deptno=20;

可見，當從表emp 中刪除部門30的員工時，觸發器被激發，而刪除其他部門的員工時，沒有激發觸發器。
在行觸發器中，同樣可以使用條件謂詞INSERT 、UPDATING和DELETING ，以判斷當前所進行的DML操作。
行觸發器通常用於對用戶的DML操作進行合法性檢查，使得用戶修改數據的操作必須按照一定的規則進行。

為了能夠比較修改前和修改後的數據，在觸發器的可執行代碼中，可以使用兩個關聯行NEW和OLD 。
它們分別表示觸發器被激發時，當前行的原數據和新數據。
NEW表示修改後的行，通過NEW可以引用新行中的各個列的值，如NEW.ename 。

OLD表示修改前的行，通過OLD可以引用原來的各個列的值，如OLD.ename 。
對於UPDATE命令， OLD表示原來的行， NEW表示修改後的行。

對於INSERT 命令， OLD沒有意義， NEW表示新寫人的一行。
對於DELETE命令， NEW沒有意義， OLD表示被刪除的一行。
在觸發器的可執行代碼中，如果要通過OLD和NEW引用某個列的值，要在前面加上“：”在其他地方，則不用使用“：”。
現在，我們要創建一個比較複雜的觸發器，這個觸發器對錶emp上的DML操作進行監視。
這個觸發器需要滿足以下要求：
1 ）在DML操作執行之前進行合法性檢查。
2 ）如果要從表emp 中刪除一行數據，不能刪除部門30的員工。

如果是其他部門的員工，則刪除這行數據，並把這一行數據在另一個表中進行備份。
3 ）如果要寫入一行數據，要保證這個員工的工資高於1000 。
4 ）如果修改表emp 的sa列 ，應保證新工資比原工資要高，並把員工的工資變化情況記錄在另一個表中。
為此，我們需創建兩個表，一個是del_action ，用來保存從表emp 中刪除的行，它的結構與表emp相同。
可以使用下麵的語句創建一個空表del_action :

CREATE TABLE del_action
AS
SELECT * FROM emp
WHERE deptno=0;

第二個表是update_action ，用來記錄員工工資的變化情況。

CREATE TABLE update_action
(
empno NUMBER(4),
ename VARCHAR2(10),
old_sal NUMBER(7,2),
new_sal NUMBER(7,2),
update_time CHAR(20),
oper_user CHAR(10)
);

現在我們可以根據上面的要求創建觸發器了。

創建這個觸發器的語句為：

CREATE OR REPLACE TRIGGER emp_dml_trg
BEFORE
INSERT OR DELETE OR UPDATE OF sal
ON emp
FOR EACH ROW
BEGIN
if INSERTING then
if :new.sal is null or :new.sal<1000 then
dbms_output.put_line ( '新員工的工資不得低於1000 元' ) ;
END if;
END if;
if UPDATING then
if :new.sal<=:old.sal then
dbms_output.put_line ('員工' ||:old.empno|| '的工資沒有增加');
else
INSERT INTO update_action VALUES(
:old.deptno,:old.ename, :old.sal,:new.sal,to_char(sysdate,'yyyy-mm-dd hh:mi:ss'), user);
END if;
END if;
if DELETING then
if :old.deptno=30 then
dbms_output.put_line ('部門' || :old.deptno || '的員工不得刪除');
else
INSERT INTO del_action VALUES(
:old.deptno,:old.ename,:old.job,:old.mgr, :old.hiredate, :old.sal, :old.comm, :old.deptno);
END if;
END if;
END;

在觸發器中指定的規則與表上的約束不同。

約束是一種強制性的規則，如果DML操作違反了約束，那麼這次DML操作是無效的，對數據將不做任何修改。
如果DML操作違反了觸發器的規則，觸發器將按照既定的方法進行響應，但是它並不阻止DML語句的執行。
例如，如果用戶執行下麵的DELETE語句，從表emp 中刪除數據：

DELETE FROM emp WHERE deptno=30;

一方面，觸發器按照既定的方法進行響應，結果是正確的。

另一方面， DELETE語句卻順利執行了。
這是因為，觸發器只能對用戶的DML操作進行合法性檢查，但並不能阻止DML操作的執行，它並不是一種強制性的規則。
如果觸發器在檢查到不合規定的DML操作時，拋出一個異常，那麼這次DML操作對數據所做的修改是無效的，也就是說，觸發器阻止了DML語句的執行。
例如，把上面的觸發器emp_dml_trg重新進行定義，則可以保證不合規定的DML操作不會影響任何數據。
創建新的觸發器的代碼如下：

CREATE OR REPLACE TRIGGER emp_dml_trg
BEFORE
INSERT OR DELETE OR UPDATE OF sal
ON emp
FOR EACH ROW
BEGIN
if INSERTING then
if :new.sal is null or :new.sal<1000 then
raise_application_error (-20000 ,'新員工的工資不得低於1000 元');
END if;
END if;
if UPDATING then
if :new.sal<=:old.sal then
dbms_output.put_line ('員工' ||:old.empno|| '的工資沒有增加');
else
INSERT INTO update_action VALUES(
:old.deptno,:old.ename, :old.sal,:new.sal,to_char(sysdate,'yyyy-mm-dd hh:mi:ss'), user);
END if;
END if;
if DELETING then
if :old.deptno=30 then
raise_application_error(-20001, '部門' || :old.deptno || '的員工不得刪除');
else
INSERT INTO del_action VALUES(
:old.deptno,:old.ename,:old.job,:old.mgr, :old.hiredate, :old.sal, :old.comm, :old.deptno);
END if;
END if;
END;

這樣，如果用戶執行了不合規定的DML操作，觸發器將拋出相應的異常，並終止觸發器的執行，用戶的DML操作不會對錶中的數據造成任何改變。
例如，如果用戶試圖修改員工的工資，使每個人的工資比原來少100, UPDATE語句的執行結果為：

UPDATE emp SET sal=sal-100;

視圖上的觸發器

視圖是建立在一個或多個表之上的虛表。

視圖中的數據來自對基表的查詢。

對於簡單視圖，可以對其進行DELETE 、UPDATE和INSERT等DML操作。
如果是複雜視圖，則不允許對其執行DML操作。

在視圖上也可以建立觸發器。

建立視圖觸發器的語法為：

CREATE [OR REPLACE] TRIGGER 觸發器
INSTEAD OF
INSERT | DELETE| UPDATE [OF 列名]
ON 視圖
[FOR EACH ROW]
BEGIN
PL/SQL語句;
END;

與表上的觸發器相比，視圖上的觸發器既不是前觸發（ BEFORE ）的，也不是後觸發(AFTER ）的，而是“INSTEAD OF ”類型的。
“INSTEAD OF”的意思是當在視圖上進行DML操作時，不去執行指定的DML語句，而是執行觸發器的代碼，這樣對視圖進行的DML操作根本就不會執行，取代它執行的是觸發器的代碼。
視圖上的觸發器都是行觸發器，因此，在創建觸發器時，“FOR EACH ROW”子句可以省略。
對視圖進行DML操作時，受影響的每一行數據都會激發觸發器執行一次。

在觸發器中也可以使用關聯行NEW和OLD 。
如果是簡單視圖，可以對其進行DML操作。

在簡單視圖上建立觸發器的目的一般是為了進行安全校驗，例如，我們可以在簡單視圖上建立觸發器，禁止某些DML操作，或者將一些DML操作轉化為其他的操作。
現在，我們首先用下麵的SQL語句在表emp上建立一個簡單視圖view_1 。

CREATE VIEW view_1 AS
SELECT ename, empno, sal, deptno FROM emp;

接下來，我們在視圖view_ 1 上建立一個觸發器trg_emp_ view ，如果對這個視圖進行DELETE或者UPDATE操作時，將激發這個觸發器。

CREATE OR REPLACE TRIGGER trg_emp_view
INSTEAD OF
DELETE OR UPDATE
ON view_1
FOR EACH ROW
BEGIN
if deleting then
if :old.deptno=20 then
raise_application_error(-20001 ,'部門20 的員工不能刪除');
END if;
END if;
if updating then
if :new.sal<=:old.sal then
raise_application_error(-20002 ,'該員工的工資沒有增加');
END if;
END if;
END;

這個觸發器的目的是當刪除部門20的員工時拋出異常，或者減少員工的工資時拋出異常。
如果執行下麵的DML語句：

DELETE FROM view_1 WHERE deptno=20;

如果執行下麵的DML語句：

DELETE FROM view_1 WHERE deptno<>20;

執行結果表明最後一條DML語句似乎已經順利執行，但是查詢視圖view＿1的結果表明這條DML語句並沒有執行。
這是因為當對視圖執行規定的DML命令時，將激發觸發器， DML操作不會真正執行，取代它執行的是觸發器的代碼。
在上面的觸發器中，僅僅對刪除部門30的操作進行判斷，而對其他部門的刪除操作沒有做任何進一步的處理，所以會得到數據已被刪除的假象。
這一點與表上的觸發器是完全不同的，使用時要特別小心。
如果對上述視圖進行UPDATE操作，將得到同樣的結果。

如果減少員工的工資，將產生一個異常。
如果增加員工工資，錶面上看UPDATE操作已經執行成功，實際上工資並沒有修改。
如果是複雜視圖，則不能在視圖上進行DML操作。

複雜視圖是建立在多個表之上的視圖，或者是使用了分組函數的視圖。
考慮用以下SQL語句創建的視圖：

CREATE OR REPLACE VIEW view_2
AS
SELECT ename, empno, sal, emp.deptno,dname
FROM emp, dept
WHERE emp.deptno=dept.deptno;

如果要在這個視圖上執行DML操作，將會發生錯誤。

例如：

INSERT INTO view_2 VALUES (' SMITH' , 7799 , 2000 , 10 ,' SALES');

如果在這個視圖上創建一個觸發器，那麼當在這個視圖上進行DML操作時，將激發觸發器的執行。
如果在觸發器中指定了其他操作，DML語句就會以其他方式執行，這樣就把用戶的DML語句轉化為其他的SQL語句了。
例如，對於視圖view_2上的INSERT操作，可以將其轉化為另一條INSERT語句，用於向emp表中插入一行。

當然，如果在寫入數據時指定的部門號在表dept 中不存在，觸發器的執行就會發生錯誤。
對於DELETE操作，可以將其轉化為另一條DELETE語句，僅從EMP表中刪除相關部門的員工。
創建這個觸發器的語句為：

CREATE OR REPLACE TRIGGER trg_1
INSTEAD OF
INSERT OR DELETE
ON view_2
FOR EACH ROW
BEGIN
if INSERTING then
INSERT INTO emp(ename,empno,sal,deptno)
VALUES ( :new.ename, :new.empno, :new.sal, :new.deptno) ;
END if;
if DELETING then
DELETE FROM emp WHERE empno=:old.empno;
END if;
END;

創建觸發器後，視圖上的DML語句就可以順利執行了。

當然， DML語句本身並沒有執行，而是轉化為另外的DML語句執行了。
例如，再執行剛纔的INSERT語句：

INSERT INTO view_2 VALUES (' SMITH' , 7799 , 2000 , 10 ,' SALES');

在視圖上進行的DML操作還得遵守基表上的完整性約束。

例如，如果在視圖view_2上執行INSERT操作，如果指定的部門號在dept表中不存在，或者指定的員工號在表emp 中已經存在，都將違反完整性約束。
例如：
INSERT INTO view_2 VALUES ( 'PANDA ', 8800,2000,90 ,' DEVELOP ');

發生錯誤的原因是在表dept中不存在部門90 ，從而違反了兩個表間的關聯完整性。

與觸發器有關的數據字典

觸發器是一種特殊的存儲程式，從被創建之時起，觸發器就被存儲在資料庫中，直到被刪除。
觸發器與一般存儲過程或者存儲函數的區別在於觸發器可以自動執行，而一般的存儲過程或者存儲函數需要調用才能執行。
與觸發器有關的數據字典有：
• USER_TRIGGERS ：存儲當前用戶所擁有的觸發器。
• DBA_TRIGGERS ：存儲所有的觸發器。
• ALL_TRIGGERS ： 存儲當前用戶可訪問的觸發器。

• USER_OBJECTS ：存儲當前用戶所擁有的對象，包括觸發器。
• DBA_OBJECTS : 存儲管理員所擁有的對象，包括觸發器。
• ALL_OBJECTS ：存儲資料庫中所有的對象，包括觸發器。

下麵是有關數據字典視圖ALL_TRIGGERS的信息：

ALL_TRIGGERS describes the triggers on tables accessible to the current user.

ALL_TRIGGERS描述當前用戶可訪問的基於表的觸發器。

If the user has the CREATE ANY TRIGGER privilege, then this view describes all triggers in the database.

如果用戶有 CREATE ANY TRIGGER 許可權，則這個視圖描述資料庫中的所有觸發器。

Related Views

• DBA_TRIGGERS describes all triggers in the database.

• USER_TRIGGERS describes the triggers owned by the current user. This view does not display the OWNER column.

可以查詢視圖user_triggers 
例如，要想瞭解觸發器TRG_1 的類型、觸發事件、所基於的對象類型和名稱、狀態等信息，可以查詢視圖user_triggers 。

SELECT trigger_type, triggering_event,base_object_type,
TABLE_NAME,status
FROM user_triggers
WHERE trigger_name='TRG_1';

觸發器中的代碼可以從數據字典視圖user_triggers的trigger_body列中獲得，如果想查看觸發器的代碼，可以對這個列進行檢索。
例如：

SELECT trigger_body
FROM user_triggers
WHERE trigger_name='TRG_1';

觸發器的信息也可以從其他視圖中獲得。

例如，視圖user_objects 記錄了當前用戶所擁有的所有對象，其中包括觸發器。
在這個視圖中，觸發器是object_type列的值為TRIGGER的對象。