觸發(fā)器實現(xiàn)數(shù)據(jù)庫復(fù)雜業(yè)務(wù)邏輯探討

羅穎，屈俊峰

（湖北文理學(xué)院數(shù)學(xué)與計算機科學(xué)學(xué)院，襄陽 441053）

0 引言

MS SQL Server數(shù)據(jù)庫管理系統(tǒng)提供了一套完整性規(guī)則的定義，包括實體完整性、參照完整性和用戶定義的完整性來維護數(shù)據(jù)庫中數(shù)據(jù)的正確性和相容性。這3類完整性定義在基本表上，只能實現(xiàn)基本的完整性維護功能，如：確保主鍵數(shù)據(jù)不重復(fù)非空、實現(xiàn)字段有效性和唯一性的檢查、自動填入默認(rèn)值、確保數(shù)據(jù)表之間引用（即外鍵）的一致性等功能。但是以下2種情況是上述3類完整性沒有辦法實現(xiàn)的。

（1）當(dāng)一個表中有匯總字段時，該匯總字段值是通過該表中其他字段值計算得出的。每當(dāng)向該表插入記錄時，新插入記錄的匯總字段值必須由該記錄上相應(yīng)的其他字段數(shù)據(jù)計算得出。

（2）對于兩個相關(guān)聯(lián)的表，其中一個表的數(shù)據(jù)改變時，另一個表中相關(guān)聯(lián)的數(shù)據(jù)也必須隨著更新，即數(shù)據(jù)聯(lián)動，否則數(shù)據(jù)就不正確了。

在上述2種情況下，定義相應(yīng)的觸發(fā)器來維護特殊的計算要求和數(shù)據(jù)聯(lián)動等復(fù)雜的業(yè)務(wù)邏輯是必要的，同時數(shù)據(jù)的維護是在數(shù)據(jù)庫內(nèi)部執(zhí)行，而不需要應(yīng)用程序來維護，更不需要人工干預(yù)。

下面通過具體的實例就觸發(fā)器實現(xiàn)數(shù)據(jù)庫特殊的計算要求和數(shù)據(jù)聯(lián)動等復(fù)雜的業(yè)務(wù)邏輯進行探討。

1 簡述2個基本概念

1.1 觸發(fā)器

觸發(fā)器是用戶定義在數(shù)據(jù)表上的一種被事件驅(qū)動的由DBMS調(diào)用執(zhí)行的特殊存儲過程[1]。觸發(fā)器是建立在一個基本表或視圖上的特殊類型的存儲過程，當(dāng)這個表發(fā)生了更新操作即Insert、Update或Delete時，會自動被激活執(zhí)行而不需要人工干預(yù)，可以處理各種復(fù)雜的業(yè)務(wù)規(guī)則。觸發(fā)器是維護數(shù)據(jù)庫完整性的另一有力工具。

1.2 Inserted表和Deleted表

inserted表和deleted表是兩個系統(tǒng)臨時表。這兩個表的結(jié)構(gòu)與觸發(fā)器作用的表的結(jié)構(gòu)相同，且由系統(tǒng)自動創(chuàng)建和撤銷，只能在觸發(fā)器中引用這2個表。In?serted表存放insert操作的記錄和update更新后的記錄，deleted表存放delete操作刪除的記錄以及update更新前的記錄。

1.3 For|After觸發(fā)器

For|After：指定DML觸發(fā)器僅在觸發(fā)SQL語句中指定的所有操作都已成功執(zhí)行時才被觸發(fā)[2]。也就是說after觸發(fā)器是在觸發(fā)命令成功執(zhí)行之后才激活觸發(fā)器，完成觸發(fā)器中規(guī)定的操作，所以，一旦由于一些特殊原因造成更新的記錄不符合要求時，需要在觸發(fā)器中回滾Rollback該操作，避免數(shù)據(jù)出現(xiàn)錯誤。After觸發(fā)器要求只執(zhí)行插入、更新、刪除中任一操作之后激活觸發(fā)器執(zhí)行[3]。

