1

I'd like to create a trigger against an insert of a table in my database. So for example, if I have a company_name table, I'd like to do the following:

  1. Insert new row into company_name table on server A
  2. Invoke a trigger that gets the newly inserted id of the row in company_name on server A.
  3. Set identity_insert ON on the company_name table in server B
  4. Insert that same exact row that was just inserted into server A into the company_name table on server B.

The linked server can be referred to as [ServerB].[ServerB-Database].dbo.company_name.

This is a requirement due to data hosting restrictions of clients in different countries.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Adam Levitt
  • 10,316
  • 26
  • 84
  • 145
  • So what is your question about this? – Martin Smith May 21 '13 at 15:00
  • How would I write this trigger to do this? – Adam Levitt May 21 '13 at 15:00
  • 2
    Is the id a guid, if not how are you going to avoid id in server a colliding with that in B? If serverB is unavailable, what do you want to do, fail the insert in server a? I see problems. – Tony Hopkinson May 21 '13 at 15:08
  • I'm basically trying to figure out how to use load SCOPE_IDENTITY into a declared @variable so I can insert that same ID into server B. – Adam Levitt May 21 '13 at 15:10
  • 1
    You don't use `Scope_Identity()` in a trigger (unless it is doing an `insert`), you use the [`inserted`](http://msdn.microsoft.com/en-us/library/ms191300.aspx) pseudo-table. And please either code the trigger to handle multiple rows or to explicitly fail with [`RaIsError`](http://msdn.microsoft.com/en-us/library/ms178592.aspx) if it cannot handle them. – HABO May 21 '13 at 15:32
  • 1
    You'd be better off looking at alternative ways to do this, like Service Broker. Invoking a cross-server/cross-country distributed transaction inside a trigger is going to lead to all kinds of unpleasantness. – RBarryYoung May 21 '13 at 15:40

1 Answers1

4

One problem is the lag and connection between countries A and B.

While the remote insert happens, the local transaction is still running. This reduces performance and increases blocking.

The remote server could be unavailable too. Or take too long to respond. You can code the trigger to not fail but then you have inconsistent data.

Finally, if there are inserts on the remote server you have a multi-master problem.

I would really consider a solution like replication to publish the data to another server. Or consider using service broker to decouple local and remote inserts.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
gbn
  • 422,506
  • 82
  • 585
  • 676