Gestion des données – La programmation au sein d’un SGBD - les déclencheurs (triggers)

Notions abordées :

• Le respect de contraintes d’intégrité avancées à l’aide de déclencheurs ;
• La conception de déclencheurs, alias triggers ;

Une contrainte d'intégrité est une règle qui définit la cohérence d'une donnée ou d'un ensemble de données de la BD. Les contraintes d’intégrité sont des règles que les attributs des relations doivent respecter afin d’assurer le bon fonctionnement du modèle.

Il existe deux types de contraintes :

• sur une colonne unique,
• ou sur une table lorsque la contrainte porte sur une ou plusieurs colonnes.

Les contraintes sont définies au moment de la création des tables.

Les contraintes d'intégrité sur une colonne sont :

• PRIMARY KEY : définit l'attribut comme la clé primaire
• UNIQUE : interdit que deux tuples de la relation aient la même valeur pour l'attribut.
• REFERENCES <nom table> (<nom colonnes>) : contrôle l'intégrité référentielle entre l'attribut et la table et ses colonnes spécifiées
• CHECK (<condition>) : contrôle la validité de la valeur de l'attribut spécifié dans la condition dans le cadre d'une restriction de domaine

Les contraintes d'intégrité sur une table sont :

• PRIMARY KEY (<liste d'attibuts>) : définit les attributs de la liste comme la clé primaire
• UNIQUE (<liste d'attibuts>) : interdit que deux tuples de la relation aient les mêmes valeurs pour l'ensemble des attributs de la liste.
• FOREIGN KEY (<liste d'attibuts>) REFERENCES <nom table>(<nom colonnes>) : contrôle l'intégrité référentielle entre les attributs de la liste et la table et ses colonnes spécifiées
• CHECK (<condition>) : contrôle la validité de la valeur des attributs spécifiés dans la condition dans le cadre d'une restriction de domaine

Les différentes contraintes d’intégrité :

• La contrainte de domaine
• La contrainte de relation
• La contrainte de référence ou contrainte d’intégrité référentielle

Le langage SQL usuel permet de nombreuses opérations sur les bases de données mais ne suffit malgré tout pas à implémenter toutes les règles de gestion inhérentes à celles-ci. En effet, il est par exemple impossible d’assurer l’intégrité des données dès lors que le modèle de données nécessite la mise en œuvre de contraintes d’intégrité avancées.

En particulier, les contraintes d’entités (l’héritage) ou encore les contraintes d’associations (inclusion, exclusion, etc.) ne peuvent être mises en œuvre au moyen de simples clefs étrangères ou encore de contraintes de domaine (CHECK). Pareillement, l’historisation ou encore la stabilité constituent des contraintes tout à fait modélisables en Merise 2 mais dont la mise en œuvre s’avère inenvisageable avec du SQL habituel. On ne pourra pas non plus envisager la mise en place de champs calculés, pourtant si pratiques aux fins d’optimiser les requêtes statistiques en outre.

Ainsi le langage SQL a-t ’il fait l’objet d’enrichissements successifs de sorte que de nouvelles notions sont apparues de manière à pouvoir solutionner des problématiques telles que celles évoquées ci-avants. Ces notions font même désormais partie de la norme SQL : trigger, événements, procédures et fonctions stockées.

C’est dans ce contexte que l’on étudie ci-après la notion de triggers. Procédures et fonctions stockées feront l’objet d’un cours venant compléter celui-ci.

Un trigger est une règle spécifiant une action à exécuter sur la BD, quand une condition est vérifiée, suite à une mise à jour ou une interrogation. C'est un algorithme exécuté à l’occasion d’un événement se produisant sur une table d’une base de données. Ce mécanisme est aussi nommé déclencheur. Un trigger est de la forme :

• sur <événement>
• si <condition>
• alors <action>

Exemple

• sur MAJ de la relation PRODUIT
• si PRODUIT.QTE < SEUIL
• alors passer une commande du produit

Les événements sont typiquement les suivants :

• BEFORE INSERT : pour exécuter un trigger avant l’insertion d’une nouvelle ligne au sein d’une table. La nouvelle ligne n’est donc pas encore dans la table au moment de l’exécution du trigger.
• AFTER INSERT : pour exécuter un trigger après l’insertion d’une nouvelle ligne au sein d’une table. La nouvelle ligne existe donc effectivement dans la table au moment de l’exécution du trigger.
• BEFORE UPDATE : pour exécuter un trigger avant la modification d’une ligne existant dans une table. L’ancienne ligne n’a pas encore été affectée par la modification au moment où le trigger est exécuté.
• AFTER UPDATE : pour exécuter un trigger après la modification d’une ligne qui existait dans une table. L’ancienne ligne a par conséquent déjà été affectée par la modification au moment où le trigger est exécuté.
• BEFORE DELETE : pour exécuter un trigger avant la suppression d’une ligne. L’ancienne ligne existe par conséquent encore au moment où le trigger est exécuté.
• AFTER DELETE : pour exécuter un trigger après la suppression d’une ligne. L’ancienne ligne n’existe par conséquent plus au moment où le trigger est exécuté.

Un trigger peut, au besoin être attaché à plusieurs événements (exemple : BEFORE INSERT, UPDATE).

Quoiqu’il en soit, un déclencheur est un réel algorithme. En cela, dès lors que l’on s’attaque à l’implémentation de trigger, le SQL devient un réel langage de programmation.

L’exécution d’un trigger est encapsulée dans ce qu’on appelle une transaction. Une transaction, c’est un ensemble de traitements qui doivent s’exécuter en bloc. Si l’un des traitements échoue, toute la transaction échoue et tous les traitements qui ont été exécutés sont annulés.

L’on peut valider une transaction. C’est ce qu’on appelle un COMMIT. Au contraire, l’on peut faire échouer une transaction. C’est ce qu’on qualifie de ROLLBACK.

En MySQL, conformément à la norme, on fait échouer une transaction en levant une erreur grâce à l’instruction SIGNAL.

Exemple :

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'An error occurred', MYSQL_ERRNO = 1001; 

Quel est l’intérêt de faire échouer une transaction ? Très simplement, la logique veut que, lors de l’exécution d’une requête, si une contrainte d’intégrité n’est pas vérifiée, la transaction en question échoue.

Que représente l’opérateur NEW ?

• Dans le code d’un trigger, le mot-clef NEW permet d’utiliser la ligne en cours d’insertion ou de modification. Syntaxe : NEW.nomChamp .

Que représente l’opérateur OLD ?

• Dans le code d’un trigger, le mot-clef OLD permet d’utiliser la ligne qui a été ou va être modifiée ou supprimée. Syntaxe : OLD.nomChamp .