Oracle觸發(fā)器語(yǔ)法

1、Oracle觸發(fā)器的概念和類型        2008-04-16 14:23:34 編輯 刪除 摘要:本文介紹了oracle觸發(fā)器的概念和類型。 關(guān)鍵詞:觸發(fā)器、oracle。1.       引言 2.       觸發(fā)器的概念和類型 觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，它在插入，刪除或修改特定表中的數(shù)據(jù)時(shí)觸發(fā)執(zhí)行，它比數(shù)據(jù)庫(kù)本身標(biāo)準(zhǔn)的功能有更精細(xì)和更復(fù)雜的數(shù)據(jù)控制能力。數(shù)據(jù)庫(kù)觸發(fā)器有以下的作用：* 安全性?？梢曰跀?shù)據(jù)

2、庫(kù)的值使用戶具有操作數(shù)據(jù)庫(kù)的某種權(quán)利。# 可以基于時(shí)間限制用戶的操作，例如不允許下班后和節(jié)假日修改數(shù)據(jù)庫(kù)數(shù)據(jù)。# 可以基于數(shù)據(jù)庫(kù)中的數(shù)據(jù)限制用戶的操作，例如不允許股票的價(jià)格的升幅一次超過(guò)10%。* 審計(jì)?？梢愿櫽脩魧?duì)數(shù)據(jù)庫(kù)的操作。# 審計(jì)用戶操作數(shù)據(jù)庫(kù)的語(yǔ)句。# 把用戶對(duì)數(shù)據(jù)庫(kù)的更新寫入審計(jì)表。* 實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)完整性規(guī)則。# 實(shí)現(xiàn)非標(biāo)準(zhǔn)的數(shù)據(jù)完整性檢查和約束。觸發(fā)器可產(chǎn)生比規(guī)則更為復(fù)雜的限制。與規(guī)則不同，觸發(fā)器可以引用列或數(shù)據(jù)庫(kù)對(duì)象。例如，觸發(fā)器可回退任何企圖吃進(jìn)超過(guò)自己保證金的期貨。# 提供可變的缺省值。* 實(shí)現(xiàn)復(fù)雜的非標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)相關(guān)完整性規(guī)則。觸發(fā)器可以對(duì)數(shù)據(jù)庫(kù)中相關(guān)的表進(jìn)行連環(huán)更新

3、。例如，在auths表author_code列上的刪除觸發(fā)器可導(dǎo)致相應(yīng)刪除在其它表中的與之匹配的行。# 在修改或刪除時(shí)級(jí)聯(lián)修改或刪除其它表中的與之匹配的行。# 在修改或刪除時(shí)把其它表中的與之匹配的行設(shè)成NULL值。# 在修改或刪除時(shí)把其它表中的與之匹配的行級(jí)聯(lián)設(shè)成缺省值。# 觸發(fā)器能夠拒絕或回退那些破壞相關(guān)完整性的變化，取消試圖進(jìn)行數(shù)據(jù)更新的事務(wù)。當(dāng)插入一個(gè)與其主健不匹配的外部鍵時(shí)，這種觸發(fā)器會(huì)起作用。例如，可以在books.author_code列上生成一個(gè)插入觸發(fā)器，如果新值與auths.author_code列中的某值不匹配時(shí)，插入被回退。* 同步實(shí)時(shí)地復(fù)制表中的數(shù)據(jù)。* 自動(dòng)計(jì)算數(shù)據(jù)值

4、，如果數(shù)據(jù)的值達(dá)到了一定的要求，則進(jìn)行特定的處理。例如，如果公司的帳號(hào)上的資金低于5萬(wàn)元?jiǎng)t立即給財(cái)務(wù)人員發(fā)送警告數(shù)據(jù)。數(shù)據(jù)庫(kù)觸發(fā)器（data triggers）是響應(yīng)插入、更新或刪除等數(shù)據(jù)庫(kù)事件而執(zhí)行的過(guò)程。它定義了當(dāng)一些數(shù)據(jù)庫(kù)相關(guān)事件發(fā)生時(shí)應(yīng)采取的動(dòng)作?？捎糜诠芾韽?fù)雜的完整性約束，或監(jiān)控對(duì)表的修改，或通知其它程序，表已發(fā)生修改。它的類型有：語(yǔ)句級(jí)觸發(fā)器，以及行級(jí)觸發(fā)器，前者可以在語(yǔ)句執(zhí)行前或執(zhí)行后被觸發(fā)。后者在每個(gè)觸發(fā)語(yǔ)句影響的行觸發(fā)一次。還有before和after觸發(fā)的命令。在insert,update,和delete之前或之后執(zhí)行，引用新舊值進(jìn)行處理。如果需通過(guò)觸發(fā)器設(shè)定插入行中的某列

