Oracle觸發(fā)器使用教程和命名規(guī)范V1.0

1、觸發(fā)器使用教程和命名規(guī)范V1.0屬性內(nèi)容文檔名稱：觸發(fā)器使用教程和命名規(guī)范文檔版本號：V1.0文檔編寫日期：2006-12-4文檔狀態(tài)：修訂稿作者：李振國發(fā)布日期：2006-12-5目 錄觸發(fā)器使用教程和命名規(guī)范1，觸發(fā)器簡介2，觸發(fā)器示例3，觸發(fā)器語法和功能4，例一：行級觸發(fā)器之一5，例二：行級觸發(fā)器之二6，例三：instead of觸發(fā)器7，例四：語句級觸發(fā)器之一8，例五：語句級觸發(fā)器之二9，例六：用包封裝觸發(fā)器代碼10，觸發(fā)器命名規(guī)范1，觸發(fā)器簡介觸發(fā)器（Trigger）是數(shù)據(jù)庫對象的一種，編碼方式類似存儲過程，與某張表（Table）相關(guān)聯(lián)，當有DML語句對表進行操作時，可以引

2、起觸發(fā)器的執(zhí)行，達到對插入記錄一致性，正確性和規(guī)范性控制的目的。在當年C/S時代盛行的時候，由于客戶端直接連接數(shù)據(jù)庫，能保證數(shù)據(jù)庫一致性的只有數(shù)據(jù)庫本身，此時主鍵（Primary Key），外鍵（Foreign Key），約束（Constraint）和觸發(fā)器成為必要的控制機制。而觸發(fā)器的實現(xiàn)比較靈活，可編程性強，自然成為了最流行的控制機制。到了B/S時代，發(fā)展成4層架構(gòu)，客戶端不再能直接訪問數(shù)據(jù)庫，只有中間件才可以訪問數(shù)據(jù)庫。要控制數(shù)據(jù)庫的一致性，既可以在中間件里控制，也可以在數(shù)據(jù)庫端控制。很多的青睞Java的開發(fā)者，隨之將數(shù)據(jù)庫當成一個黑盒，把大多數(shù)的數(shù)據(jù)控制工作放在了Servlet中執(zhí)行。

3、這樣做，不需要了解太多的數(shù)據(jù)庫知識，也減少了數(shù)據(jù)庫編程的復雜性，但同時增加了Servlet編程的工作量。從架構(gòu)設(shè)計來看，中間件的功能是檢查業(yè)務(wù)正確性和執(zhí)行業(yè)務(wù)邏輯，如果把數(shù)據(jù)的一致性檢查放到中間件去做，需要在所有涉及到數(shù)據(jù)寫入的地方進行數(shù)據(jù)一致性檢查。由于數(shù)據(jù)庫訪問相對于中間件來說是遠程調(diào)用，要編寫統(tǒng)一的數(shù)據(jù)一致性檢查代碼并非易事，一般采用在多個地方的增加類似的檢查步驟。一旦一致性檢查過程發(fā)生調(diào)整，勢必導致多個地方的修改，不僅增加工作量，而且無法保證每個檢查步驟的正確性。觸發(fā)器的應用，應該放在關(guān)鍵的，多方發(fā)起的，高頻訪問的數(shù)據(jù)表上，過多使用觸發(fā)器，會增加數(shù)據(jù)庫負擔，降低數(shù)據(jù)庫性能。而放棄使用觸

4、發(fā)器，則會導致系統(tǒng)架構(gòu)設(shè)計上的問題，影響系統(tǒng)的穩(wěn)定性。2，觸發(fā)器示例觸發(fā)器代碼類似存儲過程，以PL/SQL腳本編寫。下面是一個觸發(fā)器的示例：新建員工工資表salarycreate table SALARY(  EMPLOYEE_ID NUMBER, -員工ID MONTH       VARCHAR2(6), -工資月份 AMOUNT      NUMBER -工資金額)創(chuàng)建與salar

