葛雪瑞　陳涵

摘要：在信息系統(tǒng)的開發(fā)中，和數(shù)據(jù)庫(kù)相關(guān)的業(yè)務(wù)邏輯處理應(yīng)盡量在數(shù)據(jù)庫(kù)服務(wù)器端完成，這樣既利于系統(tǒng)開發(fā)效率的提高，也利于數(shù)據(jù)庫(kù)中數(shù)據(jù)安全性的提高。眾所周知，銀行金融賬務(wù)管理，重要敏感的數(shù)據(jù)都存儲(chǔ)在數(shù)據(jù)庫(kù)中，因此數(shù)據(jù)處理核心功能也應(yīng)在服務(wù)器上完成，文章給出了利用觸發(fā)器實(shí)現(xiàn)金融賬務(wù)的存款，取款以及限制日操作金額的方法。

關(guān)鍵詞：信息系統(tǒng)開發(fā);數(shù)據(jù)庫(kù)業(yè)務(wù)邏輯處理;開發(fā)效率;數(shù)據(jù)安全性;觸發(fā)器

中圖分類號(hào)：G642? ? ? ? 文獻(xiàn)標(biāo)識(shí)碼：A

文章編號(hào)：1009-3044（2021）03-0042-02

1引言

關(guān)系數(shù)據(jù)庫(kù)中允許開發(fā)者利用SQL語(yǔ)言編寫數(shù)據(jù)庫(kù)程序，如存儲(chǔ)過程和觸發(fā)器，以達(dá)到將代碼集中于數(shù)據(jù)庫(kù)后端，簡(jiǎn)化業(yè)務(wù)邏輯，提高開發(fā)效率，增強(qiáng)數(shù)據(jù)庫(kù)安全性的目的。除此之外，存儲(chǔ)過程還具有提前編譯，運(yùn)行速度快的特點(diǎn)，而觸發(fā)器作為一種特殊的存儲(chǔ)過程，不但具備存儲(chǔ)過程所有特性之外，還具備基于事件驅(qū)動(dòng)的即時(shí)響應(yīng)和自執(zhí)行能力，常被運(yùn)用于數(shù)據(jù)庫(kù)完整性的維護(hù)。[1-2]

2 觸發(fā)器理論

觸發(fā)器由若干條SQL 語(yǔ)句組成，可以完成既定的業(yè)務(wù)規(guī)則，特點(diǎn)在于不能像存儲(chǔ)過程一樣被調(diào)用，而是依靠事件被觸發(fā)。在數(shù)據(jù)庫(kù)操作過程中，只要觸發(fā)事件被滿足，觸發(fā)器即刻被觸發(fā)，內(nèi)部的SQL語(yǔ)句就會(huì)被執(zhí)行，因此，開發(fā)人員通常會(huì)將相關(guān)業(yè)務(wù)邏輯的SQL語(yǔ)句寫在觸發(fā)器體內(nèi)，以實(shí)現(xiàn)程序自動(dòng)運(yùn)行，無須人為干預(yù)的目的。數(shù)據(jù)庫(kù)中利用觸發(fā)器可以跟蹤數(shù)據(jù)變化，實(shí)現(xiàn)復(fù)雜規(guī)則的約束，同步數(shù)據(jù)操作，維護(hù)數(shù)據(jù)完整性。

觸發(fā)器主要分為DDL觸發(fā)器和DML觸發(fā)器，前者是數(shù)據(jù)定義型觸發(fā)器，當(dāng)執(zhí)行CREATE、ALTER、DROP等語(yǔ)句時(shí)被觸發(fā)，用于監(jiān)控?cái)?shù)據(jù)庫(kù)中的重要事件，如庫(kù)、表結(jié)構(gòu)是否被惡意篡改，這類觸發(fā)器主要用于保護(hù)數(shù)據(jù)庫(kù)中對(duì)象結(jié)構(gòu);后者是數(shù)據(jù)操縱型觸發(fā)器，當(dāng)執(zhí)行INSERT、UPDATE、DELETE語(yǔ)句時(shí)被觸發(fā)，可以保護(hù)表中數(shù)據(jù)，維護(hù)表之間數(shù)據(jù)一致性，是觸發(fā)器最典型的應(yīng)用。

