Search code examples
sqlsql-servertriggersidentity-insert

SQL Server Trigger using inserted ID

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.

Solution

  • 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.