oracle觸發器如何使用2

標籤：

觸發器 是特定事件出現的時候，自動執行的代碼塊。類似於預存程序，但是使用者不能直接調用他們。觸發器是許多關聯式資料庫系統都提供的一項技術。在ORACLE系統裡，觸發器類似過程和函數，都有聲明，執行和異常處理過程的PL/SQL塊。

　　1.說明

　　1)觸發器是一種特殊的預存程序，觸發器一般由事件觸發並且不能接受參數，儲存空間由語句塊去調用

　　2)觸發器分類：

　　1.DML觸發器： 建立在表上，由DML事件引發

　　2.instead of觸發器： 建立在視圖上並且只能在行級上觸發，用於替代insert，delete等操作(由於oracle中不能直接對有兩個以上的表建立的視圖進行DML操作，所以給出替代觸發器，它是專門為進行視圖操作的一種處理方法)

　　3.DDL觸發器： 觸發事件時資料庫物件的建立和修改

　　4.資料庫事件觸發程序：定義在資料庫或者模式上，由資料庫事件觸發

　　3)組成：

　　1.觸發事件：引發觸發器被觸發的事件 DML語句(INSERT, UPDATE, DELETE語句對錶或視圖執行資料處理操作)、DDL語句(如CREATE、ALTER、DROP語句在資料庫中建立、修改、刪除模式對象)、資料庫系統事件

　　(如系統啟動或退出、異常錯誤)、使用者事件(如登入或退出資料庫)。

　　2.觸發時間：即該觸發器是在觸發事件發生之前(BEFORE)還是之後(AFTER)觸發

　　3.觸發操作：觸發器觸發後要完成的事情

　　4.觸發對象：包括表、視圖、模式、資料庫。只有在這些對象上發生了符合觸發條件的觸發事件，觸發器才會執行觸發操作。

　　5.觸發條件：由WHEN子句指定一個邏輯運算式。只有當該運算式的值為TRUE時，遇到觸發事件才會自動執行觸發操作。

　　6.觸發頻率：說明觸發器內定義的動作被執行的次數。即語句級(STATEMENT)觸發器和行級(ROW)觸發器。(比如delete多條資料時，行級觸發器可能會執行多次，語句級觸發器只會觸發一次)

　　2.文法

　　1)說明

　　不同類型的觸發器例如DML觸發器，Instead of觸發器，系統觸發器文法格式區別較大

　　2)一般文法

　　CREATE [OR REPLACE] TIGGER觸發器名 觸發時間 觸發事件

　　ON表名/視圖名

　　[FOR EACH ROW] //加上FOR EACH ROW 即為行級觸發器，不加時為語句級觸發器　　BEGIN　　pl/sql語句

　　END

　　create [or replace] trigger [schema.]trigger_name

　　{before | after | instead of}

　　{delete [or insert][or update [of column,...n]]}

　　on [schema.]table_name | view_name

　　[for each row [when(condition)]]

　　sql_statement[,...n]

　　例如：

　　CREATE OR REPLACE TRIGGER trigger_name

　　< before | after | instead of > < insert | update | delete> ON table_name

　　[FOR EACH ROW]　　WHEN (condition)

　　DECLARE　　BEGIN　　END;

　　3)instead of 觸發器文法

　　文法：

　　CREATE [OR REPLACE] TRIGGER trigger_name

　　INSTEAD OF{INSERT|DELETE|UPDATE [OF COLUMN...]}[OR {INSERT| DELETE| UPDATE [OF COLUMN...]}]ON VIEW_NAME[REFFERENCING{OLD [AS] OLD | NEW [AS] NEW| PARENT AS PARENT}] // 可以指定相關名稱，當前的預設相關名稱為OLD和NEW,

　　應用相關名稱時需要加：[FOR EACH ROW] //instead of 觸發器只能在行級上觸發，因為沒有必要指定[WHEN CONDITION]DECLARE

　　BEGINEND;

　　說明：INSTEAD OF 用於對視圖的DML觸發，由於視圖可能有多個表進行連接而成，因而並非所有的連接均可更新，運用 INSTEAD OF 觸發器可完成相應的操作。

　　3.執行個體

　　建立測試表格：

　　CREATE TABLE "HNZC"."TRIGGERTEST"

　　(

　　"ID" VARCHAR2(20 BYTE),

　　"NAME" VARCHAR2(20 BYTE),

　　"SCORE" NUMBER

　　);create table tab1 select * from triggertest;

　　1)DML觸發器/行級觸發器

　　觸發器如下：

　　CREATE OR REPLACE TRIGGER TRIGGER1

　　AFTER INSERT ON TRIGGERTEST //插入後觸發FOR EACH ROW //行級觸發器BEGIN

　　INSERT INTO tab1(ID,NAME) VALUES(‘22‘,‘33‘);END;

　　執行語句：

　　insert into triggertest (id) values (‘aabbcc‘);

　　語句執行結束，表tab1中新增加一條資料

　　2)限制對錶的修改(例如非工作時間不能修改某些表)

　　觸發器如下：

　　CREATE OR REPLACE TRIGGER TRIGGER1

　　AFTER INSERT ON TRIGGERTEST

　　FOR EACH ROW

　　BEGIN

　　IF(TO_CHAR(SYSDATE,‘DAY‘) IN (‘星期三‘,‘星期天‘)) THEN RAISE_APPLICATION_ERROR(-20001,‘不是上班時間，不能修改表格triggertest‘); END IF;END;

