I have an application in VB.net that does inserts into three different tables. Insert into table 1 is done by DB2 stored procedure1. Insert into table 2 DEPENDS on the success of the insert into table 1 and is achieved by a DB stored procedure 2. Insert into table 3 depends on success of inserts into table2 [hence success of insert into table1] and is done with a DB2 stored procedure 3.
Now, when i am at the insert into table3 [assuming that inserts 1 and 2 have completed successfully] and the insert FAILS, I want to ROLLBACK the inserts into table 2 and inserts into table 1. Now, I cannot change the stored procedure because this is a 3 tier development architecture where the database objects are shielded from the "view" layer [this is not a WEB APP]. I have three methods that do these inserts.
I have heard of this "transaction scope" object in .net but I do not see a "rollback" option in there. So, my question is how would I go about coding the "rollback" transaction option to this scenario? More than the commit, I need the rollback.
App: Vb.net, database:DB2 on Z/os[mainframe]