By: Tim Ford | Updated: 2010-12-13 | Comments (6) | Related: More > T-SQL

Free MSSQLTips Webinar: Development Best Practices for SQL Server

Attend this webinar to learn about development best practices for SQL Server. Andy Warren partagera ses nombreuses années d’expérience pour donner quelques conseils sur ce qui a le mieux fonctionné pour lui et comment vous pouvez utiliser certaines de ces connaissances.

Problème

Souvent, je me retrouve dans des situations où j’ai besoin d’insérer des enregistrements dans une table dans une opération basée sur un ensemble enveloppée à l’intérieur d’une transaction où secondairement, et dans la même transaction, je génère des insertions ultérieures dans des tables connexes où j’ai besoin de transmettre des valeurs clés qui étaient le résultat de la commande d’INSERTION initiale. Grâce à une amélioration Transact / SQL dans SQL Server, cela est devenu beaucoup plus facile et peut être fait en une seule instruction… SANS DÉCLENCHEUR !

Solution

L’une des améliorations Transact/SQL de Microsoft SQL Server est la sous-clause OUTPUT de l’instruction INSERT. Vous pouvez maintenant capturer les enregistrements insérés via une instruction INSERT (pensez également à pouvoir capturer les valeurs de colonne d’identité pour les nouvelles lignes) pour une utilisation ultérieure dans une instruction INSERT supplémentaire pour qu’une table enfant conserve l’intégrité référentielle sans avoir besoin d’un déclencheur INSERT.

Pourquoi ne pas simplement utiliser un déclencheur? C’est une construction viable et éprouvée de SQL Server, non?

La réponse courte est  » Oui, c’est le cas. »Cependant, les déclencheurs sont l’un de ces petits secrets désagréables que la base de données conserve. Ils ne se contentent pas de vous sauter dessus et de dire « ME VOICI! »Prenons par exemple le processus de dépannage des blocages ou de réglage d’une requête peu performante – un déclencheur assis en arrière-plan qui fonctionne comme il a été demandé peut être à l’origine de vos problèmes, mais vous allez passer par de nombreuses itérations de recherche de procédures stockées et de code T / SQL ad hoc avant même de vous arrêter pour considérer qu’il y a un déclencheur déclenchant des commandes de langage de modification de données (DML) – des INSERTIONS, des MISES À JOUR ou des SUPPRESSIONS qui sont complémentaires à ce que vous essayez de diagnostiquer. J’associe l’utilisation de déclencheurs à l’utilisation de code T / SQL ad hoc utilisé dans la pile de code d’une application et transmis à une instance SQL Server pour que les pratiques de traitement soient évitées.

C’est pourquoi j’aime ce que je vois avec la construction INSERT-OUTPUT. Vous bénéficiez des avantages de pouvoir capturer les valeurs insérées que vous pouvez ensuite transmettre à une commande secondaire – et vous pouvez tout envelopper dans une seule transaction pour atomicity. La syntaxe de cette construction est illustrée ci-dessous et ne diffère que légèrement de la commande de base INSERT T/SQL:

)
DE source_table_OR_JOIN_of_multiple_tables >
OÙ filtering_criteria >

La seule différence entre ceci et une instruction INSERT standard est l’inclusion sur la SORTIE…EN déclaration. Pour que cela soit facile, considérez-le simplement comme une instruction INSERT secondaire à l’intérieur de l’instruction INSERT d’origine qui capture les valeurs de la table INSÉRÉE virtualisée – la même table qu’un déclencheur utiliserait – pour traiter une insertion secondaire vers une autre table. Dans l’exemple ci-dessous, et en accord avec la saison des fêtes, supposons que vous soyez responsable de faire un peu d’embauche dans les bureaux de l’entreprise AdventureWorks. Un bon vieux lutin est embauché pour certaines promotions en magasin et, conformément à la politique de l’entreprise, vous effectuez toujours un examen de 90 jours pour toute nouvelle embauche. Nous voulons que la notfication soit enregistrée lors de la saisie de la nouvelle embauche sans travail supplémentaire de la part des Ressources humaines. Le code ci-dessous démontre comment nous pouvons utiliser INSERT-OUTPUT pour ce faire.

Maintenant que nous avons construit les objets pour ce petit exercice, nous pouvons regarder la construction INSERT-OUTPUT en action…

En sélectionnant maintenant à la fois les tables Staff et Notification, vous verrez que les valeurs clés ont été entrées avec succès dans les deux tables:

Maintenant, il y a une mise en garde très importante – et assez limitative à l’utilisation de la sortie INSERT. La cible de sortie ne peut faire partie d’aucune relation de clé étrangère. Même s’il n’y a pas de relation en cascade avec un autre objet via cette relation dans la base de données. Regardons ce qui se passe si c’est le cas. Nous ajouterons une clé étrangère à la notification sur StaffID, en faisant référence à la colonne StaffID dans la table Staff, puis nous essaierons d’ajouter une aide supplémentaire pour les vacances:

Le message d’erreur suivant est renvoyé comme prévu :

C’est probablement bien dans ce cas, car il y a de bonnes chances que M. Bonhomme de neige ne soit pas là dans 90 jours.

Prochaines étapes

• En savoir plus sur la clause de SORTIE
• Lisez également cette astuce précédente sur la clause de SORTIE
• D’autres astuces de l’auteur sont disponibles via ce lien.

Dernière mise à jour: 2010-12-13

À propos de l’auteur

Tim Ford est un administrateur de base de données senior chez MindBody.
Voir tous mes conseils

Ressources connexes

• Plus de conseils pour les développeurs de bases de données…