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 行。