Subject RE: [firebird-support] Replicator pseudo-code (sorry, it's a little long...)
Author Alan McDonald
> 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.

I handle this a little differently to how say Jason Wharton suggests in his
paper on "an auditable series.. etc"

He states that it is OK to "clean delete" e.g. an invoice to regain the
invoice number. In so doing he suggests ways of re-using the invoice number
(recycling). His auditors must be very lenient compared with any I have
dealt with. Mine would never let this be acceptable. Once an invoice of bill
or note is drawn, it's done, there's no way back unless you issue a credit
note or similar for the transaction. In this way, I let people draft
whatevevr note they wish but once they hit the submit button, there is no
chance of a rollback, the note is there for good and can only be reverses by
a similarly non-rollback-able submission to create the opposite transaction.
This is also, therefore full transparent and auditable. So generators do
their job perfectly for me here.

> >
> >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
> >too.
> >
> 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.

If you go to the Dunston Thomas site
you'll find a great little replicator written by Alex Malinin and Paul Beach
et al. In there you will find a replication manager which installs the REPL
triggers for you across the db in one operation. It doesn a whole lot of
other nice things too, including setting up a good replication model for you
to see.