語(yǔ)句級(jí)觸發(fā)器
我們先看一個(gè)AFTER-INSERT-STATEMENT觸發(fā)器：
CREATE OR REPLACE TRIGGER temp_ais
AFTER INSERT ON TEMP
BEGIN
dbms_output.put_line(‘executing temp_ais‘);
END;
看一下下面語(yǔ)句的結(jié)果：
SQL> set feedback off
SQL> INSERT INTO temp VALUES (1); -- insert 1 row
executing temp_ais
SQL> INSERT INTO temp VALUES (1); -- insert 1 row
executing temp_ais
SQL> INSERT INTO temp SELECT * FROM temp; -- insert 2 rows
executing temp_ais
每個(gè)SQL插入語(yǔ)句將觸發(fā)一次，行級(jí)觸發(fā)器最后一條語(yǔ)句要觸發(fā)兩次。
一、事件順序
用Insert 語(yǔ)句級(jí)觸發(fā)器可以做：
·可以在表上執(zhí)行一個(gè)合計(jì)運(yùn)算，可以在insert前或后來(lái)計(jì)算。
·可以使用語(yǔ)句級(jí)觸發(fā)器去處理行級(jí)觸發(fā)器控制的數(shù)據(jù)。
·可以給事件發(fā)信號(hào)?？梢詢H僅是一個(gè)打印語(yǔ)句。也可以是一個(gè)email或使用DBMS_ALERT包向其他處理過(guò)程發(fā)送信號(hào)。
Tasks Performed
Stages -------------------
---------------------------------- ———> | Rehect the |
Fires once | BIS function | | Transaction |
per statement | Statement Level Trigger | -------------------
----------------------------------- ———> -------------------
| | Take Action |
| -------------------
insert |
3行記錄 | -------------------------- |
————> | | Row Trigger | | -------------------------------
————> | -------------------------- | |每一個(gè)行觸發(fā)器插入一|
————> | | | |行觸發(fā)一次，插入三行|
| ---------------------------------------- | |觸發(fā)三次，語(yǔ)句級(jí)觸發(fā)|
| | Oracle enforces constraints | | |器在行操作和行數(shù)據(jù)上|
| ---------------------------------------- | |不可見(jiàn) |
| | | | |
| -------------------------- | -------------------------------
| | Row Trigger | |
| -------------------------- |
|
| ----------------------
---------------------------------- ————> | Rehect the |
Fires once | AIS function | | Transaction |
per statement | Statement Level Trigger | ---------------------
---------------------------------- ————> --------------------
| Take Action |
--------------------
上圖顯示了語(yǔ)句級(jí)出發(fā)器的行為。同時(shí)也顯示了在Before statement觸發(fā)器和After statement觸發(fā)器間的行級(jí)觸發(fā)器的觸發(fā)情況。如果一個(gè)update SQL語(yǔ)句更新三行，那么行級(jí)觸發(fā)器觸發(fā)三次，語(yǔ)句級(jí)觸發(fā)器觸發(fā)一次。
二、insert 語(yǔ)句級(jí)觸發(fā)器定義語(yǔ)法
語(yǔ)法如下：
CREATE OR REPLACE TRIGGER trigger_name
[AFTER | BEFORE] INSERT ON table_name
DECLARE
Local declarations
BEGIN
Body written PL/SQL
END;
語(yǔ)句級(jí)和行級(jí)觸發(fā)器在語(yǔ)法上關(guān)鍵的不同在于：FOR EACH ROW字句。在行級(jí)觸發(fā)器中指定這個(gè)子句而語(yǔ)句級(jí)觸發(fā)器中不需要指定。
1）、WHEN(Boolean expression) 所有行觸發(fā)器可用
2）、OF column_name clause 僅對(duì)update觸發(fā)器可用
在語(yǔ)句級(jí)觸發(fā)器中：
·引用:NEW.COLUMN_NAME and :OLD.COLUMN_NAME是不正確的。
·不能使用When(boolean expression)子句中包含OLD.COLUMN_NAME和 NEW.COLUMN_NAME.
可以使用下面的語(yǔ)句：
CREATE OR REPLACE TRIGGER temp_biuds
BEFORE INSERT OR UPDATE OR DELETE ON TEMP
BEGIN
CASE
WHEN inserting THEN
PL/SQL code here
WHEN updating THEN
PL/SQL code here
WHEN deleting THEN
PL/SQL code here
END CASE;
END;
三、語(yǔ)句級(jí)組合
·使用錯(cuò)誤碼來(lái)更新Errors包
·包商業(yè)規(guī)則邏輯放到一個(gè)約束包中
·編寫(xiě)before或after語(yǔ)句級(jí)觸發(fā)器
1）、第一步是聲明錯(cuò)誤碼和錯(cuò)誤信息。Errors包更新包含了-20002和-2003兩個(gè)錯(cuò)誤碼
CREATE OR REPLACE PACKAGE errors IS
eng_dept_sal CONSTANT PLS_INTEGER := -20001;
app_error_02 CONSTANT PLS_INTEGER := -20002;
app_error_03 CONSTANT PLS_INTEGER := -20003;
eng_dept_sal_txt CONSTANT VARCHAR2(100) :=
‘The salary exceeds the ENGL maximum of $10,000.00‘;
app_error_02_txt CONSTANT VARCHAR2(100) :=
‘No additions if the budget exceeds $55,000.00‘;
app_error_03_txt CONSTANT VARCHAR2(100) :=
‘Budget cannot be over $60,000.00‘;
END errors;
2）、把商業(yè)邏輯封裝在約束包中。
CREATE OR REPLACE PACKAGE professors_cons IS
PROCEDURE constrain_budget
(limit NUMBER,err_code PLS_INTEGER,err_text
VARCHAR2);
END professors_cons;
CREATE OR REPLACE PACKAGE BODY professors_cons IS
PROCEDURE constrain_budget
(limit NUMBER,err_code PLS_INTEGER,err_text
VARCHAR2)
IS
budget_sum NUMBER;
BEGIN
SELECT SUM(salary) INTO budget_sum FROM
professors;
IF budget_sum > limit THEN
RAISE_APPLICATION_ERROR(err_code, err_text);
END IF;
END constrain_budget;
END professors_cons;
3）、定義before和after觸發(fā)器
CREATE OR REPLACE TRIGGER professors_bis
BEFORE INSERT OR UPDATE ON professors
BEGIN
professors_cons.constrain_budget
(55000, errors.budget_err_1,
errors.budget_err_1_txt);
END;
CREATE OR REPLACE TRIGGER professors_ais
AFTER INSERT OR UPDATE ON professors
BEGIN
professors_cons.constrain_budget
(60000, errors.budget_err_2,
errors.budget_err_2_txt);
END;
四、處理行獲得的數(shù)據(jù)
行級(jí)觸發(fā)器可以在全局臨時(shí)表中存儲(chǔ):OLD 和 :NEW 字段值。全局臨時(shí)表范圍僅是事務(wù)。通過(guò)復(fù)制:OLD 和 :NEW 值，商業(yè)規(guī)則的處理被延期到語(yǔ)句級(jí)觸發(fā)器上。有時(shí)是必須的，因?yàn)樯虡I(yè)規(guī)則是復(fù)雜的，需要從表中查詢，包括表被更新。
1)、首先需要一個(gè)全局臨時(shí)表，它在行級(jí)觸發(fā)器上用于存儲(chǔ)數(shù)據(jù)。
CREATE global temporary TABLE professors_g
(prof_name VARCHAR2(10),
specialty VARCHAR2(20),
hire_date DATE,
salary NUMBER(7,2),
tenure VARCHAR2(3),
department VARCHAR2(10)) ON COMMIT DELETE ROWS;
2)、為這張表編寫(xiě)存儲(chǔ)過(guò)程，放于包Professors_cons里.如下：
CREATE OR REPLACE PACKAGE professors_cons IS
PROCEDURE load_temp_table
(v_prof_name professors.prof_name%TYPE,
v_specialty professors.specialty%TYPE,
v_hire_date professors.hire_date%TYPE,
v_salary professors.salary%TYPE,
v_tenure professors.tenure%TYPE,
v_department professors.department%TYPE);
PROCEDURE dump_temp_table;
END professors_cons;
包體為：
CREATE OR REPLACE PACKAGE BODY professors_cons IS
PROCEDURE load_temp_table
(v_prof_name professors.prof_name%TYPE,
v_specialty professors.specialty%TYPE,
v_hire_date professors.hire_date%TYPE,
v_salary professors.salary%TYPE,
v_tenure professors.tenure%TYPE,
v_department professors.department%TYPE)
IS
BEGIN
INSERT INTO professors_g VALUES
(v_prof_name, v_specialty, v_hire_date,
v_salary, v_tenure, v_department);
END load_temp_table;
PROCEDURE dump_temp_table IS
BEGIN
FOR rec in (SELECT * FROM professors_g) LOOP
dbms_output.put_line(
rec.prof_name||‘ ‘||rec.specialty||‘ ‘||
rec.hire_date||‘ ‘||rec.salary||‘ ‘||
rec.tenure||‘ ‘||rec.department);
END LOOP;
END dump_temp_table;
END professors_cons;
3)、下面是一個(gè)after delete 行觸發(fā)器。當(dāng)它觸發(fā)時(shí)，通過(guò)Professors_cons插入臨時(shí)表一行數(shù)據(jù)。
CREATE OR REPLACE TRIGGER professors_adr
AFTER DELETE ON professors
FOR EACH ROW
BEGIN
professors_cons.load_temp_table
(:old.prof_name, :old.specialty, :old.hire_date,
:old.salary, :old.tenure, :old.department);
END;
下一個(gè)是after delete語(yǔ)句級(jí)觸發(fā)器，使用約束包打印刪除的行信息。
CREATE OR REPLACE TRIGGER professors_ads
AFTER DELETE ON professors
BEGIN
professors_cons.dump_temp_table;
END;
delete SQL語(yǔ)句后面是語(yǔ)句級(jí)觸發(fā)器的輸出：
SQL> DELETE FROM professors;
Blake Mathematics 08-aug-2003 02:06:27 10000 YES MATH
Milton Am Hist 09-aug-2003 02:06:27 10000 YES HIST
Wilson English 06-aug-2003 02:06:27 10000 YES ENGL
Jones Euro Hist 12-jul-2003 02:06:28 10000 YES HIST
Crump Ancient Hist 12-jul-2003 02:06:28 10000 YES HIST
5 rows deleted.
聯(lián)系客服
