🚀 go-pugleaf

Jonathan Neve wrote:

> I've been thinking about designing a replicator.
> I've come across a problem. I was thinking of using triggers to
> generate a log of all changes in a log table in the database (I think
> this is how other replicators work, right?). 

	It's often how asynchronous replicators work.

> However, I need to do
> 2-way replication. So when database A receives a change from database
> B, it will apply the changes to itself. But then, the triggers will
> get fired in database A, and the same change will get sent back to
> database B. How can this sort of thing be prevented?

	There are a couple of strategies:

o  Log the username of the last user to change the record, and give the
replicator its own user account.  Don't log any change made by the
replicator's account.
o  Change the replicator to use an UPDATE statement which won't affect
the row if it already has the values you're replicating.  This way
there will be no replication log entry for the "same" change.  Note
that this is tricky when Blobs are involved.
 
> Also, another similar problem I've run into, is that if I have
> triggers for synchronising all data tables, what happens if there are
> already other triggers? For example, suppose table A has an After
> Update trigger that inserts data into table B. If I use triggers to
> generate the log, I will get the update statement for table A as well
> as the insert statements for table B. However, when the changes are
> applied on the other database, the inserts on table B will be done
> twice due to the triggers! How can I get around this?

	You should design your replication schema to only replicate A, not B.
This is a good example of why replication is not as simple as flipping
a switch -- designing a good replication schema can be quite a bit of
work, even if you already have a fully functional replicator.

> Is there any alternative to using triggers? What do other replicators
> do?

	One alternative is to use a change stamp and SELECT records changed
since the last replication.  This can be resource-intensive if changes
are infrequent and you are replicating a lot of tables.  OTOH if
changes are frequent or the number of tables you're replicating is low
it's not too bad.  But you'll need to use a trigger/log to handle
DELETEs, even with this strategy.

	Another method of replication -- impossible on IB, and expensive on
Oracle -- is synchronous replication.  This essentially tells the
engine to make multiple DBs on multiple servers shadows of each other.
It solves some problems associated with replication and causes others.
 
	-Craig

-- 
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
  Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase in Multi-tier World -- Use multiple RDMs, IB features in 
  your appserver: http://delphi.weblogs.com/stories/storyReader$195