SQL Server OUTPUT : capturer les bonnes modifications

Modifier des lignes puis les relire pose deux questions différentes : quelles lignes cette instruction a-t-elle changées, et que contiennent maintenant les lignes correspondantes ? Sous concurrence, les réponses peuvent diverger. OUTPUT rattache le résultat directement à l'instruction de modification, ce qui sert aux clés générées et aux valeurs avant/après.

Garder une correspondance explicite

L'exemple utilise des tables temporaires et modifie deux lignes de stock. Il capture clé, ancienne quantité et nouvelle quantité depuis le même UPDATE. L'ORDER BY final définit la présentation sans dépendre de l'ordre physique des modifications.

IF @@TRANCOUNT <> 0
    THROW 50000, 'This example owns its transaction.', 1;
SET XACT_ABORT ON;
CREATE TABLE #Stock (ItemId int PRIMARY KEY, Qty int NOT NULL);
INSERT #Stock VALUES (1, 12), (2, 20);
CREATE TABLE #Changed (ItemId int, OldQty int, NewQty int);
BEGIN TRY
    BEGIN TRAN;
    UPDATE #Stock
    SET Qty = Qty - 2
    OUTPUT inserted.ItemId, deleted.Qty, inserted.Qty
        INTO #Changed(ItemId, OldQty, NewQty)
    WHERE ItemId IN (1, 2) AND Qty >= 2;
    COMMIT;
    SELECT ItemId, OldQty, NewQty FROM #Changed ORDER BY ItemId;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    THROW;
END CATCH;

Conservez la clé dans le résultat. Des quantités seules obligeraient le client à deviner leur correspondance. Pour une insertion multiple, stockez une valeur unique de corrélation fournie par le client et retournez-la avec l'identité générée. Associer la première identité reçue à la première ligne d'entrée n'est pas une stratégie valide.

Limitez la capture aux colonnes nécessaires. OUTPUT inserted.* couple l'API à la structure entière et peut copier inutilement de gros contenus. Une liste explicite rend les changements de type visibles et évite de divulguer accidentellement des champs internes.

Ne pas confondre sortie et validation

La frontière transactionnelle reste déterminante. OUTPUT n'atteste pas la validation finale de l'opération métier. Une instruction ultérieure peut échouer, la transaction peut être annulée ou la connexion disparaître pendant l'accusé de commit. Le client doit traiter le résultat complet et ses erreurs avant de considérer l'opération comme réussie.

Le modèle capture dans une table temporaire, valide, puis renvoie les lignes. En cas d'échec, CATCH annule et relance l'erreur au lieu de sélectionner un résultat de succès. La garde interdit une transaction extérieure, faute de quoi l'appelant pourrait encore annuler après réception de cette réponse.

Cela règle la séquence locale, pas la perte de réponse. Si le commit réussit sans que le client reçoive le résultat, une nouvelle tentative doit retrouver l'issue précédente par un identifiant durable. La table temporaire disparaît avec la session. Conservez donc corrélation et résultat de manière permanente lorsque l'API exige des écritures sûres à répéter.

Définir le contrat avec les déclencheurs

Les valeurs inserted d'OUTPUT correspondent à la modification avant les déclencheurs AFTER. Si un déclencheur normalise ensuite une valeur, la sortie peut différer de l'état final. Déterminez ce que l'appelant attend. Pour obtenir les valeurs finales, capturez les clés puis organisez une relecture avec une isolation adaptée dans un contrat transactionnel explicite.

Une sortie OUTPUT directe possède aussi des restrictions en présence de déclencheurs activés pour l'action. OUTPUT INTO peut être approprié, mais sa destination comporte ses propres contraintes. Vérifiez le schéma réel, ses triggers et ses contraintes plutôt qu'une simple table de démonstration.

Pour l'audit, une réponse envoyée au client n'est pas une trace durable. Une table d'audit écrite dans la même transaction suit les modifications validées ; son écriture disparaît aussi lors d'un rollback. Enregistrer les tentatives échouées demande un autre mécanisme. Choisissez si le besoin concerne les changements réussis, les tentatives, ou les deux.

Mesurez enfin le coût de capture pour les grandes modifications. Des millions de valeurs avant/après peuvent consommer mémoire, journal et réseau. Une réponse limitée à clé et statut peut mieux servir l'application. Testez succès, échec forcé, valeurs modifiées par trigger et corrélation multiple. Vérifiez particulièrement qu'un client n'interprète pas des lignes déjà reçues comme validées lorsqu'une erreur arrive ensuite.

Références techniques: Microsoft Learn: OUTPUT · Microsoft Learn: TRY CATCH.