利用數據庫觸發器實現金融賬務管理

葛雪瑞　陳涵

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

關鍵詞：信息系統開發;數據庫業務邏輯處理;開發效率;數據安全性;觸發器

中圖分類號：G642? ? ? ? 文獻標識碼：A

文章編號：1009-3044（2021）03-0042-02

1引言

關系數據庫中允許開發者利用SQL語言編寫數據庫程序，如存儲過程和觸發器，以達到將代碼集中于數據庫后端，簡化業務邏輯，提高開發效率，增強數據庫安全性的目的。除此之外，存儲過程還具有提前編譯，運行速度快的特點，而觸發器作為一種特殊的存儲過程，不但具備存儲過程所有特性之外，還具備基于事件驅動的即時響應和自執行能力，常被運用于數據庫完整性的維護。[1-2]

2 觸發器理論

觸發器由若干條SQL 語句組成，可以完成既定的業務規則，特點在于不能像存儲過程一樣被調用，而是依靠事件被觸發。在數據庫操作過程中，只要觸發事件被滿足，觸發器即刻被觸發，內部的SQL語句就會被執行，因此，開發人員通常會將相關業務邏輯的SQL語句寫在觸發器體內，以實現程序自動運行，無須人為干預的目的。數據庫中利用觸發器可以跟蹤數據變化，實現復雜規則的約束，同步數據操作，維護數據完整性。

觸發器主要分為DDL觸發器和DML觸發器，前者是數據定義型觸發器，當執行CREATE、ALTER、DROP等語句時被觸發，用于監控數據庫中的重要事件，如庫、表結構是否被惡意篡改，這類觸發器主要用于保護數據庫中對象結構;后者是數據操縱型觸發器，當執行INSERT、UPDATE、DELETE語句時被觸發，可以保護表中數據，維護表之間數據一致性，是觸發器最典型的應用。

在關系型數據庫中均能創建觸發器，以MSSQL中DML觸發器為例，定義規則如下[3]：

CREATE TRIGGER[架構名.]觸發器名

ON{表名|視圖名}

[WITH ENCRYPTION]

FOR|AFTER|INSTEAD OF操作類型

AS

BEGIN

SQL語句組;

END

GO

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

INSTEAD OF表示當滿足觸發條件時，跳過對觸發表的 INSERT，UPDATE，或 DELETE操作，直接執行觸發器定義的SQL語句。

FOR |AFTER 功能相反，當滿足觸發條件時先對觸發表做 INSERT，UPDATE，或 DELETE操作，直接執行觸發器定義的SQL語句

3工作原理

在MSSQL中，觸發器被觸發時會生成兩張臨時表，INSERTED表和DELETED 表，這兩張表中存放對觸發表操作的數據行。

若對觸發表進行INSERT操作，則插入觸發表中新數據行會被插入INSERTED表中;

若對觸發表進行DELETE操作，則觸發表中被刪除的數據行會被插入DELETED表中;

若對觸發表進行UPDATE操作，則觸發表中被修改前舊的數據行被插入DELETED表中，同時修改后新的數據行被插入INSERTED表中。

所以在編寫數據庫觸發器程序時，可以讀取這兩張表（INSERTED表和DELETED表中數據只能被讀取）中數據，進行規則判斷或進一步的業務處理。

需要注意的是：INSERTED表和DELETED 表由系統來維護﹐存在于內存中而不是在數據庫中，表的結構與觸發表的結構相同。觸發器執行完成后﹐這兩張表隨后被刪除。

4 利用觸發器實現金融賬務管理

實際生活中，我們經常會遇到賬務金額操作問題，如在銀行存取款后，賬號金額應實時更新;操作金額過大時，會給出提醒并拒絕操作;更新密碼，系統可以存儲新舊密碼等等。可以將這些業務邏輯規則對應的SQL語句寫在觸發器定義中，利用觸發器自動被觸發的特征，以避免人工操作，提高數據安全性。

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

4.1 存/取款問題級聯更新

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

觸發器設計如下：

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

驗證觸發器：

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

SELECT * FROM? account;

結果證明卡號'123456789012345'的用戶對應現有金額字段值減少500.

4.2 限制日操作金額

現實操作中，為保護資金安全，可以對用戶每日的操作金額進行限制，如ATM機上日操作限額是10萬元，當交易金額超出10萬元時，取消本次存款交易，并給出提示“每次交易金額不超過拾萬元”。

修改以上觸發器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 '當日交易金額不能大于拾萬'

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

驗證觸發器：

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

結果驗證：出現'當日交易金額不能大于拾萬'信息，程序中止。

5 結束語

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

參考文獻：

[1]馬根峰，王平.觸發器在維護關系數據庫中數據完整性方面的應用[J].微型計算機與應用，2001（12）：56-57.

[2]張玉珍.在多層應用中利用事務處理中的觸發器實現數據完整性[J].工業控制計算機，2002，15（8）：12-13.

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

[4] 李虎軍.SQL Server 觸發器應用探析[J].電腦知識與技術，2016，12（16）：13-15.

【通聯編輯：王力】