oracle 觸發器的例項

觸發器使用教程和命名規範

目  錄
觸發器使用教程和命名規範 1
1，觸發器簡介 1
2，觸發器示例 2
3，觸發器語法和功能 3
4，例一：行級觸發器之一 4
5，例二：行級觸發器之二 4
6，例三：INSTEAD OF觸發器 6
7，例四：語句級觸發器之一 8
8，例五：語句級觸發器之二 9
9，例六：用包封裝觸發器程式碼 10
10，觸發器命名規範 11

1，觸發器簡介
觸發器（Trigger）是資料庫物件的一種，編碼方式類似儲存過程，與某張表（Table）相關聯，當有DML語句對錶進行操作時，可以引起觸發器的執 行，達到對插入記錄一致性，正確性和規範性控制的目的。在當年C/S時代盛行的時候，由於客戶端直接連線資料庫，能保證資料庫一致性的只有資料庫本身，此 時主鍵（Primary Key），外來鍵（Foreign Key），約束（Constraint）和觸發器成為必要的控制機制。而觸發器的實現比較靈活，可程式設計性強，自然成為了最流行的控制機制。到了B/S時 代，發展成4層架構，客戶端不再能直接訪問資料庫，只有中介軟體才可以訪問資料庫。要控制資料庫的一致性，既可以在中介軟體裡控制，也可以在資料庫端控制。很 多的青睞Java的開發者，隨之將資料庫當成一個黑盒，把大多數的資料控制工作放在了Servlet中執行。這樣做，不需要了解太多的資料庫知識，也減少 了資料庫程式設計的複雜性，但同時增加了Servlet程式設計的工作量。從架構設計來看，中介軟體的功能是檢查業務正確性和執行業務邏輯，如果把資料的一致性檢查 放到中介軟體去做，需要在所有涉及到資料寫入的地方進行資料一致性檢查。由於資料庫訪問相對於中介軟體來說是遠端呼叫，要編寫統一的資料一致性檢查程式碼並非易 事，一般採用在多個地方的增加類似的檢查步驟。一旦一致性檢查過程發生調整，勢必導致多個地方的修改，不僅增加工作量，而且無法保證每個檢查步驟的正確 性。觸發器的應用，應該放在關鍵的，多方發起的，高頻訪問的資料表上，過多使用觸發器，會增加資料庫負擔，降低資料庫效能。而放棄使用觸發器，則會導致系 統架構設計上的問題，影響系統的穩定性。

2，觸發器示例
觸發器程式碼類似儲存過程，以PL/SQL指令碼編寫。下面是一個觸發器的示例：
新建員工工資表salary
create table SALARY
(
  EMPLOYEE_ID NUMBER, --員工ID
  MONTH       VARCHAR2(6), --工資月份
  AMOUNT      NUMBER --工資金額
)

建立與salary關聯的觸發器salary_trg_rai
1   Create or replace trigger salary_trg_rai
2   After insert on salary
3   For each row
4 declare
5   Begin
6     Dbms_output.put_line(‘員工ID：’ || :new.employee_id);
7     Dbms_output.put_line(‘工資月份：’ || :new.month);
8     Dbms_output.put_line(‘工資：’ || :new.amount);
9     Dbms_output.put_line(‘觸發器已被執行’);
10   End;
開啟一個SQL Window視窗（使用PL/SQL Developer工具），或在sqlplus中輸入：
Insert into salary(employee_id, month, amount) values(1, ‘200606’, 10000);
執行後可以在sqlplus中，或在SQL Window視窗的Output中見到
員工ID：1
工資月份：200606
工資：10000
觸發器已執行

在程式碼的第一行，定義了資料庫物件的型別是trigger，定義觸發器的名稱是salary_trg_rai
第二行說明了這是一個after觸發器，在DML操作實施之後執行。緊接著的insert說明了這是一個針對insert操作的觸發器，每個對該表進行的insert操作都會執行這個觸發器。
第三行說明了這是一個針對行級的觸發器，當插入的記錄有n條時，在每一條插入操作時都會執行該觸發器，總共執行n次。
Declare後面跟的是本地變數定義部分，如果沒有本地變數定義，此部分可以為空
Begin和end括起來的程式碼，是觸發器的執行部分，一般會對插入記錄進行一致性檢查，在本例中列印了插入的記錄和“觸發器已執行”。
其中:new物件表示了插入的記錄，可以透過:new.column_name來引用記錄的每個欄位值

