關于利用觸發器實現自動對遠程數據庫寫入數據方法的探討

蘇同建

【摘要】不同數據庫之間的數據自動同步及寫入功能作為數據庫應用中比較重要的一項功能一直為大家所重視，本文通過一個利用觸發器實現煤礦瓦斯數據中斷后自動發送短信提醒的實例展示如何實現自動對遠程數據庫寫入數據記錄一個獨特的方法，通過對此方法的探討為大家提供一種新穎的思路。

【關鍵詞】煤礦企業;觸發器;遠程數據庫;寫入數據;短信提醒

一家大型煤炭企業，下屬十多個煤礦，該煤炭企業有煤礦安全生產實時監測與監管系統（以下簡稱安全監控系統），該系統可實現各礦瓦斯數據的上傳;還有一個短信平臺系統，兩個系統使用的都是SQL SERVER數據庫，部署在不同的服務器上。現在想要實現安全監控系統中若瓦斯數據上傳中斷則自動給指定的手機發送短信，短信要寫明瓦斯中斷煤礦名稱及中斷時間和中斷情況。

安全監控系統中有一張表“實時通訊匯總顯示”，為各礦通訊狀態實時顯示，結構如下：

字段名 字段類型 說明

礦名 Char（20）

狀態 Int（4） 正常狀態為“1”，服務器中斷則為“0”

時間 Datetime（8）

網絡狀態 Int（4） 正常狀態為“1”，服務器中斷則為“-1”

匯總時間 Datetime（8）

短信平臺系統可以指定的手機發送短信，短信發送程序是每隔一定的時間對表sm_sms進行一次掃描，若表sm_sms里有內容，再根據接收人的手機號向外進行發送，同時把已發送的記錄自動插入到表sm_smsoutlog中。若接收用戶回復短消息，則系統自動把回復的內容插入到表sm_smsin中，因此，只需要把要發送的內容按照表的結構插入到sm_sms表中即可，這樣，短信即可正常發送出去。

短信平臺系統中表sm_sms的結構如下：

字段名 字段類型 說明

lsh Int（4） ?NOT NULL 插入的記錄編號

nr Varchar（200） ?NOT NULL 發送內容

jssjh Char（15） ?NOT NULL 接收手機號

jrrq Smalldatetime（4） ?YES 接收日期

fsrq Smalldatetime（4） ?YES 發送日期

fsbz Char（2） ?NOT NULL 發送標志（插入時，一定要置“否”字）

ly Char（16） ?YES 可為NULL

實現方法：

根據情況，此數據寫入量不大但是有偶發性即不是定期寫入，擬通過觸發器來實現。在安全監控系統中的“實時通訊匯總顯示”表中做一觸發器，要求“實時通訊匯總顯示”表一旦有礦的狀態和網絡狀態出現中斷的情況即朝短信平臺系統中的sm_sms表中插入一條記錄，以實現瓦斯中斷即發送短信的功能。

首先應該使兩個異地的數據庫建立聯接，在安全監控系統服務器上的SQL Server數據庫中，在“安全性”-“鏈接服務器”中新建一個名為“KYSMS”的遠程服務器鏈接，指向短信平臺服務器，如圖1所示。

在“常規”選項卡中，數據源選擇“其它數據源”中的“Microsoft OLE DB Provider for SQL Server”，然后在下面的“數據源”輸入框中填入短信平臺服務器的IP地址，在“目錄”輸入框中輸入sm_sms表所在的數據庫名稱，如圖2所示。

在“安全性”選項卡中，“本地登陸”填本地服務器SQL Server數據庫的用戶名，“遠程用戶”和“遠程密碼”中分別填寫短信平臺服務器的SQL Server數據庫的用戶名和登陸密碼。然后選擇“用此安全上下文進行：”，并且在下面的“遠程用戶”和“遠程密碼”中分別填寫短信平臺服務器的SQL Server數據庫的用戶名和登陸密碼，如圖3所示。

最后一步，在“服務器選項”卡中，選中“RPC”和“RPC輸出”這兩項，默認的只選中了“數據訪問”和“使用遠程排序規則”這兩項，也就是說一共要選中四項，如圖4所示。

然后點擊確定，至此遠程數據庫聯接建立完畢。

接下來我們再進行另一步更為重要的步驟：制作觸發器

找到我們要制作觸發器的表，即瓦斯上傳服務器中的“實時通訊匯總顯示”表，在該表上單擊右鍵，選擇“所有任務”-“管理觸發器”，如圖5所示。

