一般資料庫系統的資料登入，往往不是單純的新增更改刪除，而且會需要與其他資料表發生相關性的異動關係，如輸入出貨單必須對相對的產品庫存扣除，進貨單須對產品庫存增值等等，就是最常見的資料過帳實例。過帳的程式寫法常用的有三種方法，說明如下。

u  第一種方式：

使用後端資料庫的Trigger方式來設計，因為Trigger可以讓你在Table Insert/Delete/Update時搭配Inserted與Deleted的新舊資料來自行下達SQL語句達到資料過帳的目的，這種寫法優點是很方便，只要負責下基本Insert/Delete/Update指令給資料庫就可已完成過帳，執行效能高。缺點是維護除錯不易，並當User數量大時，會讓資料庫的負荷不來，另外就是程式與資料庫捆綁的太緊，系統不易與資料庫獨立分開。

u  另一種方式：

是寫在A/P Server上，利用EEP的UpdateComp有BeforeInsert/AfterInsert/BeforeDelete/AfterDelete/BeforeModify/AfterModify等事件可以用來同步下達SQL命令，與Trigger的方式很像，不同的是將程式寫在A/P Server上，除錯與維護都容易一些，效能也可以分散，缺點還是必需由程式人員來開發，並且開發方式與語法也不標準。

u  EEP內建一個Infotransaction元件，可以直接與UpdateComp來配合，可針對大多數的過帳與異動關係進行標準化與規格化的管理，因此，設計者只要透過視覺化方式定義，就可以達到多數過帳的目的，不必再另外設計。

 

下文中我們將舉例說明InfoTransaction的強大功能。

q  設計一個簡單的主檔過帳

我們將舉上一個採購訂單的例子，來說明過帳的使用。也就是要將採購單Purchase的採購金額TotalAmount加總到供應商Supplier的應付帳總額APAmount，並將Purchase採購日期回寫Suppliers的最後採購日LastPurchaseDate。

 

因為Suppliers並沒有這兩個欄位，因此我們必須用SQL Query Analyzer來在SQL命令中下達下列語句：

Alter table suppliers

add LastPurchaseDate datetime null,APAmount money null

 

Step1＞開啟sPurchases項目，我們貼入一個InfoTransaction元件，命名為tsMaster。先設定UpdateComp屬性，選擇要做過帳的資料，這裏選擇【ucPurchases】。代表當ucPurchases有發生Insert/Delete/Update時，會配合InfoTransaction一起過帳。

Step2＞按Transactions屬性右邊的【…】按鈕，出現Transaction Collection Editor視窗，代表要開始設定過帳的步驟。

Œ   如圖，按左下的【Add】按鈕，新增一個Transaction步驟（Step），系統過帳時是會依次按步驟來過帳，一個步驟通常代表對一個Table資料表的過帳關係，但也可以多個步驟對應同一個Table。

  再來，我們先設定『TransTableName』屬性，即要過帳的目的資料表（對方的資料表），我們用下拉選擇的方式選【Suppliers】。

Ž  接著設定『TransMode』屬性，此用來處理過帳之前是否先瞭解對方是否有相對資料。這裏設為【AutoAppend】。代表Suppliers不存在時，會自動新增一筆相對的資料到Suppliers中。上面的模式中，只有AlwaysAppend在過帳前不會去Select對方Table的相對資料是否存在，其餘都會去Select，而且也是必要的。

 

TransMode共有AutoAppend/Exception/AlwaysAppend/Ignore四種模式。分別代表：

F   對方不存在時要AutoAppend自動新增；

F   Exception發出異常錯誤並終止過帳；

F   AlwaysAppend代表無論是Insert/Update/Delete都會讓對方資料表新增；

F   Ignore忽略並繼續過帳。

 

 

 

Step2＞將『WhenInsert』、『WhenUpdat』、『WhenDelete』三個屬性設為【True】。

 

過帳時機的設定分別為When Insert/When Update/When Delete，分別代表此過帳可配合的時機為Insert或Update或Delete時發生作用。一般預設三個項目都是True，代表UpdateComp不管Insert/Delete/Update都會去執行InfoTransaction的過帳。

 

與對方Table的過帳關係共有兩種設定。一種是鍵值關係（TransKeyFields），代表資料表與資料間表的Where關係，另一種就是過帳關係（TransFields），是指主表欄位要與對方資料表的欄位處理關係。因此我們先設定TransKeyFields屬性。

 

Step2＞點擊TransKeyFields屬性右邊的【…】按鈕，出現TransKeyField Collection Editor視窗。按左下的【Add】按鈕，增加一個TransKeyField，下拉選擇 『SrcField』 屬性為【SupplierID】，『DesField』屬性也選【SupplierID】。（兩個Table的欄位名稱剛好相同）。

