setfeedbackoffSQL>INSERTINTOtempVALUES(1);--insert1rowexecutingtemp_aisSQL>INSE" />

亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

Oracle觸發器介紹

系統 1776 0

語句級觸發器
我們先看一個AFTER-INSERT-STATEMENT觸發器:
CREATE OR REPLACE TRIGGER temp_ais
AFTER INSERT ON TEMP
BEGIN
??? dbms_output.put_line('executing temp_ais');
END;
看一下下面語句的結果:
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

每個SQL插入語句將觸發一次,行級觸發器最后一條語句要觸發兩次。
一、事件順序
用Insert 語句級觸發器可以做:
??? ·可以在表上執行一個合計運算,可以在insert前或后來計算。
??? ·可以使用語句級觸發器去處理行級觸發器控制的數據。
??? ·可以給事件發信號。可以僅僅是一個打印語句。也可以是一個email或使用DBMS_ALERT包向其他處理過程發送信號。
?????????????????????????????????????????????????????????????????????????????????????????????????? Tasks Performed
???????????????????????????????????????? Stages?????????????????????????????????????????? -------------------
?????? ????????????????????? ---------------------------------- ———> |?? Rehect the???? |
Fires once???????? |?? BIS function??????????????????? |??????????????????? |?? Transaction??? |
per statement??? | Statement Level Trigger |??????????????????? -------------------
?????????????????????????????? ----------------------------------- ———>??? -------------------
???????????????????????????????????????? |?????????????????????????????????????????????????????? | Take Action???? |
???????????????????????????????????????? |??????????????????????????????????????????????????????? -------------------
?? insert??????????????????????????? |
?? 3行記錄??????? |??? --------------------------?????????????????? |????
???? ————> |??? |??? Row Trigger?????? |?????????????????? |??? -------------------------------
???? ————> |??? --------------------------????????????????? |??? |每一個行觸發器插入一|
???? ————> |??????????????? |????????????????????????????????????????? |??? |行觸發一次,插入三行|
?????????????????????? | ----------------------------------------?? |???? |觸發三次,語句級觸發|
?????????????????????? ?? | | Oracle enforces constraints | |??? |器在行操作和行數據上|
???????????????????????? | ---------------------------------------- |??? |不可見??????????????????????????? |
???????????????????? ??? |??????????????? |???????????????????????????????????????? |??? |??????????????????????????????????????? |
??????????????????????? |??? --------------------------??????????????????? |??? -------------------------------
???????????????????????? |??? |??? Row Trigger????????? |??????????????? |????
????????????????????????? |??? --------------------------?????????????????? |????????
?????????????????????????????????????? |
????????????????????????????????????? ?? |????????????????????????????????????????????????????????? ----------------------
??????????????????????????????? ---------------------------------- ————> |?? Rehect the???? |
Fires once????????? |?? AIS function???????????????????? |?????????????????????? |?? Transaction??? |
per statement??? | Statement Level Trigger |??????????????????????? ---------------------
???????????????????????????? ---------------------------------- ————>?? --------------------
????????????????????????????????????????????????????????????????????????????????????????????????? | Take Action |
????????????????????????????????????????????????????????????????????????????????????????????????? --------------------??

上圖顯示了語句級出發器的行為。同時也顯示了在Before statement觸發器和After statement觸發器間的行級觸發器的觸發情況。如果一個update SQL語句更新三行,那么行級觸發器觸發三次,語句級觸發器觸發一次。

二、insert 語句級觸發器定義語法
語法如下:
CREATE OR REPLACE TRIGGER trigger_name
[AFTER | BEFORE] INSERT ON table_name
DECLARE
??? Local declarations
BEGIN
??? Body written PL/SQL
END;

語句級和行級觸發器在語法上關鍵的不同在于:FOR EACH ROW字句。在行級觸發器中指定這個子句而語句級觸發器中不需要指定。

1)、WHEN(Boolean expression) 所有行觸發器可用
2)、OF column_name clause???? 僅對update觸發器可用
在語句級觸發器中:
·引用:NEW.COLUMN_NAME and :OLD.COLUMN_NAME是不正確的。
·不能使用When(boolean expression)子句中包含OLD.COLUMN_NAME和 NEW.COLUMN_NAME.

可以使用下面的語句:
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;
三、語句級組合

?? ·使用錯誤碼來更新Errors包
?? ·包商業規則邏輯放到一個約束包中
?? ·編寫before或after語句級觸發器
??
1)、第一步是聲明錯誤碼和錯誤信息。Errors包更新包含了-20002和-2003兩個錯誤碼
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)、把商業邏輯封裝在約束包中。
??? 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觸發器
??? 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;

四、處理行獲得的數據
行級觸發器可以在全局臨時表中存儲:OLD 和 :NEW 字段值。全局臨時表范圍僅是事務。通過復制:OLD 和 :NEW 值,商業規則的處理被延期到語句級觸發器上。有時是必須的,因為商業規則是復雜的,需要從表中查詢,包括表被更新。
1)、首先需要一個全局臨時表,它在行級觸發器上用于存儲數據。
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)、為這張表編寫存儲過程,放于包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)、下面是一個after delete 行觸發器。當它觸發時,通過Professors_cons插入臨時表一行數據。
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;

下一個是after delete語句級觸發器,使用約束包打印刪除的行信息。
CREATE OR REPLACE TRIGGER professors_ads
AFTER DELETE ON professors
BEGIN
??? professors_cons.dump_temp_table;
END;

delete SQL語句后面是語句級觸發器的輸出:

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.

Oracle觸發器介紹


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 欧美日韩不卡视频 | 亚洲国产成人久久精品动漫 | 午夜影视在线观看 | 久久久精品久久久久久 | 国产欧美亚洲精品 | 国产呦系列 欧美呦 日韩呦 | 久久久久综合中文字幕 | a网在线| 久久伊| 午夜国产精品理论片久久影院 | 美女超爽久久久久网站 | 色女孩综合| avav国产| 久久久国产精品va麻豆 | 国产99高清一区二区 | 91日本视频 | 天天综合干 | 成人免费视频视频在线不卡 | 亚洲 日本 欧美 中文幕 | 99热6这里只有精品 99热7 | 欧美日韩在线网站 | 2020国产精品视频免费 | 久久精品国产99久久久 | 男人的天堂视频在线 | 亚洲天码中文字幕第一页 | 亚洲国产成人久久综合一区77 | 欧美xxx精品| 亚洲欧美日韩国产精品第不页 | 欧洲天堂| 一级美国乱色毛片 | 国产香蕉在线观看 | 国产精品美女自在线观看免费 | 精品免费久久久久久久 | 欧美色插 | 国产高清一区二区三区四区 | 综合久久精品 | aaa一级黄色片 | 99热最新网址获取 | 免费黄色的视频 | 免费国产免费福利视频 | www.久久.com|