| 01/08/2010
|
Mettre à jour une vue multi-tables sur le serveur SQL avec le trigger INSTEAD OF
Mettre à jour une vue multi-tables sur le serveur SQL avec le trigger INSTEAD OF
Très souvent, on envisage la migration des données vers SQL Server comme une solution à des problèmes que les dbf ne peuvent pas gérer nativement (sécurité, fragilité par exemple), ou en réponse à des dysfonctionnements liés à l'évolution des OS et des machines (caches disque entre autres).
Mais on peut aussi choisir de migrer de façon positive, pour accéder à des fonctionnalités nouvelles, pour simplifier l'écriture, pour accélérer les traitements, pour faciliter la maintenance.
L'objectif de cette contribution est de vous donner envie de migrer.
Quelques définitions : Une vue sur le serveur, pourquoi faire ? Nos données sont normalisées, et nous utilisons des vues pour obtenir des jeux d'enregistrements dénormalisés, consommables dans notre code métier ou notre couche visuelle sur le client fox. En tant que développeurs fox, nous avons l'habitude de réaliser cette dénormalisation en créant des vues coté client, ou en requêtant cette jointure par du code SPT ou dans un Cursor Adapter. C'est ce que nous faisons par exemple entre une table « clients » et une table « contacts », ou bien entre une table « en_tete_facture » et une table « lignes_de_factures ».
Créons cette vue sur le serveur SQL lui-même : nous gagnerons en performance, en utilisant toute la puissance de la mise en cache des plans d'exécution et leur adaptation automatique aux données réelles (imaginez cela comme une super-optimisation Rushmore).
Une vue sur le serveur n'est rien d'autre que du code SELECT dont le résultat est « vu » comme une table, elle est mise à jour sans aucune intervention nécessaire .
Un TRIGGER INSTEAD, qu'est-ce que c'est ? En fox, nous ne connaissons que les trigger after , c'est-à-dire du code procédural exécuté automatiquement après un événement INSERT, UPDATE, ou DELETE. J'emploie ici volontairement le terme d'événement, dans le sens utilisé dans le monde SQL : ce ne sont pas des événements au sens de la POO, mais des événements de données.
En SQL, la norme SQL:1999 prévoit 2 types de triggers, les BEFORE et les AFTER. Cette norme est implémentée en Transact-SQL sous la syntaxe de TRIGGER INSTEAD OF et TRIGGER AFTER.
Un trigger AFTER s'exécute après le ou les évènements auxquels il répond, et de ce fait les contraintes de lignes et les règles d'intégrité référentielles structurelles ont déjà été vérifiées quand on arrive au début du trigger.
Un TRIGGER INSTEAD OF « intercepte » le ou les événement(s) de données pour lesquels il est écrit, et c'est le code contenu dans ce trigger qui est exécuté à la place de l'événement initial. Aucune contrainte de ligne ou d'intégrité n'a été vérifiée à l'entrée dans le trigger (uniquement les contraintes de schéma et de type de données).
Mettre à jour une vue multi-table sur le serveur, comment ? Si une vue contient des calculs (et à fortiori des agrégations) ou une clause DISTINCT, ou des sous-requêtes ailleurs que dans sa clause WHERE, elle ne peut être mise à jour directement. Nous allons donc utiliser le TRIGGER INSTEAD pour coder cette mise à jour des tables sous-jacentes.
Les objets de notre exemple :
Parcourons le script « create_tables_parent_enfant », qui définit nos 2 tables. Il crée 2 tables nommées parent et enfant, chacune dotée d'un champ identity (autoinc en fox) sur lequel est basée la clé primaire. Ces 2 tables sont liées dans une contrainte d'intégrité référentielle nommée « FK_enfant_parent ». De plus, les données du champ « enf_ch1 » sur la table « enfant » ne doivent pas prendre la valeur ' z2z2 '. C'est ce qu'en fox on appelle une règle de niveau champ. En SQL, on appelle cet objet une contrainte de validation, ou contrainte check. Comme tout objet, il porte un nom, nous l'avons appelé « CK_enfant_enf_ch1 »
Le script de création de la vue nous permet de voir qu'elle modifie la casse de la colonne « par_ch1 » issue de la table « parent » ; les 2 tables sont liées par une jointure sans surprise, de type FK/PK. Remarquons juste que nous faisons remonter dans cette vue les PK des 2 tables (nous les utiliserons dans le trigger).
Regardons maintenant plus attentivement le script de création du trigger.
Comme nous voulons mettre à jour les 2 tables ou aucune, nous empaquetons ces 2 mises à jours dans une transaction. Le code lui-même est inséré dans une structure d'interception d'erreur par TRY/CATCH, dont le principe est identique à celui que nous connaissons en fox (remarquons seulement la petite différence syntaxique). Une erreur déclenchée dans le TRY entrainera l'exécution du code contenu dans le CATCH, que nous avons ici simplifié à un simple ROLLBACK. Bien entendu, ce code devrait comporter un traitement personnalisé de gestion de l'erreur, afin de remonter ses détails jusqu'à la couche client, mais ce n'est pas l'objet de la présente contribution. Nous aurions pu également utiliser un SET XACT_ABORT ON.
Le code traite donc la mise à jour de chaque table par un UPDATE, en vérifiant d'abord si la mise à jour est requise, et en utilisant les pseudo-tables :
Vérifier si la mise à jour est requise pour cette table La commande UPDATE(nom_de_colonne) renvoie le booléen VRAI si l'évènement déclencheur impacte cette colonne.
L'utilisation de la pseudo-table INSERTED Les pseudo-tables sont des jeux d'enregistrements présentés dans les trigger, et disponibles comme des tables dans le code des trigger. Ces pseudo-tables (que nous appellerions des cursor en fox) sont nommées INSERTED et DELETED, elles ont exactement la même structure que l'objet (table ou vue) sur lequel porte le trigger, elles contiennent les jeux d'enregistrements impactés par l'évènement demandeur. Dans un trigger INSERT, seule la pseudo-table INSERTED est présentée ; dans un trigger DELETE, seule la pseudo-table DELETED est présentée. Et dans un trigger UPDATE, les 2 pseudo-tables sont présentées, DELETED contient les valeurs d'origine (avant l'update) de la table ou de la vue, et INSERTED contient les valeurs de destination.
Dans notre exemple, nous avons uniquement besoin des valeurs contenues dans INSERTED pour les appliquer aux tables sous-jacentes de la vue.
La clause FROM dans la source des données du SET Nous retrouvons les enregistrements dans chacune des tables par une simple jointure entre cette table et INSERTED sur la clé primaire. La clause FROM permet d'utiliser le jeu résultant de cette jointure à la fois pour identifier la ligne à mettre à jour et pour déterminer la valeur de remplacement. Tout simple, non ?
Le dernier script est un simple test, permettant de vérifier le fonctionnement de l'ensemble.
Il ne reste plus qu'à consommer cette vue serveur comme si c'était une table, dans notre client fox (avec une vue distante, du code SPT, ou un Cursor Adapter) ou notre client StrataFrame (avec un BO).
|
Michel Lévy
|
