首页 / MSSQL / 一次SQLSERVER触发器编写感悟

一次SQLSERVER触发器编写感悟

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了一次SQLSERVER触发器编写感悟，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9931字，纯文字阅读大概需要15分钟。

内容图文

需求：将多台採集机的数据同步到server中，假设採集端数据库与server数据库连接失败则将数据保存到记录表中

前期思路：从採集端创建server端的数据库链接，通过採集端的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)

--向server表插入该条数据

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)

--更新server表记录

update shengyang.bwdb.dbo.test set unid = inserted.unid from inserted

--推断假设记录表中存在对该条数据的记录，则更新记录表中的记录

--（针对记录表中同一时候存在对同一条数据的create,update。仅仅须要记录终于unid。

--假设有create终于仍然向server表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),(select sensor_id from inserted),‘update‘)

end

commit tran

end

可是因为须要考虑两方网络不通的情况，因此须要做异常处理。開始没查找到推断远程数据库连接的方法，因此想着直接通过try catch来实现（try块里面运行可能出现异常的——往远程server端写入的代码，catch块里写往採集端本地记录表中的代码）

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

可是不管如何，仅仅要出现异常，就会强制回滚。此时假设在catch块之前提交，触发的仍然时候就会报错，而且无法将错误的记录插入异常记录表（运行不到），触发的原表记录能够写入。假设在catch块中rollback，然后将该记录插入异常记录表能够，可是同一时候回滚后触发的原记录也回滚丢失了。假设在catch块中commit，也不行（catch块中默认回滚了全部事务），包含尝试了使用记录回滚点进行分段事务提交回滚还是无法解决。

既不能commit,又不能rollback，这如何是好。。。

。。

。

随后BOSS提了个建议。通过存储过程中先做异常处理。推断server数据库是否连接成功。随即写了个存储过程，在存储过程中訪问远程数据库。定义一个变量初始值，catch块中改动这个值，然后把这个值作为存储过程返回值进行推断。

触发器：

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)

--调用存储过程推断远程连接server以及同步事务开启是否成功。返回1则表示失败
--sp_testlinkedserver [ @servername ] = servername
EXEC @ifconnected = [boolean_if_connected]
print @ifconnected
--假设远程连接成功
IF @ifconnected != 1
--假设採集端原表没有该记录则插入该记录
IF NOT EXISTS(SELECT * FROM deleted)
begin
set NOCOUNT ON;
begin try
--推断server表中是否存在该记录
--假设不存在向server表插入该条数据
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
--否则更新server表数据
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 this data to server‘
rollback
end catch
end
else
--假设採集端原表存在该记录则更新该记录
begin
set NOCOUNT ON;
begin try
--更新server表记录
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 this date 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

触发器：（非常easy，測试就是通过一个远程查询语句推断）

CREATE PROCEDURE boolean_if_connected
AS
BEGIN
declare @flag varchar(1)
begin try
set @flag=‘0‘
select * from shengyang.bwdb.dbo.test;
end try
begin catch
set @flag=‘1‘
print @flag
end catch
return @flag
end

这样的方法作为推断是可行的。可是。。。。

。。在触发器中调用的时候，假设远程server数据库连接不上了（測试关闭数据库服务），触发的时候直接就报错了,

其它的代码根本就没有运行。

终于。

。

找到了推断远程链接的方法（此时的心情是激动的。）

sp_testlinkedserver (Transact-SQL)

https://msdn.microsoft.com/zh-cn/library/ms189809(v=sql.90).aspx
通过该方法可直接推断创建的远程server连接是否有效。

。

终于触发器測试代码例如以下：

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)

--调用存储过程推断远程连接server以及同步事务开启是否成功，返回则表示失败

--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

--推断server表中是否存在该记录

--假设不存在向server表插入该条数据

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

--否则更新server表数据

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

--更新server表记录

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

总结（用BOSS的语录）：问题总是能找到解决方式的，仅仅要你摸清楚设计者的思路，所以一定要多想为什么，人家为啥要这么设计！

