- 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)
- begin
- set @unid = (select unid from inserted)
- set @sensor_id = (select sensor_id from inserted)
-- 假设採集端原表没有该记录则插入该记录
- IF NOT EXISTS(SELECT * FROM deleted)
- begin
- set NOCOUNT ON;
- begin try
- -- BEGIN TRAN
-- 推断 server 表中是否存在该记录
-- 假设不存在向 server 表插入该条数据
- print 1111111111
- if not EXISTS(SELECT * FROM shengyang.bwdb.dbo.test where sensor_id=@sensor_id)
- begin
- --insert openrowset(sqloledb,XXX.XXX.XXX.XXX;DBUSER;DBPWD,bwdb.dbo.test)
- insert into shengyang.bwdb.dbo.test select * from inserted
- end
-- 否则更新 server 表数据
- else
- begin
- update shengyang.bwdb.dbo.test set unid = inserted.unid from inserted where test.sensor_id = @sensor_id
- end
- --COMMIT TRAN
- end try
-- 假设出错则向採集端记录表中插入数据
- begin catch
- print fail to insert this data to server
- rollback
- -- print @@TRANCOUNT
-- IF @@TRANCOUNT > 0--------------- 推断有没有事务
-- BEGIN
-- ROLLBACK TRANSACTION ts---------- 回滚事务
- -- END?
- insert into dbo.test_bak values (@unid,@sensor_id,insert)
- set @boolean = 1
- --EXEC insert_sensor_shengyang @unid,@sensor_id
- end catch
- -- if @boolean=1
- -- begin
- -- print boolean+@boolean
- -- insert into dbo.test_bak values (@unid,@sensor_id,insert)
- -- end
- end
- else
-- 假设採集端原表存在该记录则更新该记录
- begin
- set NOCOUNT ON;?
- begin try
- --update openrowset(sqloledb,XXX.XXX.XXX.XXX;DBUSER;DBPWD,bwdb.dbo.test)
-- 更新 server 表记录
- update shengyang.bwdb.dbo.test set unid = inserted.unid from inserted where test.sensor_id = @sensor_id end
- try
-- 假设出错, 推断假设记录表中存在对该条数据的记录则更新记录表中的记录
--(针对记录表中同一时候存在对同一条数据的 create,update, 仅仅须要记录终于 unid,
-- 假设有 create 终于仍然向 server 表 create, 假设是多次更新仅仅需记录最后一次更新)
- begin catch
- if exists(select * from dbo.test_bak where sensor_id=@sensor_id)
- begin
- update dbo.test_bak set unid=i.unid from inserted i
- end
-- 假设记录表中不存在对该条数据的改动记录, 则在记录标中插入该数据的 update 记录
- else
- begin
- insert into dbo.test_bak values (@unid,@sensor_id,update)
- end
- end catch
- end
- end
来源: http://www.bubuko.com/infodetail-2500387.html