Replicating AUTOINCREMENT values from trigger generated transactions

[#1]

We have this consolidated database and remote database(s) running SQL Anywhere 12 and using SQL Remote for replication. In this example both tables are identical on each site and are members of the publication.

TableA is used by our applications, so rows will be added directly to this table. In addition we have a TableB used by another application. Both tables are residing in the same database.

My scenario is a trigger-based synchronization (comment: Trigger based synchronization might not be the best option, I am open for accepting other solutions) from TableB to TableA. When a row is Updated/Deleted/Inserted in TableB the same changes shall appear in TableA.

Problem: TableA's PK is a GLOBAL AUTOINCREMENT value, hence an insert to this table will generate a new ID, say 1. When SQL Remote transfers this to the other database the trigger on the other database will generate a new ID, say 2. We then have the situation where the same row has two different primary keys in those databases. Add 40 remote databases and the same row will have 42 different primary keys, one for each database. TableB does have it's own primary key but since this is a generic integration logic we don't want to reuse primary keys from other systems.

I am aware of the option to replicate trigger-based transactions, but I cannot see how that will solve the problem, the only result as I can see it is that both row 1 (from trigger insert remote) and row 2 (from trigger insert local) will exist in the database.

---

I am trying to write down some logic that allows me to keep the AUTOINCREMENT value generated at the remote site and use this for the trigger-generated INSERT at the local site.

[regdomaratzki]

On the assumption that TableA and TableB are both in the publication, the simplest solution might be to move the logic that inserts the row into TableA from the insert trigger of TableB to your application, or possible into a stored procedure.

Old Schema :

create table tableA (
  pkA integer primary key default global autoincrement,
  c1  integer not null
);

create table tableB ( 
  pkB integer primary key default global autoincrement,
  pkA integer not null references tableA,
  c2  integer not null
);

create trigger ai_tableA after insert on tableA
referencing new as nr for each row 
begin
  insert into tableB(pkA,c2) values (nr.pkA, nr.c1);
end;

New Schema (note table definitions unchanged) :

create table tableA (
  pkA integer primary key default global autoincrement,
  c1  integer not null
);

create table tableB ( 
  pkB integer primary key default global autoincrement,
  pkA integer not null references tableA,
  c2  integer not null
);

create procedure insert_tableA ( in @c1 integer )
begin
  insert into tableA(c1) values (@c1);
  insert into tableB(pkA,c2) values (@@identity, @c1);
end;

So, in your application, instead of executing "insert into TableA(c1) values (10)" you would execute "call insert_tableA( 10 )";