allow cascade delete over multiple publications Mobilink

[Former Member]

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-cas... )

I would like to apply this logic over multiple publications as well. Is this possible?

Thank you,

[VolkerBarth]

This results in Referential integrity problems when synchronizing. Because publication 1 attempts to delete the Employee while there are still EmployeeJobs available.

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.

[Former Member]

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)):

I. 2015-11-03 19:23:37. Download stream: received 14013 bytes from MobiLink server.
I. 2015-11-03 19:23:37. Processing download stream ...
I. 2015-11-03 19:23:37. Referential integrity violations exist in table EmployeeJobs, role FK_dbmydb.EmployeeJobs_dbmydb.Employee_EMP_EmployeeID that cannot be resolved because table EmployeeJobs is not involved in synchronization.
E. 2015-11-03 19:23:37. SQL statement failed: (-194) No primary key value for foreign key 'FK_dbmydb.EmployeeJobs_dbmydb.Employee_EMP_EmployeeID' in table 'EmployeeJobs'
I. 2015-11-03 19:23:37. Unable to COMMIT
I. 2015-11-03 19:23:37. Download stream processing failed
I. 2015-11-03 19:23:37. ROLLBACK
I. 2015-11-03 19:23:37. End synchronizing subscription(s) 'PubFirstSync'
I. 2015-11-03 19:23:37. Disconnecting from MobiLink server
I. 2015-11-03 19:23:38. Synchronization completed

[VolkerBarth]

FWIW, A pair of "pre"-tags is helpful to format code within comments, I have applied them to the log snippet...

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 ?

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.