3，觸發器語法和功能
觸發器的語法如下
CREATE OR REPLACE TRIGGER trigger_name
ON table_name
[FOR EACH ROW]
WHEN (condition)
DECLARE
BEGIN
 --觸發器程式碼
END;

Trigger_name是觸發器的名稱。可以選擇before或者after或instead of。Before表示在DML語句實施前執行觸發器，而after表示在在dml語句實施之後執行觸發器，instead of觸發器用在對檢視的更新上。可以選擇一個或多個DML語句，如果選擇多個，則用or分開，如：insert or update。Table_name是觸發器關聯的表名。
[FOR EACH ROW]為可選項，如果註明了FOR EACH ROW，則說明了該觸發器是一個行級的觸發器，DML語句處理每條記錄都會執行觸發器；否則是一個語句級的觸發器，每個DML語句觸發一次。
WHEN後跟的condition是觸發器的響應條件，只對行級觸發器有效，當操作的記錄滿足condition時，觸發器才被執行，否則不執行。 Condition中可以透過new物件和old物件（注意區別於前面的:new和:old，在程式碼中引用需要加上冒號）來引用操作的記錄。
觸發器程式碼可以包括三種型別：未涉及資料庫事務程式碼，涉及關聯表（上文語法中的table_name）資料庫事務程式碼，涉及除關聯表之外資料庫事務程式碼。 其中第一種型別程式碼只對資料進行簡單運算和判斷，沒有DML語句，這種型別程式碼可以在所有的觸發器中執行。第二種型別程式碼涉及到對關聯表的資料操作，比如 查詢關聯表的總記錄數或者往關聯表中插入一條記錄，該型別程式碼只能在語句級觸發器中使用，如果在行級觸發器中使用，將會報ORA-04091錯誤。第三種 型別程式碼涉及到除關聯表之外的資料庫事務，這種程式碼可以在所有觸發器中使用。

從觸發器的功能上來看，可以分成3類：
? 重寫列（僅限於before觸發器）
? 採取行動（任何觸發器）
? 拒絕事務（任何觸發器）
“重寫列”用於對錶欄位的校驗，當插入值為空或者插入值不符合要求，則觸發器用預設值或另外的值代替，在多數情況下與欄位的default屬性相同。這種 功能只能在行級before觸發器中執行。“採取行動”針對當前事務的特點，對相關表進行操作，比如根據當前表插入的記錄更新其他表，銀行中的總帳和分戶 帳間的總分關係就可以透過這種觸發器功能來維護。“拒絕事務”用在對資料的合法性檢驗上，當更新的資料不滿足表或系統的一致性要求，則透過丟擲異常的方式 拒絕事務，在其上層的程式碼可以捕獲這個異常並進行相應操作。

下面將透過舉例說明，在例子中將觸發器主體的語法一一介紹，讀者可以在例子中體會觸發器的功能。

4，例一：行級觸發器之一
CREATE OR REPLACE TRIGGER salary_raiu
AFTER INSERT OR UPDATE OF amount ON salary
FOR EACH ROW
BEGIN
 IF inserting THEN
  dbms_output.put_line(‘插入’);
 ELSIF updating THEN
dbms_output.put_line(‘更新amount列’);
 END IF；
END;
以上是一個after insert和after update的行級觸發器。在第二行中of amount on salary的意思是隻有當amount列被更新時，update觸發器才會有效。所以，以下語句將不會執行觸發器：
Update salary set month = ‘200601’ where month = ‘200606’;
在觸發器主體的if語句表示式中，inserting, updating和deleting可以用來區分當前是在做哪一種DML操作，可以作為把多個類似觸發器合併在一個觸發器中判別觸發事件的屬性。

5，例二：行級觸發器之二
新建員工表employment
CREATE TABLE EMPLOYMENT
(
  EMPLOYEE_ID NUMBER, --員工ID
  MAXSALARY   NUMBER --工資上限
)
插入兩條記錄
Insert into employment values(1, 1000);
Insert into employment values(2, 2000);

CREATE OR REPLACE TRIGGER salary_raiu
AFTER INSERT OR UPDATE OF amount ON salary
FOR EACH ROW
WHEN ( NEW.amount >= 1000 AND (old.amount IS NULL OR OLD.amount <= 500))
DECLARE
 v_maxsalary NUMBER;
BEGIN
 SELECT maxsalary
  INTO v_maxsalary
  FROM employment
  WHERE employee_id = :NEW.employee_id;
 IF :NEW.amount > v_maxsalary THEN
  raise_application_error(-20000, '工資超限');
 END IF;
END;

