Oracle觸發器在MIS 開發中的應用

　　１．引言
　　本人在做一個大型的MIS系統（前台用powerbuild工具後台用oracle數據庫）時常碰到一些有關數據冗長性批量刪除和動態信息方面的問題由此發現數據庫觸發器簡單易用現將有關開發和應用中的一些體會總結成文供同行參考
　　
　　２．觸發器的概念和類型
　　數據庫觸發器（database triggers）是響應插入更新或刪除等數據庫事件而執行的過程它定義了當一些數據庫相關事件發生時應采取的動作可用於管理復雜的完整性約束或監控對表的修改或通知其它程序表已發生修改它的類型有語句級觸發器以及行級觸發器前者可以在語句執行前或執行後被觸發後者在每個觸發語句影響的行觸發一次還有before和after觸發的命令在insertupdate和delete之前或之後執行引用新舊值進行處理如果需通過觸發器設定插入行中的某列值則為了訪問新(new)值需使用一個觸發器before insert使用after insert則不行Instead of 觸發器命令使用它告訴oracle應執行什麼操作以上四種大類合成種小類（略）各種觸發器的執行順序如下
　　
　　⑴　　　如果有最先執行語句級before觸發器
　　
　　⑵　　　每個insertdeleteupdate影響的行
　　
　　①　　如果有最先執行行級before
　　
　　②　　執行行的delete或update
　　
　　③　　如果有執行行級after觸發器
　　
　　⑶　　　如果有執行語句級after觸發器
　　
　　３．使用數據庫觸發器管理數據冗余性
　　為了數據分析和制作報表的需要用戶在數據模型中加入了冗余數據應使用觸發器以保證數據的完整性
　　
　　冗余數據可以用規定的 for each row選項的before update觸發器進行管理update 命令可放在觸發器中對所有需要更新的冗余數據進行更新如客戶表和訂單表訂單表包括客戶的訂單和客戶表的冗余信息客戶表(customer)的表結構:客戶號(cu_no)客戶名(cu_name)客戶地址(cu_address)訂單表(order)的表結構:訂單號(or_no)客戶號(or_no)客戶名（cu_name）客戶地址（cu_address）當客戶基表中的數據被更新時更新訂單中的冗余列語法如下
　　
　　create or replace trigger bj_customer
　　
　　before update on customer
　　
　　for each row
　　
　　begin
　　
　　　　update order set
　　
　　　　cu_no=:newcu_no
　　
　　　　cu_name=:newcu_name
　　
　　　　cu_address=:newcu_addess
　　
　　　　where cu_no=:oldcu_no
　　
　　end;
　　
　　４．用觸發器完成數據復制
　　如果需求非常有限可以用數據觸發器從一個數據庫中向另一個數據庫復制數據如果數據復制需求僅與數據的插入有關當一條記錄插入到一個數據庫中的某個基表中時用戶還希望把這條記錄插入到一個遠程數據庫中需用create database link語句創建一條到遠程數據庫的連接一旦創建了一條數據庫連接後就可以在基表上創建一個after insert觸發器以把每一條記錄插入到遠程數據庫中
　　
　　（）在腳本中創建數據庫連接（database link）bj_ysd_remote基表作為數據庫基表Bj_ysd_local代表本地數據庫上的源基表
　　
　　Creat database link remote(連接名)
　　
　　Connect to bj(帳戶) indentified by bj(密碼)
　　
　　Using :;
　　
　　（）復制記錄
　　
　　create or replace trigger trig_ysd（觸發器名）
　　
　　after insert on bj_ysd_local
　　
　　for each row
　　
　　begin
　　
　　insert into bj_ysd_remote@dblink remote
　　
　　value(:newx:newx……)/＊xx代表字段名＊／
　　
　　end;
　　
　　（）刪除記錄
　　
　　create or replace trigger trig_ysd_del
　　
　　after delete on bj_ysd_local
　　
　　for each row
　　
　　begin
　　
　　　　delete from bj_ysd_remote@dblink remote
　　
　　where x=:oldx
　　
　　end;
　　
　　５．用數據庫觸發器完成瀑布式刪除操作
　　在某些情況下當要刪除一條記錄時該記錄是與外鍵有關的另外一張基表上的記錄時這個刪除操作必須在模型中進行傳遞否則會出現大量的冗長數據仍以cumstomer 和order基表為例當從customer中刪除一個客戶時order基表中所有相關記錄也應當刪除
　　
　　Create or replace trigger trig_cust
　　
　　Before delete on　customer
　　
　　For each row
　　
　　Begin
　　
　　Delete from order
　　
　　Where　cu_no=oldcu_no
　　
　　End;
　　
　　６．用觸發器完成動態數據的操作
　　在涉及如何實現動態庫存的問題時可用觸發器解決倉庫有驗收出庫調撥報廢退料讓售等這些數據必須與以前的庫存相加減才能完成動態庫存操作本文僅以驗收單觸發器為例其它的結構雷同它們涉及到兩個基表bi­_ysd(驗收單)Bj_kcb(當前庫存表)前者的表結構（rq(日期)ysdh(驗收單號) bjbm(備件編碼)yssl(驗收數量)ysdj(驗收單價)）後者的表結構為（bjbm（備件編碼）dqkcl（當前庫存量）dqkcje（當前庫存金額））觸發器如下
　　
　　create or replace trigger trig_ysd
　　
　　after insert or update or delete on bj_ysd
　　
　　for each row
　　
　　declare rq varchar();rq varchar();
　　
　　/*限於篇幅ysslysslysdjysdjbjbmbjbmii聲明略*/
　　
　　if inserting or updating then
　　
　　rq:=:newrq;bjbm:=:newbjbm;yssl:=:newyssl;
　　
　　ysdj:=:newysdj;
　　
　　select count(*) into ii from bj_dqkcb
　　
　　where bjbm=bjbm;
　　
　　if　ii=　then
　　
　　insert　into　bj_dqkcb(bjbmdqkcldqkcje)
　　
　　value(bjbmysslysdj);
　　
　　 else
　　
　　update bj_dqkcb
　　
　　set dqkcl=dqkcl+yssl;
　　
　　dqkcje=dqkcje+yssl*ysdj;
　　
　　 end if
　　
　　end if
　　
　　if deleting or updating then
　　
　　rq:=:oldrq;bjbm:=:oldbjbm;yssl:=:oldyssl;
　　
　　ysdj:=:oldysdj;
　　
　　update bj_dqkcb
　　
　　set dqkcb=dqkclyssl;
　　
　　dqkcje=dqkcjeyssl*ysdj
　　
　　end if;
　　
　　end ;
　　
　　７．結束語
　　數據庫觸發器在數據庫開發ＭＩＳ開發上有很廣泛的應用但經驗表明使用過多的觸發器將降低整個數據庫的性能如果數據庫觸發器寫得不好它會很快破壞數據庫的性能因此在適當的時候使用恰當的觸發器顯得非常重要
From:http://tw.wingwit.com/Article/program/Oracle/201311/18819.html