5、值，則為了訪問(wèn)“新(new)”值，需使用一個(gè)觸發(fā)器before insert，使用after insert則不行。Instead of 觸發(fā)器命令，使用它告訴oracle應(yīng)執(zhí)行什么操作。以上四種大類合成14種小類（略）。各種觸發(fā)器的執(zhí)行順序如下： 1 如果有，最先執(zhí)行語(yǔ)句級(jí)before觸發(fā)器 。 每個(gè)insert，delete,update影響的行； 如果有，最先執(zhí)行行級(jí)before； 執(zhí)行行的delete或update； 如果有,執(zhí)行行級(jí)after觸發(fā)器； 如果有,執(zhí)行語(yǔ)句級(jí)after觸發(fā)器。 3.       使用數(shù)據(jù)庫(kù)觸發(fā)器管理

6、數(shù)據(jù)冗余性 為了數(shù)據(jù)分析和制作報(bào)表的需要,用戶在數(shù)據(jù)模型中加入了冗余數(shù)據(jù)，應(yīng)使用觸發(fā)器,以保證數(shù)據(jù)的完整性。 冗余數(shù)據(jù)可以用規(guī)定的 for each row選項(xiàng)的before update觸發(fā)器進(jìn)行管理。update 命令可放在觸發(fā)器中對(duì)所有需要更新的冗余數(shù)據(jù)進(jìn)行更新,如客戶表和訂單表,訂單表包括客戶的訂單和客戶表的冗余信息,客戶表(customer)的表結(jié)構(gòu):客戶號(hào)(cu_no)、客戶名(cu_name)、客戶地址(cu_address)。訂單表(order)的表結(jié)構(gòu):訂單號(hào)(or_no),客戶號(hào)(or_no)，客戶名（cu_name），客戶地址（cu_address）,當(dāng)客戶基表中的數(shù)據(jù)被

7、更新時(shí)更新訂單中的冗余列。語(yǔ)法如下： create or replace trigger bj_customer before update on customer for each row begin update order set cu_no=:new.cu_no， cu_name=:new.cu_name， cu_address=:new.cu_addess， where cu_no=:old.cu_no； end; 4.       用觸發(fā)器完成數(shù)據(jù)復(fù)制 如果需求非常有限，可以用數(shù)據(jù)觸發(fā)器從一個(gè)數(shù)據(jù)庫(kù)中向另一個(gè)數(shù)據(jù)庫(kù)復(fù)制數(shù)據(jù)

8、，如果數(shù)據(jù)復(fù)制需求僅與數(shù)據(jù)的插入有關(guān)，當(dāng)一條記錄插入到一個(gè)數(shù)據(jù)庫(kù)中的某個(gè)基表中時(shí)，用戶還希望把這條記錄插入到一個(gè)遠(yuǎn)程數(shù)據(jù)庫(kù)中，需用create data link語(yǔ)句創(chuàng)建一條到遠(yuǎn)程數(shù)據(jù)庫(kù)的連接，一旦創(chuàng)建了一條數(shù)據(jù)庫(kù)連接后，就可以在基表上創(chuàng)建一個(gè)after insert觸發(fā)器，以把每一條記錄插入到遠(yuǎn)程數(shù)據(jù)庫(kù)中。 （1）在腳本中創(chuàng)建數(shù)據(jù)庫(kù)連接（data link）bj_ysd_remote基表作為數(shù)據(jù)庫(kù)基表，Bj_ysd_local代表本地?cái)?shù)據(jù)庫(kù)上的源基表。 Creat data link remote(連接名) Connect to bj(帳戶) indentified by bj(密碼) Us

9、ing :2; （2）復(fù)制記錄 create or replace trigger trig_ysd（觸發(fā)器名） after insert on bj_ysd_local for each row begin insert into bj_ysd_remotedblink remote value(:new.x1,:new.x2,)/x1.x2代表字段名 end; （3）刪除記錄 create or replace trigger trig_ysd_del after delete on bj_ysd_local for each row begin delete from bj_ysd_re

10、motedblink remote where x1=:old.x1 end;5.       用數(shù)據(jù)庫(kù)觸發(fā)器完成瀑布式刪除操作 在某些情況下，當(dāng)要?jiǎng)h除一條記錄時(shí)，該記錄是與外鍵有關(guān)的另外一張基表上的記錄時(shí)，這個(gè)刪除操作必須在模型中進(jìn)行傳遞，否則會(huì)出現(xiàn)大量的冗長(zhǎng)數(shù)據(jù)，仍以cumstomer 和order基表為例，當(dāng)從customer中刪除一個(gè)客戶時(shí)，order基表中所有相關(guān)記錄也應(yīng)當(dāng)刪除。 Create or replace trigger trig_cust Before delete oncustomer For each row

