奥克兰 怀托摩萤火虫:利用触发器进行数据实时传输的设计与实

　某加油集团包含管理中心、加油站、油库三个部分，其中加油站有多个。连锁加油站管理系统的网络通信模块负责三者之间的数据传输，其网络结构图如图1所示。某些数据的传输需要保证一定的实时性，如客户加油完毕后，管理系统通过集线器采集该笔加油数据并写入加油站服务器数据库中，系统要求在短时间（通常为几秒钟）内将该笔加油数据上传到管理中心，便于用户通过Internet网查询加油信息。传统的实现方式是为每个需要上传的数据表增加一列bool类型的字段来标记该条记录是否已上传，同时，在程序中操作数据表的地方编写网络通信代码，而操作该数据表的地方通常不止一处，而且如果增加了新的数据表，就需要重复编写功能相同的代码，这就造成代码编写量的成倍增加，并且造成网络通信代码的随机分散，不便于以后的升级和维护工作。鉴于此，通过对数据上传功能需求的分析，设计了一种利用SQL触发器进行数据实时传输的方式，有效地解决了采用上述传统方式所存在的问题。
　　1 SQL触发器介绍
　　1.1 SQL触发器的种类
　　SQL Server 2000 支持两种类型的触发器：AFTER 触发器和INSTEAD OF 触发器[1]。
　　AFTER 触发器只有执行某一操作（INSERT、UPDATE、DELETE）之后，触发器才被激活，且只能在表上定义。对于AFTER 触发器，可以为针对表的同一操作定义多个触发器，并定义哪一个触发器被最先触发，哪一个被最后触发。通常，AFTER 触发器用于监视发生在数据库表格里的数据变化，如果该语句因错误（比如违反约束或语法错误）而失败，触发器将不会被执行。
　　INSTEAD OF 触发器并不执行其所定义的操作（INSERT、UPDATE、DELETE），而仅是执行触发器本身。既可在表上定义INSTEAD OF 触发器，也可以在视图上定义INSTEAD OF 触发器，但对同一操作只能定义一个INSTEAD OF 触发器。
　　1.2 SQL触发器的工作原理
　　触发器是一种特殊的存储过程，其与一般的存储过程最大的区别是：一般的存储过程可以被用户直接调用，而触发器不能被用户直接调用。触发器通常是事务的一部分，一旦执行过程的任何部分失败，数据库将通过T-SQL语言的ROLLBACK TRANSACTION语句回滚，将所做的一切复原，能够有效确保数据的完整性[1]。
　　数据库为触发器创建两个由系统维护的逻辑表：Deleted和Inserted表，它们不是存放在数据库中，而是存放在内存中，并且不能被用户直接修改，其结构与被触发器作用的表的结构相同。Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行，Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行，其中Update是先执行Delete操作然后再执行Insert操作[2-3]。SQL Server 2000的两种触发器及触发器中的两个虚拟表是SQL Server中强制业务规则的有力工具[4]。
　　触发器是基于一个表而创建的，却可以同时对多个表进行操作，以实现更为复杂的业务逻辑。它可以级联修改数据库中相关的表；可以执行比检查约束更为复杂的约束操作；拒绝或回滚违反了引用完整性的操作；比较表修改前后数据之间的差别，并根据差别采取相应的操作。
　　2 利用SQL触发器进行实时传输
　　触发器的作用是在发生某种数据库事件时，自动完成一些动作[5]。可以将其类比为编程语言中的事件函数[6]。如果希望在某个操作之后系统可以自动根据条件去执行某种行为，就可以用触发器来实现。合理的触发器设计既可以保护表中的数据，使数据表更加安全，又可以减少前台功能代码编写量，具有事半功倍的效果。可以利用触发器的这个特点设计一种巧妙的方式，使得对数据实时传输的处理变得简单。
　　2.1 信息定位表
　　设计PositionTable表，该表的作用是保存数据表发生变化时所影响的行的定位信息，其结构如表1所示。
　　其中：Id列是标识列，标识自增量为1；TableName是被触发器作用表的表名；SerialName是该表的主键字段名；Serialno是主键字段值；OpType是触发器被触发时，对该表进行的操作类型：1表示Insert，2表示Update，3表示Delete。
　　2.2 基于T-SQL语言的触发器设计
　　分析连锁加油站管理系统数据实时传输功能的需求，由于是在数据表发生变化时才进行保存所影响的行的定位信息的操作，因此选用AFTER触发器。
　　当对数据表进行插入（Insert）、更新（Update）、删除（Delete）操作时，触发器在后台被自动调用执行，将被操作表发生变化时所影响的行的定位信息写入PositionTable表中，触发器的主体设计如下：
　　createtriggertr_被触发器作用的表名 on[dbo].[ 被触发器作用的表名]
　　afterinsert,delete,update
　　as
　　setnocounton
　　declare@OpTypeint--1:insert，2:update，3:delete
　　set@OpType=2--默认为update
　　ifnotexists(select1frominserted)set@OpType=3
　　ifnotexists(select1fromdeleted)set@OpType=1
　declare@Transbit
　　if@@TRANCOUNT>0
　　set@Trans=1--返回当前连接的活动事务数
　　else
　　set@Trans=0
　　declare @TempSerialno nchar(30)
　　declare @TableName nchar(30)
　　declare @SerialName nchar(30)
　　set @TableName='被触发器作用的表名'
　　set @SerialName='主键字段名'
　　if ((@OpType=1 or @OpType=2) and @Trans=1)--insert和update
　　begin
　　declare cursor1 cursor for
　　select主键字段名from inserted
　　open cursor1
　　fetch next from cursor1 into @TempSerialno
　　while @@fetch_status=0
　　begin
　　insert into PositionTable(TableName,SerialName,Serialno,OpType)
　　values( @TableName,@SerialName,@TempSerialno,@OpType)
　　fetch next from cursor1 into @TempSerialno
　　end
　　close cursor1
　　deallocate cursor1
　　end
　　if (@OpType=3 and @Trans=1)--delete删除
　　begin
　　declare cursor1 cursor for
　　select 主键字段名 from deleted
　　open cursor1
　　fetch next from cursor1 into @TempSerialno
　　while @@fetch_status=0
　　begin
　　insert into PositionTable(TableName,SerialName,Serialno,OpType)
　　values( @TableName,@SerialName,@TempSerialno,@OpType)
　　fetch next from cursor1 into @TempSerialno
　　end
　　close cursor1
　　deallocate cursor1
　　end
　　事实上，在对数据表执行插入（Insert）、更新（Update）、删除（Delete）操作之后，可分别为其编写插入触发器、更新触发器、删除触发器[7]，但由于要实现的功能类似，都是往PositionTable表中写定位信息，因此将其写在一个触发器中，若实现的功能不同，则可分别写在三个触发器中，以适应实际应用的需要。
　　以上触发器的功能已通过系统的阶段测试和集成测试，符合预定的业务规则，运行稳定。
　　2.3 数据实时上传线程的设计
　　管理系统启动后，开启新线程定时查询PositionTable表中有无记录，若有则根据定位信息提取出对应表中的记录，将获取的记录与定位信息一起写入文本文件并打包，然后通过网络通信模块发送到管理中心，管理中心对数据包解压后读取数据并根据定位信息导入管理中心数据库对应的表中，主要流程如图2所示。
　　值得注意的是，由于在数据实时传输的过程中，加油数据依然源源不断地被系统采集并写入数据库，所以此时PositionTable表中也有新的记录写入，因此，为了区分信息定位表中原有的记录和新写入的记录，在前端代码中使用了一个int型的数组nId[]来保存已经写入文本文件的定位信息的Id号，当上传成功后，遍历该数组，将PositionTable表中对应的记录删除，即将原有的记录删除，而新写入的记录则在下次定时时间到后得到处理。
　　这里用到了多线程与网络通信技术，但其不是本文讨论的重点，如有需要请查阅相关资料。
　　3 结束语
　　在系统的开发过程中，上述触发器所实现的功能完全可以借助编程工具通过前端代码来实现，但与之相比，采取触发器的方式具有以下几方面的优势：
　　1）不必为需要上传的表添加bool型的标记字段来记录该行是否已上传，即不需要改变被操作表的结构，PositionTable表中保存了未上传记录的定位信息，即使网络出现故障，这些信息也不会丢失，待网络恢复正常后，系统立刻将数据上传；
　　2）易于新增需要上传的数据表，无需重新编写前端代码，只需为新增的表编写对应的触发器即可，使系统功能的扩展变得十分简便；
　　3）使得前端代码的编写能够集中在一个较小的范围之内，并且有效减少了代码的编写量，有利于程序的升级与维护。
　　触发器的使用让数据上传部分的整个设计变得简洁而高效，系统的可扩展性和可维护性均得到提高。虽然触发器能够提高系统的开发效率，但是也不要过多使用，并且不要将触发器设计得过于复杂，否则不但起不到预期的效果，反而会造成数据库和应用程序维护困难。
　　本文所提到的利用触发器进行数据实时传输的方式已经应用在连锁加油站管理系统中，该系统已被某加油集团安装使用。