一次SQLSERVER触发器编写感悟
|
背景：BOSS需要我写一个工厂采集端到服务器端的数据同步触发器，数据库采用的是sqlserver2008 需求：将多台采集机的数据同步到服务器中，如果采集端数据库与服务器数据库连接失败则将数据保存到记录表中 前期思路：从采集端创建服务器端的数据库链接，通过采集端的insert,update触发，同时往远程表写入 问题：由于初始接触sqlserver，对sqlserver触发器了解不深，查阅一些资料后写出了满足正常情况下（连接服务器数据库正常）的触发器。
create trigger trig_sensor_shengyang on dbo.sensor_test for insert,update as begin --如果原表没有该记录则插入该记录 IF NOT EXISTS(SELECT * FROM deleted) begin set NOCOUNT ON; begin tran --insertopenrowset('sqloledb','XXX.XXX.XXX.XXX';'DBUSER';'DBPWD',bwdb.dbo.test) --向服务器表插入该条数据 insert into shengyang.bwdb.dbo.test select * from inserted --同时向记录表中插入数据 insert into dbo.test_bak values((select unid from inserted),(select sensor_id from inserted),'create') commit tran end else --如果原表存在该记录则更新该记录 begin set NOCOUNT ON; begin tran --update openrowset('sqloledb','XXX.XXX.XXX.XXX';'DBUSER';'DBPWD',bwdb.dbo.test) --更新服务器表记录 update shengyang.bwdb.dbo.test set unid = inserted.unid from inserted --判断如果记录表中存在对该条数据的记录，则更新记录表中的记录 --（针对记录表中同时存在对同一条数据的create,update，只需要记录最终unid， --如果有create最终仍然向服务器表create，如果是多次更新只需记录最后一次更新） if exists(select * from dbo.test_bak where sensor_id=(select sensor_id from inserted)) begin update dbo.test_bak set unid=i.unid from inserted i end --如果记录表中不存在对该条数据的修改记录，则在记录标中插入该数据的update记录 else begin insert into dbo.test_bak values ((select unid from inserted),'update') end commit tran end end 但是由于需要考虑双方网络不通的情况，因此需要做异常处理。开始没查找到判断远程数据库连接的方法，因此想着直接通过try catch来实现（try块里面执行可能出现异常的——往远程服务器端写入的代码，catch块里写往采集端本地记录表中的代码） create trigger trig_sensor_shengyang? 但是无论怎样，只要出现异常，就会强制回滚。此时如果在catch块之前提交，触发的仍然时候就会报错，并且无法将错误的记录插入异常记录表（执行不到），触发的原表记录可以写入。如果在catch块中rollback，然后将该记录插入异常记录表可以，但是同时回滚后触发的原记录也回滚丢失了。如果在catch块中commit，也不行（catch块中默认回滚了所有事务），包括尝试了使用记录回滚点进行分段事务提交回滚还是无法解决。既不能commit,又不能rollback，这如何是好。。。。。。 随后BOSS提了个建议，通过存储过程中先做异常处理，判断服务器数据库是否连接成功。随即写了个存储过程，在存储过程中访问远程数据库，定义一个变量初始值，catch块中修改这个值，然后把这个值作为存储过程返回值进行判断。 触发器： create trigger trig_sensor_shengyang? 触发器：（很简单，测试就是通过一个远程查询语句判断） CREATE PROCEDURE boolean_if_connected 这种方法作为判断是可行的，但是。。。。。。在触发器中调用的时候，如果远程服务器数据库连接不上了（测试关闭数据库服务），触发的时候直接就报错了,
其他的代码根本就没有执行。 最终。。。 找到了判断远程链接的方法（此时的心情是激动的。） sp_testlinkedserver (Transact-SQL)https://msdn.microsoft.com/zh-cn/library/ms189809(v=sql.90).aspx通过该方法可直接判断创建的远程服务器连接是否有效。。 最终触发器测试代码如下： create trigger trig_sensor_shengyang on dbo.sensor_test after insert,update as declare @unid varchar(20) declare @sensor_id varchar(8) declare @boolean varchar(1) declare @ifconnected varchar(2) begin set @unid =(select unid from inserted) set @sensor_id =(select sensor_id from inserted) --调用存储过程判断远程连接服务器以及同步事务开启是否成功，返回则表示失败 --sp_testlinkedserver[ @servername ] = servername EXEC @ifconnected = [sp_testlinkedserver]shengyang print @ifconnected --如果远程连接成功 IF @ifconnected != 1 --如果采集端原表没有该记录则插入该记录 ??? IF NOTEXISTS(SELECT * FROM deleted)?? ?????? begin ?????????? set NOCOUNT ON;????? ?????????? begin try --判断服务器表中是否存在该记录 --如果不存在向服务器表插入该条数据 ????????????? if not EXISTS(SELECT * FROM shengyang.bwdb.dbo.test where sensor_id=@sensor_id) ????????????????? begin ???????????????????? insert into shengyang.bwdb.dbo.test select * from inserted ????????????????? end --否则更新服务器表数据 ????????????? else ????????????????? begin ???????????????????? update shengyang.bwdb.dbo.test set unid = inserted.unid from inserted where test.sensor_id = @sensor_id ????????????????? end ?????????? end try ?????????? begin catch ????????????? print 'failed to insert to server' ????????????? rollback ?????????? end catch ?????? end ??? else --如果采集端原表存在该记录则更新该记录 ?????? begin ?????????? set NOCOUNT ON; ?????????? begin try --更新服务器表记录 ????????????? update shengyang.bwdb.dbo.test set unid = inserted.unid from inserted where test.sensor_id = @sensor_id ?????????? end try ?????????? begin catch ????????????? print 'failed to update to server' ????????????? rollback ?????????? end catch ?????? end else ??? if exists(select * from dbo.test_bak where sensor_id=@sensor_id) ????????????? begin ????????????????? begin tran ????????????????? update dbo.test_bak set unid=i.unid from inserted i ????????????????? commit tran ????????????? end --如果记录表中不存在对该条数据的修改记录，则在记录标中插入该数据的update记录 ?????????? else ????????????? begin ????????????????? begin tran ????????????????? insert into dbo.test_bak values (@unid,@sensor_id,'....') ????????????????? commit tran ????????????? end end ? 总结（用BOSS的语录）：问题总是能找到解决方案的，只要你摸清楚设计者的思路，所以一定要多想为什么，人家为啥要这么设计 ！ 遇到问题可以尝试用不同的方法解决，但不能一味的按照自己的思路走，从问题的根源，从设计者的角度考虑解决方式，总会寻找到的！（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
