16. PL/SQL Trigger

Trigger會自動執行一些事件發生。而Trigger觸發的機制可有以下的事件發動：

• 資料庫操作(DML)語句(DELETE，INSERT，UPDATE或)
• 資料庫定義(DDL)語句(CREATE，ALTER或DROP)
• 資料庫操作(SERVER ERROR，登錄，注銷，啟動或關機)

Trigger可以在Table，View或資料庫與該事件相關聯上定義

Trigger的優點

Trigger可以用於以下目的：

• 自動產生所需要的值
• 實施參照完整性
• 事件日誌和對表的訪問存儲信息
• 稽核
• Table的同步複製
• 確認安全許可
• 防止非法交易

建置Trigger

建置Trigger的語法：

CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition)   DECLARE   Declaration-statements BEGIN   Executable-statements EXCEPTION   Exception-handling-statements END;

• CREATE [OR REPLACE] TRIGGER trigger_name:建置或替換現有的Trigger：trigger_name
• {BEFORE | AFTER | INSTEAD OF} : 指定當觸發將被執行。在INSTEAD OF子句用於在View中建置Trigger
• {INSERT [OR] | UPDATE [OR] | DELETE}: 指定DML操作
• [OF col_name]: 指定將被更新的欄位名稱
• [ON table_name]: 指定Trigger相關聯的表格的名稱
• [REFERENCING OLD AS o NEW AS n]: 可以參考新舊值的各種DML語句，如INSERT，UPDATE和DELETE
• [FOR EACH ROW]: 指定的行級Trigger，即Trigger將每一行受到影響執行。否則，當執行SQL語句，這被稱為Table等級Trigger觸發將執行一次
• WHEN (condition): Trigger將觸發的條件。此子句僅適用於行級Trigger有效

範例:

Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME     | AGE | ADDRESS   | SALARY   | +----+----------+-----+-----------+----------+ |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | |  2 | Khilan   |  25 | Delhi     |  1500.00 | |  3 | kaushik  |  23 | Kota      |  2000.00 | |  4 | Chaitali |  25 | Mumbai    |  6500.00 | |  5 | Hardik   |  27 | Bhopal    |  8500.00 | |  6 | Komal    |  22 | MP        |  4500.00 | +----+----------+-----+-----------+----------+

下面的Trigger建置在customers table中，將觸發INSERT或UPDATE或DELETE在Customers Table進行資料等級的觸發。Trigger將顯示工資的舊值和新值之間的差額：

CREATE OR REPLACE TRIGGER display_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON customers FOR EACH ROW WHEN (NEW.ID > 0) DECLARE   sal_diff number; BEGIN   sal_diff := :NEW.salary  - :OLD.salary;   dbms_output.put_line('Old salary: ' || :OLD.salary);   dbms_output.put_line('New salary: ' || :NEW.salary);   dbms_output.put_line('Salary difference: ' || sal_diff); END; /

結果如下：

Trigger created.

觸發Trigger

讓我們在CUSTOMERS Table執行某些DML操作。這裡有一個INSERT語法，這將在Table中新增一筆記錄：

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

結果如下：

Old salary: New salary: 7500 Salary difference:

以上的結果是因為這是一筆新記錄，原本是沒有(null)。現在，讓我們對CUSTOMERS Table進行多一個DML。：

UPDATE customers SET salary = salary + 500 WHERE id = 2;

結果如下：

Old salary: 1500 New salary: 2000 Salary difference: 500