Oracle觸發器2-DML觸發器

  DML觸發器是最常見的觸發器類型，開發人員用的比較多；而其他類型的觸發器主要是用於資料庫管理或者審計，DBA用的比較多。

1、DML觸發器簡介：

BEFORE 觸發器

這種觸發器是在某個操作發生之前觸發的，比如before insert就是在插入操作之前觸發。

AFTER 觸發器

這種觸發器是在某個操作發生之後觸發的，比如after update就是在插入操作之前觸發。

語句層級觸發器

這種觸發器是由整個SQL語句觸發的。這個SQL語句可能操作資料庫表的一條或者多條資料。

行層級觸發器

這種觸發器針對的是SQL語句執行過程中操作的每一行記錄。假設books表中有1000行記錄。下面的update語句就會修改1000行記錄：

update books set title = upper(title);

如果我在books表上定義了一個行層級的更新觸發器，這個觸發器就會被觸發1000次。

偽記錄 NEW

這是一個被叫做NEW的資料結構，看起來和PL/SQL中的記錄非常相似。只有在更新操作和插入操作的DML觸發器中才能使用這個偽記錄；這個記錄包含的是被操作的行修改之後的值。

偽記錄 OLD

這是一個被叫做OLD的資料結構，看起來和PL/SQL中的記錄非常相似。只有在更新操作和插入操作的DML觸發器中才能使用這個偽記錄；這個記錄包含的是被操作的行修改之前的值。

WHEN 子句

DML觸發器用這個子句來確定是否應該執行觸發器的代碼，我們可以用它來避免不必要的執行。

有關事務

DML觸發器會參與到觸發他們的事務中。

如果觸發器拋出了異常，這部分事務會復原（rollback）。

如果觸發器本身也執行了DML語句（比如向日誌表中插入一行資料），這個DML同時也會成為主體事務的一部分。

不能在DML觸發器裡執行commit或者rollback語句。

2、建立DML觸發器

1 CREATE [OR REPLACE] TRIGGER trigger_name   --指定一個觸發器名字， or replace 可選
2 {BEFORE | AFTER}     --指定觸發器時機是在語句執行之前或者之後。
3 {INSERT | DELETE | UPDATE | UPDATE OF column_list } ON table_name --指定觸發器應用的DML類型組合：插入、更新或者刪除操作。
4 [FOR EACH ROW]   --如果指定了for each row 則語句處理的每一行記錄都會啟用觸發器。
5 [WHEN (...)]            --通過這個可選的when子句，可以避免不必要的執行
6 [DECLARE ... ]  
7 BEGIN
8 ...executable statements...    --執行體
9 [EXCEPTION ... ]     --可選異常處理部分
10 END [trigger_name];    

Examples:

-- an after statement level trigger

CREATE OR REPLACE TRIGGER statement_trigger
AFTER INSERT ON to_table
BEGIN
  DBMS_OUTPUT.PUT_LINE('After Insert Statement Level');
END;
/
/*-- an after row level trigger */
CREATE OR REPLACE TRIGGER row_trigger
AFTER INSERT ON to_table
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('After Insert Row Level');
END;
/

-- a before statement level trigger
CREATE OR REPLACE TRIGGER before_statement_trigger
BEFORE INSERT ON to_table
BEGIN
  DBMS_OUTPUT.PUT_LINE('Before Insert Statement Level');
END;
/

-- a before row level trigger
CREATE OR REPLACE TRIGGER before_row_trigger
BEFORE INSERT ON to_table
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('Before Insert Row Level');
END;
/

-- after insert statement
CREATE OR REPLACE TRIGGER after_insert_statement
AFTER INSERT ON to_table
BEGIN
  DBMS_OUTPUT.PUT_LINE('After Insert Statement');
END;
/

-- after update statement
CREATE OR REPLACE TRIGGER after_update_statement
AFTER UPDATE ON to_table
BEGIN
  DBMS_OUTPUT.PUT_LINE('After Update Statement');
END;
/

-- after delete statement
CREATE OR REPLACE TRIGGER after_delete_statement
AFTER DELETE ON to_table
BEGIN
  DBMS_OUTPUT.PUT_LINE('After Delete Statement');
END;
/

2.1、使用WHEN子句

例如使用WHEN子句確保只有把薪水修改成不同的值時觸發器代碼才會執行：

CREATE OR REPLACE TRIGGER check_raise
   AFTER UPDATE OF salary
   ON employees
   FOR EACH ROW
   WHEN (   (old.salary != new.salary)
         OR (old.salary IS NULL AND new.salary IS NOT NULL)
         OR (old.salary IS NOT NULL AND new.salary IS NULL))
BEGIN
   NULL;
END;
/

WHEN子句使用注意事項：

a.要把整個判斷邏輯運算式括起來（）

b.不要在OLD和NEW之前加上”:”

c.使用WHEN子句時只能使用SQL內建函數；

2.2、使用NEW和OLD偽記錄

CREATE OR REPLACE TRIGGER bef_ins_ceo_comp
   BEFORE INSERT
   ON ceo_compensation
   FOR EACH ROW
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO ceo_comp_history
       VALUES (
                  :new.name
                , :old.compensation
                , :new.compensation
                , 'AFTER INSERT'
                , SYSDATE
              );

   COMMIT;
END;
/