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 表：

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 | 
+----+----------+-----+-----------+----------+

以下程式為 customers 表建立了一個行級觸發器，該觸發器將在對 CUSTOMERS 表執行 INSERT、UPDATE 或 DELETE 操作時觸發。此觸發器將顯示舊值和新值之間的薪資差異：

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 提示符下執行時，將產生以下結果：

Trigger created.

此處需要注意以下幾點：

  • OLD 和 NEW 引用不適用於表級觸發器，而您可以將它們用於記錄級觸發器。

  • 如果您想在同一個觸發器中查詢表，則應使用 AFTER 關鍵字，因為觸發器只有在初始更改應用且表恢復到一致狀態後才能查詢表或再次更改表。

  • 上述觸發器是在任何 DELETE、INSERT 或 UPDATE 表操作之前觸發的，但您可以為單個或多個操作編寫觸發器，例如 BEFORE DELETE，它將在使用表上的 DELETE 操作刪除記錄時觸發。

觸發觸發器

讓我們對 CUSTOMERS 表執行一些 DML 操作。這是一個 INSERT 語句，它將在表中建立一個新記錄：

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

當在 CUSTOMERS 表中建立記錄時，上述建立的觸發器 display_salary_changes 將被觸發，並顯示以下結果：

Old salary: 
New salary: 7500 
Salary difference:

因為這是一個新記錄，所以舊工資不可用，以上結果為 null。現在讓我們對 CUSTOMERS 表執行另一個 DML 操作。UPDATE 語句將更新表中現有記錄：

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

當在 CUSTOMERS 表中更新記錄時，上述建立的觸發器 display_salary_changes 將被觸發，並顯示以下結果：

Old salary: 1500 
New salary: 2000 
Salary difference: 500
列印頁面
上一頁
下一頁
廣告