Question about replication issue

Question about replication issue
Actually I'm using replication with Interbase/Firebird, I have a problem and
I wanted to know if someone knows how to solve this, and if people from SQL
Server and Oracle replication knows if this happens too with that engines (I
hope not)
I start describing a the tables for the example:
/* Table: TABLE1 */
CREATE TABLE TABLE1 (
ID INTEGER NOT NULL,
NAME VARCHAR (40) CHARACTER SET NONE COLLATE NONE,
FK_TABLE2 INTEGER);
ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (ID);
ALTER TABLE TABLE1 ADD CONSTRAINT R_T2_T1 FOREIGN KEY (ID) REFERENCES TABLE2
(ID);
/* Table: TABLE2 */
CREATE TABLE TABLE2 (
ID INTEGER NOT NULL,
DESCRIPTION VARCHAR (40) CHARACTER SET NONE COLLATE NONE);
ALTER TABLE TABLE2 ADD CONSTRAINT PK_TABLE2 PRIMARY KEY (ID);
That gives me two tables, one with a relation to the other with a foreign
key.
I'm replicating with a one-way schema, and the replication manager creates
the needed triggers and tables in the master database. In one of that
tables, the trigger saves information about the insert, update or delete
occurred. The problem is that in the table (REPL_LOG), only stays a
reference to the record inserted (for example), not the real data inserted.
I give an example:
I insert a record into TABLE1:
ID = 1
NAME = 'My Name'
FK_TABLE2 = Null
The trigger in TABLE1 saves in REPL_LOG = 'I' (Insert), 1 (the PK of the
Table), TABLE1 (an internal ID of the TABLE1 inside the internal dictionary
of the replication schema)
Now, I insert a record into TABLE2:
ID = 1
DESCRIPTION = 'My Description'
The trigger in TABLE2 save in REPL_LOG = 'I', 1, TABLE2
Now, comes the problematic part. I update TABLE1, setting the FK to TABLE2.
FK_TABLE2 = 1 (the PK value of the inserted record in TABLE2)
This creates a record in REPL_LOG like this: 'U' (Update), 1, TABLE1
Up to this, It's all OK with the master database. But the problem comes when
It's time to replicate. Supposing that the replication does not occur
between the prior changes to the database, and all of that comes after
modifying the master database:
The replication fires:
The server finds all records of REPL_LOG in master database, so It first
tries to insert a record in TABLE1, but, here's the problem: the replicator
doesn't know what fields the real insert filled, and it tries to insert ALL
fields (FK_TABLE2 too). So, the replicator server tries to insert a record
like this:
ID = 1 (OK)
NAME = 'My Name' (OK)
FK_TABLE2 = 1 (Ohhh, problem, FOREIGN KEY CONSTRAINT ERROR, It doesn't exist
a record in TABLE2 with PK = 1), (Yet)
So the replication fails...
I can think that in a following step, I can be solved, but I find this
behavior a BIG problem, because this can happen in a lot of situations.
My question is if there is a solution or workaround to solve this with IB
Replicator, and I wanted to know if SQL Server and Oracle replication works
the same as Interbase one.
Are they smart enough to get rid of this problem?
Thank you very much.
Daniel Alvarez
IMS Soluciones Tecnolgicas S.A.
Buenos Aires - Argentina
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.729 / Virus Database: 484 - Release Date: 27/07/2004
Daniel,
in merge, problems like this can be alleviated using the NOT FOR REPLICATION
attribute for the foreign key (see
http://support.microsoft.com/default...b;EN-US;308266).
For transactional, if the articles are part of the same publication, then
AFAIK the changes should be applied in the same order on the subscriber.
HTH,
Paul Ibison