在“文本”框中輸入觸發器內容，如圖6所示。

觸發器中的文本內容如下：

CREATE trigger 實時通訊中斷觸發器1

on 實時通訊匯總顯示

FOR INSERT UPDATE

as

declare @礦名 char（20），@狀態 int，@時間 datetime，@網絡狀態 int，@匯總時間 datetime

begin

set @礦名= （select [礦名] from Inserted ）

set @狀態= （select [狀態] from Inserted ）

set @時間= （select [時間] from Inserted ）

set @網絡狀態= （select [網絡狀態] from Inserted ）

set @匯總時間= （select [匯總時間] from Inserted ）

if （@狀態 = 0 or @網絡狀態 = -1）

begin

set XACT_ABORT ON

insert into KYSMS.kysms.dbo.sm_sms（[lsh]，[nr]，[jssjh]，[fsbz]） values（193753，[‘+ltrim（@礦名）+]+通訊中斷，+服務器狀態：+cast（ltrim（@狀態） as char（2））+，+網絡狀態：+cast（ltrim（@網絡狀態） as char（2））+，+時間：+cast（ltrim（@時間） as char（18）），134********，否）

insert into KYSMS.kysms.dbo.sm_sms（[lsh]，[nr]，[jssjh]，[fsbz]） values（193754，[‘+ltrim（@礦名）+]+通訊中斷，+服務器狀態：+cast（ltrim（@狀態） as char（2））+，+網絡狀態：+cast（ltrim（@網絡狀態） as char（2））+，+時間：+cast（ltrim（@時間） as char（18）），133********，否）

insert into KYSMS.kysms.dbo.sm_sms（[lsh]，[nr]，[jssjh]，[fsbz]） values（193755，[‘+ltrim（@礦名）+]+通訊中斷，+服務器狀態：+cast（ltrim（@狀態） as char（2））+，+網絡狀態：+cast（ltrim（@網絡狀態） as char（2））+，+時間：+cast（ltrim（@時間） as char（18）），189********，否）

set XACT_ABORT OFF

end

end

說明：

1.declare @礦名 char（20），@狀態 int，@時間 datetime，@網絡狀態 int，@匯總時間 datetime 這一句定義變量。

2.set @礦名= （select [礦名] from Inserted ）

……

set @匯總時間= （select [匯總時間] from Inserted ）設置變量名。

3.SET XACT_ABORT 為 ON 時，如果執行 Transact-SQL 語句產生運行時錯誤，則整個事務將終止并回滾。

4.insert into KYSMS.kysms.dbo.sm_sms（...）中，“KYSMS.kysms.dbo.sm_sms”用于聯接遠程SQL Server數據庫中的表sm_sms，其中第一個KYSMS指我們前面建立的數據庫聯接（為便于區分也可以起其他名字），第二個kysms指sm_sms表所在的數據庫名。對應每一個手機號寫一條insert插入語句。

設置該觸發器之后，如果遇到瓦斯數據上傳中斷，則指定的手機會收到一條內容為：“[某礦]通訊中斷，服務器狀態：0（或1），網絡狀態：-1（或1），時間：** * **** *：**AM（或PM）”的短信。

通過使用觸發器，實現了煤礦瓦斯數據上傳中斷時及時通過手機短信提醒的功能。瓦斯作為大多數煤礦事故的元兇其數據值極為重要，通過此功能的實現可以使相關人員在第一時間掌握到影響煤礦安全生產的瓦斯數據上傳中斷的情況，及時發現及時處理，為信息化提升安全生產提供了堅實的保證。

參考文獻

[1]Jiawei Han.數據挖掘：概念與技術（原書第3版）[M].機械工業出版社，2012.

[2]賽奎春.SQL Server數據庫開發實例解析[M].機械工業出版社，2006.

[3]德萊尼.深入解析SQL Server 2008[M].人民郵電出版社，2009.

[4]趙輝，陸冷飛.SQL Server觸發器在保持數據庫完整性中的應用[J].電腦開發與應用，2007.

[5]魏錦茂.SQL觸發器在數據庫設計中的應用[J].電腦編程技巧與維護，2010.

[6]黃汛，程治剛.數據庫連接池技術的應用研究[N].武漢大學學報（工學版），2002.

[7]俞建，張燎軍.數據庫訪問技術研究[J].計算機與現代化，2004.