以上的例子引入了一個新的表employment，表中的maxsalary欄位代表該員工每月所能分配的最高工資。下面的觸發器根據插入或修改記錄的 employee_id，在employment表中查到該員工的每月最高工資，如果插入或修改後的amount超過這個值，則報錯誤。
程式碼中的when子句表明了該觸發器只針對修改或插入後的amount值超過1000，而修改前的amount值小於500的記錄。New物件和old對 象分別表示了操作前和操作後的記錄物件。對於insert操作，由於當前操作記錄無歷史物件，所以old物件中所有屬性是null；對於delete操 作，由於當前操作記錄沒有更新物件，所以new物件中所有屬性也是null。但在這兩種情況下，並不影響old和new物件的引用和在觸發器主體中的使 用，和普通的空值作同樣的處理。
在觸發器主體中，先透過:new.employee_id，得到該員工的工資上限，然後在if語句中判斷更新後的員工工資是否超限，如果超限則錯誤程式碼為 -20000，錯誤資訊為“工資超限”的自定義錯誤。其中的raise_application_error包含兩個引數，前一個是自定義錯誤程式碼，後一 個是自定義錯誤程式碼資訊。其中自定義錯誤程式碼必須小於或等於-20000。執行完該語句後，一個異常被丟擲，如果在上一層有exception子句，該異 常將被捕獲。如下面程式碼：
DECLARE
 code NUMBER;
 msg  VARCHAR2(500);
BEGIN
 INSERT INTO salary (employee_id, amount) VALUES (2, 5000);
EXCEPTION
 WHEN OTHERS THEN
  code := SQLCODE;
  msg  := substr(SQLERRM, 1, 500);
  dbms_output.put_line(code);
  dbms_output.put_line(msg);
END;
執行後，將在output中或者sqlplus視窗中見著以下資訊：
-20000
ORA-20000: 工資超出限制
ORA-06512: 在"SCOTT.SALARY_RAI", line 9
ORA-04088: 觸發器 'SCOTT.SALARY_RAI' 執行過程中出錯

這裡的raise_application_error相當於拒絕了插入或者修改事務，當上層程式碼接受到這個異常後，判斷該異常程式碼等於-20000，可以作出回滾事務或者繼續其他事務的處理。

