Les déclencheurs, ou triggers, constituent un élément fondamental des bases de données SQL Server. Ils se présentent comme une forme spécifique de procédures qui s’activent en réponse à des manipulations précises de la base de données.
Ce guide vous apportera une compréhension approfondie des déclencheurs SQL, un outil précieux dans le domaine professionnel. Entamons cette exploration!
Définition des déclencheurs SQL
Le terme « déclencheur » fait référence à une directive qui ordonne au serveur d’exécuter automatiquement une requête dès qu’une modification survient dans la base de données.
Un déclencheur est, en substance, un ensemble de requêtes SQL stockées en mémoire et dotées d’un nom spécifique. Il s’agit d’une fonction spéciale qui est invoquée immédiatement lorsqu’un événement de base de données se produit. Chaque déclencheur est lié à une table particulière.
Par exemple, un déclencheur peut être activé lorsqu’une nouvelle colonne est ajoutée à une table donnée ou si des données spécifiques sont mises à jour.
Selon le Microsoft Developer Network, les déclencheurs sont une catégorie particulière de procédures stockées. Dans une définition de déclencheur, nous spécifions d’abord quand il doit être exécuté, puis nous précisons l’action à entreprendre suite à son activation.
Syntaxe :
CREATE TRIGGER nom_du_declencheur BEFORE/AFTER INSERT/UPDATE/DELETE ON nom_de_la_table FOR EACH ROW SET operation [corps_du_declencheur];
Explication des paramètres :
- CREATE TRIGGER nom_du_declencheur – Commande pour créer ou modifier le nom d’un déclencheur.
- AVANT/APRÈS – Indique le moment d’exécution du déclencheur (avant ou après l’événement).
- INSERT/UPDATE/DELETE – Spécifie l’opération qui déclenche l’exécution.
- ON nom_de_la_table – Définit la table sur laquelle le déclencheur est actif.
- POUR CHAQUE LIGNE – S’applique aux déclencheurs de ligne, s’exécutant à chaque modification d’une ligne.
- corps_du_declencheur – Action à exécuter lorsque le déclencheur est activé.
Les déclencheurs sont des fonctions sauvegardées, avec des identifiants uniques, permettant la réutilisation de requêtes stockées en mémoire. Voyons maintenant pourquoi SQL en a besoin.
Les déclencheurs servent principalement à contrôler le déploiement de code en réponse à un événement. Ainsi, ils sont idéaux pour exécuter un fragment de code spécifique en réaction à un événement particulier.
Voici quelques avantages à utiliser des déclencheurs dans les opérations de bases de données SQL :
L’intégrité référentielle est un principe essentiel des systèmes de gestion de bases de données relationnelles. Elle garantit que les données sont précises pour chaque transaction et opération.
Si deux tables se trouvent dans des bases ou systèmes différents, il est difficile de valider les données à l’aide de contraintes. Dans ce cas, les déclencheurs représentent la seule solution applicable.
Combinaison des arguments de déclencheurs
Il est possible de définir six types de déclencheurs différents pour chaque table. Il s’agit de combinaisons d’arguments de déclencheurs dans les déclencheurs au niveau des lignes.
BEFORE INSERT : Ces déclencheurs s’exécutent avant toute opération INSERT sur la table ou base de données.
AFTER INSERT : Ils s’activent immédiatement après toute insertion dans la base de données.
BEFORE UPDATE : Une fonction est exécutée sur les lignes avant l’exécution d’une opération UPDATE.
AFTER UPDATE : L’action s’effectue sur les lignes suite à toute mise à jour de la base ou de la table.
BEFORE DELETE : L’opération est effectuée avant toute suppression dans la base ou la table.
AFTER DELETE : Ces déclencheurs s’exécutent après chaque opération DELETE.
Types de déclencheurs SQL
Les déclencheurs SQL sont des fonctions stockées qui s’exécutent automatiquement lors d’événements spécifiques, à l’image d’une programmation événementielle. Voici les contextes d’activation des déclencheurs :
Déclencheurs DML – DML (Data Manipulation Language) permet l’exécution de code en réponse à des modifications de données. Ces déclencheurs sont activés lors de commandes DML telles que INSERT, UPDATE et DELETE. On les appelle également « déclencheurs au niveau de la table ».
Déclencheurs DDL – DDL (Data Definition Language) permet d’exécuter du code en réaction à des modifications de schéma de base, comme l’ajout ou suppression de tables, ou d’événements serveurs, comme l’enregistrement d’un utilisateur. Ce sont des « déclencheurs au niveau de la base de données ».
Ils peuvent s’activer lors d’instructions DDL comme CREATE, ALTER ou DROP dans la base active. Ils servent aussi à surveiller et gérer les activités réalisées.
Déclencheurs de connexion – Ils s’activent lors d’événements de connexion (démarrage, connexion, déconnexion, arrêt), après l’authentification et avant toute transaction utilisateur. Ils ne s’activent pas en cas d’échec d’autorisation.
Ces déclencheurs peuvent servir à enregistrer l’historique des connexions ou à établir des restrictions pour une connexion, entre autres fonctions d’audit et de gestion des identités.
Déclencheurs CLR – CLR (Common Language Runtime) désigne un sous-ensemble de déclencheurs construits sur le CLR de la technologie .NET. Utiles si le déclencheur doit effectuer des calculs importants ou s’interfacer avec des entités externes à SQL.
Les déclencheurs DML et DDL peuvent être construits en utilisant des langages comme Visual Basic, C# et F-sharp, supportés par les technologies .NET.
Exemple de déclencheur SQL Server
Illustrons ces concepts avec un exemple.
Commençons par créer une base de données avec des commandes SQL :
CREATE DATABASE testdb; use testdb;
Ici, la base de données s’appelle « testdb ». L’étape suivante est la création d’une table :
CREATE TABLE student( name varchar(25), id int(2), maths int(2), physics int(2), biology int(2), social int(2), total int(2) );
Une table pour stocker les détails des étudiants. Voici la commande pour décrire la structure de la table ; « étudiant » est le nom de la table.
DESC student;
Voici la structure de la table créée :
| Field | Type | Null | Key | Default | Extra |
| name | varchar(25) | YES | NULL | ||
| id | int | YES | NULL | ||
| maths | int | YES | NULL | ||
| physics | int | YES | NULL | ||
| biology | int | YES | NULL | ||
| social | int | YES | NULL | ||
| total | int | YES | NULL |
7 rows in set (0.00 sec)
Après la table, nous configurons un déclencheur. Utilisons l’argument BEFORE INSERT.
Le déclencheur est nommé « marques ». Dès que la table est modifiée avec des notes, le déclencheur ci-dessous calcule automatiquement la note totale de l’élève.
CREATE TRIGGER marks BEFORE INSERT ON student FOR EACH ROW set new.total=new.maths+new.physics+new.biology+new.social;
Les données des lignes sont remplacées et non traitées, donc le « total » est défini en utilisant le mot-clé ‘new’ et l’opérateur point. Ajoutons des valeurs et observons les résultats. La note totale est initialement de 0 pour chaque élève.
INSERT INTO student VALUES("George",02,99,87,92,91,0);
INSERT INTO student VALUES("James",03,91,81,94,90,0);
INSERT INTO student VALUES("Harry",04,86,70,73,88,0);
INSERT INTO student VALUES("John",05,73,89,78,92,0);
INSERT INTO student VALUES("Lisa",01,94,75,69,79,0);
L’instruction de déclenchement s’active automatiquement lors de l’insertion de données dans la table « student ». Le total des notes est calculé par le déclencheur. Vérifions son activation par une instruction SELECT.
SELECT * FROM table_name;
Voici le résultat final :
mysql> select * from student;
| name | id | maths | physics | biology | social | total |
| George | 2 | 91 | 81 | 94 | 90 | 356 |
| James | 3 | 86 | 70 | 73 | 88 | 317 |
| Harry | 4 | 73 | 89 | 78 | 92 | 332 |
| John | 5 | 94 | 75 | 69 | 79 | 317 |
| Lisa | 1 | 99 | 87 | 92 | 91 | 369 |
Dans le résultat, on constate que toutes les notes sont automatiquement additionnées pour chaque étudiant. Le déclencheur a donc fonctionné avec succès.
Opérations additionnelles sur les déclencheurs
Diverses opérations sont possibles avec les déclencheurs, certaines simples, d’autres plus complexes, mais faciles à comprendre une fois les requêtes assimilées. Les instructions Transact-SQL permettent d’activer, désactiver ou supprimer les déclencheurs à l’aide des commandes suivantes :
Requête pour vérifier l’existence d’un déclencheur
Cette commande recherche un déclencheur spécifique dans toute la base de données.
SELECT * FROM [sys].[triggers] WHERE [name] = 'Nom_du_declencheur'
Requête pour afficher les déclencheurs
L’instruction suivante liste tous les déclencheurs de la base de données active.
SHOW TRIGGERS;
Requête pour désactiver un déclencheur
La commande ci-dessous désactive un déclencheur de la base en cours d’utilisation.
DISABLE TRIGGER Nom_du_declencheur ON DATABASE;
Il est également possible de spécifier une table pour désactiver un déclencheur.
DISABLE TRIGGER Nom_du_declencheur ON Nom_de_la_table;
Requête pour activer un déclencheur
La commande suivante désactive puis réactive un déclencheur sur une table spécifique dans la base de données active.
ALTER TABLE Nom_de_la_table DISABLE TRIGGER Nom_du_declencheur ALTER TABLE Nom_de_la_table ENABLE TRIGGER Nom_du_declencheur
Un déclencheur doit être désactivé avant d’être activé.
Requête pour activer ou désactiver tous les déclencheurs d’une table
En remplaçant un nom de déclencheur spécifique par « ALL », on active ou désactive tous les déclencheurs d’une table.
ALTER TABLE Nom_de_la_table DISABLE TRIGGER ALL ALTER TABLE Nom_de_la_table ENABLE TRIGGER ALL
Requête pour supprimer un déclencheur
Un déclencheur est éliminé par sa suppression ou par suppression de la table associée. La suppression d’une table supprime tous les déclencheurs liés.
DROP TRIGGER [Nom_du_declencheur];
Lors de la suppression d’un déclencheur, les données associées sont supprimées de la table sys.objects.
Avantages des déclencheurs
- Les déclencheurs sont simples à créer et peuvent invoquer des fonctions et méthodes stockées.
- Ils permettent une mise en œuvre facile de l’audit.
- Bien qu’il ne soit pas possible de créer des contraintes sur les entités dans SQL Server, on peut émuler ce fonctionnement avec des déclencheurs.
- Ils sont utiles pour implémenter des contraintes d’intégrité.
- Les déclencheurs sont pratiques lorsqu’une validation groupée est requise plutôt qu’une vérification ligne par ligne des données insérées ou modifiées.
Inconvénients des déclencheurs
Dans certaines situations, les déclencheurs SQL ne sont pas le meilleur choix en raison de leurs limites :
- Ils doivent être documentés avec précision.
- En raison d’exécutions simultanées, il peut être difficile de déboguer les déclencheurs.
- L’utilisation de déclencheurs complexifie les instructions DML.
- Un problème de déclencheur, même mineur, peut causer des erreurs logiques.
Conclusion
Les déclencheurs sont des composants essentiels de Transact-SQL et SQL, utilisables aussi dans Oracle. Ils sont cruciaux pour l’invocation de méthodes stockées. Ils permettent d’analyser les chronologies d’activité et d’y réagir si nécessaire. Il est également possible de rechercher une table liée à un déclencheur pour obtenir des informations.
Les déclencheurs peuvent activer la récursivité. Chaque fois qu’un déclencheur sur une table exécute une commande sur la table parente, une deuxième itération du déclencheur est activée, c’est un déclencheur récursif, utile lors de la résolution d’une corrélation d’identité.
De plus, les déclencheurs gèrent le modèle de mise à jour que la base peut accepter. Ils sont bénéfiques pour maintenir l’intégrité des données, notamment en l’absence de clés SQL (clé primaire et clé étrangère).
J’espère que cet article vous a éclairé sur les déclencheurs SQL.
Si vous souhaitez approfondir vos connaissances sur les bases de données, voici d’excellentes ressources pour apprendre SQL et NoSQL.