TSQL: транзакция Try-Catch в триггере

По моему опыту, любая ошибка, обнаруженная в try catch в триггере, приведет к откату всей транзакции; вы можете использовать транзакцию сохранения. Я думаю, вам нужно посмотреть, что происходит в «Еще sql», и определить, можете ли вы написать операторы case/if вокруг него, чтобы остановить ошибку.

В зависимости от того, что вы делаете, вы можете использовать сохранить транзакцию и зафиксировать это в улове

В вашем коде что-то вроде этого

SAVE TRANSACTION BeforeUpdate;
BEGIN TRY
        --Some More SQL
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION BeforeUpdate;
        return
END CATCH

Не откатывайтесь в триггере и не нужно запускать транзакцию.

ROLLBACK TRANSACTION отменяет исходный триггер DML и дополнительную транзакцию триггера. Таким образом, партия будет прервана

Редактировать:

Я предлагаю не использовать «RETURN» в блоке catch и просто позволить коду завершиться. Я никогда не игнорировал пойманную ошибку в триггере (но я использую TRY/CATCH в триггерах с откатом и рейзеррором для повторного броска), поэтому это предположение, но возврат, вероятно, является ненормальным условием выхода в триггере

Кроме того, постарайтесь в первую очередь избежать состояния ошибки. Измените --some more sql, чтобы избежать ошибки. Пример: добавьте if exists(... для проверки наличия дубликата первым или подобным

Используйте SET XACT_ABORT OFF. Когда оператор Transact-SQL сталкивается с ошибкой, он просто вызывает сообщение об ошибке, и транзакция продолжает обработку. Следующий код предназначен для создания триггера:

Create TRIGGER [dbo].tr_Ins_Table_Master ON [dbo].Table_Master
 AFTER INSERT
AS
BEGIN
set xact_abort off
BEGIN TRY
        --your SQL          
        INSERT INTO Table_Detail
        SELECT MasterID,Name FROM INSERTED

END TRY

BEGIN CATCH     
    select ERROR_MESSAGE()
END CATCH

END

Чтобы избежать потери транзакционных данных до действия триггера, вам нужно вызвать COMMIT TRAN. Сделайте это перед блоком TRY/CATCH, и вы получите желаемые результаты.

Пример:

COMMIT TRAN
BEGIN TRY
    -- possible error occurs here...
END TRY
BEGIN CATCH
    PRINT 'Error on line ' + CAST(ERROR_LINE() AS VARCHAR(10))
    PRINT ERROR_MESSAGE()
END CATCH

Он все равно выдаст следующую ошибку - не знаю, как ее избежать:

The transaction ended in the trigger. The batch has been aborted.

Но и исходная транзакция, и триггерная транзакция должны быть успешно зафиксированы.

ОБНОВЛЕНИЕ: Чтобы избежать последней ошибки исключения, вызовите BEGIN TRAN в инструкции TRY. Обратите внимание: Microsoft рекомендует НЕ вызывать COMMIT TRAN в триггере, но если это неизбежно, это должно сработать для вас.

Пример:

COMMIT TRAN
BEGIN TRY
    BEGIN TRAN

Эта демонстрация достигает многих целей, о которых говорилось выше. Сообщения об ошибках становятся необязательными. Трюк, который заставляет его работать, заключается во вложенном динамическом выполнении.

    if object_id('toto')  is not  null drop table toto
    go
    create table toto (i int);
    go
    if object_id('toto2')  is not  null drop table toto2
    go
    create table toto2 (i int);
    go
    create Trigger trtoto
    ON toto
    Instead Of Insert
    as
    Begin
      BEGIN TRY
        set nocount  on
        insert into  toto  values(2)

        declare @sql nvarchar(max) =  'insert into toto2 values(3); select * from ThisTableDoesntexist'

        Exec sp_executeSql N'set xact_abort off; exec (@sql) ', N'@sql nvarchar(max)', @sql

      END TRY

      BEGIN CATCH
        PRINT  'Error on line ' + CAST(ERROR_LINE() AS VARCHAR(10))
        PRINT ERROR_MESSAGE()
      END CATCH
    End

GO
-- tests
set nocount on
insert into toto values (1)  -- is not inserted on purpose by  the trigger
select * from toto   -- other value inserted despite the error
select * from toto2  -- other value inserted in other table despite the error

u07ch,

К сожалению, вы не можете использовать save transaction и try.. catch вместе — они просто не могут работать вместе:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/11/15/avoid-mixing-old-and-new-styles-of-error-handling.aspx

Может быть полезно знать, что вы пытаетесь сделать в триггере.

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

Не лучший способ, но он работает. Запустите новую транзакцию и выполните обычный откат фиксации и в конце начните другую транзакцию для неявной фиксации транзакции.

http://msdn.microsoft.com/en-us/library/ms187844(v=SQL.90).aspx

Вы можете установить для XACT_Abort значение OFF в начале триггера.