以上兩個例子中用到的inserting, updating, deleting和raise_application_error都是dbms_standard包中的函式，具體的說明可以參照Oracle的幫助文件。
create or replace package sys.dbms_standard is
  procedure raise_application_error(num binary_integer, msg varchar2,
  function inserting return boolean;
  function deleting  return boolean;
  function updating  return boolean;
  function updating (colnam varchar2) return boolean;
end;

對於before和after行級觸發器，:new和:old物件的屬性值都是一樣的，主要是對於在Oracle約束（Constraint）之前或之後 的執行觸發器的選擇。需要注意的是，可以在before行觸發器中更改:new物件中的值，但是在after行觸發器就不行。

下面介紹一種instead of觸發器，該觸發器主要使用在對檢視的更新上，以下是instead of觸發器的語法：
CREATE OR REPLACE TRIGGER trigger_name
INSTEAD OF ON view_name
[FOR EACH ROW]
WHEN (condition)
DECLARE
BEGIN
 --觸發器程式碼
END;

其他部分語法同前面所述的before和after語法是一樣的，唯一不同的是在第二行用上了instead of關鍵字。對於普通的檢視來說，進行insert等操作是被禁止的，因為Oracle無法知道操作的欄位具體是哪個表中的欄位。但我們可以透過建立instead of觸發器，在觸發器主體中告訴Oracle應該更新，刪除或者修改哪些表的哪部分欄位。如：

6，例三：instead of觸發器
新建檢視
CREATE VIEW employee_salary(employee_id, maxsalary, MONTH, amount) AS
SELECT a.employee_id, a.maxsalary, b.MONTH, b.amount
FROM employment a, salary b
WHERE a.employee_id = b.employee_id

如果執行插入語句
INSERT INTO employee_salary(employee_id, maxsalary, MONTH, amount)
VALUES(10, 100000, '200606', 10000);
系統會報錯：
ORA-01779：無法修改與非鍵值儲存表對應的列

我們可以透過建立以下的instead of儲存過程，將插入檢視的值分別插入到兩個表中：
create or replace trigger employee_salary_rii
  instead of insert on employee_salary 
  for each ROW
DECLARE
 v_cnt NUMBER;
BEGIN
  --檢查是否存在該員工資訊
 SELECT COUNT(*)
  INTO v_cnt
  FROM employment
  WHERE employee_id = :NEW.employee_id;
 IF v_cnt = 0 THEN
  INSERT INTO employment
   (employee_id, maxsalary)
  VALUES
   (:NEW.employee_id, :NEW.maxsalary);
 END IF;
  --檢查是否存在該員工的工資資訊
 SELECT COUNT(*)
  INTO v_cnt
  FROM salary
  WHERE employee_id = :NEW.employee_id
   AND MONTH = :NEW.MONTH;
 IF v_cnt = 0 THEN
  INSERT INTO salary
   (employee_id, MONTH, amount)
  VALUES
   (:NEW.employee_id, :NEW.MONTH, :NEW.amount);
 END IF;
END employee_salary_rii;

該觸發器被建立後，執行上述insert操作，系統就會提示成功插入一條記錄。
但需要注意的是，這裡的“成功插入一條記錄”，只是Oracle並未發現觸發器中有異常丟擲，而根據insert語句中涉及的記錄數作出一個判斷。若觸發 器的主體什麼都沒有，只是一個空語句，Oracle也會報“成功插入一條記錄”。同樣道理，即使在觸發器主體裡往多個表中插入十條記錄，Oracle的返 回也是“成功插入一條記錄”。

 

行級觸發器可以解決大部分的問題，但是如果需要對本表進行掃描檢查，比如要檢查總的工資是否超限了，用行級觸發器是不行的，因為行級觸發器主體中不能有涉及到關聯表的事務，這時就需要用到語句級觸發器。以下是語句級觸發器的語法：
CREATE OR REPLACE TRIGGER trigger_name
ON table_name
DECLARE
BEGIN
 --觸發器主體
END;

從語法定義上來看，行級觸發器少了for each row，也不能使用when子句來限定入口條件，其他部分都是一樣的，包括insert, update, delete和instead of都可以使用。

7，例四：語句級觸發器之一
CREATE OR REPLACE TRIGGER salary_saiu
AFTER INSERT OR UPDATE OF amount ON salary
DECLARE
 v_sumsalary NUMBER;
BEGIN
  SELECT SUM(amount) INTO v_sumsalary FROM salary;
 IF v_sumsalary > 500000 THEN
  raise_application_error(-20001, '總工資超過500000');
 END IF;
END;

以上程式碼定義了一個語句級觸發器，該觸發器檢查在insert和update了amount欄位後操作後，工資表中所有工資記錄累加起來是否超過 500000，如果超過則丟擲異常。從這個例子可以看出，語句級觸發器可以對關聯表表進行掃描，掃描得到的結果可以用來作為判斷一致性的標誌。需要注意的 是，在before語句觸發器主體和after語句觸發器主體中對關聯表進行掃描，結果是不一樣的。在before語句觸發器主體中掃描，掃描結果將不包 括新插入和更新的記錄，也就是說當以上程式碼換成 before觸發器後，以下語句將不報錯：
INSERT INTO salary(employee_id, month, amount) VALUEs(2, '200601', 600000)
這是因為在主體中得到的v_sumsalary並不包括新插入的600000工資。
另外，在語句級觸發器中不能使用:new和:old物件，這一點和行級觸發器是顯著不同的。如果需要檢查插入或更新後的記錄，可以採用臨時表技術。
臨時表是一種Oracle資料庫物件，其特點是當建立資料的程式結束後，程式所建立的資料也隨之清除。程式與程式不可以互相訪問同一臨時表中對方的資料，而且對臨時表進行操作也不產生undo日誌，減少了資料庫的消耗。具體有關臨時表的知識，可以參看有關書籍。
為了在語句級觸發器中訪問新插入後修改後的記錄，可以增加行級觸發器，將更新的記錄插入臨時表中，然後在語句級觸發器中掃描臨時表，獲得修改後的記錄。臨時表的表結構一般與關聯表的結構一致。

8，例五：語句級觸發器之二
目的：限制每個員工的總工資不能超過50000，否則停止對該表操作。
建立臨時表
create global temporary table SALARY_TMP
(
  EMPLOYEE_ID NUMBER,
  MONTH       VARCHAR2(6),
  AMOUNT      NUMBER
)
on commit delete rows;

為了把操作記錄插入到臨時表中，建立行級觸發器：
CREATE OR REPLACE TRIGGER salary_raiu
AFTER INSERT OR UPDATE OF amount ON salary
FOR EACH ROW
BEGIN
  INSERT INTO salary_tmp(employee_id, month, amount)
  VALUES(:NEW.employee_id, :NEW.MONTH, :NEW.amount);
END;
該觸發器的作用是把更新後的記錄資訊插入到臨時表中，如果更新了多條記錄，則每條記錄都會儲存在臨時表中。

建立語句級觸發器：
CREATE OR REPLACE TRIGGER salary_sai
AFTER INSERT OR UPDATE OF amount ON salary
DECLARE
 v_sumsalary NUMBER;
BEGIN
 FOR cur IN (SELECT * FROM salary_tmp) LOOP
  SELECT SUM(amount)
   INTO v_sumsalary
   FROM salary
   WHERE employee_id = cur.employee_id;
  IF v_sumsalary > 50000 THEN
   raise_application_error(-20002, '員工累計工資超過50000');
  END IF;
    DELETE FROM salary_tmp;
 END LOOP;
END;

該觸發器首先用遊標從salary_tmp臨時表中逐條讀取更新或插入的記錄，取employee_id，在關聯表salary中查詢所有相同員工的工資記錄，並求和。若某員工工資總和超過50000，則丟擲異常。如果檢查透過，則清空臨時表，避免下次檢查相同的記錄。
執行以下語句：
INSERT INTO salary(employee_id, month, amount) VALUEs(7, '200601', 20000);
INSERT INTO salary(employee_id, month, amount) VALUEs(7, '200602', 20000);
INSERT INTO salary(employee_id, month, amount) VALUEs(7, '200603', 20000);
在執行第三句時系統報錯：
ORA-20002：員工累計工資超過50000
查詢salary表，發現前兩條記錄正常插入了，第三條記錄沒有插入。

如果系統結構比較複雜，而且觸發器的程式碼比較多，在觸發器主體中寫過多的程式碼，對於維護來說是一個困難。這時可以將所有觸發器的程式碼寫到同一個包中，不同的觸發器程式碼以不同的儲存過程封裝，然後觸發器主體中呼叫這部分程式碼。

9，例六：用包封裝觸發器程式碼
目的：改寫例五，封裝觸發器主體程式碼
建立程式碼包：
CREATE OR REPLACE PACKAGE BODY salary_trigger_pck IS

 PROCEDURE load_salary_tmp(i_employee_id IN NUMBER,
       i_month       IN VARCHAR2,
       i_amount      IN NUMBER) IS
 BEGIN
  INSERT INTO salary_tmp VALUES (i_employee_id, i_month, i_amount);
 END load_salary_tmp;

 PROCEDURE check_salary IS
  v_sumsalary NUMBER;
 BEGIN
  FOR cur IN (SELECT * FROM salary_tmp) LOOP
   SELECT SUM(amount)
    INTO v_sumsalary
    FROM salary
    WHERE employee_id = cur.employee_id;
   IF v_sumsalary > 50000 THEN
    raise_application_error(-20002, '員工累計工資超過50000');
   END IF;
   DELETE FROM salary_tmp;
  END LOOP;
 END check_salary;
END salary_trigger_pck;
包salary_trigger_pck中有兩個儲存過程，load_salary_tmp用於在行級觸發器中呼叫，往salary_tmp臨時表中裝載更新或插入記錄。而check_salary用於在語句級觸發器中檢查員工累計工資是否超限。

修改行級觸發器和語句級觸發器：
CREATE OR REPLACE TRIGGER salary_raiu
 AFTER INSERT OR UPDATE OF amount ON salary
 FOR EACH ROW
BEGIN
 salary_trigger_pck.load_salary_tmp(:NEW.employee_id,  :NEW.MONTH, :NEW.amount);
END;

CREATE OR REPLACE TRIGGER salary_sai
AFTER INSERT OR UPDATE OF amount ON salary
BEGIN
 salary_trigger_pck.check_salary;
END;

這樣主要程式碼就集中到了salary_trigger_pck中，觸發器主體中只實現了一個呼叫功能。

10，觸發器命名規範
為了方便對觸發器命名和根據觸發器名稱瞭解觸發器含義，需要定義觸發器的命名規範：
Trigger_name = table_name_trg_

觸發器名限於30個字元。必須縮寫表名，以便附加觸發器屬性資訊。
基於行級（row）還是語句級（statement）的觸發器
after, before或者是instead of觸發器
觸發事件是insert，update還是delete。如果有多個觸發事件則連著寫

例如：
Salary_rai  salary表的行級after觸發器，觸發事件是insert
Employee_sbiud employee表的語句級before觸發器，觸發事件是insert,update和delete

轉載於:http://blog.sina.com.cn/s/blog_7cc3b8db010113oj.html