Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-31 : 22:44:11
|
| Ken Wong writes "I have a couple of tables in different databases that basically share the same information. The problem is I need to keep the data synchronized between the two tables in real-time.To do this, I thought about using insert and update triggers. However, a problem with using triggers on both tables is that you end up in an endless loop when you insert a new record into one table and it gets inserted into the other table via the trigger. This will trigger the insert trigger in the other table and thus try to re-insert the record into the first table...and so on.I've tried adding Transact-SQL code to check if the record already existed by comparing the data in each of the fields, and if all the fields are the same, then I do not insert the record into the second table.I was wondering is there another way to synchronize the data? If triggers is the answer, then how would it be implemented.ThanksKen" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-03-31 : 23:30:28
|
| put a source field on the tables and set it in the trigger so you can tell where the data came from.If the source is the other system then ignore it in the synchronisation trigger.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-03-31 : 23:33:45
|
Ken you can do that with couple of ways.1) Hard coding comparision logic and insertion statements in a Sp and running it prediodically as a batch2) Thru triggers , you have to disable nested trigger execution.3) Can opt for Replication too.SNIPED HTH--------------------------------------------------------------Edited by - Nazim on 03/31/2002 23:36:34 |
|
|
|
|
|
|
Triggers and synchronizing tables