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