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.
Replicating trigger generated transactions (dbremote -t) and wrapping the trigger body with IF CURRENT REMOTE USER IS NULL did the trick :)
answered 13 Dec '11, 04:35
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 )";
answered 12 Dec '11, 16:34
There seems to be a misunderstanding here:
(GLOBAL) AUTOINCREMENT is a column DEFAULT - i.e. if you add a new row and don't specify a value for that column (say, pkA), it will get a value automatically. And you usually will then have to use "SELECT @@identity" to get the freshly created value. - However, if you insert a row and specify a value for that column, exactly that value will be inserted (if there's no PK violation, apparently!).
When SQL Remote runs, it sends "complete SQL Statements" to the remote databases (unless you have omitted the pkA column from the article definition), including the generated default values, and therefore the generated pkA value in the first database will be inserted in the remote database as is. As a consequence, the row should be stored with identical values at each site it belongs to.
Your repication setup needs to have the same rows identified with the same PK, otherwise something strange is happening...
The reason to use DEFAULT GLOBAL AUTOINCREMENT with according DatabaseIDs is just to assure that two different databases won't create the same PK values for the rows. - By design, rows created a different remotes are different rows.