I have 2 database, on 2 remote pcs, let's call it dbA and dbB. There will be synchronized with each other.
New rows are allowed to be inserted to both of the database. To avoid primary key conflict, my table have an identity column as pk, which is an auto incremental seed, and a generated column that concatenate the DB name and auto incremental seed, that acts as
the 'real' pk for the table. I created a trigger, that will do the concatenation when a new row inserted to the table. So 3 rows are inserted into the 2 db's table respectively, the result will be like
for DB-A
id col1 col2
1 A1 cat
2 A2 dog
3 A3 elephant
for DB-B
id col1 col2
1 B1 cow
2 B2 sheep
3 B3 horse
I created scopes, and provisioning for these 2 DB. And to not sync the id column, I remove the column in the provisioning.tables, and use the isprimary to set col2 as the primary key in synchronization.
So when 2 tables are synced, the expected result will be something like that
for DB-A
id col1 col2
1 A1 cat
2 A2 dog
3 A3 elephant
4 B1 cow
5 B2 sheep
6 B3 horse
but instead of getting that result, this is what I get
1 A1 cat
2 A2 dog
3 A3 elephant
4 A4 cow (supposed to be B1)
5 B2 sheep
6 A6 horse (supposed to be B3)
Noted the 2 rows synced down, the col1 have changed based on the trigger. But what I don't understand is why is that so, and the result is not consistent, as id=5, the col1=B2 which is the same in DB-B. And when I looked into the tbl_tracking table, the
primary key still stored B1 and B3 instead of A4 and A6.
So my question is, during synchronization, when an insert is done on tbl, will it call the trigger that I created? And whether the answer is a yes or no, why is the data synchronized seems to be inconsistent? Could it be that I miss out something?
Can anybody shed some lights on this please?? Thank you so much.
