Triggers (ou Déclencheurs)

SQL Server 2000, les déclencheurs disposent toujours de quelques fonctions importantes permettant d’appliquer l’intégrité référentielle.
Même en prenant en charge l’action CASCADE, SQL Server 2000 ne peut accomplir que deux des quatre actions référentielles définies par ANSI. Pour supprimer des enregistrements d’une table référencée et pour que la table de référence remplace la clé étrangère par une valeur par défaut ou par la valeur NULL, il faut également utiliser les déclencheurs. Le listing 2 présente un déclencheur simple définissant toutes les valeurs de référence de Table2 à  NULL en cas de suppression dans Table1.
On peut aussi utiliser les triggers pour rendre les messages d’erreur plus conviviaux. Le paragraphe précédent nous a fait apparaître des messages d’erreur générés automatiquement par SQL Server lorsqu’une contrainte de clé étrangère est transgressée : ils ne sont pas très attrayants. En écrivant des déclencheurs pour vérifier l’intégrité référentielle, on peut créer tous les messages d’erreur que l’on souhaite.
Si on utilise des déclencheurs pour appliquer l’intégrité référentielle, il faut se rappeler de quelques détails. Avant tout, on ne peut pas associer des contraintes de clé étrangère et des déclencheurs pour la même relation clé primaire/clé étrangère. L’intégrité référentielle s’applique aux modifications des deux tables. Ensuite, on ne peut pas utiliser une contrainte de clé étrangère pour s’assurer de la validité de nouveaux enregistrements insérés dans la table de référence et attendre du déclencheur qu’il répercute en cascade les mises à  jour ou les suppressions à  la table référencée. SQL Server permet de créer à  la fois la contrainte de clé étrangère et le déclencheur. Mais si on essaye de supprimer un enregistrement référencé dans la table référencée, SQL Server va d’abord vérifier la contrainte, puis déterminera que la suppression de l’enregistrement constitue une transgression de l’intégrité référentielle et, enfin, interdira la suppression. Ainsi un déclencheur de suppression de la table référencée ne sera jamais activé. Si on décide d’appliquer l’intégrité référentielle à  l’aide de triggers, il faut en créer deux : un dans la table référencée pour traiter les suppressions et les mises à  jour, et un dans la table de référence pour gérer les insertions et les mises à  jour. On souhaitera peut-être aussi déclarer la relation de clé étrangère pour clarifier la relation entre les tables. On utilise simplement l’option NOCHECK de ALTER TABLE pour s’assurer que SQL Server n’impose pas la contrainte ; ensuite, le déclencheur pourra fonctionner.
La difficulté à  traiter les mises à  jour de plusieurs enregistrements de la table référencée représente l’un des inconvénients de l’utilisation des déclencheurs pour appliquer l’intégrité référentielle. Supposons par exemple que l’on souhaite que toutes les clés primaires de Table1 soient des multiples de 10 et non des entiers à  un chiffre. Effectuer une mise à  jour de Table1 pour multiplier toutes les valeurs de clé primaire par 10 serait simple, mais créer un déclencheur pour propager la modification à  toutes les clés étrangères serait une tâche compliquée, voire impossible. SQL Server utilise une pseudo-table appelée inserted pour garder une trace de tous les nouveaux enregistrements générés par une instruction de mise à  jour. Toutes les anciennes versions des enregistrements modifiés se trouvent dans la pseudo-table deleted. Ces pseudo-tables ne sont accessibles que depuis l’intérieur d’un déclencheur.
Pour créer un déclencheur et propager les modifications, il faut connaître les correspondances entre les enregistrements de inserted et les enregistrements de deleted. Pour propager les valeurs mises à  jour de Table1, un déclencheur doit mettre à  jour Table2. Et pour chaque enregistrement de Table2 ayant une valeur correspondant à  une valeur de deleted, on doit modifier la valeur de Table2 en valeur correspondante dans inserted. Deleted aura les valeurs 1, 2 et 3 et inserted aura les valeurs 10, 20 et 30.
Par programme, on ne peut pas associer les enregistrements des deux pseudo-tables les uns aux autres. Certaines personnes utilisent les curseurs pour passer en revue les enregistrements des deux tables, en supposant que le premier enregistrement de inserted correspond au premier enregistrement de deleted. Mais les systèmes de gestion de bases de données relationnelles (SGBDR) comme SQL Server n’ont aucune notion de premier enregistrement ; pour eux, une table est un ensemble non-ordonné d’enregistrements. On ne peut pas utiliser les triggers pour la propagation d’une table référencée à  une table de référence de plusieurs mises à  jours d’une clé primaire, sauf si une autre colonne de la table référencée est unique elle aussi. On pourrait alors utiliser cette colonne unique pour effectuer une jointure entre les tables inserted et deleted pour déterminer les anciennes valeurs correspondant aux nouvelles valeurs.
Il ne faudrait pas que cette limitation vous empêche d’utiliser les déclencheurs si les fonctions de clé étrangère de SQL Server répondent à  vos besoins. En fait, on n’a que rarement besoin de mettre à  jour plusieurs clés primaires en une seule instruction de mise à  jour et, parfois, on voudra peut-être interdire toutes les mises à  jour de clés primaires. S’il vous faut mettre à  jour plusieurs clés primaires, vous pouvez écrire un script spécifique pour cela. Vous pouvez utiliser la commande ALTER TABLE pour désactiver temporairement vos déclencheurs, écrire le code mettant à  jour la table référencée et la table de référence avec les nouvelles valeurs.
SQL Server 2000 comporte une nouvelle fonction de trigger, elle aussi relative aux clés étrangères. Les déclencheurs de SQL Server 7.0 et des versions précédentes sont des post déclencheurs (c’est-à -dire qu’ils sont exécutés après la prise en compte de l’instruction de modification des données). Par exemple, si on a un déclencheur sur suppression portant sur Table1, les enregistrements supprimés ne font plus partie de Table1 lorsque que le déclencheur est activé : ils ont déjà  été supprimés. Les autres SGBDR possèdent des pré déclencheurs spécifiant des actions à  effectuer par le système de base de données avant la modification des données. SQL Server 2000 dispose d’une fonction identique, appelée déclencheurs de substitution, qui diffèrent des pré déclencheurs dans le sens où ils remplacent l’opération de modification de données. Si on a un déclencheur de substitution sur suppression dans la Table1 et que l’on tente d’exécuter une instruction de suppression dans la Table1, le déclencheur de substitution se déclenche. La suppression n’aura pas lieu, sauf si le déclencheur de substitution réédite l’instruction de suppression.
Je ne détaillerai pas entièrement les déclencheurs de substitution, mais leur objectif premier est de modifier des vues que l’on ne peut pas modifier autrement, telles que les vues couvrant plusieurs tables. En général, on ne peut pas supprimer d’enregistrement depuis une vue construite à  partir d’une jointure. Toutefois, un déclencheur de substitution sur la vue permet d’exécuter des suppressions séparées sur chaque table à  partir de laquelle la vue est construite.
Souvenez-vous que l’on ne peut pas associer des déclencheurs de substitution et des clés étrangères à  l’action référentielle CASCADE. Si on définit des clés étrangères en CASCADE dans une table, le message d’erreur suivant apparaîtra si on tente de créer un déclencheur de substitution pour la même action.

Server: Msg 2113, Level 16, State 1, Procedure Table2_IOT_update, Line 5
Cannot CREATE INSTEAD OF DELETE or UPDATE TRIGGER ‘Table2_IOT_update’
on table ‘Table2’ because the table has a foreign key with cascaded DELETE
or UPDATE.

Cette restriction implique que si l’action CASCADE est destinée aux mises à  jour, vous ne pouvez pas avoir également un déclencheur de substitution pour les mises à  jour, mais vous pouvez en avoir un pour les insertions ou les suppressions. De la même façon, si vous avez un déclencheur de substitution portant sur Table2, vous ne pouvez pas modifier la table et ajouter une contrainte de clé étrangère à  l’aide de l’action CASCADE pour la même opération de modification de données.