PL/SQL觸發器

在本章中，我們將討論和學習PL/SQL中的觸發器。 觸發器是儲存的程式，在發生某些事件時會自動執行或觸發。事實上，觸發器是為了響應以下任何事件而被執行的 -

• 資料庫操作(DML)語句(DELETE，INSERT或UPDATE)
• 資料庫定義(DDL)語句(CREATE，ALTER或DROP)。
• 資料庫操作(SERVERERROR，LOGON，LOGOFF，STARTUP或SHUTDOWN)。

可以在事件關聯的表，檢視，模式或資料庫上定義觸發器。

使用觸發器的好處

觸發器可以用於以下目的 -

• 自動生成一些派生列值
• 強化參照完整性
• 事件記錄和儲存表存取資訊
• 審計
• 表的同步複製
• 實施安全授權
• 防止無效的事務

建立觸發器

建立觸發器的語法是 -

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_name建立或替換現有的觸發器。
• {BEFORE | AFTER | INSTEAD OF} - 指定何時執行觸發器。INSTEAD OF子句用於在檢視上建立觸發器。
• {INSERT [OR] | UPDATE [OR] | DELETE} - 這指定了DML操作。
• [OF col_name] ? 這指定了將要更新的列名稱。
• [ON table_name] - 這指定了與觸發器關聯的表的名稱。
• [REFERENCING OLD AS o NEW AS n] - 這允許各種DML語句(如INSERT，UPDATE和DELETE)參照新值和舊值。
• [FOR EACH ROW] - 這指定了一個行級別的觸發器，即觸發器將被執行的每一行受到影響。否則觸發器將在執行SQL語句時執行一次，這稱為表級觸發器。
• WHEN(condition) - 這為觸發器觸發的行提供了一個條件。該子句僅對行級觸發器有效。

範例

首先，將使用前面章節中建立和使用的CUSTOMERS表，表的定義和資料如下 -

CREATE TABLE CUSTOMERS( 
   ID   INT NOT NULL, 
   NAME VARCHAR (20) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR (25), 
   SALARY   DECIMAL (18, 2),        
   PRIMARY KEY (ID) 
);
-- 插入範例資料
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

下面的程式為customers表建立一個行級觸發器，該觸發器將觸發在customers表上執行的INSERT，UPDATE或DELETE操作。這個觸發器將顯示舊值和新值之間的工資差異 -

SET SERVEROUTPUT ON SIZE 999999;
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; 
/

當上面的程式碼在SQL提示符下執行時，它會產生以下結果 -

這裡需要考慮以下幾點 -

• OLD和NEW參照不可用於表級觸發器，而是可以將它們用於記錄級觸發器。
• 如果要在同一個觸發器中查詢表，則應該使用AFTER關鍵字，因為觸發器只能在應用初始更改並且表返回一致狀態後才能查詢表或進行更改。
• 上面的觸發器是這樣：在表上執行任何DELETE或INSERT或UPDATE操作之前觸發，但是可以在一個或多個操作上編寫觸發器，例如BEFORE DELETE，當表中的一條記錄被刪除時，自動觸發。

觸發一個觸發器

現在，在customers表上執行一些DML操作。這裡以執行一個INSERT語句作為範例，它將在表中建立一個新記錄 -

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (7, 'Hinew', 23, 'Oracle', 9500.00 );

當在CUSTOMERS表中建立一條記錄時，上面的建立觸發器display_salary_changes將被觸發，並且將顯示以下結果 -

SQL> INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
  2  VALUES (7, 'Hinew', 23, 'Oracle', 9500.00);
Old salary:
New salary: 9500
Salary difference:

已建立 1 行。

SQL>

因為這是一個新的記錄，舊的薪水(salary)列是不可用的，上述結果為空。下面再向CUSTOMERS表上執行另一個更多的DML操作。這次使用UPDATE語句來更新表中的現有記錄 -

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

執行上面範例程式碼，得到以下結果 -

SQL> UPDATE customers
  2  SET salary = salary + 500
  3  WHERE id = 2;
Old salary: 1500
New salary: 2000
Salary difference: 500

已更新 1 行。