Trigger integration between two DBMS instances

One method to replicate tables between databases is to use triggers.

Using triggers is both immediate and synchronous, which is desirable as long as the solution performs adequately. Performance is an issue if you are using distributed transactions with two-phased commit. Long wait times in the UI for simple updates are just unacceptable.

I recently completed one integration job with triggers and here's what I found.

Job description
Synchronize customer tables between two databases running on different servers in the same network. The old "legacy" database is a client-server application running on SQL Server 2000. The new web application runs on SQL Server Express 2005.

Tables to syncronize in four-part syntax, somewhat simplified:
[SQL2000].[legacy].[dbo].[person] with
[SQL2005].[webapp].[dbo].[person]

[SQL2000].[legacy].[dbo].[company] with
[SQL2005].[webapp].[dbo].[company]

Synchronization method:
AFTER/FOR TRIGGER

While writing T-SQL triggers you should set up test cases.

A simple test case:

1. read values before operation
2. perform an operation
3. read values after operation
4. compare values

For step 4 I suggest comparing values manually at first. When initial development is completed you should automate this phase too, just to have a test case ready for later maintenance work.

An example of 1.-3., running at legacyapp database

-- Test company
SET @companyID = ...

-- Initial state
SELECT *
FROM [legacyapp].[dbo].[company]
WHERE companyID= @companyID

SELECT *
FROM [SQL2005].[webapp].[dbo].[company]
WHERE legacyapp_companyID = @companyID

-- Perform Operation
UPDATE [legacyapp].[dbo].[Client]
SET [CompanyName] = 'TestCompany'
WHERE companyID = @companyID

-- Result state
SELECT *
FROM [legacyapp].[dbo].[company]
WHERE companyID= @companyID

SELECT *
FROM [SQL2005].[webapp].[dbo].[company]
WHERE legacyapp_companyID = @companyID

Test cases are necessary in debugging. In a distributed setting I found that the most common symptom of trouble is not an error message but a deadlock: your query just hangs.

Be aware of deadlocks!

MSDTC or SQL Server can detect some distributed deadlocks. For example, if two triggers fire each other they would end up playing ping pong forever in a livelock. Instead of this, I got an error message:

Transaction context in use by another session.

I couldn't find a way out of this. Apparently SQL Server uses locks that make insert/delete/update triggers that fire each other impossible. Well, on a second thought, this is a sane and good policy you actually want your DBMS to enforce. As a consequence, in order to get two-way synchronization I had to program in application logic the replication from webapp to the legacy app.

I would guess that inside a single DBMS you could tweak locking to get mutually firing triggers to work. For example, I tried to implement a monitor that checks the cause of the fired trigger and cuts short if it is due to the other trigger.

DECLARE @monitor data_type
SET @monitor = (SELECT condition_field FROM Inserted)
IF @monitor IS NULL OR @monitor <> condition
BEGIN
... triggered transaction
END

IF @monitor = condition
BEGIN
 UPDATE table
 SET condition_field= NULL
 WHERE companyID= @companyID -- identify inserted tuple
END

In distributed setting this approach didn't work. Inserted tuple is probably exclusively locked and cannot be updated in a trigger. Or maybe I got confused because of other deadlock issues like when writing a wrong data type to the destination field...

Be careful with data types!

Declare parameters to the type you are going to write, not the type you are going to read. This is critical in UPDATE transactions.

An example:

1. You update a value in [SQL2000].[legacy].[dbo].[person], firing a trigger
   
2. In the trigger, you read the updated value in a parameter
3. You update [SQL2005].[webapp].[dbo].[person] with the parameter

In phase 2 you should declare the parameter to the data type you are writing in phase 3. It helps to name the parameter according to the destination field.

DECLARE @destination destination_data_type
SET @destination = (SELECT source_field FROM Inserted)

Updating a text or ntext field is impossible this way because you cannot read them to a parameter at all. But hey, wait a minute... why use parameters in the first place?

Because all update statements are not equal!

You can try to propagate an update to webapp.person without parameters like this:

SET @personID = (SELECT personID FROM Inserted)

UPDATE [SQL2005].[webapp].[dbo].[person]
SET [Firstname] = Firstname
[Lastname] = Lastname
FROM [legacyapp].[dbo].[person]
WHERE personID = @personID

Unfortunately, this method does not work. You can't select new values directly from legacyapp tables directly in the UPDATE statement of webapp table. Don't ask me why. I guess this has something to do with MSDTC, but the query just hangs forever.

You do have to read new values beforehand to parameters:

UPDATE [SQL2005].[webapp].[dbo].[person]
SET [Firstname] = @Firstname
[Lastname] = @Lastname
WHERE personID = @personID

Finally, don't trust what I say!
(but you still might want to do as I did)

What I have really done is just an example of cargo cult programming. I did not understand the causes of these issues but found solutions anyway by trial and error.