Réponse.

Historique de la question
Lors des opérations de traitement de la logique métier, il est souvent nécessaire de modifier des données dans plusieurs tables liées. Si quelque chose échoue (par exemple, en raison d'une contrainte violée), il est important d'annuler non seulement la dernière opération, mais toutes les actions dans le cadre de la logique. Pour cela, on utilise la mécanique des transactions et la gestion des erreurs.

Problème
Tous les SGBD ne garantissent pas par défaut le rollback en cas d'erreur dans des procédures multi-étapes (surtout lorsqu'on utilise des gestionnaires TRY/CATCH, EXCEPTION). Une mauvaise rédaction de la logique transactionnelle conduit à des modifications "par morceaux" (une partie des données a été mise à jour, une partie ne l'est pas), ce qui compromet l'intégrité métier.

Solution
Pour une gestion correcte, on utilise l'ouverture explicite et la clôture des transactions (BEGIN TRANSACTION, COMMIT/ROLLBACK) avec la capture des erreurs. La syntaxe et les possibilités varient d'un SGBD à l'autre.

Exemple pour MS SQL Server:

CREATE PROCEDURE update_balances(@from INT, @to INT, @amount DECIMAL(10,2))
AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION;
      UPDATE accounts SET balance = balance - @amount WHERE id = @from;
      UPDATE accounts SET balance = balance + @amount WHERE id = @to;
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    INSERT INTO error_log(err_message, err_date) VALUES(ERROR_MESSAGE(), GETDATE());
    THROW; -- relancer l'erreur
  END CATCH
END;

Pour PostgreSQL:

CREATE OR REPLACE FUNCTION update_balances(from_id INT, to_id INT, amount NUMERIC)
RETURNS void AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  -- Une exception annulera automatiquement la transaction
EXCEPTION WHEN OTHERS THEN
  INSERT INTO error_log(err_message, err_date) VALUES(SQLERRM, NOW());
  RAISE;
END;
$$ LANGUAGE plpgsql;

Points clés :

• On ouvre et on clôture explicitement la transaction.
• On gère les erreurs dans le bloc catch/exception et on annule les modifications.
• On enregistre les informations sur les erreurs pour l'audit.

Questions piégeuses.

Une erreur à l'intérieur d'une procédure stockée peut-elle toujours annuler automatiquement la transaction ? Non ! Par exemple, dans MS SQL Server, cette annulation n'est pas garantie - un ROLLBACK explicite est nécessaire. Dans PostgreSQL, en cas d'erreur, toute la transaction devient "corrompue" et nécessite une clôture.

Peut-on "partiellement" valider à l'intérieur d'une même procédure ? Mauvaise pratique. Il est recommandé de faire un commit/rollback une seule fois à la fin de l'opération métier. Certaines plateformes permettent des "savepoints", mais cela pour des tâches spécifiques.

Quels types d'erreurs ne peuvent pas être captées via TRY/CATCH/EXCEPTION en SQL ? Certains échecs système (par exemple, la coupure d'une connexion avec le serveur) ne passeront pas par le gestionnaire et peuvent rendre le rollback impossible.

Erreurs typiques et anti-patterns

• Laisser un COMMIT sans gestion des erreurs : enregistrer dans la base des données "corrompues" ou partiellement modifiées.
• Multiples COMMIT/ROLLBACK dans une même opération métier.
• Ignorer l'enregistrement des erreurs.

Exemple de la vie réelle

Cas négatif

La procédure stockée mettait à jour trois tables et se terminait par un COMMIT sans gestion des erreurs. En conséquence, en cas d'erreur dans la deuxième table, la première avait déjà été modifiée, et il a fallu des heures pour "restaurer" manuellement les données à partir des sauvegardes.

Avantages:
Simple et "fonctionne", jusqu'à ce qu'une erreur se produise.

Inconvénients:
Risque d'incohérence; impossible de récupérer rapidement lors des erreurs.

Cas positif

Mise en place d'une gestion explicite des erreurs + rollback des transactions en enregistrant tous les échecs dans un log séparé. Le retour à l'état intact de la base prend quelques secondes, les pannes sont analysées et minimisées.

Avantages:
Garantie d'intégrité des données même en cas d'échecs; logique transparente.

Inconvénients:
Légèrement plus compliqué pour les débutants, nécessite de la discipline dans le code.