🚀 go-pugleaf

It seems to me that you are doing this in the wrong order. I would insert into table 2 first, and then insert into table 1 with the appropriate FK value - it shouldn't allow you insert with a null PK value and then patch it up later.

If replication tries to add the table 1 entry first it will fail, however it will succeed with the table 2 entry. On the next replication the table 1 entry will replicate.

HTH

Mark





"Daniel Alvarez" <dalvarez1973@yahoo.com.ar> wrote:
>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 Tecnol�gicas 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
>
>