2 觸發(fā)器實現(xiàn)數(shù)據(jù)庫特殊的業(yè)務(wù)規(guī)則和數(shù)據(jù)聯(lián)動復(fù)雜業(yè)務(wù)邏輯

2.1 建立商品銷售數(shù)據(jù)庫

為了簡單起見，假設(shè)該數(shù)據(jù)庫僅包含2個表：商品庫存表和商品銷售表，表結(jié)構(gòu)分別如下：

庫存表 kc（spname（商品名稱），kcprice（庫存單價），kcqty（庫存數(shù)量），kctotal（庫存金額）），spname為主鍵。業(yè)務(wù)規(guī)則為：kctotal=kcprice×kcqty。

銷售表 xs（spname（商品名稱），buyer（購貨商），xsprice（銷售單價），xsqty（銷售數(shù)量），xstotal（銷售金額）），spname為主鍵。業(yè)務(wù)規(guī)則為：xstotal=xsprice×xsqty。

這2個表之間數(shù)據(jù)存在這樣的關(guān)系：每當(dāng)向銷售表插入記錄時，庫存表中相應(yīng)商品的庫存數(shù)量要減去銷售數(shù)量，庫存金額也要根據(jù)該表的業(yè)務(wù)規(guī)則重新計算。

2.2 為庫存表建立一個Insert觸發(fā)器維護特殊的業(yè)務(wù)規(guī)則

為庫存表創(chuàng)建一個Insert操作的After觸發(fā)器，每當(dāng)向庫存表插入記錄時觸發(fā)該觸發(fā)器。每當(dāng)插入一條新記錄，保證插入的數(shù)據(jù)中，庫存金額始終等于庫存數(shù)量和庫存單價的乘積。

創(chuàng)建觸發(fā)器的代碼如下：

上述四條命令，有的符合業(yè)務(wù)規(guī)則，有的不符合。查看商品庫存表，select*from kc，結(jié)果如表1所示。從表中數(shù)據(jù)可以看出，無論輸入數(shù)據(jù)時，庫存金額kctotal是否符合業(yè)務(wù)規(guī)則都沒有關(guān)系，因為有了這個觸發(fā)器，保證了每條記錄的庫存金額始終是符合業(yè)務(wù)規(guī)則的。

表1 庫存表KC

2.3 為銷售表XS建立一個Insert觸發(fā)器實現(xiàn)數(shù)據(jù)聯(lián)動

為銷售表創(chuàng)建一個Insert觸發(fā)器，每當(dāng)向銷售表xs插入記錄時，觸發(fā)此觸發(fā)器。該觸發(fā)器完成如下工作：檢查銷售的商品是否在庫存表kc中存在，有以下三種情況：1）kc中不存在此商品；2）kc中有此商品，但是庫存為零；3）kc中存在此商品，且?guī)齑鏀?shù)量大于零，則更新銷售表xs中的銷售金額，維護該表的業(yè)務(wù)規(guī)則，同時自動減少kc中對應(yīng)商品的庫存數(shù)量kcqty，實現(xiàn)數(shù)據(jù)聯(lián)動，并更新庫存表kc中的庫存金額kcto?tal，維護庫存表的業(yè)務(wù)規(guī)則。其中前2種情況需要提示相應(yīng)的信息，并且回滾該insert操作，避免出現(xiàn)庫存表中不存在或庫存為零的商品卻可以銷售的錯誤情況，從而維護數(shù)據(jù)的完整性。該觸發(fā)器代碼如下：

此商品在庫存表中存在，且?guī)齑娲笥诹?，可以銷售，命令中的銷售金額不符合業(yè)務(wù)規(guī)則，但是因為建立了此觸發(fā)器，銷售金額會始終符合業(yè)務(wù)規(guī)則，同時庫存表中的相應(yīng)商品的庫存數(shù)量和庫存金額也隨著更新了，從而實現(xiàn)了兩個表中數(shù)據(jù)的聯(lián)動。

