oracle觸發(fā)器使用總結(jié)

XML導(dǎo)入Oracle工具(XmlToOracle)v2.0官方版

• 類型：數(shù)據(jù)庫(kù)類大�。�6.5M語(yǔ)言：中文 評(píng)分：10.0
• 標(biāo)簽：

立即下載

觸發(fā)器 是特定事件出現(xiàn)的時(shí)候，自動(dòng)執(zhí)行的代碼塊。類似于存儲(chǔ)過(guò)程，但是用戶不能直接調(diào)用他們。觸發(fā)器是許多關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)都提供的一項(xiàng)技術(shù)。在ORACLE系統(tǒng)里，觸發(fā)器類似過(guò)程和函數(shù)，都有聲明，執(zhí)行和異常處理過(guò)程的PL/SQL塊。

1.說(shuō)明

1）觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，觸發(fā)器一般由事件觸發(fā)并且不能接受參數(shù)，存儲(chǔ)器由語(yǔ)句塊去調(diào)用

2）觸發(fā)器分類：

　　1.DML觸發(fā)器： 創(chuàng)建在表上，由DML事件引發(fā)

　　2.instead of觸發(fā)器： 創(chuàng)建在視圖上并且只能在行級(jí)上觸發(fā)，用于替代insert，delete等操作（由于oracle中不能直接對(duì)有兩個(gè)以上的表建立的視圖進(jìn)行DML操作，所以給出替代觸發(fā)器，它是專門為進(jìn)行視圖操作的一種處理方法）

　　3.DDL觸發(fā)器： 觸發(fā)事件時(shí)數(shù)據(jù)庫(kù)對(duì)象的創(chuàng)建和修改

　　4.數(shù)據(jù)庫(kù)事件觸發(fā)器：定義在數(shù)據(jù)庫(kù)或者模式上，由數(shù)據(jù)庫(kù)事件觸發(fā)

3）組成：

　　1.觸發(fā)事件：引發(fā)觸發(fā)器被觸發(fā)的事件 DML語(yǔ)句(INSERT, UPDATE, DELETE語(yǔ)句對(duì)表或視圖執(zhí)行數(shù)據(jù)處理操作)、DDL語(yǔ)句（如CREATE、ALTER、DROP語(yǔ)句在數(shù)據(jù)庫(kù)中創(chuàng)建、修改、刪除模式對(duì)象）、數(shù)據(jù)庫(kù)系統(tǒng)事件

　　　　　�。ㄈ缦到y(tǒng)啟動(dòng)或退出、異常錯(cuò)誤）、用戶事件（如登錄或退出數(shù)據(jù)庫(kù)）。

　　2.觸發(fā)時(shí)間：即該觸發(fā)器是在觸發(fā)事件發(fā)生之前（BEFORE）還是之后(AFTER)觸發(fā)

　　3.觸發(fā)操作：觸發(fā)器觸發(fā)后要完成的事情

　　4.觸發(fā)對(duì)象：包括表、視圖、模式、數(shù)據(jù)庫(kù)。只有在這些對(duì)象上發(fā)生了符合觸發(fā)條件的觸發(fā)事件，觸發(fā)器才會(huì)執(zhí)行觸發(fā)操作。

　　5.觸發(fā)條件：由WHEN子句指定一個(gè)邏輯表達(dá)式。只有當(dāng)該表達(dá)式的值為TRUE時(shí)，遇到觸發(fā)事件才會(huì)自動(dòng)執(zhí)行觸發(fā)操作。

　　6.觸發(fā)頻率：說(shuō)明觸發(fā)器內(nèi)定義的動(dòng)作被執(zhí)行的次數(shù)。即語(yǔ)句級(jí)(STATEMENT)觸發(fā)器和行級(jí)(ROW)觸發(fā)器。（比如delete多條數(shù)據(jù)時(shí)，行級(jí)觸發(fā)器可能會(huì)執(zhí)行多次，語(yǔ)句級(jí)觸發(fā)器只會(huì)觸發(fā)一次）　　　　　　　

2.語(yǔ)法

1）說(shuō)明

不同類型的觸發(fā)器例如DML觸發(fā)器，Instead of觸發(fā)器，系統(tǒng)觸發(fā)器語(yǔ)法格式區(qū)別較大

2)一般語(yǔ)法

　　CREATE [OR REPLACE] TIGGER觸發(fā)器名 觸發(fā)時(shí)間 觸發(fā)事件
　　ON表名/視圖名
　　[FOR EACH ROW]  //加上FOR EACH ROW 即為行級(jí)觸發(fā)器，不加時(shí)為語(yǔ)句級(jí)觸發(fā)器　　BEGIN　　pl/sql語(yǔ)句
　　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 觸發(fā)器語(yǔ)法

語(yǔ)法：

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}]   // 可以指定相關(guān)名稱，當(dāng)前的默認(rèn)相關(guān)名稱為OLD和NEW,
應(yīng)用相關(guān)名稱時(shí)需要加：[FOR EACH ROW]  //instead of 觸發(fā)器只能在行級(jí)上觸發(fā)，因?yàn)闆](méi)有必要指定[WHEN CONDITION]DECLARE
BEGINEND;

說(shuō)明：INSTEAD OF 用于對(duì)視圖的DML觸發(fā)，由于視圖可能有多個(gè)表進(jìn)行聯(lián)結(jié)而成，因而并非所有的聯(lián)結(jié)均可更新，運(yùn)用 INSTEAD OF 觸發(fā)器可完成相應(yīng)的操作。

3.實(shí)例

創(chuàng)建測(cè)試表格：

