Oracle 觸發器練習

Oracle觸發器開發與設計

 

        一、　什麼是觸發器？
              資料庫觸發器是一個儲存的PL/SQL程式塊，它與一個基表聯絡，當在表上執行特定的資料庫維護（插入、刪除、更新這三種操作）時，隱含地執行一個PL/SQL程式塊。

 

        二、觸發器的作用：
              。防止非法的資料庫操縱、維護資料庫安全
              。對資料庫的操作進行審計，儲存曆史資料
              。完成資料庫初始化處理
              。控制資料庫的資料完整性
              。進行相關資料的修改
              。完成資料複製
              。自動完成資料庫統計計算
              。限制資料庫操作的時間、許可權等，控制實體的安全性

 
         三、觸發器的組成：
             1、觸發時間：觸發事件的時間次序（before, afer)[2]

             2、觸發事件：什麼SQL語句會引起觸發器觸發(Insert, delete, update)[3]

             3、觸發子體：觸發器觸發時要執行的操作(一個完整的PL/SQL程式)

             4、觸發類型：觸發器被執行的次數(語句級、行級）[2] //語句級只執行一次，行級會執行多次。

                  [*]一個表上最多可以建立12個不同類型的觸發器：3*2*2 = 12

 

          四、建立觸發器注意事項：
              1、在觸發器中可以調用預存程序、包；在預存程序中不得調用觸發器。

              2、在觸發器中不得使用commit, rollback, savepoint語句。

              3、在觸發器中不得間接調用含有commit, rollback, savepoint的語句的預存程序及函數。

 

           五、建立語句級觸發器：
                    語句級觸發器： 請參考PowerPoint教程：預存程序1.ppt[Page19]　該觸發器在資料庫操作時只執行一次。
                 說明：
                       。update中的of是可選項，用於指定語句要修改的列
                       。要建立的觸發器已經存在時，使用replace選項

1. //例1：before型觸發器：    
2. Create or replace trigger DelEmp   
3.   before delete on emp   
4.   Begin  
5.    if (To_Char(sysdate,'dy') in ('星期六','星期日') or  
6.         To_number(To_Char(sysdate,'hh24'))  not between  8   and 18)   
7.    then dbms_output.put_line('現在是非工作時間,請退出!!!');   
8.    end if;   
9.   End;  

[觸發器資料字典]
SQL> select table_owner, table_name,trigger_body from user_triggers where trigger_name='DELEMP';

1. //例2：After型觸發器：   
2. Create or replace trigger InsertEmp   
3. after insert on emp     // 如果是before，就會比after的結果少一名。   
4. Declare  
5.  v_empcount number(7);   
6. Begin  
7.  select count(*) into v_empcount from emp;   
8.  dbms_output.put_line('目前員工總數已達到：'|| v_empcount|| '名。');   
9. End;  

 

1. //例3：多個觸發條件   
2. Create or replace trigger ChangeEmp   
3. before delete or insert or update on emp   
4. Begin  
5.    if (To_Char(sysdate,'dy') in ('星期六','星期日') or  
6.         To_number(To_Char(sysdate,'hh24'))  not between 8 and 18)   
7.    then dbms_output.put_line('現在是非工作時間,請不要修改資料!!!');   
8.    end if;   
9. End;   
10.   
11. // 更完善的寫法：   
12. Create or replace trigger ChangeEmp   
13.   before delete or insert or update  on emp   
14.   Begin  
15.    if (DELETING and (To_Char(sysdate,'dy') in ('星期六','星期日') or  
16.         To_number(To_Char(sysdate,'hh24'))  not between  8   and 18))   
17.    then dbms_output.put_line('現在是非工作時間,不要刪除資料！');   
18.   
19.    elsif (UPDATING and (To_Char(sysdate,'dy') in ('星期六','星期日') or  
20.         To_number(To_Char(sysdate,'hh24'))  not between  8 and 18))   
21.    then dbms_output.put_line('現在是非工作時間,不要更新資料！');   
22.   
23.    elsif (INSERTING and (To_Char(sysdate,'dy') in ('星期六','星期日') or  
24.         To_number(To_Char(sysdate,'hh24'))  not between  8   and 18))   
25.    then dbms_output.put_line('現在是非工作時間,不要插入資料！');   
26.   
27.    end if;   
28.   End;   

 

        六、建立行級觸發器：
            等級觸發器：增加選項for each row, 使觸發器在每一行上觸發。

             1、建立行級觸發器注意事項：
                      (1) 在行級觸發器中，在列名前增加old表示該列修改前值，增加new表示該列修改後值。
                      (2) 在PL/SQL中引用時，前邊增加冒號。
 

1. [例4: 行級觸發器] //必須是對所有的行進行操作才行。   
2. Create or Replace trigger UpdateEmp   
3. Before update on emp    
4. for each row    
5. Begin  
6.  dbms_output.put_line(:old.sal||'--------->'||:new.sal);   
7. End;  

 

1. [例5：儲存曆史資料，這種使用方法很重要，用來儲存關鍵錶的歷史資料]   
2. CReate or Replace trigger ChangeEmp   
3. Before update or delete on emp   
4. for each row   
5. Begin  
6.  Insert into oldemp(empno, ename,job,hiredate,sal)   
7.  values(:old.empno,:old.ename,:old.job,sysdate,:old.sal);   
8. End;   
9.   
10. SQL> create table oldemp   
11.  as select empno, ename,job,hiredate,sal from emp where 1>2;   

1. [例6：修改外鍵]   
2. Create or Replace trigger UpdateDept   
3. after update on dept   
4. for each row   
5. Begin  
6.  update emp   
7.   set emp.deptno = :new.deptno   
8.   where emp.deptno = :old.deptno;   
9. End;  

1. [例7：刪除外鍵、刪除相關資料]   
2. Create or Replace trigger DeleteDept   
3. before delete on dept   
4. for each row   
5. Begin  
6.  delete from emp where deptno = :old.empno;   
7. End;  

 

        七、觸發器管理
             1、使觸發器失效：

1. SQL> alter trigger 觸發器名稱 disable;  // 失效   
2. SQL> Alter Trigger 觸發器名稱 enable;  // 生效   
3.   
4. SQL> Alter table 表名 DISABLE all triggers; // 一個表上的所有觸發器失效   
5. SQL> Alter table 表名 ENABLE all triggers; // 使一個表上的所有觸發器生效   
6.   
7. SQL> Drop Trigger 觸發器名;  // 刪除觸發器;   

 

　觸發器是一種特殊類型的預存程序，它不同於預存程序。觸發器主要是通過事件進行觸發而被執行的，觸發器的觸發事件分可為3類，分別是DML事件、DDL事件和資料庫事件，而預存程序可以通過預存程序名字而被直接調用。當對某一表進行諸如UPDATE、 INSERT、 DELETE 這些操作時，SQL Server 就會自動執行觸發器所定義的SQL 陳述式，從而確保對資料的處理必須符合由這些SQL 陳述式所定義的規則。

　　觸發器是特定事件出現的時候，自動執行的代碼塊。類似於預存程序，但是使用者不能直接調用他們。

　　觸發器功能

　　1、 允許/限制對錶的修改

　　2、 自動產生衍生的資料行，比如自增欄位

　　3、 強制資料一致性

　　4、 提供審計和日誌記錄

　　5、 防止無效的交易處理

　　6、 啟用複雜的商務邏輯

　　觸發器種類

　　觸發器的種類可劃分為4種：1.資料操縱語言(DML)觸發器、2.替代(INSTEAD OF)觸發器、3.資料定義語言 (Data Definition Language)(DDL)觸發器、4.資料庫事件觸發程序。

　　資料操縱語言(DML)觸發器：簡稱DML觸發器，是定義在表上的觸發器，建立在表上。由DML事件引發的觸發器，編寫DML觸發器時的兩點要素是： 1.確定觸發的表，即在其上定義觸發器的表。2.確定觸發的事件，DML觸發器的觸發事件有INSERT、UPDATE和DELETE三種;替代觸發器，簡稱INSTEAD　OF觸發器，建立在視圖上，用來替換對視圖進行的刪除、插入和修改操作; 資料定義語言 (Data Definition Language)(DDL)觸發器，簡稱DDL觸發器，定義在模式上，觸發事件是資料對象的建立和修改;資料庫事件觸發程序，定義在整個資料庫或模式上，觸發事件是資料庫事件.

　　ORACLE產生資料庫觸發器的文法為：

　　CREATE [OR REPLACE] TRIGGER 觸發器名

　　{BEFORE|AFTER|INSTEAD OF} 觸發事件1 [OR 觸發事件2...]

　　ON 表名

　　WHEN 觸發條件

　　[FOR EACH ROW]

　　DECLARE

　　聲明部分

　　BEGIN

　　主體部分

　　END;

　　其中：

　　觸發器名：觸發器對象的名稱。由於觸發器是資料庫自動執行的，因此該名稱只是一個名稱，沒有實質的用途。一個觸發器可由多個不同的資料操縱語言操作觸發。在觸發器中，可用INSERTING、DELETING、UPDATING謂詞來區別不同的資料操縱語言操作。這些謂詞可以在IF分支條件陳述式中作為判斷條件來使用。

　　觸發時間：指明觸發器何時執行，該值可取, 觸發的時間有BEFORE和AFTER兩種，分別表示觸發動作發生在DML語句執行之前和語句執行之後。確定觸發層級，有語句級觸發器和行級觸發器兩種。語句級觸發器表示SQL語句只觸發一次觸發器，行級觸發器表示SQL語句影響的每一行都要觸發一次。

　　Before：表示在資料庫動作之前觸發器執行;在SQL語句的執行過程中，如果存在行級BEFORE觸發器，則SQL語句在對每一行操作之前，都要先執行一次行級BEFORE觸發器，然後才對行進行操作。如果存在行級AFTER觸發器，則SQL語句在對每一行操作之後，都要再執行一次行級AFTER觸發器。

　　after：表示在資料庫動作之後出發器執行。如果存在語句級AFTER觸發器，則在SQL語句執行完畢後，要最後執行一次語句級AFTER觸發器。

　　觸發事件：指明哪些資料庫動作會觸發此觸發器,指INSERT、DELETE或UPDATE事件，事件可以並行出現，中間用OR串連;

　　insert：資料庫插入會觸發此觸發器;

　　update：資料庫修改會觸發此觸發器;

　　delete：資料庫刪除會觸發此觸發器。

　　表 名：資料庫觸發器所在的表。

　　for each row：表示觸發器為行級觸發器，省略則為語句級觸發器,對錶的每一行觸發器執行一次。

　　觸發器的建立者或具有DROP ANY TIRGGER系統許可權的人才能刪除觸發器。刪除觸發器的文法如下：

　　DROP TIRGGER 觸發器名

　　可以通過命令設定觸發器的可用狀態，使其暫時關閉或重新開啟，即當觸發器暫時不用時，可以將其置成無效狀態，在使用時重新開啟。該命令文法如下：

　　ALTER TRIGGER 觸發器名 {DISABLE|ENABLE}

　　其中，DISABLE表示使觸發器失效，ENABLE表示使觸發器生效。

　　同預存程序類似，觸發器可以用SHOW ERRORS 檢查編譯錯誤。

　　如果有多個觸發器被定義成為相同時間、相同事件觸發，且最後定義的觸發器是有效，則最後定義的觸發器被觸發，其他觸發器不執行。觸發器體內禁止使用COMMIT、ROLLBACK、SAVEPOINT語句，也禁止直接或間接地調用含有上述語句的預存程序。定義一個觸發器時要考慮上述多種情況，並根據具體的需要來決定觸發器的種類。

　　觸發器的作用

　　觸發器的主要作用就是其能夠實現由主鍵和外鍵所不能保證的複雜的參照完整性和資料的一致性。除此之外，觸發器還有其它許多不同的功能：

　　(1) 強化約束(Enforce restriction)

　　觸發器能夠實現比CHECK 語句更為複雜的約束。

　　(2) 跟蹤變化Auditing changes

　　觸發器可以偵測資料庫內的操作，從而不允許資料庫中未經許可的指定更新和變化。

　　(3) 級聯運行(Cascaded operation)。

　　觸發器可以偵測資料庫內的操作，並自動地級聯影響整個資料庫的各項內容。例如，某個表上的觸發器中包含有對另外一個表的資料操作(如刪除，更新，插入)而該操作又導致該表上觸發器被觸發。

　　(4) 預存程序的調用(Stored procedure invocation)。

　　為了響應資料庫更新觸，發器可以調用一個或多個預存程序，甚至可以通過外部過程的調用而在DBMS( 資料庫管理系統)本身之外進行操作。

　　由此可見，觸發器可以解決進階形式的商務規則或複雜行為限制以及實現定製記錄等一些方面的問題。例如，觸發器能夠找出某一表在資料修改前後狀態發生的差異，並根據這種差異執行一定的處理。此外一個表的同一類型(INSERT、 UPDATE、 DELETE)的多個觸發器能夠對同一種資料操作採取多種不同的處理。

　　總體而言，觸發器效能通常比較低。

      當運行觸發器時，系統處理的大部分時間花費在參照其它表的這一處理上，因為這些表既不在記憶體中也不在資料庫裝置上，而刪除表和插入表總是位於記憶體中。可見觸發器所參照的其它表的位置決定了操作要花費的時間長短。