p0peye (9/23/2010)

---

Please correct me if i am wrong...

My understanding is, you wont be able to commit or rollback inner transaction separately as the outer transaction will override with its commit/rollback command. If this is true, why do we need to use nested transactions or in which scenario nested transactions can be used?

let me know if my question is not clear


_________
Andrew

tommyh (9/23/2010)

---

Now this is a bit out off my league. But i would say that your partly correct. You cant commit an inner transaction and then later rollback the outer one. You can however using "save transaction" rollback a part and then commit the rest. Like this

 
create table #TransTest (i integer)

begin tran OuterTran
  insert into #TransTest (i) values (1)

  save tran InnerTran
  insert into #TransTest (i) values (2)
  rollback tran InnerTran

commit tran OuterTran

select * from #TransTest

drop table #TransTest

Here only one value is inserted into the table the second row is rolled back.

/T

The following error is raised "Cannot roll back InnerTran. No transaction or savepoint of that name was found." and All transactions are rolled back and an empty recordset is returned.