• Artículo

Revertir y confirmar acciones en procedimientos almacenados y desencadenadores

La ejecución de una instrucción ROLLBACK TRANSACTION o COMMIT TRANSACTION de Transact-SQL dentro de un procedimiento almacenado o desencadenador es posible, pero puede generar errores.

En los procedimientos almacenados

Si @@TRANCOUNT tiene un valor diferente cuando concluye un procedimiento almacenado que el que tenía cuando se ejecutó el procedimiento, se genera el error informativo 266. Esto puede suceder de dos maneras:

  • Se llama a un procedimiento almacenado con un @@TRANCOUNT de 1 o superior y el procedimiento almacenado ejecuta una instrucción ROLLBACK TRANSACTION. @@TRANCOUNT se reduce a 0 y provoca un error 266 cuando el procedimiento almacenado finaliza.

  • Se llama a un procedimiento almacenado con un @@TRANCOUNT de 1 o superior y el procedimiento almacenado ejecuta una instrucción COMMIT TRANSACTION. @@TRANCOUNT se reduce en 1 y provoca un error 266 cuando el procedimiento almacenado finaliza. Sin embargo, si BEGIN TRANSACTION se ejecuta después de COMMIT TRANSACTION, el error no se produce.

En los desencadenadores

Un desencadenador funciona como si hubiera una transacción pendiente en curso cuando se ejecuta el desencadenador. Esto es cierto si la instrucción que activó el desencadenador es una transacción implícita o explícita.

Cuando una instrucción comienza a ejecutarse en modo de confirmación automática, hay implicada una instrucción BEGIN TRANSACTION que permite la recuperación de todas las modificaciones generadas por la instrucción si ésta encuentra un error. Esta transacción implicada no tiene efecto sobre las demás instrucciones del proceso por lotes debido a que se confirma o se revierte cuando concluye la instrucción. Sin embargo, sigue teniendo efecto cuando se llama a un desencadenador.

Cuando se ejecuta un desencadenador, se inicia una transacción implícita. Si el desencadenador completa la ejecución y @@TRANCOUNT = 0, se produce un error 3609 y el lote finaliza. Si una instrucción BEGIN TRANSACTION se emite en un desencadenador, crea una transacción anidada. En esta situación, cuando se ejecute una instrucción COMMIT TRANSACTION, dicha instrucción sólo se aplicará a la transacción anidada.

Al utilizar ROLLBACK TRANSACTION en un desencadenador, debe tener en cuenta el siguiente comportamiento:

  • Se revierten todas las modificaciones de datos realizadas hasta ese punto en la transacción actual, incluyendo las que realizara el desencadenador.

  • El desencadenador continúa la ejecución del resto de las instrucciones después de la instrucción ROLLBACK. Si alguna de estas instrucciones modifica datos, no se revierten las modificaciones.

  • La instrucción ROLLBACK en un desencadenador cierra y retira la asignación de todos los cursores que se declararon y abrieron en el proceso por lotes que contenía la instrucción que activó el desencadenador. Esto incluye los cursores declarados y abiertos en procedimientos almacenados llamados por el proceso por lotes que activó el desencadenador. Los cursores declarados en el lote anterior al que activó el desencadenador sólo se cierran, con excepción de los cursores STATIC o INSENSITIVE que se dejan abiertos si:

    • CURSOR_CLOSE_ON_COMMIT es OFF.

    • El cursor estático es un cursor sincrónico o un cursor asincrónico completamente lleno.

En lugar de utilizar ROLLBACK TRANSACTION, la instrucción SAVE TRANSACTION puede utilizarse para ejecutar una reversión parcial de un desencadenador.