5、y關(guān)聯(lián)的觸發(fā)器salary_trg_rai1   Create or replace trigger salary_trg_rai2   After insert on salary3   For each row4     declare5   Begin6     Dbms_output.put_line(員工ID： | :new.employee_id);7     Dbms_output.

6、put_line(工資月份： | :new.month);8     Dbms_output.put_line(工資： | :new.amount);9     Dbms_output.put_line(觸發(fā)器已被執(zhí)行);10   End;打開一個SQL Window窗口（使用PL/SQL Developer工具），或在sqlplus中輸入：Insert into salary(employee_id, month, amount) values(1, 200606, 10000);執(zhí)行后可以在

7、sqlplus中，或在SQL Window窗口的Output中見到員工ID：1工資月份：200606工資：10000觸發(fā)器已執(zhí)行在代碼的第一行，定義了數(shù)據(jù)庫對象的類型是trigger，定義觸發(fā)器的名稱是salary_trg_rai第二行說明了這是一個after觸發(fā)器，在DML操作實施之后執(zhí)行。緊接著的insert說明了這是一個針對insert操作的觸發(fā)器，每個對該表進行的insert操作都會執(zhí)行這個觸發(fā)器。第三行說明了這是一個針對行級的觸發(fā)器，當插入的記錄有n條時，在每一條插入操作時都會執(zhí)行該觸發(fā)器，總共執(zhí)行n次。Declare后面跟的是本地變量定義部分，如果沒有本地變量定義，此部分可以為空B

8、egin和end括起來的代碼，是觸發(fā)器的執(zhí)行部分，一般會對插入記錄進行一致性檢查，在本例中打印了插入的記錄和“觸發(fā)器已執(zhí)行”。其中:new對象表示了插入的記錄，可以通過:new.column_name來引用記錄的每個字段值3，觸發(fā)器語法和功能觸發(fā)器的語法如下CREATE OR REPLACE TRIGGER trigger_name<before | after | instead of> <insert | update | delete> ON table_nameFOR EACH ROWWHEN (condition)DECLAREBEGIN&

9、#160;      -觸發(fā)器代碼END;Trigger_name是觸發(fā)器的名稱。<before | after | instead of>可以選擇before或者after或instead of。Before表示在DML語句實施前執(zhí)行觸發(fā)器，而after表示在在dml語句實施之后執(zhí)行觸發(fā)器，instead of觸發(fā)器用在對視圖的更新上。<insert | update | delete>可以選擇一個或多個DML語句，如果選擇多個，則用or分開，如：insert or update。Table_name是觸發(fā)器關(guān)聯(lián)的表名

10、。FOR EACH ROW為可選項，如果注明了FOR EACH ROW，則說明了該觸發(fā)器是一個行級的觸發(fā)器，DML語句處理每條記錄都會執(zhí)行觸發(fā)器；否則是一個語句級的觸發(fā)器，每個DML語句觸發(fā)一次。WHEN后跟的condition是觸發(fā)器的響應條件，只對行級觸發(fā)器有效，當操作的記錄滿足condition時，觸發(fā)器才被執(zhí)行，否則不執(zhí)行。Condition中可以通過new對象和old對象（注意區(qū)別于前面的:new和:old，在代碼中引用需要加上冒號）來引用操作的記錄。觸發(fā)器代碼可以包括三種類型：未涉及數(shù)據(jù)庫事務(wù)代碼，涉及關(guān)聯(lián)表（上文語法中的table_name）數(shù)據(jù)庫事務(wù)代碼，涉及除關(guān)聯(lián)表之外數(shù)據(jù)庫

11、事務(wù)代碼。其中第一種類型代碼只對數(shù)據(jù)進行簡單運算和判斷，沒有DML語句，這種類型代碼可以在所有的觸發(fā)器中執(zhí)行。第二種類型代碼涉及到對關(guān)聯(lián)表的數(shù)據(jù)操作，比如查詢關(guān)聯(lián)表的總記錄數(shù)或者往關(guān)聯(lián)表中插入一條記錄，該類型代碼只能在語句級觸發(fā)器中使用，如果在行級觸發(fā)器中使用，將會報ORA-04091錯誤。第三種類型代碼涉及到除關(guān)聯(lián)表之外的數(shù)據(jù)庫事務(wù)，這種代碼可以在所有觸發(fā)器中使用。從觸發(fā)器的功能上來看，可以分成3類：l         重寫列（僅限于before觸發(fā)器）l    

