PL/SQL 觸發(fā)器

2021-08-30 15:59 更新

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

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

可以在事件關聯的表,視圖,模式或數據庫上定義觸發(fā)器。

使用觸發(fā)器的好處

觸發(fā)器可以用于以下目的 -

  • 自動生成一些派生列值
  • 強化參照完整性
  • 事件記錄和存儲表訪問信息
  • 審計
  • 表的同步復制
  • 實施安全授權
  • 防止無效的事務

創(chuàng)建觸發(fā)器

創(chuàng)建觸發(fā)器的語法是 -

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;
SQL

其中,

  • CREATE [OR REPLACE] TRIGGER trigger_name - 使用trigger_name創(chuàng)建或替換現有的觸發(fā)器。
  • {BEFORE | AFTER | INSTEAD OF} - 指定何時執(zhí)行觸發(fā)器。INSTEAD OF子句用于在視圖上創(chuàng)建觸發(fā)器。
  • {INSERT [OR] | UPDATE [OR] | DELETE} - 這指定了DML操作。
  • [OF col_name] ? 這指定了將要更新的列名稱。
  • [ON table_name] - 這指定了與觸發(fā)器關聯的表的名稱。
  • [REFERENCING OLD AS o NEW AS n] - 這允許各種DML語句(如INSERT,UPDATE和DELETE)引用新值和舊值。
  • [FOR EACH ROW] - 這指定了一個行級別的觸發(fā)器,即觸發(fā)器將被執(zhí)行的每一行受到影響。否則觸發(fā)器將在執(zhí)行SQL語句時執(zhí)行一次,這稱為表級觸發(fā)器。
  • WHEN(condition) - 這為觸發(fā)器觸發(fā)的行提供了一個條件。該子句僅對行級觸發(fā)器有效。

示例

首先,將使用前面章節(jié)中創(chuàng)建和使用的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 );
SQL

下面的程序為customers表創(chuàng)建一個行級觸發(fā)器,該觸發(fā)器將觸發(fā)在customers表上執(zhí)行的INSERT,UPDATE或DELETE操作。這個觸發(fā)器將顯示舊值和新值之間的工資差異 -

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

當上面的代碼在SQL提示符下執(zhí)行時,它會產生以下結果 -

這里需要考慮以下幾點 -

  • OLD和NEW引用不可用于表級觸發(fā)器,而是可以將它們用于記錄級觸發(fā)器。
  • 如果要在同一個觸發(fā)器中查詢表,則應該使用AFTER關鍵字,因為觸發(fā)器只能在應用初始更改并且表返回一致狀態(tài)后才能查詢表或進行更改。
  • 上面的觸發(fā)器是這樣:在表上執(zhí)行任何DELETE或INSERT或UPDATE操作之前觸發(fā),但是可以在一個或多個操作上編寫觸發(fā)器,例如BEFORE DELETE,當表中的一條記錄被刪除時,自動觸發(fā)。

觸發(fā)一個觸發(fā)器

現在,在customers表上執(zhí)行一些DML操作。這里以執(zhí)行一個INSERT語句作為示例,它將在表中創(chuàng)建一個新記錄 -

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

當在CUSTOMERS表中創(chuàng)建一條記錄時,上面的創(chuàng)建觸發(fā)器display_salary_changes將被觸發(fā),并且將顯示以下結果 -

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

已創(chuàng)建 1 行。

SQL>
SQL

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

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

執(zhí)行上面示例代碼,得到以下結果 -

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

已更新 1 行。

 



以上內容是否對您有幫助:
在線筆記
App下載
App下載

掃描二維碼

下載編程獅App

公眾號
微信公眾號

編程獅公眾號