I'm calling a store procedure from BW and I'm trying to undertsand the error handling behaviour for this. For example I'm calling store procedure 'A' from BW which updates main tables and these tables inturn has a trigger to update the same data in history tables. The store procedure and some other JDBC activities are kept in TRANSACTION group in BW.
1) When the store procedure 'A' errors out-- everything is rolled back as expected.
2) In this scenario the store procedure 'A' runs successfully and the success response is received to BW and the flow exited that transaction group (which means BW commited the transaction) and that thread is ended. But when updating (because of trigger) the history tables fails, the DB (oracle db) itself is rolling back all the session information present in it which includes both Main and History tables.
a) Is this an expected behaviour?
b) Even though BW commits the transaction, can DB still roll it back on Main tables for that session?
c) DB is saying that they are propagating the error to the main calling store proc 'A', but I'm not seeing any error on BW which I'm assuming the thread was already completed on BW side.
Please let me know your inputs.