12、     采取行動（任何觸發(fā)器）l         拒絕事務(wù)（任何觸發(fā)器）“重寫列”用于對表字段的校驗，當插入值為空或者插入值不符合要求，則觸發(fā)器用缺省值或另外的值代替，在多數(shù)情況下與字段的default屬性相同。這種功能只能在行級before觸發(fā)器中執(zhí)行?！安扇⌒袆印贬槍Ξ斍笆聞?wù)的特點，對相關(guān)表進行操作，比如根據(jù)當前表插入的記錄更新其他表，銀行中的總帳和分戶帳間的總分關(guān)系就可以通過這種觸發(fā)器功能來維護。“拒絕事務(wù)”用在對數(shù)據(jù)的合法性檢驗上，當更新的數(shù)據(jù)不

13、滿足表或系統(tǒng)的一致性要求，則通過拋出異常的方式拒絕事務(wù)，在其上層的代碼可以捕獲這個異常并進行相應操作。下面將通過舉例說明，在例子中將觸發(fā)器主體的語法一一介紹，讀者可以在例子中體會觸發(fā)器的功能。4，例一：行級觸發(fā)器之一CREATE OR REPLACE TRIGGER salary_raiuAFTER INSERT OR UPDATE OF amount ON salaryFOR EACH ROWBEGIN    IF 

14、;inserting THEN        dbms_output.put_line(插入);    ELSIF updating THENdbms_output.put_line(更新amount列);         END IF；END;以上是一個after insert和after update的行級觸發(fā)器。在第二行中of amount on salary的

