Does SQL ROLLBACK undo the enabling or disabling of triggers?

Question

Let's say I have a SQL script that looks something like this:

BEGIN TRAN
BEGIN TRY
    DISABLE TRIGGER dbo.myTrigger ON dbo.myTable;

    --do something that requires the trigger to be disabled, but which might throw an error

    ENABLE TRIGGER dbo.myTrigger ON dbo.myTable;
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    THROW;
END CATCH

If an error gets thrown in the "do something" part, there are two possible things that could happen:

1. Because the ENABLE TRIGGER statement is never hit, myTrigger remains disabled.
2. The ROLLBACK statement in the CATCH block rolls back everything that was attempted in the transaction, including the DISABLE TRIGGER statement, meaning myTrigger remains enabled.

I would intuitively expect 2) to be true, but a colleague of mine suspects that 1) is true instead, and you would have to manually re-enable the trigger inside the CATCH block. Neither of us could find anything on Stack Overflow or elsewhere about which interpretation is correct. So here's the question: does SQL's ROLLBACK statement roll back the enabling or disabling of triggers?

Answer 1

Having tested this myself, it seems that ROLLBACK does indeed undo the enabling and disabling of triggers. If I run the following script:

BEGIN TRAN
BEGIN TRY
    DISABLE TRIGGER dbo.myTrigger ON dbo.myTable;

    RAISERROR('test', 11, 11);

    ENABLE TRIGGER dbo.myTrigger ON dbo.myTable;
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    THROW;
END CATCH

Then run the following query:

SELECT is_disabled FROM sys.triggers WHERE name = 'myTrigger'

I get back "0", meaning that the DISABLE TRIGGER statement was rolled back.