程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> 關於Oracle數據庫 >> oracle觸發器如何使用

oracle觸發器如何使用

編輯:關於Oracle數據庫

       觸發器 是特定事件出現的時候,自動執行的代碼塊。類似於存儲過程,但是用戶不能直接調用他們。觸發器是許多關系數據庫系統都提供的一項技術。在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表格數據如下

    oracle觸發器如何使用 三聯

      創建視圖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

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved