S
steven.p24
Hi,
i have requirement to synchronize few tables on different databases.
for that i'm doing below mentioned steps
(Note: i'm using .NET frameowork & trigger logic beacausei have to
perform few additional steps while synchronizing DBs which cant be
done using SSIS or something else)
I'm following below steps to synchronize two tables in different
database
1. create insert/update/delete triggers on tables to capture the event
on table and insert primary key of record in some different temp
table.
2. my c# application polls this temp table and when it found entry in
that table it will fetch the the original record from DB using primary
key.
3. c# application performs the additional step and also synchronize
the remote database as well.
so far it sounds good But issue is i have to capture the insert/
update/
delete events of remote database and update the local database as well
i.e. 2 way communications. so i will have same trigger and c#
application logic at the remote site as well...
ISSUE: it will create some sort of infinite loop, say site A has
inserted record, using trigger of Site A i'll found the event has
occurred and will try to synchronize site B as well i.e. insert record
at side B and this will fire trigger at site B and it will try to
synchronize site A for this event and go on on & on....
can anyone tell me how to avoid such scenarios? 1 solution might be,
to disable the trigger on remote DB while i'm synchronizing the remote
DB and enable it when finished but problem in this approach is if some
one inserts new record at site B during this interval i will be not
able to capture the event as trigger is disabled.
Can you please suggest any solution to such scenario?
PS: try to stick this approach of trigger and c# application as this
is really something i want.
i have requirement to synchronize few tables on different databases.
for that i'm doing below mentioned steps
(Note: i'm using .NET frameowork & trigger logic beacausei have to
perform few additional steps while synchronizing DBs which cant be
done using SSIS or something else)
I'm following below steps to synchronize two tables in different
database
1. create insert/update/delete triggers on tables to capture the event
on table and insert primary key of record in some different temp
table.
2. my c# application polls this temp table and when it found entry in
that table it will fetch the the original record from DB using primary
key.
3. c# application performs the additional step and also synchronize
the remote database as well.
so far it sounds good But issue is i have to capture the insert/
update/
delete events of remote database and update the local database as well
i.e. 2 way communications. so i will have same trigger and c#
application logic at the remote site as well...
ISSUE: it will create some sort of infinite loop, say site A has
inserted record, using trigger of Site A i'll found the event has
occurred and will try to synchronize site B as well i.e. insert record
at side B and this will fire trigger at site B and it will try to
synchronize site A for this event and go on on & on....
can anyone tell me how to avoid such scenarios? 1 solution might be,
to disable the trigger on remote DB while i'm synchronizing the remote
DB and enable it when finished but problem in this approach is if some
one inserts new record at site B during this interval i will be not
able to capture the event as trigger is disabled.
Can you please suggest any solution to such scenario?
PS: try to stick this approach of trigger and c# application as this
is really something i want.
Check your actual SQL product's