Il s’agit de programmes déclenchés automatiquement lors d’opérations de mises à jour sur une table.
Ils représentent une catégorie spécialisée de procédures stockées définies pour s'exécuter automatiquement lors d’une opération sur une table ou une vue.
Ils seront donc associés à une opération d’insertion, de mise à jour ou de suppression d’une ligne d’une table particulière.
Ce sont des outils puissants qui peuvent être utilisés pour mettre automatiquement en application des règles de gestion lorsque des données sont modifiées. Ils sont notamment utilisés pour programmer des transactions dépendantes d’autres transactions.
Si nous prenons l’exemple de la prise de commande dans une application de gestion commerciale, dans le cas de l’insertion d’une nouvelle ligne de commande, nous pourrions mettre à jour les quantités de produits réservés pour commande et générer une ligne dans la table mouvements de stocks.
Les déclencheurs peuvent aussi étendre la logique de contrôle d'intégrité des données et permettent de dépasser les limites fixées aux contraintes d’intégrité, valeurs par défaut et règles programmées.
Il est possible de programmer un Trigger spécifique pour une opération ou pour un ensemble d’opérations. Il est toutefois plus aisé de créer un trigger pour chacune des opérations de base sur une table.
Il existe deux catégories de Triggers :
Les restrictions interdites au sein d’un programme déclencheur sont :
Create Database, Alter Database, Drop Database, Load Database, Reconfigure, Restore Database
Les triggers qui se déclenchent sur une opération et cette opération font partie d’une seule et même transaction, ainsi que tous les autres triggers éventuellement déclenchés par la suite.
Ainsi, si un trigger échoue, l’opération à l’origine de son exécution sera elle aussi annulée.
Les règles définies sur les données (contraintes d’intégrité, valeurs par défaut, …) sont toujours vérifiées avant l’exécution d’un trigger.
Il faut éviter la récursivité directe dans un trigger. Elle n’est d’ailleurs pas autorisée par défaut.
Exemple de récursivité directe :
Un Trigger T1 se déclenche sur l’ajout d’une ligne dans une table Tab1 et comporte une instruction d’ajout d’une ligne dans cette même table.
Les triggers INSTEAD OF essentiellement conçus pour prendre en charge les opérations de modifications de données sur les vues multi-tables qui, par nature, sont en lecture seule.
Cas où seul un trigger INSTEAD OF est associé à l'action de mise à jour (insert, delete ou update) : Dans le trigger INSTEAD OF, les enregistrements ajoutés (respectivement modifiés ou supprimés) apparaissent uniquement dans les tables temporaires mais pas dans la table d'origine et si le code associé à ce trigger ne prend pas en charge l'ajout (respectivement la modification ou la suppression) de ces enregistrements, ils ne seront pas ajoutés (respectivement modifiés et supprimés) même si aucune action n'annule le déclencheur.
Ceci est dû au fait que l'exécution des triggers INSTEAD OF remplace l'action de déclenchement.
2 . Programmation des triggers
2.1 Les tables temporaires INSERTED et DELETED
Ces deux tables virtuelles sont créées au moment de la MAJ sur une table, elles sont manipulées au sein des triggers et stockent les lignes en cours de modification et elles ont des structures identiques à la table associée au Trigger.
2.2 Principe de fonctionnement Les tables temporaires INSERTED et DELETED
Les tables INSERTED et DELETED peuvent être utilisées par le trigger pour déterminer comment le traitement doit se dérouler (ce traitement est à écrire par le développeur).
Les mécanismes d’utilisation par SQL Server de ces deux tables diffèrent en fonction de l’opération effectuée sur la table contenant le ou les triggers.
Cas de suppression d’une ligne de table (delete) :
Les lignes supprimées sont placées dans la table temporaire DELETED et supprimées de la table réelle ; la table DELETED et les tables de la base ne peuvent pas avoir de lignes en commun.
Cas de création d’une ligne de table (insert) :
Les lignes nouvelles sont placées dans la table temporaire INSERTED et dans la table réelle ; toutes les lignes de la table INSERTED apparaissent dans la table de la base.
Cas de modification d’une ligne de table (update) :
Les lignes avant modification sont placées dans la table temporaire DELETED et les lignes après modification sont placées dans la table temporaire INSERTED et dans la table réelle.
Une transaction UPDATE est en fait assimilée à une opération de suppression suivie d'une opération d'insertion ; les anciennes lignes figurent dans la table deleted et les nouvelles lignes dans la table inserted.
Ces tables sont uniquement accessibles en lecture. Elles permettent d’extraire les valeurs des lignes sur lesquelles porte la transaction.
Dans la base de données "Compagnie de Voyage"
Exemple 1 : Trigger sur insertion dans la table chauffeur
Exemple 2 : Trigger sur modification dans la table chauffeur
J’envisage de tracer les opérations de modification de salaire des chauffeurs et de consigner la trace de ces dernières dans une table nommée Augmentation.
Je souhaite conserver la trace :
Ce trigger va systématiquement se déclencher sur toute opération de mise à jour de la table chauffeur.
En fait, je ne souhaite effectuer une mise en historique que lorsque la colonne Salaire a fait l’objet d’une modification. Je peux recourir à la fonction UPDATE qui permet de déterminer si une colonne est mise à jour.
2.3.1 Création de la table historique des augmentations
Les tables peuvent comporter plusieurs déclencheurs mais un déclencheur ne porte que sur une seule table ou vue.
L'instruction CREATE TRIGGER peut être définie avec les clauses FOR UPDATE, FOR INSERT ou FOR DELETE pour affecter un déclencheur à une catégorie spécifique d'actions de modification des données ou pour un ensemble d’opérations FOR INSERT, UPDATE par exemple.
Le premier test concerne la mise à jour d’une seule ligne identifiée par la clé primaire :
Une ligne est bien inscrite dans la table des augmentations.
A ce stade des tests, le trigger semble fonctionner correctement.
Mais que se passerait-il si je demandais la mise à jour d’un ensemble de lignes par le biais d’une seule transaction ?
Le résultat par l’exemple …
Cinq lignes concernées par la modification !
Qu’en est-il de la mise à jour des lignes et de la table historique des augmentations ?
Réponse par l’illustration suivante (résultat de la demande de mise à jour précédente) :
Cinq lignes ont été mise à jour et un seul enregistrement a été inséré dans la table historique des augmentations !
2.3.4Correction du trigger augmentation de salaire
Que faut-il faire pour que le trigger fonctionne correctement ?
Il convient de traiter chaque ligne des tables temporaires individuellement et, pour ce faire, recourir à la mise en place d’un curseur.
Pour compléter notre mise au point et vérifier l’exactitude du trigger, j’ai de plus inséré une fonction d’impression des données traitées.
Vous trouverez page suivante le nouveau trigger correctement codé.
On remarque que la première a été insérer deux fois dans la table historique des augmentations, ceci revient au fait que deux triggers sont liés à la table chauffeur sur mise à jour du salaire. D'où on doit supprimer le premier.
Et enfin si je réalise une mise à jour du nom des chauffeurs ?
Nous constatons que seule la table des chauffeurs a été mise à jour et nous n’avons pas d’informations mentionnant la mise à jour de la colonne salaire !
La fonction UPDATE () a donc correctement été mise en œuvre.
Ajouter un commentaire
Veuillez vous connecter pour ajouter un commentaire.
Pas encore de commentaires.