Using SQLA 18.104.22.1686
Situation: - Client database contains FKs with cascading deletes. - Oracle consolidated contains same FKs, but not with cascading delete clause. - error occurred during ML sync when deleting a row with child rows.
My Initial Assumption: - When I issue a delete on a parent row, the cascading delete option causes deletes to be issued for each child row. - these deletes will be in the transaction logs, and thus used by MobiLink. - ML orders the upload data stream by examining FK definitions to insure referential integrity will be maintained. - Therefore, the upload stream will issue child deletes on the consolidated database before the parent row. - MobiLink will have done all the work for me and life will be good.
But the error tells me my Initial Assumption must be faulty. I am asking 2 questions: 1. Where did my Initial Assumption get off track? 2. If I merely add “ON DELETE CASCADE” to the FKs on the consolidated, will that eliminate the error?
to your first question:
Trigger-generated actions are not contained in the updload stream by default. This can be changed by the SendTriggers extended option. Note that all RI actions (except RESTRICT), i.e. CASCADE, SET NULL and SET DEFAULT, are implemented by system triggers.
You may also check the similar question will-a-publication-upload-trigger-generated-data-to-mobilink.
To your second question: I guess this should work but I do not know the Oracle implementation. I remember having had some trouble with cascading deletes and ML used between SQL Anywhere and MS SQL Server, though. In that case, SendTriggers might be the easier solution...