在本章中,我們將討論和學習PL/SQL中的觸發(fā)器。 觸發(fā)器是存儲的程序,在發(fā)生某些事件時會自動執(zhí)行或觸發(fā)。事實上,觸發(fā)器是為了響應以下任何事件而被執(zhí)行的 -
可以在事件關聯的表,視圖,模式或數據庫上定義觸發(fā)器。
使用觸發(fā)器的好處
觸發(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
其中,
首先,將使用前面章節(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í)行時,它會產生以下結果 -
這里需要考慮以下幾點 -
觸發(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 行。
更多建議: