• Good question. A slight variation on the question helps explain the behavior of @@TRANCOUNT which isn't very clearly stated in the BOL.

    A trigger operates as if there were an outstanding transaction in effect when the trigger is executed. This is true whether the statement firing the trigger is in an implicit or explicit transaction.

    So consider the following.

    CREATE TABLE TranTest

    (num int)

    GO

    CREATE TRIGGER TrgTranTest

    ON TranTest

    FOR INSERT

    AS

    BEGIN

    SET NOCOUNT ON

    PRINT 'TranCount is ' + CAST(@@TRANCOUNT as VARCHAR(10))

    END

    GO

    SET NOCOUNT ON

    INSERT INTO TranTest VALUES (1) -- Implicit Transaction (@@Trancount = 1 inside of Trigger)

    GO

    SET NOCOUNT ON

    BEGIN TRAN

    BEGIN TRAN

    INSERT INTO TranTest VALUES (2) -- Explict Transaction (@@Trancount = 2 inside of Trigger)

    COMMIT TRAN

    COMMIT TRAN

    GO

    SET NOCOUNT ON

    BEGIN TRAN

    INSERT INTO TranTest VALUES (3) -- Explicit Transaction (@@Trancount = 1 inside of Trigger)

    COMMIT TRAN

    GO

    Which produces the following output.

    TranCount is 1

    TranCount is 2

    TranCount is 1