开发者

TSQL Create trigger with transaction and try catch block

开发者 https://www.devze.com 2023-01-04 18:53 出处:网络
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

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 TRANSACTIO开发者_开发百科N -- does it work without BEGIN TRANSACTION? if so, is it the whole try block?
    END CATCH

GO


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.

0

精彩评论

暂无评论...
验证码 换一张
取 消