After Trigger to save data on based on uncommitted update

[daljitsb]

Env: Oracle ADF, Oracle 11g

Situation: Have ADF transaction processing 2 tables (a, b) data logic and committing them in same transaction.

ADF Application Transaction T1 starts
Table A, record A1 updated
Table B, new record B1 inserted --> After insert Autonomous database Trigger T1 fires
Trigger T1 logic: Based on A1 updated value, insert new Record B2 record in Table B and explicitly commit in trigger logic.
Transaction completes (commit)



Issue - B2 insert depends upon A1 commit, A1 gets committed only after Transaction T1 completes

Question: How can I make Table A A1 commited data avaiable to Trigger on Table B

[LKBrwn_DBA]

Env: Oracle ADF, Oracle 11g

Situation: Have . . . whatever ...
. . . . and explicitly commit in trigger logic.
Transaction completes (commit)

Issue - B2 insert depends upon A1 commit, A1 gets committed only after Transaction T1 completes

Question: How can I make Table A A1 commited data avaiable to Trigger on Table B

What business requirement forces you to have: "B2 insert depends upon A1 commit,"?

When you do commit in the middle of transaction with the "AUTONOMOUS_TRANSACTION pragma" it create a new SCN for that transaction and when the original transaction commits" you may end up with an SCN mismatch corruption error thrown. So you use it at your own peril.