CREATE TABLE "HNZC"."TRIGGERTEST"
  (
    "ID"    VARCHAR2(20 BYTE),
    "NAME"  VARCHAR2(20 BYTE),
    "SCORE" NUMBER
  );create table tab1 select * from triggertest;

1)DML觸發(fā)器/行級(jí)觸發(fā)器

觸發(fā)器如下：

CREATE OR REPLACE TRIGGER TRIGGER1 
AFTER INSERT ON TRIGGERTEST    //插入后觸發(fā)FOR EACH ROW                   //行級(jí)觸發(fā)器BEGIN
   INSERT INTO tab1(ID,NAME) VALUES('22','33');END;

執(zhí)行語(yǔ)句：

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

語(yǔ)句執(zhí)行結(jié)束，表tab1中新增加一條數(shù)據(jù)

2）限制對(duì)表的修改（例如非工作時(shí)間不能修改某些表）

觸發(fā)器如下：

CREATE OR REPLACE TRIGGER TRIGGER1 
AFTER INSERT ON TRIGGERTEST 
FOR EACH ROW 
BEGIN
   IF(TO_CHAR(SYSDATE,'DAY') IN ('星期三','星期天'))   THEN RAISE_APPLICATION_ERROR(-20001,'不是上班時(shí)間，不能修改表格triggertest');   END IF;END;

執(zhí)行語(yǔ)句：

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

今天周三因而輸出結(jié)果為：

在行 1 上開始執(zhí)行命令時(shí)出錯(cuò):insert into triggertest (id) values ('aabbcc')
錯(cuò)誤報(bào)告:
SQL 錯(cuò)誤: ORA-20001: 不是上班時(shí)間，不能修改表格triggertest
ORA-06512: 在 "HNZC.TRIGGER1", line 3ORA-04088: 觸發(fā)器 'HNZC.TRIGGER1' 執(zhí)行過(guò)程中出錯(cuò)

通常對(duì)表的修改限制如下（即周一至周五9——18點(diǎn)能修改表格）

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,'不是上班時(shí)間，不能修改表格triggertest');   END IF;END;

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

觸發(fā)器如下：（如下實(shí)現(xiàn)月兒的分?jǐn)?shù)只能增加）

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,'月兒的分?jǐn)?shù)只能提升不能下降');        END IF;    END CASE;END;

當(dāng)前月兒的分?jǐn)?shù)為20

當(dāng)修改為10時(shí)出錯(cuò)

UPDATE "HNZC"."TRIGGERTEST" SET SCORE = '10' WHERE ROWID = 'AAAdEzAAPAAAAH+AAB' AND ORA_ROWSCN = '47685303'ORA-20001: 月兒的分?jǐn)?shù)只能提升不能下降
ORA-06512: 在 "HNZC.TRIGGER1", line 4ORA-04088: 觸發(fā)器 'HNZC.TRIGGER1' 執(zhí)行過(guò)程中出錯(cuò)

當(dāng)修改為30時(shí)成功

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

4）在觸發(fā)器中調(diào)用存儲(chǔ)過(guò)程

觸發(fā)器為：

CREATE OR REPLACE TRIGGER TRIGGER1 
BEFORE INSERT OR DELETE OR UPDATE ON TRIGGERTEST 
FOR EACH ROW 
BEGIN
  TESTPRO1();END;

存儲(chǔ)過(guò)程為：

create or replacePROCEDURE TESTPRO1 AS BEGIN
 insert into tab1(id,name,score) VALUES('AAA','BBB',200);END TESTPRO1;

執(zhí)行完畢后tab1中增加一條數(shù)據(jù)

5）級(jí)聯(lián)更新

觸發(fā)器如下（triggertest表中name修改時(shí)同時(shí)修改tab1中的name）

create or replacePROCEDURE TESTPRO1 AS BEGIN
 insert into tab1(id,name,score) VALUES('AAA','BBB',200);END TESTPRO1;

執(zhí)行語(yǔ)句：

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

結(jié)果：tab1中name為月兒的也更改為水兒

6）instead of觸發(fā)器

TABLE STUDENT表格數(shù)據(jù)如下

創(chuàng)建視圖student_view

CREATE OR REPLACE VIEW STUDNET_VIEW 
AS SELECT CLASSID,AVG(SCORE) AVERAGE_SCORE FROM STUDENTGROUP BY CLASSID;

視圖數(shù)據(jù)如下：

對(duì)視圖student_view 執(zhí)行如下操作：

DELETE FROM STUDNET_VIEW WHERE CLASSID='111';

執(zhí)行結(jié)果：

錯(cuò)誤報(bào)告:
SQL 錯(cuò)誤: ORA-01732: 此視圖的數(shù)據(jù)操縱操作非法01732. 00000 -  "data manipulation operation not legal on this view"

解決方法：創(chuàng)建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;

執(zhí)行刪除語(yǔ)句

DELETE FROM STUDNET_VIEW WHERE CLASSID='111';

執(zhí)行結(jié)果：刪除成功

1 行已刪除。

4.注意事項(xiàng)

1） 在觸發(fā)器的執(zhí)行部分只能用DML語(yǔ)句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL語(yǔ)句（CREATE、ALTER、DROP）

2） 觸發(fā)器中不能使用commit語(yǔ)句，觸發(fā)器的操作與觸發(fā)事件（INSERT,UPDATE,DELETE）一起進(jìn)行COMMIT和ROLLBACK;

3)  一個(gè)表上的觸發(fā)器越多，對(duì)于表的DML操作性能影響越大

4） 觸發(fā)器最大為32K