PL-SQL 觸發器，pl-sql觸發器

PL-SQL 觸發器，pl-sql觸發器


PL-SQL 觸發器

觸發器是許多關聯式資料庫系統都提供的一項技術。在ORACLE系統裡，觸發器類似過程和函數，都有聲明，執行和異常處理過程的PL/SQL塊。

①觸發器類型
觸發器在資料庫裡以獨立的Object Storage Service，它與預存程序不同的是，預存程序通過其它程式來啟動運行或直接啟動運行，而觸發器是由一個事件來啟動運行。
即觸發器是當某個事件發生時自動地隱式運行。並且，觸發器不能接收參數。所以運行觸發器就叫觸發或點火（firing）。
ORACLE事件指的是對資料庫的表進行的INSERT、UPDATE及DELETE操作或對視圖進行類似的操作。
ORACLE將觸發器的功能擴充到了觸發ORACLE，如資料庫的啟動與關閉等。
1.DML觸發器
ORACLE可以在DML語句進行觸發，可以在DML操作前或操作後進行觸發，並且可以對每個行或語句操作上進行觸發。
2.替代觸發器
由於在ORACLE裡，不能直接對由兩個以上的表建立的視圖進行操作。所以給出了替代觸發器。
3.系統觸發器
它可以在ORACLE資料庫系統的事件中進行觸發，如ORACLE系統的啟動與關閉等。

②觸發器組成: 
觸發事件：即在何種情況下觸發TRIGGER; 例如：INSERT, UPDATE, DELETE。
觸發時間：即該TRIGGER 是在觸發事件發生之前（BEFORE）還是之後(AFTER)觸發，也就是觸發事件和該TRIGGER 的操作順序。
觸發器本身：即該TRIGGER 被觸發之後的目的和意圖，正是觸發器本身要做的事情。例如：PL/SQL 塊。
觸發頻率：說明觸發器內定義的動作被執行的次數。即語句級(STATEMENT)觸發器和行級(ROW)觸發器。
      語句級(STATEMENT)觸發器：是指當某觸發事件發生時，該觸發器只執行一次；
      行級(ROW)觸發器：是指當某觸發事件發生時，對受到該操作影響的每一行資料，觸發器都單獨執行一次。

③建立觸發器
建立觸發器的一般文法是:
CREATE[OR REPLACE] TRIGGER trigger_name
    {BEFORE| AFTER}
    {INSERT| DELETE| UPDATE[OF column [, column …]]}
    ON[schema.] table_name
    [FOR EACH ROW]
    [WHEN condition]
    begin
        trigger_body;
    end;
其中：
    BEFORE 和AFTER指出觸發器的觸發時序分別為前觸發和後觸發方式，
        前觸發是在執行觸發事件之前觸發當前所建立的觸發器，後觸發是在執行觸發事件之後觸發當前所建立的觸發器。
    FOR EACH ROW選項說明觸發器為行觸發器。
        行觸發器和語句觸發器的區別表現在：行觸發器要求當一個DML語句操做影響資料庫中的多行資料時，對於其中的每個資料行，
 只要它們符合觸發約束條件，均啟用一次觸發器；
 而語句觸發器將整個語句操作作為觸發事件，當它符合約束條件時，啟用一次觸發器。
 當省略FOR EACH ROW 選項時，BEFORE和AFTER觸發器為語句觸發器，而INSTEAD OF 觸發器則為行觸發器。
    WHEN 子句說明觸發約束條件。Condition 為一個邏輯表達時，其中必須包含相關名稱，而不能包含查詢語句，也不能調用PL/SQL 函數。
        WHEN 子句指定的觸發約束條件只能用在BEFORE 和AFTER 行觸發器中，不能用在INSTEAD OF 行觸發器和其它類型的觸發器中。

    當一個基表被修改( INSERT, UPDATE, DELETE)時要執行的預存程序，執行時根據其所依附的基表改動而自動觸發，因此與應用程式無關，
    用資料庫觸發器可以保證資料的一致性和完整性。

每張表最多可建立12種類型的觸發器，它們是:
    BEFORE INSERT
    BEFORE INSERT FOR EACH ROW
    AFTER INSERT
    AFTER INSERT FOR EACH ROW

    BEFORE UPDATE
    BEFORE UPDATE FOR EACH ROW
    AFTER UPDATE
    AFTER UPDATE FOR EACH ROW

    BEFORE DELETE
    BEFORE DELETE FOR EACH ROW
    AFTERDELETE
    AFTER DELETE FOR EACH ROW

④觸發器觸發次序
1. 執行BEFORE語句級觸發器;
2. 對與受語句影響的每一行：
 執行BEFORE行級觸發器
 執行DML語句
 執行AFTER行級觸發器
3. 執行AFTER語句級觸發器

⑤建立DML觸發器
觸發器名可以和表或過程有相同的名字，但在一個模式中觸發器名不能相同。
觸發器的限制
 CREATE TRIGGER語句文本的字元長度不能超過32KB；
 觸發器體內的SELECT 語句只能為SELECT … INTO …結構，或者為定義遊標所使用的SELECT 語句。
 觸發器中不能使用資料庫事務控制語句COMMIT; ROLLBACK, SVAEPOINT 語句；
 由觸發器所調用的過程或函數也不能使用資料庫事務控制語句；

問題：當觸發器被觸發時，要使用被插入、更新或刪除的記錄中的列值，有時要使用操作前、後列的值.
實現:  :NEW  修飾符訪問操作完成後列的值
       :OLD  修飾符訪問操作完成前列的值
特性 INSERT UPDATE DELETE
OLD NULL 有效 有效
NEW 有效 有效 NULL
[例]
create or replace trigger hello_trigger
after
update on employees
for each row
begin
    dbms_output.put_line('hello...');
    dbms_output.put_line('old.salary:'|| :OLD.salary||',new.salary'||:NEW.salary);
