Обработка транзакций в триггере (TRY/CATCH....XACT_ABORT ON)

Question

У меня есть сценарий процесса на SQL Server 2008R2:

• Usp для сбора данных, а затем передачи данных между двумя серверами SQL

Этот процесс должен выполняться с транзакцией на всех уровнях процесса (usp, SSIS и триггер).

В потоке данных, передающем данные в DB7.dbo.Dest, эта таблица имеет триггер AFTER INSERT, который вставляет только что полученные данные в финальную таблицу DB7.dbo.FinalDestination:

CREATE TRIGGER [dbo].[Insert_OnStaging] ON [dbo].[Dest]
AFTER INSERT, UPDATE
AS
    BEGIN

SET NOCOUNT ON;    
SET XACT_ABORT ON;  --Rollsback complete transaction if there are any errors

BEGIN TRY

BEGIN TRANSACTION

INSERT INTO [DB7].[dbo].[FinalDestination] WITH (TABLOCK)
       (Column1
       ,Column2
       )
SELECT I.Column1, I.Column2 
FROM INSERTED I
INNER JOIN [DB7].[dbo].[Dest] PR
ON I.IDcol = PR.IDcol

COMMIT TRANSACTION

END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0 AND XACT_STATE() <> 0
    ROLLBACK TRANSACTION;

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;

SELECT 
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE()
;

RAISERROR (@ErrorMessage, -- Message text.
           @ErrorSeverity, -- Severity.
           @ErrorState, -- State.
           @ErrorLine  --Error Line
           );    
END CATCH;
END

На каждом уровне я пытался защищать данные из-за того, что данные должным образом чувствительны и полностью доходят до финальной таблицы.

Что касается SSIS, то из того, что я прочитал и протестировал, похоже, работает нормально.

Больше всего меня беспокоит триггер, который я описал выше. Из моего прочтения и понимания, установка XACT_ABORT ON откатит транзакцию внутри блока TRY, если возникнут какие-либо ошибки (иными словами, есть транзакция, которая не была выполнена). В этом случае я продолжил и все еще добавил часть транзакции отката в блоке CATCH как часть разума, поскольку она никогда не достигнет (из моего понимания). В то же время я добавил опцию WITH (TABLOCK), чтобы заблокировать таблицу во время выполнения INSERT.

В случае триггера является ли TRY...CATCH даже необходимым при включенном XACT_ABORT? Необходима ли транзакция COMMIT внутри блока TRY? Как я также видел, это зафиксировано после блока CATCH на основе @@TRANCOUNT

BEGIN TRY
BEGIN TRANSACTION
[Tsql here]
END TRY
BEGIN CATCH
[Error Handling]
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END

Ответы и критика приветствуются и заранее благодарны. Пожалуйста, извините за любые опечатки, как я пытался обобщить имена...

Answer 1

Тебе нужно TRY..CATCH даже если вы используете XACT_ABORT, XACT_ABORT прерывает транс, но продолжает запускать пакет / процедуру! Это очень, очень противное поведение. Это означает, что DML/DDL все еще может работать после возникновения ошибки, но вне транзакции, так что вы никогда не сможете откатить ее.

SQL Server не имеет никакого механизма, чтобы избежать этого, за исключением TRY..CATCH, Я не уверен что XACT_ABORT это всегда хорошо для. В вашем примере это тоже не помогает, не делает больно.

И да, вы можете переместить COMIT вне TRY если хотите. Просто убедитесь, что правильно сбалансировать его с BEGIN TRAN,