March 25, 2009 at 8:29 am
According to the BOL:
The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.
And this code:
CREATE TABLE #TEMP (
value int
)
SET NOCOUNT ON;
PRINT @@TRANCOUNT
BEGIN TRANSACTION;
PRINT @@TRANCOUNT
insert into #TEMP select 1;
insert into #TEMP select 2;
PRINT @@TRANCOUNT
COMMIT TRANSACTION;
PRINT @@TRANCOUNT
SELECT * FROM #TEMP;
PRINT '-----';
PRINT @@TRANCOUNT
BEGIN TRANSACTION MyTran;
PRINT @@TRANCOUNT
insert into #TEMP select 3;
insert into #temp select 4;
PRINT @@TRANCOUNT
ROLLBACK TRANSACTION MyTran;
PRINT @@TRANCOUNT
select * from #TEMP;
drop table #temp;
SET NOCOUNT OFF;
Why does 'ROLLBACK TRANSACTION MyTran;' affect the rowcount? Isn't this what BOL was talking about? Why would it be useful for 'ROLLBACK TRANSACTION MyTran;' to not affect @@TRANSACOUNT?
---
Dlongnecker
March 25, 2009 at 3:10 pm
If you re-read the BOL entry you will see it is behaving exactly as defined. I think you may be confusing a rollback of a named transaction (which is what your code is doing) with a rollback to a savepoint (which must be defined with a savepoint statement).
Named transactions are a bit of an oddity really as the rollback always rolls back the lot - personally I have never found any benefit in named transactions.
Mike John
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply