Oracle資料庫中的觸發器

Oracle資料庫中的觸發器

觸發器是一種特殊的預存程序，它在發生某種資料庫事件時由Oracle系統自動觸發。觸發器通常用於加強資料庫的完整性條件約束和商務規則等，對於表來說，觸發器可以實現比CHECK約束更為複雜的約束。
Oracle中的觸發器的類型主要有DML觸發器、替代觸發器、系統事件觸發器和DDL觸發器。

觸發器的分類
DML觸發器
DML觸發器由DML語句觸發，例如INSERT、UPDATE和DELETE語句,其對應的trigger_event具體內容如下：
{INSERT|DELETE|UPDATE[OF column[，...]]}1
針對所有的DML事件，按照觸發器的觸發時間可以將DML觸發器分為BEFORE觸發器和AFTER觸發器，分別表示在DML事件發生之前與之後採取行動。

--------------------------------------------------------------------------------

BEFORE行觸發器
在開發資料庫應用時，為了確保資料符合商業或企業規則，應該使用約束對輸入資料加以限制，在某些情況下約束可能無法是想複雜的商業邏輯或企業規則，此時因考慮BEFORE行觸發器。
AFTER行觸發器
為了審計DML操作，可以使用語句級觸發器或Oracle提供的審計功能，而為了審計資料變化，則應使用AFTER行觸發器。

--------------------------------------------------------------------------------

另外，DML觸發器也可以分為語句級觸發器和行級觸發器，語句級觸發器針對某一條語句觸發一次，而行級觸發器針對語句所影響的每一行都觸發一次。
可以將DML操作細化到列，即針對某列進行DML操作時啟用觸發器。
在進行行級觸發器中，為了擷取某列在DML操作前後的資料，Oracle提供了兩種特殊的標示符：:OLD和:NEW，通過:OLD.column_name和:NEW.column_name分別擷取該列的舊資料和新資料。INSERT觸發器只能使用:NEW，DELETE只能使用:OLD，而UPDATE則兩種都可以使用。

例1：
1.首先以SYSDBA身份登入，建立使用者SIEGE，賦予相關許可權（此步可省略）：

CREATE USER siege IDENTIFIED BY "123456";
GRANT CREATE SESSION TO siege;
GRANT CREATE TABLE TO siege;
GRANT CREATE TABLESPACE TO siege;
GRANT CREATE VIEW TO siege;
GRANT CREATE ANY TRIGGER TO SIEGE;
CREATE TABLESPACE learning;
DATAFILE 'D:\oraclexe\app\oracle\oradata\XE\leaning.dbf'
SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M;

ALTER USER SIEGE DEFAULT TABLESPACE leaning;
ALTER USER SIEGE QUOTA UNLIMITED ON leaning;  1
2.建立student表和record表，並插入相關資料:

CREATE TABLE student(
    sid  NUMBER(4),
    sname VARCHAR2(10),
    sage NUMBER(4)
);
CREATE TABLE record(
    content VARCHAR2(80),
    rtime timestamp
);
INSERT INTO student VALUES(1001,'CANDY',24);
INSERT INTO student VALUES(1002,'TRACY',25);
INSERT INTO student VALUES(1003,'SAM',23);1
3.建立student表的觸發器：

CREATE OR REPLACE TRIGGER update_student_trigger
AFTER UPDATE
ON student
FOR EACH ROW
BEGIN
  INSERT INTO RECORD VALUES('執行了UPDATE操作，執行該操作前的資料為：sid='||:OLD.sid,SYSDATE);
END    update_student_trigger;1
4.測試觸發器：
修改student表資料：

UPDATE student SET  sage=26;1
此時查看record資料，發現更新了3條資料，說明該觸發器為行觸發器：

1 執行了UPDATE操作，執行該操作前的資料為：sid=1001 15-FEB-15 00.03.42.000000
2 執行了UPDATE操作，執行該操作前的資料為：sid=1002 15-FEB-15 00.03.42.000000
3 執行了UPDATE操作，執行該操作前的資料為：sid=1003 15-FEB-15 00.03.42.000000

INSTEAD OF觸發器
INSTEAD OF觸發器（替代觸發器），用於執行一個替代操作來代替觸發事件的操作，例如針對INSERT事件的INSTEAD OF觸發器，它由INSERT語句觸發，當出現INSERT語句時，該語句不會被執行，而是執行INSTEAD OF觸發器中定義的語句。
不過，Oracle中的INSTEAD OF觸發器不能針對錶，而只能針對視圖。並不是視圖的所有列都支援， 例如對列進行了數學或Function Compute，則不能對該列進行DML操作，這時可以使用INSTEAD OF觸發器。
例2：
針對例1中的student表進行建立視圖，其語句如下：

CREATE VIEW student_view
AS
SELECT sid,sname,sage+1 NEW_age
FROM student
WITH CHECK OPTION;1
建立好視圖後，進行插入操作：

INSERT INTO student_view VALUES(1004,'PETER',22);1
此時，報ORA-01773錯誤。對此我們應該對student_view建立INSTEAD OF觸發器：

CREATE OR REPLACE TRIGGER insteadof_student_view
INSTEAD OF INSERT
ON student_view
FOR EACH ROW
BEGIN
INSERT INTO student (sid,sname,sage)
VALUES(:NEW.sid,:NEW.sname,:NEW.new_age);
END insteadof_student_view;
1
建立好觸發器後，再次執行上面的插入語句，則可以成功進行插入了。

系統事件觸發器
系統事件觸發器在發生如資料庫啟動或關閉等系統事件時觸發。建立系統事件觸發器需要使用ON DATABASE子句，即表示建立的觸發器是資料庫級觸發器，同時建立系統事件觸發器需要具有DBA許可權。系統事件如下：

系統事件	說明
LOGOFF	使用者從資料庫登出
LOGON	使用者登入資料庫
SERVERERROR	伺服器發生錯誤
SHUTDOWN	關閉資料庫執行個體

例3：
以SYSDBA身份登入，建立table_event：

CREATE TABLE  table_event(
      event VARCHAR2(30),
      time DATE
)
1
然後建立tr_startup觸發器和tr_shutdown觸發器:

CREATE OR REPLACE TRIGGER tr_startup
AFTER startup ON DATABASE
BEGIN
      INSERT INTO table_event VALUES (ora_sysevent,SYSDATE);
END tr_startup;   
1

--------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER tr_shutdown
BEFORE shutdown ON DATABASE
BEGIN
      INSERT INTO table_event  VALUES (ora_sysevent,SYSDATE);
END tr_shutdown;    1
然後關閉和啟動資料庫，查看table_event表：

SHUTDOWN 16/02/2015 01:01:02
STARTUP 16/02/2015 01:01:56

說明系統觸發器已起作用了。

DDL觸發器
DDL觸發器由DDL語句觸發，如CREATE、ALTER和DROP語句。DDL觸發器同樣分為BEFORE觸發器與AFTER觸發器。
觸發器的建立
建立觸發器需要使用CREATE TRIGGER語句，其文法如下：

CREATE [OR REPLACE] TRIGGER trigger_name
[BEFORE|AFTER|INSTEAD OF] trigger_event
{ON table_name|view_name|DATABASE}
[FOR EACH ROW]
[ENABLE|DISABLE]
[WHEN trigger_condition]
[DECLARE declaration_statements;]
BEGIN
    trigger_body;
END [trigger_name];