在關(guān)系型數(shù)據(jù)庫(kù)中均能創(chuàng)建觸發(fā)器，以MSSQL中DML觸發(fā)器為例，定義規(guī)則如下[3]：

CREATE TRIGGER[架構(gòu)名.]觸發(fā)器名

ON{表名|視圖名}

[WITH ENCRYPTION]

FOR|AFTER|INSTEAD OF操作類型

AS

BEGIN

SQL語(yǔ)句組;

END

GO

其中，操作類型分為：INSERT ，UPDATE，DELETE三種操作。

INSTEAD OF表示當(dāng)滿足觸發(fā)條件時(shí)，跳過對(duì)觸發(fā)表的 INSERT，UPDATE，或 DELETE操作，直接執(zhí)行觸發(fā)器定義的SQL語(yǔ)句。

FOR |AFTER 功能相反，當(dāng)滿足觸發(fā)條件時(shí)先對(duì)觸發(fā)表做 INSERT，UPDATE，或 DELETE操作，直接執(zhí)行觸發(fā)器定義的SQL語(yǔ)句

3工作原理

在MSSQL中，觸發(fā)器被觸發(fā)時(shí)會(huì)生成兩張臨時(shí)表，INSERTED表和DELETED 表，這兩張表中存放對(duì)觸發(fā)表操作的數(shù)據(jù)行。

若對(duì)觸發(fā)表進(jìn)行INSERT操作，則插入觸發(fā)表中新數(shù)據(jù)行會(huì)被插入INSERTED表中;

若對(duì)觸發(fā)表進(jìn)行DELETE操作，則觸發(fā)表中被刪除的數(shù)據(jù)行會(huì)被插入DELETED表中;

若對(duì)觸發(fā)表進(jìn)行UPDATE操作，則觸發(fā)表中被修改前舊的數(shù)據(jù)行被插入DELETED表中，同時(shí)修改后新的數(shù)據(jù)行被插入INSERTED表中。

所以在編寫數(shù)據(jù)庫(kù)觸發(fā)器程序時(shí)，可以讀取這兩張表（INSERTED表和DELETED表中數(shù)據(jù)只能被讀?。┲袛?shù)據(jù)，進(jìn)行規(guī)則判斷或進(jìn)一步的業(yè)務(wù)處理。

需要注意的是：INSERTED表和DELETED 表由系統(tǒng)來維護(hù)﹐存在于內(nèi)存中而不是在數(shù)據(jù)庫(kù)中，表的結(jié)構(gòu)與觸發(fā)表的結(jié)構(gòu)相同。觸發(fā)器執(zhí)行完成后﹐這兩張表隨后被刪除。

4 利用觸發(fā)器實(shí)現(xiàn)金融賬務(wù)管理

實(shí)際生活中，我們經(jīng)常會(huì)遇到賬務(wù)金額操作問題，如在銀行存取款后，賬號(hào)金額應(yīng)實(shí)時(shí)更新;操作金額過大時(shí)，會(huì)給出提醒并拒絕操作;更新密碼，系統(tǒng)可以存儲(chǔ)新舊密碼等等。可以將這些業(yè)務(wù)邏輯規(guī)則對(duì)應(yīng)的SQL語(yǔ)句寫在觸發(fā)器定義中，利用觸發(fā)器自動(dòng)被觸發(fā)的特征，以避免人工操作，提高數(shù)據(jù)安全性。

已知有賬戶表 account表和金額交易表dealMoney，賬戶表中字段有：card_id（賬號(hào)），card_name（賬戶名稱），card_pass（密碼），current_moneny（現(xiàn)有金額）。金額交易表中字段有：id（編號(hào)），card_id （賬號(hào)），交易類型（trans_type），交易金額（trans_money），交易日期（trans_time）。

4.1 存/取款問題級(jí)聯(lián)更新

在實(shí)際操作中，每一次操作都會(huì)向金額交易表中插入一條數(shù)據(jù)，交易類型定為存、取款兩種，如果是存款，則操作完成后賬戶表中現(xiàn)有金額=現(xiàn)有金額+交易金額;反之，如果是取款，則操作完成后賬戶表中現(xiàn)有金額=現(xiàn)有金額-交易金額。

觸發(fā)器設(shè)計(jì)如下：

IF EXISTS（SELECT name FROM sysobjects WHERE name='t_dealMoney ' AND type='TR'）

