TSQL Create trigger with transaction and try catch block

Question

Hi ! i have some questions about a transaction in the trigger, for which I haven't found an answer yet.

CREATE TRIGGER A_AI ON A
AFTER INSERT AS

    BEGIN TRY --is the try block 1 transaction ? or do I have to begin the transaction?

        --BEGIN TRAN: may I start the transaction like this?
        -- SOME DANGEROUS OPERATIONS

        COMMIT --has this sense here?
    END TRY
    BEGIN CATCH --silent catch no rasing errors.
        ROLLBACK TRANSACTION -- does it work without BEGIN TRANSACTION? if so, is it the whole try block?
    END CATCH

GO

Answer 1

A trigger always executes in the context of a transaction - every DML statement operates within a transaction. This is normally hidden from view by the fact that Implicit Transactions are set to commit automatically in SQL Server.

If you issue a rollback from within a trigger, this will (as always with rollback) rollback all transactions, whether nested or not.

In general, you wouldn't commit within a trigger, unless (as in your commented out example) you're opening a nested transaction explicitly.

If there are other aspects to your question, I'm unable to work out what they are from your posted example. Although I'm always a fan of people posting actual SQL when asking SQL questions, sometimes a little commentary, or a bullet-point list of actual questions can help.