How can I copy changes from one table to another, while avoiding an infinite loop, in SQL?

Question

I have two databases, A, and B. On database A, I have a table X, with 10 columns in it. On database B, I have a table Y, with 7 columns in it. 4 of the columns from these tables match, and whenever one table updates one or more of these columns, I need the other table to update these columns. How can I do this? Replication wouldn't seem to work because the table structures are different, and insert/update triggers would seem to create infinite loops.

Answer 1

To avoid the loops you could have your triggers not do an update if the values are equal?

Answer 2

Replication works fine on tables with different structures, see Filtering Published Data.

As for triggers to avoid infinite loop, you would use context information to set up that you're currently in a 'replication' trigger so that you'd avoid looping, see Using Session Context Information:

• in the trigger, you check if CONTEXT_INFO() says you're already in a trigger.
  • if YES, do nothing (return)
  • if NO, SET CONTEXT INFO to reflect your operation
• copy the data
• when the 'replica' trigger fires, will find your context info and do nothing
• clear context info
• return