Oracle之觸發器

觸發器介紹：

    資料庫觸發器（DataBase Trigger）是存儲在資料庫中的過程，當表被修改時它隐式的被激發執行。在Oracle中允許在對表進行insert、update和delete操作時隐式的執行所定義的過程，這些過程稱為資料庫的觸發器。

    觸發器一般用于：

      1、自動生成導出的列值；

      2、防止無效的事務；

      4、實施更複雜的安全性檢查；

      5、在分布式資料庫中實施跨越節點的引用完整性；

      6、實施複雜的事務規則；

      7、提供透明事件日志；

      8、提供進階的審計；

      9、維護同步表複制；

      10、收集關于存取表的統計。

觸發器的優點：

    1、觸發器時自動的：當對表中的資料做了任何修改之後立即被激活（比如手工輸入或者應用程式采取的操作）；

    2、觸發器可以通過資料庫中的相關表進行層疊更改；

    3、觸發器可以強制限制，這些限制比用check限制定義的更複雜。

觸發器的類型：

    每一觸發語句可有三種類型的觸發器：

     1、DML觸發器：由對表的insert，delete，update激發

     2、INSTEAD  OF觸發器：代替直接對視圖insert，delete，update操作

     3、系統觸發器：a.DDL事件（CREATE，ALTER，DROP語句）需要有ADMISTRATER，DATABASE，TRIGGER特權；

                    b.資料庫事件

                        伺服器啟動，關閉

                        使用者登入，登出

                        伺服器錯誤等

建立觸發器文法：

   Create [or replace] trigger [模式] 觸發器名稱

          Before|after   insert|delete(uodate of 列名)

    on 表名

    [for each row]

    [When 條件]

    PL/SQL塊      

 注意：觸發器名稱：觸發器對象的名稱。由于觸發器是資料庫自動執行的，是以該名稱隻是一個名稱，沒有實質的用途。          

  例如：

   create trigger  T_emp_trigger    

   before insert or update  --定義觸發事件

   of ename                 --定義觸發字段

   on emp                   --定義需要觸發的表

   referencing old as old_value   --定義觸發前的資料

               new as new_value   --定義觸發後的資料

   for each row                   --觸發範圍每一行

   when (new_value.ename <>'jack')  --定義觸發條件

   begin                            --定義觸發内容

       dbms_output.put_line('myRS_emp_trigger is start!');

       :new_value.ename:='Mark';

   end;

參數說明：

  for each row 

    語句級觸發器：如果在建立觸發器時未使用for each row子句，則該觸發器為語句級觸發器，該觸發器在每個資料修改語句執行後隻調用一次，而不管這一操作将影響多少行。

    行級觸發器：如果在建立觸發器時使用for each row子句，則該觸發器為行級觸發器，當一個DML操作影響資料庫中的多行資料時，對于每一資料行，行級觸發器均會被觸發一次。

  When條件的出現說明了，在DML操作的時候也許一定會觸發觸發器，但是觸發器    不一定會做實際的工作，比如when後的條件不為真的時候，觸發器隻是簡單的跳過了PL/SQL塊；

觸發器的原理：

 一、 DML觸發器的臨時表 :NEW和:OLD

  當使用者對資料庫做事務處理的操作時，資料系統會産生臨時表:NEW和:OLD，這些臨時表僅僅存在與目前的DML操作，表的結構與DML操作表的結構一緻，存儲的值分别是： 

    :NEW ：如果DML是insert或update的操作，則産生:NEW臨時表，存儲的值是DML操作插入或更改的新值。

    :OLD ：如果DML是delete或update的操作，則産生:OLD臨時表，存儲的值是DML操作删除或更改前的值。

二、:NEW和:OLD僅用在于行級觸發器，不适用與語句級觸發器

三、這兩個表是動态駐留在記憶體中，當觸發器的工作完成，這兩個表也被删除

四、這兩個表的主要儲存因使用者操作而影響到的原始資料值或新資料值

五、這兩個表示隻讀的，使用者不能向這兩個表寫入資料，但是可以引用表中的資料

六、:NEW與:OLD ：必須是針對行級觸發器的，也就是說要使用這兩個變量的觸發器一定有for each row

    這兩個變量是系統自動提供的數組變量，:new用來記錄新插入的值，:old用來記錄被删除的值；

    使用insert的時候隻有:new 裡面有值；

    使用delete的時候隻有:old 裡面有值；

    使用update的時候:new和:old 裡面都有值；

Instead of 觸發器：

  1、當為表或視圖定義針對某一操作（insert、delete、update）的instead of類型的觸發器且執行了相應的操作時，盡管觸發器被觸發，但相應的操作并不被執行而運作的僅是觸發器的SQL語句本身。

  2、Instead of觸發器主要的優點是使不可修改的視圖能夠支援修改。其中最典型的是分割視圖。為了提高查詢的性能，分割視圖通常來自多個表的結果集，但是也正是是以而不支援視圖的更新。

  3、通過使用邏輯語句以執行批處理的某一部分而放棄執行其餘的部分。可以定義觸發器在遇到某一錯誤時，轉而執行觸發器的另外部分。

  4、instead of insert執行你在觸發器裡面的代碼，替代系統insert操作。

