|
Hi, I have 2 publications for 1 database and between those 2 publications there are tables with referential relationships. For example: publication 1 has "Employees" and publication 2 has "EmployeeJobs" Our software Always syncrhonizes publication 1 first and then publication 2. Unfortunately, sometimes EmployeeJobs and Employees are deleted at the same time in the Consolidated database. This results in Referential integrity problems when synchronizing. Because publication 1 attempts to delete the Employee while there are still EmployeeJobs available. (I could reverse the order of sync between the publications but then I will get problems when Adding new Employees with EmployeeJobs). When the 2 tables are inside 1 publication I know that Mobilink solves this issue for you (see thread: http://sqlanywhere-forum.sap.com/questions/25236/why-are-child-records-deleted-while-there-is-no-cascade-in-remote-db-mobilink ) I would like to apply this logic over multiple publications as well. Is this possible? Thank you, |
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 04 Nov '15, 04:11
question was seen: 2,539 times
last updated: 04 Nov '15, 08:35
Is my understanding correct that this leads to a failing synchronization, as ML does only handle RI violations between tables that are currently synchronized, and that does not apply here? What error do you get?
I would think you could add a trigger at your remote for table Employee to delete dependent EmployeeJobs - and possibly just perform that clean-up when the DELETE is run within MobiLink (I'm currently not sure how to detect that, AFAIK there's no builtin special value to detect that as with CURRENT REMOTE USER for SQL Remote).
When the second publication would then be performed and would try to download and apply the deleted EmployeeJobs records, that would turn into a no-op at the remotes as they are already deleted, but that should not be a problem.
Hi, I agree with the second part you are saying about the no-op.
Do I understand correctly that you suggest to add a trigger for automatic delete of child records... but you're not sure how to detect this ? would implementing a cascade delete on table level be possible solution? (as described here: http://sqlanywhere-forum.sap.com/questions/13984/change-all-foreign-keys-delete-to-cascade )
This is the Error That I get (I slightly modified the Original message so that it fits with Employee and EmployeeJobs (they are in reality different tablenames)):
FWIW, A pair of "pre"-tags is helpful to format code within comments, I have applied them to the log snippet...
No, I'm not sure how you can detect whether the DELETE statement is run from MobiLink and not from within your application. (I was relating to SQL Remote as that has such a builtin feature by querying CURRENT REMOTE USER.) With MobiLink, we have differentiated that by knowing that the ML session would be run with a particular user that is not used for "ordinary connections" and therefore have coded "if CURRENT USER = 'MyMobiLinkUserName' THEN ..." conditions without triggers.
What I do not know is whether you would need to make such a difference within your trigger logic, i.e. if it is desired to "automatically" delete dependent child rows only when within a MobiLink session or if this might be doable all the time. In the latter case, a FK with CASCADING DELETE might do as well.