11、 Begin Delete from order Wherecu_no=old.cu_no； End; 6.       用觸發(fā)器完成動(dòng)態(tài)數(shù)據(jù)的操作 在涉及如何實(shí)現(xiàn)動(dòng)態(tài)庫(kù)存的問(wèn)題時(shí)，可用觸發(fā)器解決。倉(cāng)庫(kù)有驗(yàn)收、出庫(kù)、調(diào)撥、報(bào)廢、退料、讓售等這些數(shù)據(jù)必須與以前的庫(kù)存相加減，才能完成動(dòng)態(tài)庫(kù)存操作。本文僅以驗(yàn)收單觸發(fā)器為例，其它的結(jié)構(gòu)雷同。它們涉及到兩個(gè)基表：bi_ysd(驗(yàn)收單)，Bj_kcb(當(dāng)前庫(kù)存表)，前者的表結(jié)構(gòu)（rq(日期)，ysdh(驗(yàn)收單號(hào))， bjbm(備件編碼)，yssl(驗(yàn)收數(shù)量)，ysdj(驗(yàn)收單價(jià))），后者的表結(jié)構(gòu)

12、為（bjbm（備件編碼）,dqkcl（當(dāng)前庫(kù)存量）,dqkcje（當(dāng)前庫(kù)存金額）觸發(fā)器如下： create or replace trigger trig_ysd after insert or update or delete on bj_ysd for each row declare rq1 varchar2(8);rq2 varchar2(8); /*限于篇幅，yssl1,yssl2,ysdj1,ysdj2,bjbm1,bjbm2,ii聲明略*/ if inserting or updating then rq1:=:new.rq;bjbm1:=:new.bjbm;yssl1:=:ne

13、w.yssl; ysdj1:=:new.ysdj; select count(*) into ii from bj_dqkcb where bjbm=bjbm1; ifii=0then insertintobj_dqkcb(bjbm,dqkcl,dqkcje) value(bjbm1,yssl1,ysdj1); else update bj_dqkcb set dqkcl=dqkcl+yssl1; dqkcje=dqkcje+yssl1*ysdj1; end if end if if deleting or updating then rq2:=:old.rq;bjbm2:=:old.bjbm

14、;yssl2:=:old.yssl; ysdj2:=:old.ysdj; update bj_dqkcb set dqkcb=dqkcl-yssl2; dqkcje=dqkcje-yssl2*ysdj2 end if; end ; 7.       結(jié)束語(yǔ) 數(shù)據(jù)庫(kù)觸發(fā)器在數(shù)據(jù)庫(kù)開(kāi)發(fā)上有很廣泛的應(yīng)用，但經(jīng)驗(yàn)表明，使用過(guò)多的觸發(fā)器將降低整個(gè)數(shù)據(jù)庫(kù)的性能。如果數(shù)據(jù)庫(kù)觸發(fā)器寫得不好，它會(huì)很快破壞數(shù)據(jù)庫(kù)的性能，因此，在適當(dāng)?shù)臅r(shí)候使用恰當(dāng)?shù)挠|發(fā)器顯得非常重要。-ORACLE與SY數(shù)據(jù)庫(kù)的觸發(fā)器有一定的區(qū)別，下面將分別講述這兩種數(shù)據(jù)庫(kù)觸發(fā)器的作用和

15、寫法。1.       ORACLE 觸發(fā)器ORACLE產(chǎn)生數(shù)據(jù)庫(kù)觸發(fā)器的語(yǔ)法為：create or replace trigger 觸發(fā)器名 觸發(fā)時(shí)間 觸發(fā)事件on 表名for each rowpl/sql 語(yǔ)句其中：觸發(fā)器名：觸發(fā)器對(duì)象的名稱。由于觸發(fā)器是數(shù)據(jù)庫(kù)自動(dòng)執(zhí)行的，因此該名稱只是一個(gè)名稱，沒(méi)有實(shí)質(zhì)的用途。觸發(fā)時(shí)間：指明觸發(fā)器何時(shí)執(zhí)行，該值可?。篵efore-表示在數(shù)據(jù)庫(kù)動(dòng)作之前觸發(fā)器執(zhí)行；after-表示在數(shù)據(jù)庫(kù)動(dòng)作之后出發(fā)器執(zhí)行。觸發(fā)事件：指明哪些數(shù)據(jù)庫(kù)動(dòng)作會(huì)觸發(fā)此觸發(fā)器：insert：數(shù)據(jù)庫(kù)插入會(huì)觸發(fā)此觸發(fā)器；upda

