PL/SQL 觸發(fā)器

在本章中，我們將討論和學(xué)習(xí)PL/SQL中的觸發(fā)器。 觸發(fā)器是存儲(chǔ)的程序，在發(fā)生某些事件時(shí)會(huì)自動(dòng)執(zhí)行或觸發(fā)。事實(shí)上，觸發(fā)器是為了響應(yīng)以下任何事件而被執(zhí)行的 -

• 數(shù)據(jù)庫(kù)操作(DML)語(yǔ)句(DELETE，INSERT或UPDATE)
• 數(shù)據(jù)庫(kù)定義(DDL)語(yǔ)句(CREATE，ALTER或DROP)。
• 數(shù)據(jù)庫(kù)操作(SERVERERROR，LOGON，LOGOFF，STARTUP或SHUTDOWN)。

可以在事件關(guān)聯(lián)的表，視圖，模式或數(shù)據(jù)庫(kù)上定義觸發(fā)器。

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

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

• 自動(dòng)生成一些派生列值
• 強(qiáng)化參照完整性
• 事件記錄和存儲(chǔ)表訪問(wèn)信息
• 審計(jì)
• 表的同步復(fù)制
• 實(shí)施安全授權(quán)
• 防止無(wú)效的事務(wù)

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

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

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

示例

首先，將使用前面章節(jié)中創(chuàng)建和使用的CUSTOMERS表，表的定義和數(shù)據(jù)如下 -

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) 
);
-- 插入示例數(shù)據(jù)
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

下面的程序?yàn)閏ustomers表創(chuàng)建一個(gè)行級(jí)觸發(fā)器，該觸發(fā)器將觸發(fā)在customers表上執(zhí)行的INSERT，UPDATE或DELETE操作。這個(gè)觸發(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

當(dāng)上面的代碼在SQL提示符下執(zhí)行時(shí)，它會(huì)產(chǎn)生以下結(jié)果 -

這里需要考慮以下幾點(diǎn) -

• OLD和NEW引用不可用于表級(jí)觸發(fā)器，而是可以將它們用于記錄級(jí)觸發(fā)器。
• 如果要在同一個(gè)觸發(fā)器中查詢(xún)表，則應(yīng)該使用AFTER關(guān)鍵字，因?yàn)橛|發(fā)器只能在應(yīng)用初始更改并且表返回一致?tīng)顟B(tài)后才能查詢(xún)表或進(jìn)行更改。
• 上面的觸發(fā)器是這樣：在表上執(zhí)行任何DELETE或INSERT或UPDATE操作之前觸發(fā)，但是可以在一個(gè)或多個(gè)操作上編寫(xiě)觸發(fā)器，例如BEFORE DELETE，當(dāng)表中的一條記錄被刪除時(shí)，自動(dòng)觸發(fā)。

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

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

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

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

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

因?yàn)檫@是一個(gè)新的記錄，舊的薪水(salary)列是不可用的，上述結(jié)果為空。下面再向CUSTOMERS表上執(zhí)行另一個(gè)更多的DML操作。這次使用UPDATE語(yǔ)句來(lái)更新表中的現(xiàn)有記錄 -

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

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

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

已更新 1 行。