TSQL Create trigger with transaction and try catch block
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.
精彩评论