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

Ответ 1

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

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

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

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

Ответ 2

Не откат в триггере, и нет необходимости запускать транзакцию.

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

Edit:

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

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

Ответ 3

Чтобы не потерять транзакционные данные перед действием триггера, вам нужно позвонить 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.

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

UPDATE: Чтобы избежать последней ошибки исключения, вызовите BEGIN TRAN в инструкции TRY. Примечание. Корпорация Майкрософт рекомендует НЕ называть COMMIT TRAN внутри триггера, но если это неизбежно, это должно сработать для вас.

Пример:

COMMIT TRAN
BEGIN TRY
    BEGIN TRAN

Ответ 4

u07ch,

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

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

Ответ 5

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

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

Ответ 6

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

    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

Ответ 7

Используйте 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

Ответ 8

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

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

Ответ 9

Вы можете установить XACT_Abort в положение OFF в начале запуска.