利用instead of觸發器，對更新分割視圖：

   1、首先建立三個表salemay，salejune，salejuly這三個表分别用來儲存五、六、七月的銷售量資訊：

   create table salemay(|salejune|salejuly)

    (

     sale_id  char(6) nor null,

     sale_name varchar(20),

     sale_qua smallint

    )

  2、建立視圖進行聯合查詢

    create or replace view saleview

      as 

        select * from salemay

        union all

        select * from salejune

        union all

        select * from salejuly; 

  3、在視圖上建立instead of insert觸發器，當視圖上觸發insert操作時，通過對Sale_id的分析，判斷出插入的記錄應該在那個表中進行。其中利用substring（）函數，第一個參數為目标字元串，第二個參數是起始位元組，第三個參數是位元組的長度。

 Create or replace trigger saleviewtr

   instead of insert

   on saleview 

 Declare

  sale_id varchar(6);

  sale_name varchar2(20);

  sale_qua number(8);

 BEGIN

  sale_id:=:new.sale_id;

  sale_name:=:new.sale_name;

  sale_qua:=:new.sale_qua;

 IF substr(sale_id,1,3)='may' THEN

    INSERT INTO SALEMAY VALUES(sale_id,sale_name,sale_qua);

 END IF;

 IF substr(sale_id,1,3)='jun' THEN

     INSERT INTO salejune  VALUES(sale_id,sale_name,sale_qua);

 END IF;

 IF substr(sale_id,1,3)='jul' THEN

     INSERT INTO salejuly  VALUES(sale_id,sale_name,sale_qua);

 END IF;

END;

在視圖上執行插入操作，分别插入以下記錄

  insert into saleview values('jul001','www',100)

  go 

  insert into saleview values('jun001','eee',80)

  go

  insert into saleview values('jun001','rrr',50)

  go

然後在相應的表中檢視相應的記錄。可以看到每個表中均插入了一條記錄。

系統時間觸發器：

 系統時間觸發器是指由資料庫系統事件觸發的資料庫觸發器。資料庫系統包括以下幾種：

  1、資料庫的啟動（startup）

  2、資料庫的關閉（shutdown）

  3、資料庫伺服器的出錯（servererror）

  注意：系統事件觸發器不是與特定的表或視圖關聯。

建立一個系統事件觸發器：

 create [or replace] trigger 觸發器名稱

 {before|after}

 {database_event_list}

 on {database|schema}

 pl/sql語句；

例：建立一個系統事件觸發器，記錄系統每次的啟動時間：

   create table database_log(op_datetimestamp);

   crete or replace trigger 

   database_startup

   after startup

   on database

   begin

      insert into database_log

   values(sysdate);

   end;

使用者事件觸發器：

 使用者事件觸發器是指：與資料庫定義語句DDL或使用者登入/登出等事件相關的觸發器，并且可以規定觸發時間before和after：

  create,alter,drop,analyze,audit,notaudit,grant,revoke,rename,truncate

隻可以指定觸發時間before的使用者事件：logoff

隻可以指定觸發時間after的使用者事件：logon

 例：建立一個使用者事件觸發器，記錄使用者登入系統的使用者名與時間

 create table login(who varchar2(20),log

 timestamp);

 create or replace trigger tr_log

 after logon

 on database

 begin

   insert into login  values(user,sysdate);

 end;

ALTER TRIGGER語句：

  alter trigger語句用來重新編譯、啟用或禁用觸發器。如果在觸發器内調用了函數或過程，則當這些函數或過程被删除或修改後，觸發器的狀态将被辨別為無效INVALID。

   當觸發一個無效的觸發器時，Oracle将重新編譯觸發器代碼，如果重新編譯時發現錯誤，這将導緻DML語句執行失敗。

   在PL/SQL程式中可以調用alter trigger語句，重新編譯已經建立的觸發器：

 Alter trigger [schema.] trigger_name compile;

 Alter trigger的另外一種用法是禁用和啟用觸發器；

 Alter trigger [schema.] trigger_name disable|enable;

觸發器注意事項：

   1、create trigger語句必須是批進行中的第一個語句；

   2、建立觸發器的權限預設配置設定給表的所有者，且不能将該權限轉讓給其他使用者；

   3、觸發器為資料庫對象，其名稱必須遵循辨別符的命名規則；

   4、雖然觸發器可以引用目前資料庫以外的對象，但隻能在目前資料庫中建立觸發器；

   5、雖然不能再臨時表或系統表上建立觸發器，但是觸發器可以引用臨時表；

   6、雖然truncate  table語句類似于沒有where子句（用于删除行）的delete語

句，但它并不會引發delete觸發器，因為truncate table語句沒有記錄。

   7、writetext語句不會引發insert或update觸發器。

   8、當建立一個觸發器時必須指定：a）名稱；

                                                         b）在其上定義觸發器的表；

                                                         c）觸發器将何時激發；

                                                         d） 激活觸發器的資料修改語句；