@@TRANCOUNT Query on INSERT

Question

Can anyone explain the following?

 

Code Snippet

create table trancount

(dt datetime, trn int)

 

select getdate(), @@trancount -- 0 here

 

insert into trancount (dt, trn)

select getdate(), @@trancount

 

select dt, trn -- 2 here!!!

from trancount

drop table trancount

 

 

I don't understand why the @@trancount is 2.

 

Answer 1

Hi,

 

The insert statement is actually made up of two transactions, a selection that is subsequently placed into another transaction that performs the INSERT part.

 

Code Snippet

insert into trancount (dt, trn)

select getdate(), @@trancount

 

 

 

You can see that the above code block is responsible for two transactions by wrapping the section in an explicit transaction.

 

Code Snippet

create table trancount

(dt datetime, trn int)

go

 

BEGIN TRANSACTION

select getdate(), @@trancount -- 0 here

 

BEGIN TRANSACTION

insert into trancount (dt, trn)

select getdate(), @@trancount

COMMIT TRANSACTION

 

 

select dt, trn -- 2 here!!!

from trancount

 

GO

COMMIT transaction

drop table trancount

GO

 

 

Answer 2

@@trancount is typically used with explicit transaction not implicit.

 

A.D.T.