遇到问题能够尝试用不同的方法解决，但不能一味的依照自己的思路走，从问题的根源。从设计者的角度考虑解决方案，总会寻找到的！

一次SQLSERVER触发器编写感悟

标签：开启 his 异常处理 ace rto 事务 content 数据保存问题：

本文系统来源：http://www.cnblogs.com/mthoutai/p/7052606.html

内容总结

以上是互联网集市为您收集整理的一次SQLSERVER触发器编写感悟全部内容，希望文章能够帮你解决一次SQLSERVER触发器编写感悟所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/484448.html

来源：【匿名】

【上一篇】sqlserver--QA 【下一篇】一个odbc连mssql分页的类

更多 ►

【一次SQLSERVER触发器编写感悟】教程文章相关的互联网学习教程文章

sql server 的触发器的demo例子【代码】

-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================CREATETRIGGER TIG_IST_CUT_MTL_TYPEON CUT_MTL_TYPEAFTER insertASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;DECLARE@new_mtl_type_noVAR...

SQL Server 触发器【代码】

触发器是一种特殊类型的存储过程，它不同于之前的我们介绍的存储过程。触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的名称被调用。? 什么是触发器触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作。诸如：update、insert、delete这些操作的时候，系统会自动调用执行该...

SQLServer之创建DML AFTER INSERT触发器【图】

DML AFTER INSERT触发器创建原理触发器触发时，系统自动在内存中创建deleted表或inserted表，内存中创建的表只读，不允许修改，触发器执行完成后，自动删除。insert触发器工作原理：第一步执行insert插入语句，在表中插入数据行，第二步触发insert触发器，向系统临时表insert表中插入新行的备份(副本)，第三部触发器检查insert表中插入的新行数据，确定是否要回滚或执行其他操作。不能使用SSMS数据库管理工具直接创建DML添加触发器...

SQL Server 触发器【代码】

转自：http://www.cnblogs.com/hoojo/archive/2011/07/20/2111316.html 触发器是一种特殊类型的存储过程，它不同于之前的我们介绍的存储过程。触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的名称被调用。? 什么是触发器触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的区别是：触发器是当对某一个表进行操...

SqlServer2008--建立触发器实例

??http://www.cnblogs.com/caishuhua226/archive/2012/12/11/2813227.htmlSqlServer2008--建立触发器实例一﹕ 触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约`束。二﹕ SQL Server为每个触发器都创建了两个专用表﹕Inserted表和Deleted表。这两个表。　　一﹕ 触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是...

SQLServer触发器

ALTER TRIGGER [dbo].[PriceRange] ON[dbo].[Tab_SaleAndCarStyle] for update,insert,delete AS declare @Saleshop int,@minDfJjSale decimal(18,3),@maxDfJjSale decimal(18,3),@isDfjj varchar(50) set @isDfjj=否 if exists(select 1 from inserted) BALTER TRIGGER [dbo].[PriceRange] ON [dbo].[Tab_SaleAndCarStyle] for update,insert,deleteASdeclare @Saleshop int,@minDfJjSale decimal(18,3),@maxDfJjSale decima...

如何使用SQLServer2005INSTEAD-OF触发器_PHP教程

触发器是类似于存储程序的数据库对象，它响应数据库环境下的某个请求。SQL Sever 2005包含3个触发器对象：AFTER，数据定义语言 (DDL)和INSTEAD-OF。　　AFTER触发器是存储程序，它发生于数据操作语句作用之后，例如删除语句等。DDL是SQL Server 2005的新触发器，允许响应数据库引擎中对象定义水平事件（例如：DROP TABLE语句）。INSTEAD-OF触发器是对象，在数据库引擎中可以取代数据操作语句而执行。例如：将INSTEAD-OF INSERT触发...

喜忧参半的SQL Server触发器【代码】【图】

