SQL 2012 Use Trigger to update DB in a different SQL instance

Is it possible to add a trigger to DB-1.[TB-1] that will update a table in DB-2 ( Called like [TB-2]) where DB-1 and DB-2 are in separate SQL Server instances? (DB-1 is in Instance-1 and DB-2 is in instance-2)

If it is, how do i script this?

Thanks for any help
PitSquadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
You can do that and your trigger will look like this:
CREATE TRIGGER myTrigger 
ON [TB-1] AFTER INSERT, UPDATE
AS
BEGIN 

  UPDATE [TBL2] SET
       ColumnToUpdate = 'value to update with'
  FROM [SRV-2].[DB-2].dbo.[TBL-2] as [TBL2]
       INNER JOIN inserted [TBL1] ON TBL1.XYZ = TBL2.ABC

END

Open in new window

However, I worry about reliability. If the trigger fails (say the other server is down for maintenance or due to a fault), the original INSERT, UPDATE will fail as well. So we are creating a dependency here.
1
HuaMin ChenProblem resolverCommented:
Hi,
You should create Stored procedure for doing this. And you can also schedule to run it periodically. Read
https://www.mssqltips.com/sqlservertutorial/160/sql-server-stored-procedure/
1
MlandaTCommented:
I like the stored procedure idea. It runs asynchronously from the original update and won't be a problem if the other server is down. You'll need to find a way to track which records your speed procedure had processed and what it hasn't. You can do this with a flag or instead of writing to the other server in the trigger, write to a local table , then your stored proc could delete what it had processed etc
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
A trigger will be just fine but you'll need to create a Linked Server for the 2nd instance and have the MSDTC running so you can have a distributed transaction in the case of the update fails in the 2nd instance it will rollback all operation.
Inside your trigger you should have something like:
UPDATE LinkedServerName.[DB-2].dbo.[TB-2] 
SET ColumnName=Value

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.