CODEWITHSUNDEEP
sql-server-2005
Brisbe
Brisbe

Reputation: 1628

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

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.

Upvotes: 0

Views: 365

Answers (3)

raja
raja

Reputation: 343

select * into NewTable from PastTable

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294407

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

Upvotes: 2

Conrad Frix
Conrad Frix

Reputation: 52675

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

Upvotes: 1

Related Questions