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.
Bluesky
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.