Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
everurssantosh
Starting Member
9 Posts |
 Posted - 2007-11-07 : 08:17:58
|
| I have one store procedure which is having a transaction.Inside this transaction i am calling one more procedureThe child procedure is also having one transaction.now the situation is if my child procedure succeeds and parent failsi want to roll back the childhow to achieve this? |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-11-07 : 08:28:14
|
| If you write ROLLBACK TRANSACTION in parent SP, it will rollback entire transaction, including child transaction as well.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
everurssantosh
Starting Member
9 Posts |
Posted - 2007-11-07 : 08:43:08
|
| Thanks for the reply..you are right.but by the time it returns to the parent procedure, the child procedure will be commiting its transaction after successful execution.but after the execution of the parent procedure , if there is an exception in the parent procedure inside the transaction, it needs to rollback the whole transaction including that of the child procedure..so rolling back after the execution , will it rollback the transaction in the child procedure too? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-07 : 08:48:55
|
| "will it rollback the transaction in the child procedure too?"Yes |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-07 : 08:50:55
|
| [code]-- OuterBEGIN TRANSACTION -- Transaction count = 1-- Call ChildBEGIN TRANSACTION-- Transaction count = 2...COMMIT -- Transaction Count decremented, now 1. No commit yet ...RETURN-- Back in outerCOMMIT -- Transaction Count decremented, now 0. Commit will now happenORROLLBACK -- Everything rolled back to matching BEGIN TRANS - i.e. the one at the top of this example[/code]Kristen |
|
|
|
|
|
|
Transaction Help