Insert into xs values（'FFER','BUYER2',10,270,2700）

此商品在庫存表中不存在，所以不能銷售，回滾了

此條命令。

Insert into xs values（'DDD','BUYER3',10,410,4000）

此商品在庫存中存在，但是庫存小于等于零，即缺貨，所以不能銷售，回滾了此條命令。

查詢銷售表，Select*from xs，如表2所示?？梢钥闯?，上述三條Insert語句只有第一條成功插入，且銷售金額符合業(yè)務(wù)規(guī)則。

查詢商品庫存表，Select*from kc，如表3所示?？梢钥闯?，商品名稱為“AAA”的商品的庫存數(shù)量和庫存金額被自動更新了。

表2 商品銷售表xs

表3 商品庫存表kc

觀察表2和表3中數(shù)據(jù)可以看出兩個表中的數(shù)據(jù)是一致的，每當(dāng)向銷售表中插入一條新記錄，該表上的銷售金額始終符合業(yè)務(wù)規(guī)則，同時庫存表上該商品的庫存數(shù)量和庫存金額隨著更新，實現(xiàn)了2個表之間的數(shù)據(jù)聯(lián)動。

由此可以得出：當(dāng)表中某字段的數(shù)據(jù)是通過計算得出時，建立相應(yīng)觸發(fā)器可以保證該字段數(shù)據(jù)的正確性；當(dāng)表之間數(shù)據(jù)有關(guān)聯(lián)時，即一個表中的數(shù)據(jù)變動會引起關(guān)聯(lián)表中數(shù)據(jù)的聯(lián)動，這時建立一個觸發(fā)器是明智的，因為人工維護這樣的數(shù)據(jù)是很麻煩且容易出錯的，隨著數(shù)據(jù)量的增加，人工是無法完成的，但是觸發(fā)器就可以很好地勝任此工作，而且不會出錯。由此可以看出觸發(fā)器在數(shù)據(jù)庫完整性的維護方面起到了不可替代的作用。

當(dāng)然上訴兩個觸發(fā)器并不完美，因為插入的記錄必須是新的記錄，即和表中數(shù)據(jù)沒有重復(fù)（因為商品名稱是主鍵），這和現(xiàn)實是有沖突的。例如：同一種商品需要經(jīng)常進貨，同一種商品也需要多次銷售，在這種情況下，上述2個觸發(fā)器就不能完成這樣的要求了，所以觸發(fā)器要做相應(yīng)改動，例如改成Instead of觸發(fā)器就可以很好完成上述要求，從而保證數(shù)據(jù)的完整性。由于篇幅有限就不在此做探討了。

3 結(jié)語

綜上所述，可以看出觸發(fā)器在維護數(shù)據(jù)庫完整性性方面有其特殊的貢獻，數(shù)據(jù)庫中數(shù)據(jù)的正確性、有效性、一致性和相容性始終是數(shù)據(jù)庫的宗旨，沒有正確的數(shù)據(jù)就沒有正確的信息，所以當(dāng)數(shù)據(jù)庫中數(shù)據(jù)有特殊的業(yè)務(wù)規(guī)則及復(fù)雜的業(yè)務(wù)邏輯例如數(shù)據(jù)聯(lián)動時，設(shè)計相應(yīng)的觸發(fā)器是數(shù)據(jù)庫數(shù)據(jù)正確性的有力保證。

[1]李虎軍，金泉，邢旺，張政.探析SQL Server觸發(fā)器與完整性約束的區(qū)別[J].電腦知識與技術(shù),2016（12）:10-12.

[2]馬建紅，李占波.數(shù)據(jù)庫原理及應(yīng)用（SQL SQL Server 2008）[M].北京:清華大學(xué)出版社，2011：229.

[3]陳舒心.觸發(fā)器在數(shù)據(jù)庫設(shè)計中的應(yīng)用[J].廣東教育，2016（4）:110-111,119.2016（23）:10-12.