end;

然後執行：update employees set salary = salary + 1000;

⑥建立替代(INSTEAD OF)觸發器
建立觸發器的一般文法是:
CREATE [OR REPLACE] TRIGGER trigger_name
    INSTEAD OF
    {INSERT | DELETE | UPDATE [OFcolumn [, column …]]}
    ON[schema.] view_name
    [FOR EACH ROW]
    [WHENcondition]
    begin
        trigger_body;
    end;
其中：
    INSTEAD OF 選項使ORACLE啟用觸發器，而不執行觸發事件。
    只能對視圖和物件檢視建立INSTEAD OF觸發器，而不能對錶、模式和資料庫建立INSTEAD OF 觸發器。
    FOR EACH ROW選項說明觸發器為行觸發器。
        行觸發器和語句觸發器的區別表現在：行觸發器要求當一個DML語句操做影響資料庫中的多行資料時，對於其中的每個資料行，只要它們符合觸發約束條件，均啟用一次觸發器；
        而語句觸發器將整個語句操作作為觸發事件，當它符合約束條件時，啟用一次觸發器。
        當省略FOR EACH ROW 選項時，BEFORE 和AFTER 觸發器為語句觸發器，而INSTEAD OF 觸發器則為行觸發器。
    WHEN 子句說明觸發約束條件。Condition 為一個邏輯表達時，其中必須包含相關名稱，而不能包含查詢語句，也不能調用PL/SQL 函數。
        WHEN 子句指定的觸發約束條件只能用在BEFORE 和AFTER 行觸發器中，不能用在INSTEAD OF 行觸發器和其它類型的觸發器中。
    INSTEAD_OF 用於對視圖的DML觸發，由於視圖有可能是由多個表進行連接(join)而成，因而並非是所有的連接都是可更新的。
    但可以按照所需的方式執行更新，
例如下面情況：
CREATE OR REPLACE VIEW emp_view
AS
    SELECT deptno, count(*) total_employeer, sum(sal) total_salary
    FROM emp GROUP BY deptno;

在此視圖中直接刪除是非法：
SQL>DELETE FROM emp_view WHERE deptno=10;
DELETE FROM emp_view WHERE deptno=10
*
ERROR 位於第1 行:
ORA-01732: 此視圖的資料操縱操作非法

但是可以建立INSTEAD_OF觸發器來為DELETE 操作執行所需的處理，即刪除EMP表中所有基準行：
   
CREATE OR REPLACE TRIGGER emp_view_delete
    INSTEAD OF DELETE ON emp_view FOR EACH ROW
BEGIN
    DELETE FROM emp WHERE deptno= :old.deptno;
END emp_view_delete;

DELETE FROM emp_view WHERE deptno=10;

⑦建立系統事件觸發器
ORACLE提供的系統事件觸發器可以在DDL或資料庫系統上被觸發。DDL指的是資料定義語言 (Data Definition Language)，如CREATE 、ALTER及DROP 等。
而資料庫系統事件包括資料庫伺服器的啟動或關閉，使用者的登入與退出、資料庫服務錯誤等。建立系統觸發器的文法如下：

1.建立觸發器的一般文法是:
CREATE OR REPLACE TRIGGER [sachema.] trigger_name
    {BEFORE|AFTER} 
    {ddl_event_list | database_event_list}
    ON { DATABASE | [schema.] SCHEMA }
    [WHEN_clause]
    begin
        trigger_body;
    end;
其中: ddl_event_list：一個或多個DDL 事件，事件間用OR 分開；database_event_list：一個或多個資料庫事件，事件間用OR 分開；
系統事件觸發器既可以建立在一個模式上，又可以建立在整個資料庫上。
當建立在模式(SCHEMA)之上時，只有模式所指定使用者的DDL操作和它們所導致的錯誤才啟用觸發器, 預設時為目前使用者模式。
當建立在資料庫(DATABASE)之上時，該資料庫所有使用者的DDL操作和他們所導致的錯誤，以及資料庫的啟動和關閉均可啟用觸發器。
要在資料庫之上建立觸發器時，要求使用者具有ADMINISTER DATABASE TRIGGER許可權。

⑧刪除觸發器：
DROP TRIGGER trigger_name;
當刪除其他使用者模式中的觸發器名稱，需要具有DROP ANY TRIGGER系統許可權，
當刪除建立在資料庫上的觸發器時，使用者需要具有ADMINISTER DATABASE TRIGGER系統許可權。
此外，當刪除表或視圖時，建立在這些對象上的觸發器也隨之刪除。

觸發器的狀態
資料庫TRIGGER 的狀態：
有效狀態(ENABLE)：當觸發事件發生時，處於有效狀態的資料庫觸發器TRIGGER 將被觸發。
無效狀態(DISABLE)：當觸發事件發生時，處於無效狀態的資料庫觸發器TRIGGER 將不會被觸發，此時就跟沒有這個資料庫觸發器(TRIGGER) 一樣。
資料庫TRIGGER的這兩種狀態可以互相轉換。格式為：
ALTER TIGGER trigger_name [DISABLE | ENABLE ];
例：ALTER TRIGGER emp_view_delete DISABLE;
ALTER TRIGGER語句一次只能改變一個觸發器的狀態，而ALTER TABLE語句則一次能夠改變與指定表相關的所有觸發器的使用狀態。格式為：
ALTER TABLE [schema.]table_name {ENABLE|DISABLE} ALL TRIGGERS;
例：使表EMP 上的所有TRIGGER 失效：
ALTER TABLE emp DISABLE ALL TRIGGERS;