SQL Server : Comment conserver des données lors d’une transaction annulée (ROLLBACK)
Question de stagiaire : « Comment est-il possible de loguer (donc d'enregistrer les données) qui ont été passée en ROLLBACK dans une transaction ? »
En effet le ROLLBACK a pour effet d'annuler toutes les modifications qui ont eu lieu depuis le BEGIN TRANSACTION. Si vous êtes dans un TRIGGER l'effet s'applique à l'action qui a déclenché le trigger et à tous les triggers déclenchés suite à cette action. Le ROLLBACK annulera toute écriture faites quelque soit la base de données, même si vous avez insérer des données dans tempdb celles-ci seront supprimées.
Le seul moyen envisageable pour sauvegarder des données qui « survivent » au ROLLBACK est de passer par une variable. En effet celle-ci n'est pas impactée par le ROLLBACK.
Exemple ne fonctionnant pas :
BEGIN TRANSACTION
-- Essaye de modifier les données
UPDATE MaTable
SET xxx = 'xyz'
WHERE Id = 90
IF @@ERROR
BEGIN
-- On essaye de loguer l'erreur
INSERT INTO LogTable
VALUES(@@ERROR, 'abc')
ROLLBACK TRANSACTION
-- La table de Log est vide à cet endroit
END
Exemple qui fonctionnera grace à notre variable de type table :
DECLARE @logtable TABLE (ErrNum int, Descr varchar(50))
BEGIN TRANSACTION
-- Essaye de modifier les données
UPDATE MaTable
SET xxx = 'xyz'
WHERE Id = 90
IF @@ERROR
BEGIN
-- On essaye de loguer l'erreur
INSERT INTO @logtable
VALUES(@@error, 'abc')
ROLLBACK TRANSACTION
END
-- Insère le contenu de la variable dans la table
INSERT INTO LogTable
SELECT * FROM @logtable
Dans le second exemple l'insertion se fait dans la table de Log après le ROLLBACK quand la transaction est terminée ce qui ne pose aucuns soucis.
Si en plus je veux avoir le ou les enregistrements sur lesquels a eu lieu l'erreur je m'appuie cet fois sur les fonctionnalités xml du moteur :
DECLARE @logtable TABLE (ErrNum int, Descr varchar(50), Contenu xml)
BEGIN TRANSACTION
-- Essaye de modifier les données
UPDATE MaTable
SET xxx = 'xyz'
WHERE Id = 90
IF @@ERROR
BEGIN
-- On essaye de loguer l'erreur
INSERT INTO @logtable
VALUES(@@error, 'abc', SELECT * FROM MaTable WHERE Id = 90 FOR XML AUTO)
ROLLBACK TRANSACTION
END
-- Insère le contenu de la variable dans la table
INSERT INTO LogTable
SELECT * FROM @logtable
Sur SQL Server 2000 vous pouvez remplacer le type xml par un type varchar ou nvarchar.
L'avantage de cette méthode est qu'elle fonctionne quelle que soit la structure de la table sur laquelle se produit l'erreur. Dans ce cas vous pouvez factoriser le code de « log » des erreurs dans une procédure stockée, en lui passant les paramètres nécessaires.
Bon sql…
Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
