Utilisation de la clause output

Lorsqu'on modifie les données d'une table, soit par l'ajout, la mise à jour ou la suppression d'enregistrement, il est souvent utile de connaitre quelles données ont été modifiée. Que ce soit pour être inséré dans une table d'audit ou juste pour valider le bon déroulement de notre requête, il existe une clause qui permet d'obtenir ces informations sans passer par un trigger ou tout autre méthode.

La clause OUTPUT nous permet d'obtenir, à l'écran ou de sauvegarder le résultat dans une table, les enregistrements qui ont été modifiés par la requête. Pour obtenir ces informations, deux pseudo-tables sont disponibles, les mêmes pseudo-tables que dans les triggers. La pseudo-table INSERTED contiendra les données insérées tandis que la pseudo-table DELETED contiendra les données supprimées.

Clause OUTPUT dans une requête INSERT

Auparavant, lorsqu'on désirait connaitre l'identifiant du nouvel enregistrement, on devait se fier à la variable système @@IDENTITY ou la fonction SCOPE_IDENTITY pour obtenir l'id du dernier enregistrement inséré. Si bien utilisée, cette méthode fonction fonctionne bien mais avaient certaines limitations, comme par exemple, si votre clef primaire n'est pas un champ identity ou si vous insérez plus d'une ligne à la fois

La clause OUTPUT dans une requête INSERT nous permet de palier à ces limitations et d'avoir plus de contrôle. Prenons le script suivant :

Lors de son exécution, les données insérées sont affichées à l'écran. Si on veut sauvegarder ces données, on peut ajouter la clause INTO.

L'ordre des colonnes dans lequel les colonnes de la pseudo-table apparaissent peut être défini comme dans une clause SELECT. Pour la table dans la clause INTO, on peut spécifier l'ordre des colonnes entre parenthèses après le nom de la table.

Clause OUTPUT dans une requête UPDATE

De la même façon, on peut utiliser la clause OUTPUT pour obtenir les données qui ont été modifié par une requête UPDATE. La clause OUTPUT nous retournera que les enregistrements qui ont été modifiés. Dans ce cas-ci, on reprends la requête ci-dessus, puis on modifie seulement les enregistrements dont l'id est impair.

Clause OUTPUT dans une requête DELETE

La requête DELETE n'est pas en reste non plus. La clause output peut être utilisée pour connaitre les enregistrements qui ont été supprimé, cette fois avec la pseudo-table DELETED pour connaitre les enregistrements qui ont été supprimés

Utilisation de la clause OUTPUT

À quoi bon cela peut servir? Outre le fait de faire afficher à l'écran les données modifiées, on peut maintenant utiliser ces informations pour mettre à jour une autre table ou renvoyer ces informations vers un autre système.

Dans le cas ci-dessous, nous nous en servons pour remplir une table d'audit temporaire qui pourra être utilisée plus tard. En plus des informations sur les enregistrements modifiés, on sauvegardera aussi le contexte dans lequel ils ont été modifiés, avec par exemple, le nom de l'utilisateur qui a modifié les données, l'identifiant de sa session, le nom du programme utilisé, ....

Référence: https://msdn.microsoft.com/en-us/library/ms177564.aspx (clause output)