• Article

Restauration et validation de transactions dans les procédures stockées et les déclencheurs

L'exécution d'une instruction Transact-SQL ROLLBACK TRANSACTION ou COMMIT TRANSACTION à l'intérieur d'une procédure stockée ou d'un déclencheur est possible, mais elle peut provoquer des erreurs.

Dans des procédures stockées

Si la valeur de @@TRANCOUNT à la fin d'une procédure stockée est différente de celle qu'elle avait au début, une erreur d'informations (266) est produite. Cela peut se produire de deux manières :

  • Une procédure stockée est appelée avec une valeur @@TRANCOUNT égale à 1 ou plus et la procédure stockée exécute une instruction ROLLBACK TRANSACTION. @@TRANCOUNT redescend à 0 et provoque une erreur 266 à la fin de la procédure stockée.

  • Une procédure stockée est appelée avec une valeur @@TRANCOUNT égale à 1 ou plus et la procédure stockée exécute une instruction COMMIT TRANSACTION. @@TRANCOUNT redescend de 1 et provoque une erreur 266 à la fin de la procédure stockée. Toutefois, si BEGIN TRANSACTION est exécutée après COMMIT TRANSACTION, l'erreur ne se produit pas.

Dans des déclencheurs

Un déclencheur se comporte comme si une transaction était en cours au moment de son exécution. C'est le cas en particulier si l'instruction activant le déclencheur fait partie d'une transaction implicite ou explicite.

Au début de l'exécution d'une instruction en mode autocommit, une instruction BEGIN TRANSACTION implicite est exécutée afin de permettre, en cas d'erreur, la récupération des modifications effectuées par cette instruction. Cette transaction implicite n'a aucune influence sur les autres instructions du traitement, car elle est toujours validée ou restaurée à la fin de l'instruction. Cette transaction implicite est cependant toujours active lors de l'appel d'un déclencheur.

Lorsqu'un déclencheur s'exécute, une transaction implicite est lancée. S'il termine son exécution et que @@TRANCOUNT = 0, une erreur 3609 se produit et le lot est arrêté. Si une instruction BEGIN TRANSACTION est émise dans un déclencheur, cela crée une transaction imbriquée. Dans ce cas, lorsqu'une instruction COMMIT TRANSACTION est exécutée, elle ne s'applique qu'à la transaction imbriquée.

Lors de l'utilisation de ROLLBACK TRANSACTION dans un déclencheur, tenez compte de ce comportement :

  • toutes les modifications de données effectuées jusque là dans la transaction en cours sont restaurées, y compris celles effectuées par le déclencheur ;

  • le déclencheur termine l'exécution des instructions postérieures à l'instruction ROLLBACK. Si l'une de ces instructions modifie les données, les modifications ne sont pas restaurées.

  • L'exécution d'une instruction ROLLBACK dans un déclencheur entraîne la fermeture et le déchargement de la mémoire de tous les curseurs déclarés et ouverts dans le traitement qui contient l'instruction activant le déclencheur. Ceux-ci comprennent les curseurs déclarés et ouverts dans les procédures stockées appelées par le traitement qui a activé le déclencheur. Les curseurs déclarés dans un traitement avant le traitement qui a activé le déclencheur sont simplement fermés. Toutefois, les curseurs STATIC ou INSENSITIVE restent ouverts si :

    • CURSOR_CLOSE_ON_COMMIT a la valeur OFF ;

    • le curseur statique est soit synchrone, soit asynchrone avec remplissage complet ;

au lieu d'utiliser ROLLBACK TRANSACTION, vous pouvez recourir à l'instruction SAVE TRANSACTION pour exécuter une restauration partielle dans un déclencheur.