int NOT NULL IDENTITY(1,1) CONSTRAINT PK_Sales_Orders_log PRIMARY KEY CLUSTERED,OrderID int NOT NULL,CustomerID_Old int NOT NULL,CustomerID_New int NOT NULL,SalespersonPersonID_Old int NOT NULL,SalespersonPersonID_New int NOT NULL,PickedByPersonID_Old int NULL,PickedByPersonID_New int NULL,ContactPersonID_Old int NOT NULL,ContactPersonID_New int NOT NULL,BackorderOrderID_Old int NULL,BackorderOrde...

SQL Server 触发器例题： --次性删除course表数据，使用触发器替换删除操作，先删除外键表相关数据，再删除course。很难理解

trigger Course_Delete on course instead of delete asdeclare @cno varchar(20) --定义变量select @cno = cno from deleted --临时表里的信息是instead of 替换 delete要删除的信息delete from score where cno = @cno --instead of 替换操作后执行的代码命令delete from course where cno=@cno go delete from course where cno=‘3-245‘---‘3-245‘为输入的参数 select *from score select *from course --上面的...

SQL Server 触发器【代码】

触发器是一种特殊类型的存储过程，对特定事件作出相应。触发器有两种类型：数据定义语言(DDL)触发器和数据操纵语言(DML)触发器。DDL触发器在用户以某些方式(CREATE、ALTER、DROP或相似的语句)对数据库结构进行修改时激活作出响应。一般来说，只会在对数据库结构的改变或历史进行极为严格的审计时才会用到DDL触发器。DML触发器是一些附加在特定表或视图上的代码片段。与需要显式调用代码的存储过程不同，只要有附加触发器的时间在表...

SQL Server 触发器【代码】

触发器是一种特殊类型的存储过程，它不同于之前的我们介绍的存储过程。触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的名称被调用。什么是触发器触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作。诸如：update、insert、delete这些操作的时候，系统会自动调用执行该...

SQL Server 触发器【代码】

SQL server触发器、存储过程操作远程数据库插入数据,解决服务器已存在的问题【代码】

最近弄了一个小项目，也不是很复杂，需要将一个数据库的一些数据备份到另外一个库，不是本地，可能是网络上其他的数据库，想了一下，用了存储过程和触发器。也不是很复杂，首先我需要操作远程数据库，于是写了一个存储过程： CREATE PROCEDURE sendInfoToRemoteDb @CardNo varchar(50),@CardStyle varchar(20), @userId varchar(20),@UserName varchar(30),@passDate datetime,@inOut int AS BEGIN--exec sp_addlinkedserver <span...

SQL Server 触发器【代码】

触发器，他与存储过程很相似，可以说触发器是一种特殊类型的存储过程，但触发器主要是通过事件进行触发被自动调用执行的，而存储过程可以通过存储过程的名称被调用。 1，创建触发器 <span style="font-size:18px;">-- ================================================ -- Template generated from Template Explorer using: -- Create Trigger (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- command...

（转）SQL Server 触发器【代码】

SQL Server 触发器触发器是一种特殊类型的存储过程，它不同于之前的我们介绍的存储过程。触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的名称被调用。什么是触发器触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作。诸如：update、insert、delete这些操作的时候，系...

SQLSERVER - 相关标签

MSSQL - 最热教程

sqlServer 查询特定的表（字段或者文字...sqlserver建表及注释 sql server 2008出现远程过程调用失败 Window Server2019安装SqlServer2012管...SQLserver使用truncate语句报错：找不...sql server导出到excel错误：未在本地计...SQL Server 2008 下载及安装教程 SQL SERVER将多行数据合并成一行（转）SQL SERVER 中设置字段默认值为当前时间 php Sql Server连接失败问题及解决办法

首页 / MSSQL / 一次SQLSERVER触发器编写感悟

一次SQLSERVER触发器编写感悟

内容导读

内容图文

sp_testlinkedserver (Transact-SQL)

内容总结

内容备注

内容手机端

【一次SQLSERVER触发器编写感悟】教程文章相关的互联网学习教程文章

SQLSERVER - 相关标签

MSSQL - 最新教程

MSSQL - 最热教程