-
I guess I'm having a bit of a logic problem. My procedure should update table1, and then call another proc to update table2. However, if the update to table2 is unsuccessful for any reason (the proc sends back a success code), I need to rollback the update to table1. The problem is, my rollback is not working; the update is happening anyway. Here is the gist of the code:
begin
UPDATE TABLE1
set ....... where....;
success_flag := 'N';
update_table2(arg 1, arg 1, success_flag);
if success_flag = 'Y' THEN
COMMIT; /* commit the update to table 1 */
else
ROLLBACK; /* rollback the update to table 1 */
end if;
EXCEPTION
when others then
etc. etc.
ROLLBACK;
end;
I could update table2 first, and if succesful update table1, but then I Have the same problem if the update to table1 is unsuccessful (less likely), I would need to rollback the update to table2.
Any help is appreciated.
-
Does update_table2 have a COMMIT in it?
- Chris
-
Yes, update_table2 has it's own COMMIT
-
There's the problem.
Your answer did not imply that you recognized that as the culprit, so I will explain.
You only have a single transaction in a single connection.
...
Called proc1
---...
---INSERT
---...
---Called proc2
------...
------COMMIT; -- Transaction 2 commits, including insert
------
------end;
---...
---ROLLBACK; -- only changes in transaction 3 are rolled back.
HTH,
- Chris
-
Logic for table 2
What is your logic for table2? It looks like wether the table2 update works or no it always passes back a success_flag ='Y'.
-
I'm learning alot! Thanks to all for your input. I removed the commits to the main calling procedure, and then my rollback works, plus I think it is better structured now.
-
Originally posted by chrisrlong
...
Called proc1
---...
---INSERT
---...
---Called proc2
------...
------COMMIT; -- Transaction 2 commits, including insert
------
------end;
---...
---ROLLBACK; -- only changes in transaction 3 are rolled back.
Oy, I hate this forum software sometimes. Here is what it was intended to be, just for posterity:
(last COMMIT or ROLLBACK)
(transaction 2 is started)
...
Called proc1
---...
---INSERT
---...
---Called proc2
------...
------COMMIT; -- Transaction 2 commits, including insert
------(transaction 3 is started)
------end;
---...
---ROLLBACK; -- only changes in transaction 3 are rolled back.
- Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|