oracle觸發器使用

標籤：scn   values   系統觸發器   級聯   工作   分享   llb   多次   ror   

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
 BEGIN
 END;

說明：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 3
 　　ORA-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 4
 　　ORA-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 replace
 　　PROCEDURE TESTPRO1 AS
 　　BEGIN
 　　 insert into tab1(id,name,score) VALUES(‘AAA‘,‘BBB‘,200);
 　　END TESTPRO1;
執行完畢後tab1中增加一條資料 

5）串聯更新
觸發器如下（triggertest表中name修改時同時修改tab1中的name）
 　　create or replace
 　　PROCEDURE 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 STUDENT
 　　GROUP 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 ROW
 　　BEGIN
 　　  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觸發器使用