Subject Re: [firebird-support] Replicator pseudo-code (sorry, it's a little long...)
Author Jonathan Neve
Alan McDonald wrote:

>>>(auditable series is taken via a
>>>differnet route).
>>What do you mean by this?
>Often people want their PKs to have meaning wihtin their database. They also
>want to allow users the ability to edit these PK values. Then on top of
>that, they want these values to not have gaps in their sequence.
>This is a whole other discussion about which I am rather outspoken.
Yes, this is a problem that I'm very familiar with! :-(
Now, I try to avoid this sort of thing by using a separate column (not
the PK), with a UNIQUE constraint. Ensuring that there are no gaps is an
anoying problem though. If you have any ideas, I would be very interrested.

>>Suppose table A has a trigger (after insert or after update), which
>>inserts records automatically into table B. This happens all the time,
>>while people are using the database, quite independantly from the
>>replicator. Obviously, each record that gets inserted into table A
>>generates an line in the log table, and the same goes for table B. Then,
>>the replicator kicks in, and replicated table A first. In the remote
>>database, the same thing is going to happen : when I insert a record
>>into table A, a certain number of records are automatically going to get
>>inserted into table B. This has nothing to do with replication, that's
>>just the way I designed my database (admittedly not very well!). So then
>>afterwards, table B will get replicated, and all the same rows are going
>>to get inserted again. So it will either generate a PK violation, or, it
>>the PK values aren't the same, it will simply duplicate all the lines.
>>So I can't think of any other (nor simpler) solution to this problem
>>than to do as I did, that is, to not record the type of change that
>>occured, but merely indicate which record was affected. That way, in the
>>case I described above, we can simply notice that the records are
>>already present, and so we update rather and inserting. This is very
>The automatic trigger that inserts a record into table B on insert/update to
>table A will not fire under replication since the USER is recognised here
Yes it will. Of course, that depends how you define your trigger, but if
you have a lot of triggers (as was my case), it could be very tedious to
change each one of them so that they don't fire under replication. Only
the triggers I added for replication purposes work that way, all other
triggers fire all the time.

Jonathan Neve.

[Non-text portions of this message have been removed]