> I think using one central log table is much better. This means,
> obviously, that you won't have all the fields of each table in the log
> table, but only the primary key(s). I guess this could be seen as a
> disadvantage, but in practice, I don't see what problems it could bring.

I agree - central log table with PKs works for me

>
> Another thing is that I found in my experience that having a log
> containing each change that was made to the database, indicating whether
> it was an insert, a delete or an update, and then trying to blindly
> perform the same operation on the remote database, is not a flexible
> structure at all. I had started out like this, but I found that it
> brought many problems. I know it's possible, but it's not flexible.
>
> Consider the following examples :
>
> - Suppose someone creates a record, realises that it was a mistake,
> and deletes it right away. In the log you would have two lines. The
> first would say that the record was inserted, and the second would say
> that it was deleted. So the replicator trys to create an insert
> statement for a record that doesn't exist any more. But it can't because
> in order to make the statement, it needs the values from all the fields.
> So this fails.

It doesn't fail, since the select of the now non-existent record returns no
values, so there is nothing to insert - this is not a failure. The delete
will also not fail since delete from table where PK=4 when no PK 4 exists is
a silent action as well - no error.

>
> - Suppose a primary key value gets changed. The same problem as above
> occurs. You have a line saying to insert a certain record, but by the
> time you try to create that SQL statement, that record no longer has the
> same primary key value, and so you can't find it. At first I thought I
> could get around this problem by using another field called PKOldValue,
> in which I would put the old primary key value every time it gets
> updated. But this doesn't work either, because the initial insert still
> couldn't take place.

Primary keys should never be changed - this can be a long discussion but I
never shange my PKs - it's a golden rule. (auditable series is taken via a
differnet route).

>
> - Suppose you have a trigger on one of your tables (Table A) that
> inserts a record into another table (Table B). So you insert a record
> into table A, and a record gets automatically inserted into table B. In
> the log, you therefore get two lines, one for each table. But when you
> replicate table A, the insert gets done automatically into table B on
> the other server. When you then subsequently replicate table B, the
> insert fails because it has already taken place.

No - because every replication trigger recognises the user (e.g. REPL) and
only inserts a log when the user is not REPL. All replicaton is done by the
REPL user.


But there's lots of ways to skin a cat
Alan