Getsuyodev, Programming is mainly used when constructing an application. Programming requires knowledge of application domains, algorithms, and programming language expertise. Programming can be developed in different logic based on developer knowledge.

SGBD – Programmer des triggers

Système de gestion de base de données



Introduction:

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 transaction 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.

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 triggers classiques qui se déclenchent après l’opération associée. Ils sont désignés sous le terme de triggers AFTER
  • Les triggers qui se substituent à l’opération associée, dits INSTEAD OF.

Restrictions interdites au sein d’un programme déclencheur :

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 statiques 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 n’est donc pas possible de réaliser des suppressions en cascade par le biais d’un trigger.

Par exemple, sur suppression d’une ligne dans la table des commandes (orders), je ne peux pas supprimer les lignes détail (order details) par le biais d’un Trigger. Le système émettra un message d’erreur précisant la violation d’une contrainte d’intégrité référentielle.

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 « AFTER »:

Vous pouvez associer plusieurs triggers de cette nature pour une même opération sur une même table.

Il n’est toutefois pas possible de préciser l’ordre d’exécution des différents triggers. Il est donc nécessaire d’être prudent sur la nature des opérations à faire prendre en charge par les différents triggers.

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.

Il ne peut-être programmé qu’un seul programme déclencheur de cette nature par opération.

Ce type de trigger est toutefois peu usité car peu conforme aux standards.

Programmation des triggers:

Les tables temporaires INSERTED et DELETED

Ces tables sont manipulées au sein des triggers et stockent les lignes en cours de modification.

Ces tables ont des structures (définition) identiques à la table associée au Trigger.

La table INSERTED comprend les lignes en cours d’ajout (INSERT) ou les nouvelles valeurs des lignes modifiées (UPDATE).

La table DELETED comprend les lignes supprimées (DELETE) ou les valeurs des lignes avant modification (UPDATE).

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.

Exemple de trigger

On envisage de tracer les opérations de modification de salaire des pilotes et de consigner la trace de ces dernières dans une table nommée Augmentations lorsque l’augmentation est supérieure à 10%.

On crée donc un programme déclencheur sur la table PILOTES pour l’opération UPDATE (les autres opérations ne  nous intéressent pas).

On souhaite conserver la trace :

  • du salaire d’origine et celui nouvellement alloué
  • l’identifiant et le nom du pilote
  • le compte utilisateur ayant réalisé la transaction
  • la date de la transaction

Ce trigger va systématiquement se déclencher sur toute opération de mise à jour de la table PILOTES.

En fait, on ne souhaite effectuer une mise en historique que lorsque la colonne Salaire a fait l’objet d’une modification. On peut recourir à la fonction UPDATE qui permet de déterminer si une colonne est mise à jour.

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.

Exemple de trigger: Test

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 …

Le programme déclencheur s’est arrêté avec une erreur mettant fin à la transaction. La ligne en erreur dans ce trigger est l’expression conditionnelle qui contient une sous requête SQL :

En fait, elle ne renvoie pas une valeur scalaire, mais un ensemble de valeurs et la condition ne peut alors pas être évaluée correctement.

Ceci pour confirmer un point essentiel : Les tables temporaires SELECTED et DELETED contiennent l’ensemble des lignes sur lesquelles porte la transaction.

Deuxième point : qu’en est-il de la mise à jour des lignes et de la table historique des augmentations ?

Réponse par l’illustration suivante :

Aucune ligne n’a été mise à jour et aucun enregistrement n’a été inséré dans la table historique des augmentations. Cela confirme que la demande de mise à jour et les opérations effectuées par le trigger ne constituent qu’une seule et même transaction. Une erreur survenue dans le trigger provoque un roll back de la transaction.

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, On a en plus inséré une fonction d’impression des données traitées.

Exemple de trigger: Correction

Deux lignes ont été insérées dans la table de l’historique des augmentations de salaire hors normes et les salaires ont été correctement impactés par la mise à jour. Le trigger fonctionne !

Et si on réalise une mise à jour du patronyme des pilotes ?  

Nous constatons que seule la table des pilotes 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.


Comments are closed, but trackbacks and pingbacks are open.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More