16、te：數(shù)據(jù)庫(kù)修改會(huì)觸發(fā)此觸發(fā)器；delete：數(shù)據(jù)庫(kù)刪除會(huì)觸發(fā)此觸發(fā)器。表名：數(shù)據(jù)庫(kù)觸發(fā)器所在的表。for each row：對(duì)表的每一行觸發(fā)器執(zhí)行一次。如果沒(méi)有這一選項(xiàng)，則只對(duì)整個(gè)表執(zhí)行一次。舉例：下面的觸發(fā)器在更新表auths之前觸發(fā)，目的是不允許在周末修改表：create trigger auth_securebefore insert or update or delete /對(duì)整表更新前觸發(fā)on authsbeginif(to_char(sysdate,'DY')='SUN'RAISE_APPLICATION_ERROR(-20600,'不能在

17、周末修改表auths');end if;end2.       SY數(shù)據(jù)庫(kù)觸發(fā)器SY數(shù)據(jù)庫(kù)觸發(fā)器的作用與ORACLE非常類似，僅有較小的差異。SY產(chǎn)生觸發(fā)器的語(yǔ)法為：CREATE TRIGGER 觸發(fā)器名ON 表名FOR INSERT，UPDATE，DELETEASSQL_statement |FOR INSERT，UPDATEASIF UPDATE(column_name) AND|OR UPDATE(column_name).SQL_statements上面FOR子句用來(lái)指定在觸發(fā)器上的哪些數(shù)據(jù)更新命令可激活該觸發(fā)器。IF

18、 UPDATE子句檢查對(duì)指定列的操作類型，在IF UPDATE子句中可指定多個(gè)列。與ORACLE不同，對(duì)于每條SQL語(yǔ)句，觸發(fā)器只執(zhí)行一次。觸發(fā)器在數(shù)據(jù)更新語(yǔ)句完成以后立即執(zhí)行。觸發(fā)器和啟動(dòng)它的語(yǔ)句被當(dāng)作一個(gè)事務(wù)處理，事務(wù)可以在觸發(fā)器中回退。下面舉例說(shuō)明SY觸發(fā)器的寫法。create trigger forinsert_bookson booksfor insertasif(select count(*) from auths,insertedwhere auths.author_code=insert.author_code)!=rowcountbeginrollback transacti

19、onprint "books 表中 author_code 列的值在auths 表中不存在。"endORACLE觸發(fā)器語(yǔ)法2008年03月13日 星期四 10:35觸發(fā)器有兩種after和before，區(qū)別在于每次提交事務(wù)觸發(fā)一次和每一行數(shù)據(jù)的更新都觸發(fā)一次，使用時(shí)候要注意，以免達(dá)不到目的 ORACLE產(chǎn)生數(shù)據(jù)庫(kù)觸發(fā)器的語(yǔ)法為：create or replace trigger 觸發(fā)器名 觸發(fā)時(shí)間 觸發(fā)事件on 表名for each rowpl/sql 語(yǔ)句其中：觸發(fā)器名：觸發(fā)器對(duì)象的名稱。由于觸發(fā)器是數(shù)據(jù)庫(kù)自動(dòng)執(zhí)行的，因此該名稱只是一個(gè)名稱，沒(méi)有實(shí)質(zhì)的用途。觸發(fā)時(shí)間：指明

20、觸發(fā)器何時(shí)執(zhí)行，該值可?。篵efore-表示在數(shù)據(jù)庫(kù)動(dòng)作之前觸發(fā)器執(zhí)行；after-表示在數(shù)據(jù)庫(kù)動(dòng)作之后出發(fā)器執(zhí)行。觸發(fā)事件：指明哪些數(shù)據(jù)庫(kù)動(dòng)作會(huì)觸發(fā)此觸發(fā)器：insert：數(shù)據(jù)庫(kù)插入會(huì)觸發(fā)此觸發(fā)器；update：數(shù)據(jù)庫(kù)修改會(huì)觸發(fā)此觸發(fā)器；delete：數(shù)據(jù)庫(kù)刪除會(huì)觸發(fā)此觸發(fā)器。表 名：數(shù)據(jù)庫(kù)觸發(fā)器所在的表。for each row：對(duì)表的每一行觸發(fā)器執(zhí)行一次。如果沒(méi)有這一選項(xiàng)，則只對(duì)整個(gè)表執(zhí)行一次。舉例：下面的觸發(fā)器在更新表auths之前觸發(fā)，目的是不允許在周末修改表：create trigger auth_securebefore insert or update or delete /對(duì)整表更新前觸發(fā)on authsbeginif(to_char(sysdate,'DY')='SUN'RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表auths');end if;end例子：CREATE OR REPLACE TRIGGER CRM.T_SUB_USERINFO_AUR_NAMEAFTER UPDATE OF STAFF_NAMEON CRM.T_SUB_USERINFOREFERENCING OLD AS OLD NEW AS NEWFOR EACH