　　執行語句：

　　insert into triggertest (id) values (‘aabbcc‘);

　　今天周三因而輸出結果為：

　　在行 1 上開始執行命令時出錯:insert into triggertest (id) values (‘aabbcc‘)

　　錯誤報表:

　　SQL 錯誤: ORA-20001: 不是上班時間，不能修改表格triggertest

　　ORA-06512: 在 "HNZC.TRIGGER1", line 3ORA-04088: 觸發器 ‘HNZC.TRIGGER1‘ 執行過程中出錯

　　通常對錶的修改限制如下(即周一至周五9——18點能修改表格)

　　CREATE OR REPLACE TRIGGER TRIGGER1

　　BEFORE INSERT OR DELETE OR UPDATE ON TRIGGERTEST

　　FOR EACH ROW

　　BEGIN

　　IF(TO_CHAR(SYSDATE,‘DAY‘) IN (‘星期六‘,‘星期天‘)) OR(TO_CHAR(SYSDATE,‘HH24:MI‘) NOT BETWEEN ‘9:00‘ AND ‘18:00‘) THEN RAISE_APPLICATION_ERROR(-20001,‘不是上班時間，不能修改表格triggertest‘); END IF;END;

　　3)增加限制條件(如不能更改某個員工的記錄)

　　觸發器如下：(如下實現月兒的分數只能增加)

　　CREATE OR REPLACE TRIGGER TRIGGER1

　　BEFORE INSERT OR DELETE OR UPDATE ON TRIGGERTEST

　　FOR EACH ROW

　　WHEN(OLD.NAME=‘月兒‘)BEGIN

　　CASE WHEN UPDATING(‘SCORE‘) THEN

　　IF:NEW.SCORE<:OLD.SCORE THEN RAISE_APPLICATION_ERROR(-20001,‘月兒的分數只能提升不能下降‘); END IF; END CASE;END;

　　當前月兒的分數為20

　　當修改為10時出錯

　　UPDATE "HNZC"."TRIGGERTEST" SET SCORE = ‘10‘ WHERE ROWID = ‘AAAdEzAAPAAAAH+AAB‘ AND ORA_ROWSCN = ‘47685303‘ORA-20001: 月兒的分數只能提升不能下降

　　ORA-06512: 在 "HNZC.TRIGGER1", line 4ORA-04088: 觸發器 ‘HNZC.TRIGGER1‘ 執行過程中出錯

　　當修改為30時成功

　　UPDATE "HNZC"."TRIGGERTEST" SET SCORE = ‘30‘ WHERE ROWID = ‘AAAdEzAAPAAAAH+AAB‘ AND ORA_ROWSCN = ‘47685303‘提交成功

　　4)在觸發器中調用預存程序

　　觸發器為：

　　CREATE OR REPLACE TRIGGER TRIGGER1

　　BEFORE INSERT OR DELETE OR UPDATE ON TRIGGERTEST

　　FOR EACH ROW

　　BEGIN

　　TESTPRO1();END;

　　預存程序為：

　　create or replacePROCEDURE TESTPRO1 AS BEGIN

　　insert into tab1(id,name,score) VALUES(‘AAA‘,‘BBB‘,200);END TESTPRO1;

　　執行完畢後tab1中增加一條資料

　　5)串聯更新

　　觸發器如下(triggertest表中name修改時同時修改tab1中的name)

　　create or replacePROCEDURE TESTPRO1 AS BEGIN

　　insert into tab1(id,name,score) VALUES(‘AAA‘,‘BBB‘,200);END TESTPRO1;

　　執行語句：

　　update triggertest set name= ‘水兒‘ where name=‘月兒‘;

　　結果：tab1中name為月兒的也更改為水兒

　　6)instead of觸發器

　　TABLE STUDENT表格式資料如下

　　建立視圖student_view

　　CREATE OR REPLACE VIEW STUDNET_VIEW

　　AS SELECT CLASSID,AVG(SCORE) AVERAGE_SCORE FROM STUDENTGROUP BY CLASSID;

　　視圖資料如下：

　　對視圖student_view 執行如下操作：

　　DELETE FROM STUDNET_VIEW WHERE CLASSID=‘111‘;

　　執行結果：

　　錯誤報表:

　　SQL 錯誤: ORA-01732: 此視圖的資料操縱操作非法01732. 00000 - "data manipulation operation not legal on this view"

　　解決方案：建立INSTEAD OF 視圖

　　CREATE OR REPLACE TRIGGER STUDENT_VIEW_DELETE

　　INSTEAD OF DELETE ON STUDNET_VIEW

　　FOR EACH ROWBEGIN

　　DELETE FROM STUDENT WHERE CLASSID=:OLD.CLASSID;END STUDENT_VIEW_DELETE;

　　執行刪除語句

　　DELETE FROM STUDNET_VIEW WHERE CLASSID=‘111‘;

　　執行結果：刪除成功

　　1 行已刪除。

　　4.注意事項

　　1) 在觸發器的執行部分只能用DML語句(SELECT、INSERT、UPDATE、DELETE)，不能使用DDL語句(CREATE、ALTER、DROP)

　　2) 觸發器中不能使用commit語句，觸發器的操作與觸發事件(INSERT,UPDATE,DELETE)一起進行COMMIT和ROLLBACK;

　　3) 一個表上的觸發器越多，對於表的DML操作效能影響越大

　　4) 觸發器最大為32K

oracle觸發器如何使用2