How to use FORALL SAVE EXCEPTIONS but still get the right transactions?
steffiApr 5 2011 — edited Apr 6 2011So currently I have objects of type A, B and C
A is the parent of B and B is the parent of C
My process tries to move A's with all their children and their children.
Currently it uses FORALL at each level of the hierarchy
The FORALL have SAVE EXCEPTIONS
What I want to know is if I have SAVE EXCEPTIONS in multiple FORALLs how can I arrange so that if any given instance of A fails that I also fail as part of the same transaction all related children.
Currently I think I have to run without constraints due to potential deadlock. This isn't final as it's still being investigated but assuming I can only do what I want without constraints enabled I need to be able to run where by if any given A fails I've also failed it's children. I do not want children at the destination without their parents. Even though I process an A and it's children all in the same transaction and then COMMIT it seems I'm ending up with things that have been inserted at the destination but have not been deleted at the source. This is unexpected because I have the insert and deletes in the same transaction in that I only have one COMMIT and the end of my logical unit of work.