asked on

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

Mlanda T

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.

Peter Chan

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/

Mlanda T

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

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial