Link to home
Create AccountLog in
Avatar of PitSquad
PitSquad

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
Avatar of Mlanda T
Mlanda T
Flag of South Africa image

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.
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/
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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account