此代表要以Purchase資料表的SupplierID去找Suppliers的SupplierID。在過帳時此TransKeyFields就是用來組合Where的語法，因此可以設定多組KeyFields，而且必須一對一對應。設定完畢按下【OK】。

接著設定TransFields屬性來定義主表與過帳目的表的過帳欄位關係。

Step3＞點擊TransFields屬性右邊的【…】按鈕，出現TransField Collection Editor視窗，點擊左下的【Add】按鈕，增加一個TransField，下拉選擇『DesField』為【ApAmount】，『SrcField』選擇為【TotalAmount】，『UpdateMode』選擇為【inc】。

 

此代表要以Purchase的TotalAmount欄位值去累加到Suppliers的ApAmount欄位中。

UpdateMode有Inc/Dec/WriteBack/Replace/Disable等5種方式。除了Inc代表累加之外；Dec代表累減；Replace代表更換（覆蓋對方的欄位）；WriteBack則是與Replace相反，反而要將對方欄位內容回寫到主表的欄位上，一般用來記錄對方動態的欄位值或AlwaysAppend所產生的單據號碼的回寫。

 

Step4＞於TransFields中繼續新增第二個過帳欄位，這個我們將『DesField』選擇為【LastPurchaseDate】，『SrcField』選擇為【PurchaseDate】，『UpdateMode』為【Replace】。此代表要將Purchase的PurchaseDate記錄到Suppliers的LastPurchaseDate中。

 

Step4＞請編譯sPurchases項目，過帳功能基本上已完成。

 

為了方便檢驗過帳的實現，我們將bPurchases打開，並在JQDefault中增加對PurchaseDate的預設值處理。

Step5＞在先前的JQDefault的Columns屬性按下【…】設定增加一個欄位。『FieldName』選【PurchaseDate】，『DefaultValue』設定為一個系統變數【_today】。

 

 

Step6＞執行結果，此時我們可以再打開Purchase，並新增一筆資料。如圖，在「廠商編號」與「員工編號」中各輸入1，訂單日期（應該代表採購日期）會自動帶入今天的日期，在「金額」上輸入5000，最後按下「存檔」。

此時也可以利用SQL Profiler看到其過帳的SQL語句。

首先，可以看到整個存檔與過帳被一組BEGIN TRANSACTION與COMMIT TRANSACTION包起來，也就是過帳會與主檔明細檔的異動在同一個交易處理當中，一同成功或一同退回（這是因為ucPurchases.AutoTranas=True）。

再來，過帳的部份因為TransMode=AutoAppend，所以會有一個取Select Suppliers的語句，再下達一個Update Suppliers的語句，並對APAmount進行增值與LastPurchaseDate進行設值的動作，如果是Update與Delete時，其SQL語句會自動對應。

 

 

q   設計明細檔的過帳

再來就是要處理採購單明細檔Purchase Detail的過帳處理，讓Purchase Detail的Quantity累加到Products的UnitsInStock的欄位上，原本此欄位應該是進貨單時才累加到UnitsInStock，因為Products中並沒有UnitsOnPurchase的欄位，我們就以UnitsInStock做為實驗對象，其過帳理論是一致的，另外我們也在Products中另外增加一個LastPurchasePrice代表最後採購單價，讓Products能自動記錄下來。

因為Products並沒有這LastPurchasePrice欄位，所以我們用SQL Query Analyzer來在SQL命令中下達下列語句：

Alter table products

add LastPurchasePrice money null

 

Step1＞Detail的過帳與Master的設定基本相同。打開sPurchases項目，增加一個infoTransaction元件，命名為【tsDetail】。先設定『UpdateComp』屬性，這裏選擇【ucPurchaseDetails】。

 

Step2＞連點兩下（double click）tsDetail就會出現如下的InfoTransaction Component Editor設定介面。按下【Add】按鈕，在新增的TransTableName1的方格中連點兩下（double click）。

Step3＞在出現的設定介面中，下拉『TransTableName』屬性為【Products】。『TransMode』屬性同樣設為【AutoAppend】（代表如果對方不在會自動新增相對的資料）。

 

Step4＞按下TransKeyFields屬性右邊的小按鈕【…】，出現新的設定視窗。開始對此Transaction來定義彼此的鍵值關係。按【Add】新增一個TransKeyField。將『DesField』屬性下拉選擇為【ProductID】，『SrcField』屬性為【ProductID】（在此明細檔與Products的欄位名稱剛好一樣），按下【OK】。

 

Step5＞接著設定與Products關連的過帳欄位，按下TransFields屬性右邊的小按鈕【…】，出現TransField Collection Editor視窗。按【Add】新增一個TransField，將『DesField』屬性下拉選擇為【UnitsInStock】，『SrcField』屬性則選擇為【Quantity】，『UpdateMode』屬性則為【Inc】。按下【OK】。

 

Step6＞再新增一個過帳欄位，按下【Add】按鈕，將『DesField』屬性下拉選擇為【LastPurchasePrice】，『SrcField』屬性則選擇為【UnitPrice】， 『UpdateMode』屬性則為【Replace】。按下【OK】。

 

Step7＞完成之後的InfoTransaction Component Editor設計介面如下圖。

 

按下連接線的起點（圓點）可以設定TransKeyfield；按下終點（箭頭），可以設定TransField。

 

Step8＞同樣打開Purchase這個視窗，這次要測試的是更改的動作，選擇一筆有Detail的資料，在明細中將1號產品的單價多加了5元，如圖把20元改成25元，並在2號產品的數量上加10，如將數量40改成50，最後按下Master的「存檔」即可。

 

最後，我們用SQL Profiler來監看此過帳的SQL語句。如圖所示。

可以看到交易時有兩筆Purchase Detail都進行了更改的動作，也各進行了過帳的處理，1號產品的UnitsInStock沒有改變，改變的是LastPurchasePrice成為25；2號產品的UnitsInStock增加的10，LastPurchasePrice則一樣為20。

q   AlwaysAppend的應用實例

在TransMode（過帳模式）有一種AlwaysAppend用來特殊處理不管主檔是Insert/Delete/Update時，都是以Insert來記錄到對方的目的資料表（Update時會有新舊值各Insert一筆到目的資料表）。此目的就是讓交易的資料表可以用OnLine的方式集中到幾個資料表上統一處理，來讓系統的計算與統計功能變得更容易處理。在這裏我們另外開立一個Detail_Log資料表，來將所有庫存的交易都存到此統一處理。如進貨/出貨/入庫/出庫/借出/借入/調撥出/調撥入等所有庫存交易作業的明細資料表都會統一Log到此Detail_Log表中。

 

Step1＞新建一個Table，命名為Detail_Log，資料結構與Order Details幾乎完全一樣，只是新增一個Log_ID的Indentity欄位，作為主鍵，這樣的資料結構可以記錄Detail的每個欄位的值。當然，也可以只選擇性的按需要設計Log的欄位。

CREATE TABLE [Detail_Log] (

[Log_ID] [int] IDENTITY (1, 1) NOT NULL ,

[PurchaseID] [nchar] (10) NULL ,

[ProductID] [int] NULL ,

[Seq] [nchar] (3） NULL ,

[UnitPrice] [money] NULL ,

[Quantity] [smallint] NULL,

[Discount] [real] NULL,

CONSTRAINT [PK_Detail_Log] PRIMARY KEY CLUSTERED

(

    [Log_ID]

)  ON [PRIMARY]

) ON [PRIMARY]

GO

 

Step2＞打開sPurchases項目，一樣在之前的tsDetail中新增一個Transaction，設定『TransTableName』為【Detail_Log】， 『TransMode』為【AlwaysAppend】。

 

Step3＞接著設定TransKeyFields為【PurchaseID】對應到【PurchaseID】，以及【ProductID】對應到【ProductID】。

 

一般AlwaysAppend模式下設定在TransKeyFields與TransFields是近似的，但不能重複設定，也就是設定在TransKeyFields中又設定在TransFields，最大的差別是TransKeyFields只有Replace Mode，但TrnasFields則有inc/dec的模式。

 

Step3＞設定TransFields，表示哪些要記錄的Log欄位。這裏新增Seq，再新增UnitPrice，再新增Quantity，再新增Discount。

 

 

因為過帳的方式是AlwaysAppend，所以UpdateMode都設為Replace，只有Quantity是設為inc。在原始檔更改時，會Insert兩筆到Detail_Log資料表中，一筆是更改前的舊值，一筆是更改後的新值，因此在UpdateMode為inc時，第一筆Insert的舊值會自動為負值（用來扣除先前的交易），第二筆Insert的新值會自動為正值（再加上去）。如果Dec則正負後剛好與inc相反。

 

Step4＞編譯sPurchases，執行查看結果。

我們試著在某一筆Master所對應的Detail中，去新增一筆Detail。如輸出「產品號碼」為4，「單價」為10，「數量」為40；並在「產品號碼」為3的那筆進行更改，將「數量」原本為10改成30，最後按下Master的「存檔」。

 

最後，我們一樣用Profiler來查看後端對Detail_Log所執行的SQL語法來瞭解AlwaysAppend的作用。

我們可以看會有三筆Insert到Detail_Log資料表中，第一二筆為Update所產生的，Quantity從10改成30時，可以看到Quantity為inc模式，產生一筆 -10的數量，與另一筆 +30的數量；第三筆為Insert的資料，相對也產生另一比「產品編號」為4的Detail_Log資料；如果有Delete的話，Detail_Log也會Insert，只是Quantity會自動為負值。