tommyh (9/23/2010)

---

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

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