SQL server trigger, stored procedure operation remote database insertion data, solve the existing problems on the server, server trigger

SQL server trigger, stored procedure operation remote database insertion data, solve the existing problems on the server, server trigger

I recently created a small project, which is not very complicated. I need to back up some data of a database to another database, not local. It may be other databases on the network. I thought about it, stored Procedures and triggers are used. It is not very complex. First I need to operate a remote database, so I wrote a stored procedure:

CREATE PROCEDURE sendInfoToRemoteDb @CardNo varchar(50),@CardStyle varchar(20),@userId varchar(20),@UserName varchar(30),@passDate datetime,@inOut intASBEGIN--exec sp_addlinkedserver<span style="white-space:pre"></span>--@server='ims_srv_lnk',<span style="white-space:pre"></span>--@srvproduct='',<span style="white-space:pre"></span>--@provider='MSDASQL',<span style="white-space:pre"></span>--@provstr='Driver={SQL SERVER};SERVER=xxxx.com;UID=xxx;PWD=xxx;Database=xxx'insert into ims_srv_lnk.IMSDBBAK.dbo.tb_record(cardNo,cardStyle,userId,userName,passDate,inOut) values(@CardNo,@CardStyle,@userId,@UserName,@passDate,@inOut)ENDGO

It is actually a remote database operation. Insert the data and write a trigger to the local database:

Create trigger tgr_passrecord_insert on PassCheckRecord for insert -- insert trigger as -- Define variable declare @ CardNo varchar (50), @ CardStyle varchar (20), @ userId varchar (20 ), @ UserName varchar (30), @ passDate datetime, @ inOut int -- Query inserted records in the inserted Table select @ CardNo = CardNo, @ CardStyle = CardStyle, @ userId = userId, @ UserName = UserName, @ passDate = passDate, @ inOut = inOutfrom inserted; set @ CardNo = @ CardNo; set @ CardStyle = @ CardStyle; set @ userId = @ userId; set @ UserName = @ UserName; set @ passDate = @ passDate; set @ inOut = @ inOut; exec [dbo]. [sendInfoToRemoteDb] @ CardNo = @ CardNo, @ CardStyle = @ CardStyle, @ userId = @ userId, @ UserName = @ UserName, @ passDate = @ passDate, @ inOut = @ inOutprint: the message is sent successfully! ';

Then, the result is successfully executed for the first time, but the second time it prompts that ims_srv_lnk already exists and cannot be executed. If you want to delete the connection server in the Code, the result will be OK.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.