How can we improve SQL Data Sync?

Support changes to composite keys

Due to a legacy database and codebase issue I've encountered a situation where an update made to a key comprising a composite key on a table causes issues with the Sync process.

A table has
key 1: A
key 2: B

If an update is made to this record to change key 2 to instead have a value of C, Data sync doesn't like this.

Data sync presently only takes the insert values in the trigger but doesn't consider the potential for a corresponding delete value where the key may be different. In this case it could instead look at the delete values, determine if the key has changed and if so mark the existing record for deletion during the next sync.

What happens today is that the Sync process gets confused and you end up with duplicate records across the database.

Of course ideally there would never be a situation when composite keys are changed but rather unfortunately sometimes you have to work with legacy databases and code where your predecessors simply didn't know that making such a change is not the best practice.

2 votes
Vote
Sign in
(thinking…)
Password icon
Signed in as (Sign out)
You have left! (?) (thinking…)
Denis Pitcher shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

0 comments

Sign in
(thinking…)
Password icon
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base