1、Oracle觸發器的概念和類型        2008-04-16 14:23:34 編輯 刪除 摘要:本文介紹了oracle觸發器的概念和類型。 關鍵詞:觸發器、oracle。1.       引言 2.       觸發器的概念和類型 觸發器是一種特殊的存儲過程，它在插入，刪除或修改特定表中的數據時觸發執行，它比數據庫本身標準的功能有更精細和更復雜的數據控制能力。數據庫觸發器有以下的作用：* 安全性。可以基于數據

2、庫的值使用戶具有操作數據庫的某種權利。# 可以基于時間限制用戶的操作，例如不允許下班后和節假日修改數據庫數據。# 可以基于數據庫中的數據限制用戶的操作，例如不允許股票的價格的升幅一次超過10%。* 審計。可以跟蹤用戶對數據庫的操作。# 審計用戶操作數據庫的語句。# 把用戶對數據庫的更新寫入審計表。* 實現復雜的數據完整性規則。# 實現非標準的數據完整性檢查和約束。觸發器可產生比規則更為復雜的限制。與規則不同，觸發器可以引用列或數據庫對象。例如，觸發器可回退任何企圖吃進超過自己保證金的期貨。# 提供可變的缺省值。* 實現復雜的非標準的數據庫相關完整性規則。觸發器可以對數據庫中相關的表進行連環更新

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

4、，如果數據的值達到了一定的要求，則進行特定的處理。例如，如果公司的帳號上的資金低于5萬元則立即給財務人員發送警告數據。數據庫觸發器（data triggers）是響應插入、更新或刪除等數據庫事件而執行的過程。它定義了當一些數據庫相關事件發生時應采取的動作。可用于管理復雜的完整性約束，或監控對表的修改，或通知其它程序，表已發生修改。它的類型有：語句級觸發器，以及行級觸發器，前者可以在語句執行前或執行后被觸發。后者在每個觸發語句影響的行觸發一次。還有before和after觸發的命令。在insert,update,和delete之前或之后執行，引用新舊值進行處理。如果需通過觸發器設定插入行中的某列

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

6、數據冗余性 為了數據分析和制作報表的需要,用戶在數據模型中加入了冗余數據，應使用觸發器,以保證數據的完整性。 冗余數據可以用規定的 for each row選項的before update觸發器進行管理。update 命令可放在觸發器中對所有需要更新的冗余數據進行更新,如客戶表和訂單表,訂單表包括客戶的訂單和客戶表的冗余信息,客戶表(customer)的表結構:客戶號(cu_no)、客戶名(cu_name)、客戶地址(cu_address)。訂單表(order)的表結構:訂單號(or_no),客戶號(or_no)，客戶名（cu_name），客戶地址（cu_address）,當客戶基表中的數據被

7、更新時更新訂單中的冗余列。語法如下： 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.       用觸發器完成數據復制 如果需求非常有限，可以用數據觸發器從一個數據庫中向另一個數據庫復制數據

8、，如果數據復制需求僅與數據的插入有關，當一條記錄插入到一個數據庫中的某個基表中時，用戶還希望把這條記錄插入到一個遠程數據庫中，需用create data link語句創建一條到遠程數據庫的連接，一旦創建了一條數據庫連接后，就可以在基表上創建一個after insert觸發器，以把每一條記錄插入到遠程數據庫中。 （1）在腳本中創建數據庫連接（data link）bj_ysd_remote基表作為數據庫基表，Bj_ysd_local代表本地數據庫上的源基表。 Creat data link remote(連接名) Connect to bj(帳戶) indentified by bj(密碼) Us

9、ing :2; （2）復制記錄 create or replace trigger trig_ysd（觸發器名） 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.       用數據庫觸發器完成瀑布式刪除操作 在某些情況下，當要刪除一條記錄時，該記錄是與外鍵有關的另外一張基表上的記錄時，這個刪除操作必須在模型中進行傳遞，否則會出現大量的冗長數據，仍以cumstomer 和order基表為例，當從customer中刪除一個客戶時，order基表中所有相關記錄也應當刪除。 Create or replace trigger trig_cust Before delete oncustomer For each row

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

12、為（bjbm（備件編碼）,dqkcl（當前庫存量）,dqkcje（當前庫存金額）觸發器如下： 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.       結束語 數據庫觸發器在數據庫開發上有很廣泛的應用，但經驗表明，使用過多的觸發器將降低整個數據庫的性能。如果數據庫觸發器寫得不好，它會很快破壞數據庫的性能，因此，在適當的時候使用恰當的觸發器顯得非常重要。-ORACLE與SY數據庫的觸發器有一定的區別，下面將分別講述這兩種數據庫觸發器的作用和

15、寫法。1.       ORACLE 觸發器ORACLE產生數據庫觸發器的語法為：create or replace trigger 觸發器名 觸發時間 觸發事件on 表名for each rowpl/sql 語句其中：觸發器名：觸發器對象的名稱。由于觸發器是數據庫自動執行的，因此該名稱只是一個名稱，沒有實質的用途。觸發時間：指明觸發器何時執行，該值可取：before-表示在數據庫動作之前觸發器執行；after-表示在數據庫動作之后出發器執行。觸發事件：指明哪些數據庫動作會觸發此觸發器：insert：數據庫插入會觸發此觸發器；upda

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

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

18、 UPDATE子句檢查對指定列的操作類型，在IF UPDATE子句中可指定多個列。與ORACLE不同，對于每條SQL語句，觸發器只執行一次。觸發器在數據更新語句完成以后立即執行。觸發器和啟動它的語句被當作一個事務處理，事務可以在觸發器中回退。下面舉例說明SY觸發器的寫法。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觸發器語法2008年03月13日 星期四 10:35觸發器有兩種after和before，區別在于每次提交事務觸發一次和每一行數據的更新都觸發一次，使用時候要注意，以免達不到目的 ORACLE產生數據庫觸發器的語法為：create or replace trigger 觸發器名 觸發時間 觸發事件on 表名for each rowpl/sql 語句其中：觸發器名：觸發器對象的名稱。由于觸發器是數據庫自動執行的，因此該名稱只是一個名稱，沒有實質的用途。觸發時間：指明

20、觸發器何時執行，該值可取：before-表示在數據庫動作之前觸發器執行；after-表示在數據庫動作之后出發器執行。觸發事件：指明哪些數據庫動作會觸發此觸發器：insert：數據庫插入會觸發此觸發器；update：數據庫修改會觸發此觸發器；delete：數據庫刪除會觸發此觸發器。表 名：數據庫觸發器所在的表。for each row：對表的每一行觸發器執行一次。如果沒有這一選項，則只對整個表執行一次。舉例：下面的觸發器在更新表auths之前觸發，目的是不允許在周末修改表：create trigger auth_securebefore insert or update or delete /對整表更新前觸發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