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 lot, 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 avec une valeur @@TRANCOUNT = 0, une erreur 3609 se produit et le lot est arrêté. C'est pour cette raison qu'il est préférable d'éviter d'utiliser ROLLBACK TRANSACTION à l'intérieur des déclencheurs, car cela ramène @@TRANCOUNT à 0, et COMMIT TRANSACTION, qui peut faire descendre @@TRANCOUNT à 0. L'exécution d'une instruction BEGIN TRANSACTION après une restauration empêche l'erreur de se produire, mais cela peut causer des problèmes au niveau de la logique de l'application.
Il faut bien comprendre qu'une instruction BEGIN TRANSACTION produite dans le déclencheur commence en fait une transaction imbriquée. Dans ce cas, l'exécution d'une instruction COMMIT TRANSACTION ne s'appliquera qu'à la transaction imbriquée. Les instructions BEGIN TRANSACTION imbriquées étant ignorées lors de la restauration de transactions imbriquées, une instruction ROLLBACK TRANSACTION exécutée dans le déclencheur restaure toutes les opérations effectuées en ignorant les instructions BEGIN TRANSACTION exécutées par le déclencheur. ROLLBACK restaure toutes les opérations jusqu'à la première transaction et affecte à @@TRANCOUNT la valeur 0.
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 lot 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 lot d'instructions qui a activé le déclencheur. Les curseurs déclarés dans un lot avant le lot 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.
Voir aussi
Concepts
Autres ressources
@@TRANCOUNT (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL)
ROLLBACK WORK (Transact-SQL)
SAVE TRANSACTION (Transact-SQL)