Поделиться через

  • Статья

Откат и фиксация транзакций в хранимых процедурах и триггерах

Выполнять инструкции ROLLBACK TRANSACTION и COMMIT TRANSACTION языка Transact-SQL в хранимых процедурах и триггерах можно, но это может привести к ошибкам.

В хранимых процедурах

Если за время выполнения хранимой процедуры значение @@TRANCOUNT изменяется, происходит информационная ошибка (266). Это может случиться в двух следующих сценариях.

  • Хранимая процедура вызывается со значением @@TRANCOUNT, равным 1 или более, и выполняет инструкцию ROLLBACK TRANSACTION. Значение @@TRANCOUNT уменьшается до 0, что приводит к ошибке 266 при завершении хранимой процедуры.

  • Хранимая процедура вызывается со значением @@TRANCOUNT, равным 1 или более, и выполняет инструкцию COMMIT TRANSACTION. Значение @@TRANCOUNT уменьшается на 1, что приводит к ошибке 266 при завершении хранимой процедуры. Однако если после инструкции COMMIT TRANSACTION будет выполнена инструкция BEGIN TRANSACTION, ошибка не возникнет.

В триггерах

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

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

При выполнении триггера инициируется неявная транзакция. Если выполнение триггера завершается со значением @@TRANCOUNT, равным 0, происходит ошибка 3609, а обработка пакета инструкций отменяется. Если в триггере запускается инструкция BEGIN TRANSACTION, она создает вложенную транзакцию. В этой ситуации инструкция COMMIT TRANSACTION будет применяться только к вложенной транзакции.

Используя инструкцию ROLLBACK TRANSACTION в триггере, имейте в виду следующее.

  • При этом будут отменены все изменения данных, уже выполненные в текущей транзакции, в том числе изменения, выполненные триггером.

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

  • При выполнении инструкции ROLLBACK в триггере она закрывает и освобождает все курсоры, которые были объявлены и открыты в пакете, содержащем инструкцию, приведшую к срабатыванию триггера. В их число входят курсоры, объявленные и открытые в хранимых процедурах, вызванных пакетом, который привел к срабатыванию триггера. Курсоры, объявленные в пакете до пакета, который привел к срабатыванию триггера, только закрываются. Однако курсоры STATIC и INSENSITIVE остаются открытыми, если справедливо следующее:

    • параметр CURSOR_CLOSE_ON_COMMIT имеет значение OFF;

    • статический курсор является или синхронным, или полностью заполненным асинхронным курсором.

Чтобы выполнить частичный откат транзакций в триггере, вместо инструкции ROLLBACK TRANSACTION можно использовать инструкцию SAVE TRANSACTION.