DROP TRIGGER t_dealMoney

GO

CREATE TRIGGER? t_dealMoney

ON dealMoney

FOR INSERT

AS

DECLARE @card_id char（20），@trans_type varchar（30），@trans_money INT

SELECT? @card_id=card_id，@trans_type=trans_type，@trans_money=trans_money

FROM INSERTED

IF（@trans_type='支取'）

UPDATEaccount SET current_money=current_money-@trans_money where card_id=@card_id

ELSE

UPDATEaccount SET current_money=current_money+@trans_money where card_id=@card_id

GO

驗(yàn)證觸發(fā)器：

INSERT INTO dealMoney VALUES（'123456789012345'，'支取'，500，getdate（））;

SELECT * FROM? account;

結(jié)果證明卡號(hào)'123456789012345'的用戶對(duì)應(yīng)現(xiàn)有金額字段值減少500.

4.2 限制日操作金額

現(xiàn)實(shí)操作中，為保護(hù)資金安全，可以對(duì)用戶每日的操作金額進(jìn)行限制，如ATM機(jī)上日操作限額是10萬元，當(dāng)交易金額超出10萬元時(shí)，取消本次存款交易，并給出提示“每次交易金額不超過拾萬元”。

修改以上觸發(fā)器t_dealmoney，為其增加限制日操作金額的功能：

ALTERTRIGGER? t_dealmoney

ON dealmoney

FOR INSERT

AS

DECLARE? @card_id char（20），@trans_type varchar（30），@trans_money INT

SELECT? @card_id =card_id，@trans_type =trans_type，@trans_money=trans_money FROMINSERTED

IF（@trans_money >100000）

BEGIN

ROLLBACK TRAN

PRINT '當(dāng)日交易金額不能大于拾萬'

END

ELSE

IF（@trans_type='支取'）

UPDATEaccount SET current_money=current_money-@trans_money WHERE card_id=@card_id

ELSE

UPDATE account SET current_money=current_money+@trans_money WHERE card_id=@card_id

GO

驗(yàn)證觸發(fā)器：

INSERT INTO dealMoney VALUES（'123456789012345'，'支取'，150000，getdate（））;

結(jié)果驗(yàn)證：出現(xiàn)'當(dāng)日交易金額不能大于拾萬'信息，程序中止。

5 結(jié)束語(yǔ)

大數(shù)據(jù)時(shí)代，數(shù)據(jù)庫(kù)使用越來越廣泛，在程序開發(fā)中合理設(shè)計(jì)與使用觸發(fā)器，可以幫助系統(tǒng)開發(fā)者和數(shù)據(jù)庫(kù)管理者實(shí)現(xiàn)諸多復(fù)雜業(yè)務(wù)功能，降低他們的勞動(dòng)強(qiáng)度[4]，同時(shí)也能提高系統(tǒng)的數(shù)據(jù)安全性與完整性。但任何事物都有雙面性，觸發(fā)器的使用尤其如此，它像一把雙刃劍，在給人們帶來方便的同時(shí)，也存在缺點(diǎn)，由于它的隱蔽性，往往在不經(jīng)意間被觸發(fā)執(zhí)行，這也給后期運(yùn)行維護(hù)帶來風(fēng)險(xiǎn)與困難，所以，如何在系統(tǒng)開發(fā)中合理使用觸發(fā)器還需進(jìn)一步研究探討。

參考文獻(xiàn)：

[1]馬根峰，王平.觸發(fā)器在維護(hù)關(guān)系數(shù)據(jù)庫(kù)中數(shù)據(jù)完整性方面的應(yīng)用[J].微型計(jì)算機(jī)與應(yīng)用，2001（12）：56-57.

[2]張玉珍.在多層應(yīng)用中利用事務(wù)處理中的觸發(fā)器實(shí)現(xiàn)數(shù)據(jù)完整性[J].工業(yè)控制計(jì)算機(jī)，2002，15（8）：12-13.

[3] 鄭阿奇.SQL Server 實(shí)用教程[M].4版，北京：電子工業(yè)出版社，2016.

[4] 李虎軍.SQL Server 觸發(fā)器應(yīng)用探析[J].電腦知識(shí)與技術(shù)，2016，12（16）：13-15.

【通聯(lián)編輯：王力】