15、意思是只有當amount列被更新時，update觸發(fā)器才會有效。所以，以下語句將不會執(zhí)行觸發(fā)器：Update salary set month = 200601 where month = 200606;在觸發(fā)器主體的if語句表達式中，inserting, updating和deleting可以用來區(qū)分當前是在做哪一種DML操作，可以作為把多個類似觸發(fā)器合并在一個觸發(fā)器中判別觸發(fā)事件的屬性。5，例二：行級觸發(fā)器之二新建員工表employmentCREATE TABLE EMPLOYMENT( EMPLOYEE_ID NUMBER, -員工ID 

16、MAXSALARY   NUMBER -工資上限)插入兩條記錄Insert into employment values(1, 1000);Insert into employment values(2, 2000);CREATE OR REPLACE TRIGGER salary_raiuAFTER INSERT OR UPDATE OF amount ON salaryFOR EACH ROWWHEN (

17、0;NEW.amount >= 1000 AND (old.amount IS NULL OR OLD.amount <= 500)DECLARE    v_maxsalary NUMBER;BEGIN    SELECT maxsalary        INTO v_maxsalary   &

18、#160;    FROM employment     WHERE employee_id = :NEW.employee_id;    IF :NEW.amount > v_maxsalary THEN        raise_application_error(-20000, '工資超限');   

19、 END IF;END;以上的例子引入了一個新的表employment，表中的maxsalary字段代表該員工每月所能分配的最高工資。下面的觸發(fā)器根據(jù)插入或修改記錄的employee_id，在employment表中查到該員工的每月最高工資，如果插入或修改后的amount超過這個值，則報錯誤。代碼中的when子句表明了該觸發(fā)器只針對修改或插入后的amount值超過1000，而修改前的amount值小于500的記錄。New對象和old對象分別表示了操作前和操作后的記錄對象。對于insert操作，由于當前操作記錄無歷史對象，所以old對象中所有屬性是null；對于delete操

20、作，由于當前操作記錄沒有更新對象，所以new對象中所有屬性也是null。但在這兩種情況下，并不影響old和new對象的引用和在觸發(fā)器主體中的使用，和普通的空值作同樣的處理。在觸發(fā)器主體中，先通過:new.employee_id，得到該員工的工資上限，然后在if語句中判斷更新后的員工工資是否超限，如果超限則錯誤代碼為-20000，錯誤信息為“工資超限”的自定義錯誤。其中的raise_application_error包含兩個參數(shù)，前一個是自定義錯誤代碼，后一個是自定義錯誤代碼信息。其中自定義錯誤代碼必須小于或等于-20000。執(zhí)行完該語句后，一個異常被拋出，如果在上一層有exception子句，

21、該異常將被捕獲。如下面代碼：DECLARE    code NUMBER;    msg VARCHAR2(500);BEGIN    INSERT INTO salary (employee_id, amount) VALUES (2, 5000);EXCEPTION    WHEN OTHERS THEN     

22、   code := SQLCODE;        msg := substr(SQLERRM, 1, 500);        dbms_output.put_line(code);        dbms_output.put_line(msg);END;執(zhí)行后，將在output中或者sqlplus窗口中見著以下信息：-2

23、0000ORA-20000: 工資超出限制ORA-06512: 在"SCOTT.SALARY_RAI", line 9ORA-04088: 觸發(fā)器 'SCOTT.SALARY_RAI' 執(zhí)行過程中出錯這里的raise_application_error相當于拒絕了插入或者修改事務(wù)，當上層代碼接受到這個異常后，判斷該異常代碼等于-20000，可以作出回滾事務(wù)或者繼續(xù)其他事務(wù)的處理。以上兩個例子中用到的inserting, updating, deleting和raise_application_error都是d

24、bms_standard包中的函數(shù)，具體的說明可以參照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 b

25、oolean; function updating return boolean; function updating (colnam varchar2) return boolean;end;對于before和after行級觸發(fā)器，:new和:old對象的屬性值都是一樣的，主要是對于在Oracle約束（Constraint）之前或之后的執(zhí)行觸發(fā)器的選擇。需要注意的是，可以在before行觸發(fā)器中更改:new對象中的值，但是在after行觸發(fā)器就不行。下面介紹一種instead of觸發(fā)器，該觸發(fā)器主要使

26、用在對視圖的更新上，以下是instead of觸發(fā)器的語法：CREATE OR REPLACE TRIGGER trigger_nameINSTEAD OF <insert | update | delete> ON view_nameFOR EACH ROWWHEN (condition)DECLAREBEGIN       -觸發(fā)器代碼END;其他部分語法同前面所述的before和after語法是一樣的，唯一不同的是在第二行用上了instead of關(guān)鍵字。對于普通的視圖來說，進行insert等操作

27、是被禁止的，因為Oracle無法知道操作的字段具體是哪個表中的字段。但我們可以通過建立instead of觸發(fā)器，在觸發(fā)器主體中告訴Oracle應該更新，刪除或者修改哪些表的哪部分字段。如：6，例三：instead of觸發(fā)器新建視圖CREATE VIEW employee_salary(employee_id, maxsalary, MONTH, amount) AS SELECT a.employee_id, a.maxsalary, b.MONTH, b.amountFROM employment a, salary

28、bWHERE a.employee_id = b.employee_id如果執(zhí)行插入語句INSERT INTO employee_salary(employee_id, maxsalary, MONTH, amount)VALUES(10, 100000, '200606', 10000);系統(tǒng)會報錯：ORA-01779：無法修改與非鍵值保存表對應的列我們可以通過建立以下的instead of存儲過程，將插入視圖的值分別插入到兩個表中：create or replace trig

29、ger employee_salary_rii instead of insert on employee_salary  for each ROWDECLARE    v_cnt NUMBER;BEGIN -檢查是否存在該員工信息    SELECT COUNT(*)        INTO v_cnt&#

30、160;       FROM employment     WHERE employee_id = :NEW.employee_id;    IF v_cnt = 0 THEN        INSERT INTO employment     &

31、#160;      (employee_id, maxsalary)        VALUES            (:NEW.employee_id, :NEW.maxsalary);    END IF; -檢查是否存在該員工的工資信息    SEL

32、ECT COUNT(*)        INTO v_cnt        FROM salary     WHERE employee_id = :NEW.employee_id         AND MONTH = :NEW.MONTH; &#

33、160;  IF v_cnt = 0 THEN        INSERT INTO salary            (employee_id, MONTH, amount)        VALUES   

34、0;        (:NEW.employee_id, :NEW.MONTH, :NEW.amount);    END IF;END employee_salary_rii;該觸發(fā)器被建立后，執(zhí)行上述insert操作，系統(tǒng)就會提示成功插入一條記錄。但需要注意的是，這里的“成功插入一條記錄”，只是Oracle并未發(fā)現(xiàn)觸發(fā)器中有異常拋出，而根據(jù)insert語句中涉及的記錄數(shù)作出一個判斷。若觸發(fā)器的主體什么都沒有，只是一個空語句，Oracle也會報“成功插入一條記錄”

35、。同樣道理，即使在觸發(fā)器主體里往多個表中插入十條記錄，Oracle的返回也是“成功插入一條記錄”。行級觸發(fā)器可以解決大部分的問題，但是如果需要對本表進行掃描檢查，比如要檢查總的工資是否超限了，用行級觸發(fā)器是不行的，因為行級觸發(fā)器主體中不能有涉及到關(guān)聯(lián)表的事務(wù)，這時就需要用到語句級觸發(fā)器。以下是語句級觸發(fā)器的語法：CREATE OR REPLACE TRIGGER trigger_name<before | after | instead of ><insert | update | delete > ON table_nameDECLAREBEGIN

36、       -觸發(fā)器主體END;從語法定義上來看，行級觸發(fā)器少了for each row，也不能使用when子句來限定入口條件，其他部分都是一樣的，包括insert, update, delete和instead of都可以使用。7，例四：語句級觸發(fā)器之一CREATE OR REPLACE TRIGGER salary_saiuAFTER INSERT OR UPDATE OF amount ON salaryDECLARE

37、    v_sumsalary NUMBER;BEGIN SELECT SUM(amount) INTO v_sumsalary FROM salary;    IF v_sumsalary > 500000 THEN        raise_application_error(-20001, '總工資超過500000');

38、    END IF;END;以上代碼定義了一個語句級觸發(fā)器，該觸發(fā)器檢查在insert和update了amount字段后操作后，工資表中所有工資記錄累加起來是否超過500000，如果超過則拋出異常。從這個例子可以看出，語句級觸發(fā)器可以對關(guān)聯(lián)表表進行掃描，掃描得到的結(jié)果可以用來作為判斷一致性的標志。需要注意的是，在before語句觸發(fā)器主體和after語句觸發(fā)器主體中對關(guān)聯(lián)表進行掃描，結(jié)果是不一樣的。在before語句觸發(fā)器主體中掃描，掃描結(jié)果將不包括新插入和更新的記錄，也就是說當以上代碼換成before觸發(fā)器后，以下語句將不報錯：INSERT

39、 INTO salary(employee_id, month, amount) VALUEs(2, '200601', 600000)這是因為在主體中得到的v_sumsalary并不包括新插入的600000工資。另外，在語句級觸發(fā)器中不能使用:new和:old對象，這一點和行級觸發(fā)器是顯著不同的。如果需要檢查插入或更新后的記錄，可以采用臨時表技術(shù)。臨時表是一種Oracle數(shù)據(jù)庫對象，其特點是當創(chuàng)建數(shù)據(jù)的進程結(jié)束后，進程所創(chuàng)建的數(shù)據(jù)也隨之清除。進程與進程不可以互相訪問同一臨時表中對方的數(shù)據(jù)，而且對臨時表進行操作也不產(chǎn)生

40、undo日志，減少了數(shù)據(jù)庫的消耗。具體有關(guān)臨時表的知識，可以參看有關(guān)書籍。為了在語句級觸發(fā)器中訪問新插入后修改后的記錄，可以增加行級觸發(fā)器，將更新的記錄插入臨時表中，然后在語句級觸發(fā)器中掃描臨時表，獲得修改后的記錄。臨時表的表結(jié)構(gòu)一般與關(guān)聯(lián)表的結(jié)構(gòu)一致。8，例五：語句級觸發(fā)器之二目的：限制每個員工的總工資不能超過50000，否則停止對該表操作。創(chuàng)建臨時表create global temporary table SALARY_TMP( EMPLOYEE_ID NUMBER, MONTH   &#

41、160;   VARCHAR2(6), AMOUNT      NUMBER)on commit delete rows;為了把操作記錄插入到臨時表中，創(chuàng)建行級觸發(fā)器：CREATE OR REPLACE TRIGGER salary_raiuAFTER INSERT OR UPDATE OF amount ON salaryFOR EACH

42、0;ROWBEGIN INSERT INTO salary_tmp(employee_id, month, amount) VALUES(:NEW.employee_id, :NEW.MONTH, :NEW.amount);END;該觸發(fā)器的作用是把更新后的記錄信息插入到臨時表中，如果更新了多條記錄，則每條記錄都會保存在臨時表中。創(chuàng)建語句級觸發(fā)器：CREATE OR REPLACE TRIGGER salary_saiAFTER INSERT OR UPDATE O

43、F amount ON salaryDECLARE    v_sumsalary NUMBER;BEGIN    FOR cur IN (SELECT * FROM salary_tmp) LOOP        SELECT SUM(amount)      

44、0;     INTO v_sumsalary            FROM salary         WHERE employee_id = cur.employee_id;        IF v_sumsalary &

45、gt; 50000 THEN            raise_application_error(-20002, '員工累計工資超過50000');        END IF;    DELETE FROM salary_tmp;    END

46、60;LOOP;END;該觸發(fā)器首先用游標從salary_tmp臨時表中逐條讀取更新或插入的記錄，取employee_id，在關(guān)聯(lián)表salary中查找所有相同員工的工資記錄，并求和。若某員工工資總和超過50000，則拋出異常。如果檢查通過，則清空臨時表，避免下次檢查相同的記錄。執(zhí)行以下語句：INSERT INTO salary(employee_id, month, amount) VALUEs(7, '200601', 20000);INSERT INTO salary(employee_id,&

47、#160;month, amount) VALUEs(7, '200602', 20000);INSERT INTO salary(employee_id, month, amount) VALUEs(7, '200603', 20000);在執(zhí)行第三句時系統(tǒng)報錯：ORA-20002：員工累計工資超過50000查詢salary表，發(fā)現(xiàn)前兩條記錄正常插入了，第三條記錄沒有插入。如果系統(tǒng)結(jié)構(gòu)比較復雜，而且觸發(fā)器的代碼比較多，在觸發(fā)器主體中寫過多的代碼，對于維護來說是一個困難。這

48、時可以將所有觸發(fā)器的代碼寫到同一個包中，不同的觸發(fā)器代碼以不同的存儲過程封裝，然后觸發(fā)器主體中調(diào)用這部分代碼。9，例六：用包封裝觸發(fā)器代碼目的：改寫例五，封裝觸發(fā)器主體代碼創(chuàng)建代碼包：CREATE OR REPLACE PACKAGE BODY salary_trigger_pck IS    PROCEDURE load_salary_tmp(i_employee_id IN NUMBER,      &

49、#160;                     i_month       IN VARCHAR2,                  

50、          i_amount      IN NUMBER) IS    BEGIN        INSERT INTO salary_tmp VALUES (i_employee_id, i_month, i_amount);  

51、0; END load_salary_tmp;    PROCEDURE check_salary IS        v_sumsalary NUMBER;    BEGIN        FOR cur IN (SELECT * FROM salary_t

52、mp) LOOP            SELECT SUM(amount)                INTO v_sumsalary                FROM salary             WHERE emp