Les déclencheurs sont des entités de base de données dans SQL Server. Techniquement, il s’agit d’une classe particulière d’appels de fonctions qui répondent à des opérations spécifiques de la base de données.

Ce guide essentiel vous fournira des informations approfondies sur les déclencheurs SQL qui peuvent s’avérer très utiles dans votre profession. Commençons par le commencement !

Que sont les déclencheurs SQL ?

Le mot “trigger” décrit une instruction qui permet à un serveur d’exécuter automatiquement la requête à chaque fois que le contenu de la base de données est modifié.

Un déclencheur est un groupe de requêtes SQL spécialement nommées qui sont stockées dans l’espace mémoire. Il s’agit d’un type spécifique d’appel de fonction qui est immédiatement invoqué chaque fois qu’un événement se produit dans la base de données. Chaque déclencheur est associé à une table.

What-are-SQL-Triggers

Par exemple, un déclencheur peut être activé lorsqu’une nouvelle colonne est ajoutée à une table particulière ou lorsque des enregistrements spécifiques sont modifiés.

Selon le Microsoft Developer Network, les déclencheurs sont une classe particulière de procédures stockées. Dans une instruction de déclenchement, nous définissons d’abord le moment où le déclencheur doit être exécuté, puis nous indiquons l’action qui doit être effectuée après l’activation du déclencheur.

Syntaxe :

CREATE TRIGGER nom_du_gâchette
AVANT/APRÈS
INSÉRER/METTRE À JOUR/SUPPRIMER
ON nomTable
FOR EACH ROW SET operation [trigger_body];

Explication de chaque paramètre

  • CREATE TRIGGER nom_du_déclencheur – Il est utilisé pour construire un déclencheur ou pour modifier le nom d’un déclencheur existant.
  • BEFORE/AFTER – Cette requête est utilisée pour définir le moment d’exécution du déclencheur (avant ou après un certain événement).
  • INSERT/UPDATE/DELETE – Ceci décrit l’action que nous souhaitons effectuer sur les tables.
  • ON nomTable – Ici, nous définissons le nom de la table pour configurer un déclencheur.
  • FOR EACH ROW – Cette déclaration se rapporte au déclencheur de ligne, ce qui signifie que les déclencheurs seront exécutés chaque fois qu’une ligne est modifiée.
  • corps_du_déclencheur – Il spécifie l’action à effectuer lorsque le déclencheur est activé.

Les déclencheurs sont des fonctions stockées avec des identités distinctes qui nous permettent de réutiliser des requêtes qui ont déjà été exécutées et stockées en toute sécurité dans la mémoire. Essayons maintenant de comprendre pourquoi SQL en a besoin.

Les triggers sont principalement utilisés pour réguler le déploiement du code lorsqu’un événement se produit. En d’autres termes, l’utilisation des déclencheurs est le choix idéal si vous souhaitez qu’un fragment de code spécifique soit constamment exécuté en réponse à un événement précis.

Voici quelques avantages de l’utilisation des déclencheurs dans les opérations de base de données SQL.

  1. Effectue des vérifications supplémentaires lors de l’insertion, de la mise à jour ou de la suppression de données dans la table concernée.
  2. Réduit les temps de réponse, ce qui contribue à augmenter les dépenses informatiques.
  3. Permet l’encodage de paramètres par défaut sophistiqués qui sont inaccessibles par les contraintes initiales.

L’intégrité référentielle est une propriété essentielle des systèmes de bases de données relationnelles. Cela signifie que les données conservées dans le système de base de données doivent toujours être exactes pour chaque transaction et chaque opération.

Si deux tables se trouvent dans des bases de données ou des systèmes distincts, il n’est pas possible d’assurer la validation des données à l’aide de valeurs de contrainte. Dans une telle situation, les déclencheurs sont la seule option d’exécution.

Combinaison d’arguments de déclenchement

Combination-of-Trigger-Arguments

Pour chaque table, nous pouvons spécifier six types de déclencheurs différents. Il s’agit de la combinaison des arguments des déclencheurs inclus dans les déclencheurs SQL au niveau des lignes.

BEFORE INSERT: ces déclencheurs exécutent l’action sur les lignes avant d’effectuer toute opération INSERT dans la table spécifiée ou dans la base de données.

AFTER INSERT: il exécute l’action sur les lignes immédiatement après toute activité INSERT dans la base de données.

AVANT UPDATE: avec ces déclencheurs, une fonction sur les lignes est exécutée avant qu’une action UPDATE ne soit effectuée sur la base de données.

APRÈS LA MISE À JOUR: il exécute l’action sur les lignes immédiatement après une activité de mise à jour de la base de données ou d’une table spécifique.

BEFORE DELETE: il exécute une certaine opération sur les lignes avant même que la base de données ou la table ne fasse l’objet d’une action DELETE.

AFTER DELETE: ces déclencheurs exécutent l’action sur les lignes après chaque transaction DELETE.

Types de déclencheurs SQL

Types-of-SQL-Triggers

Les déclencheurs SQL sont des fonctions stockées qui s’exécutent immédiatement lorsque des événements spécifiques se produisent. Ils s’apparentent à une planification basée sur les événements. Les situations suivantes peuvent déclencher l’exécution des déclencheurs.

Déclencheurs DML – DML signifie Data Manipulation Language (langage de manipulation de données). L’exécution du code en réaction à la modification des données est rendue possible grâce aux déclencheurs DML. Ce déclencheur est activé lorsque des commandes DML telles que INSERT, UPDATE et DELETE sont exécutées. Ces déclencheurs sont également appelés “déclencheurs au niveau de la table”.

Déclencheurs DD L – DDL est l’abréviation de Data Definition Language (langage de définition des données). Les déclencheurs DDL nous permettent d’exécuter du code en réaction à des modifications du schéma de la base de données, telles que l’ajout ou la suppression de tables, ou à des événements du serveur, tels que l’enregistrement d’un utilisateur. C’est ce qu’on appelle les “déclencheurs au niveau de la base de données”.

Ces déclencheurs peuvent être activés lorsque certaines instructions DDL telles que CREATE, ALTER ou DROP sont exécutées dans la base de données active. Ils peuvent également être utilisés pour garder un œil sur les activités exécutées et les gérer.

Déclencheurs de connexion – Chaque fois qu’un événement LOGON (démarrage, connexion, déconnexion, arrêt) se produit, les déclencheurs de connexion sont immédiatement invoqués. Ils ne sont exécutés qu’après un processus d’authentification de l’utilisateur, avant même que la transaction de l’utilisateur ne soit initiée. Les déclencheurs de connexion ne sont pas déclenchés en cas d’échec de l’autorisation.

Ces déclencheurs peuvent être utilisés pour enregistrer l’historique des connexions ou établir une restriction d’événement pour une connexion particulière, parmi d’autres fonctions d’audit et de gestion de l’identité pour les connexions au serveur.

Déclencheurs CLR – CLR est l’abréviation de Common Language Runtime. Les déclencheurs CLR sont en effet un sous-ensemble unique de déclencheurs construits principalement sur CLR au sein de la technologie .NET. Ces déclencheurs sont utiles si le déclencheur doit effectuer de nombreux calculs ou s’il doit se rapporter à une entité autre que SQL.

Les déclencheurs DML et DDL peuvent en effet être construits en permettant le codage des déclencheurs CLR pris en charge dans les technologies .NET, notamment Visual Basic, C# et F-sharp.

Exemple de déclencheur SQL Server

Sample-SQL-Server-Trigger

Comprenons ces concepts de déclenchement à l’aide d’un exemple.

Tout d’abord, créons une base de données à l’aide d’ instructions SQL.

CREATE DATABASE testdb ;
utilisez testdb ;

Ici, j’ai donné le nom “testdb” à la base de données. L’étape suivante consiste à créer une table. 

CREATE TABLE student(
  name varchar(25),
  id int(2),
  maths int(2),
  physique int(2),
  biologie int(2),
  social int(2),
  total int(2)
 ) ;

J’ai créé une table pour stocker les informations relatives aux étudiants. Voici la commande qui décrit la structure de la table. Ici, “étudiant” est le nom de la table que j’ai donné.

DESC étudiant ;

Vous trouverez ci-dessous la structure de la table que j’ai créée. 

 --------- ------------- ------ ----- --------- ------- 
| Champ | Type | Nul | Clé | Défaut | Extra |
 --------- ------------- ------ ----- --------- ------- 
| nom | varchar(25) | OUI | | | NULL | | | id | int | OUI | | NULL | NULL
| id | int | OUI | | NULL | | | | maths | int | OUI | NULL | | NULL | | NULL
| maths | int | OUI | | NULL | | | physique | int | OUI | NULL | | NULL | | NULL
| physique | int | YES | | NULL | | | biologie | int | YES | NULL | | NULL | NULL | NULL
| biologie | int | YES | | NULL | | | sciences sociales | int | YES | | NULL | | | physique
| social | int | OUI | | NULL | | | total | int | OUI | NULL | | NULL | NULL
| total | int | OUI | | NULL | | | | social | int | OUI | NULL | | NULL | | NULL
 --------- ------------- ------ ----- --------- ------- 
7 lignes dans le jeu (0.00 sec)

Après avoir créé une table, l’étape suivante consiste à configurer un déclencheur. Essayons d’utiliser l’argument BEFORE INSERT.

Le nom du trigger que j’ai créé est “marks”. Dès que la table est modifiée avec les notes de l’étudiant, le déclencheur ci-dessous tente de déterminer automatiquement la note globale de l’étudiant.

CREATE TRIGGER notes
AVANT INSERT
ON
étudiant
POUR CHAQUE LIGNE
set new.total=new.maths new.physics new.biology new.social ;

Puisque nous devons remplacer les données des lignes plutôt que de travailler avec les anciennes, nous avons défini “total” en utilisant un nouveau nom de classe, et toutes les expressions suivantes sont préfixées avec de nouveaux mots-clés après total en utilisant l’opérateur point. Nous allons maintenant ajouter des valeurs à chaque ligne et voir les résultats. Initialement, le total des notes est de 0 pour chaque étudiant. 

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) ;

Dans ce cas, l’instruction de déclenchement sera automatiquement déclenchée lorsque des données seront insérées dans la table des étudiants. Les notes totales de chaque étudiant seront calculées par le déclencheur. Voyons maintenant si le déclencheur est invoqué ou non à l’aide d’une instruction SELECT.

SELECT * FROM nom_table ;

Voici le résultat final.

mysql> select * from student ;
 -------- ------ ------- --------- --------- -------- ------- 
| nom | id | maths | physique | biologie | social | total |
 -------- ------ ------- --------- --------- -------- ------- 
| George - 2 - 91 - 81 - 94 - 90 - 356 - 3 - 86 - 70 - 70 - 70 - 90 - 90 - 90 - 90 - 90 - 356
| James | 3 | 86 | 70 | 73 | 88 | 317 |
| Harry | 4 | 73 | 89 | 78 | 92 | 332 |
| John - 5 - 94 - 75 - 69 - 79 - 317 - 1 - 99 - 87 - 90 - 90 - 90 - 90 - 356 - 1 - 86 - 70 - 73 - 88 - 317
| Lisa | 1 | 99 | 87 | 92 | 91 | 369 |
 -------- ------ ------- --------- --------- -------- ------- 
5 lignes dans le jeu (0.00 sec)

Dans le résultat ci-dessus, vous pouvez voir que toutes les notes des matières sont automatiquement ajoutées pour chaque étudiant. Nous pouvons donc conclure que le déclencheur a été invoqué avec succès.

Opérations supplémentaires sur les déclencheurs

Nous pouvons effectuer de nombreuses opérations à l’aide de déclencheurs. Certaines peuvent être simples et d’autres un peu complexes, mais une fois que nous avons parcouru les requêtes, il est facile de les comprendre. En utilisant des instructions Transact-SQL, vous pouvez activer, désactiver ou supprimer les déclencheurs à l’aide des commandes suivantes.

Requête pour vérifier si un déclencheur spécifique est présent ou non

Cette commande vérifie la présence du déclencheur spécifié dans l’ensemble de la base de données.

SELECT * FROM [sys].[triggers] WHERE [name] = 'Trigger_name' (nom du déclencheur)

Requête pour afficher les déclencheurs

Tous les déclencheurs disponibles dans la base de données active seront affichés par l’instruction suivante.

SHOW TRIGGERS ;

Requête pour désactiver un déclencheur

La commande ci-dessous désactive le déclencheur dans la base de données active.

DISABLE TRIGGER nom_du_gâchette SUR LA BASE DE DONNÉES ;

Vous pouvez également spécifier un certain nom de table pour désactiver un déclencheur.

DISABLE TRIGGER nom_du_déclencheur SUR nom_de_la_table ;

Requête pour activer un déclencheur

La commande suivante désactive d’abord un déclencheur spécifique défini sur la table spécifiée dans la base de données active avant de le réactiver.

ALTER TABLE nom_table DISABLE TRIGGER nom_du_déclencheur

ALTER TABLE nom_table ENABLE TRIGGER nom_du_déclencheur

Le déclencheur doit être désactivé avant d’essayer de l’activer,

Requête pour activer ou désactiver tous les déclencheurs d’une table

En utilisant l’instruction SQL ci-dessus, nous pouvons désactiver ou activer tous les déclencheurs d’une table à la fois en remplaçant le nom d’un déclencheur spécifique par “ALL”.

ALTER TABLE nom_table DISABLE TRIGGER ALL 

ALTER TABLE nom_table ENABLE TRIGGER ALL

Requête de suppression ou d’abandon d’un déclencheur

Un déclencheur peut être éliminé en le supprimant ou en supprimant la table entière. Tous les déclencheurs associés sont également supprimés lorsqu’une table est supprimée.

DROP TRIGGER [nom_du_déclencheur];

Lorsqu’un déclencheur est supprimé, les données correspondantes sont éliminées de la table de données sys.objects.

Avantages des déclencheurs

  • Il est facile de créer des déclencheurs et le déclencheur lui-même peut invoquer des fonctions et des méthodes stockées.
  • Les utilisateurs peuvent mettre en œuvre un audit simple à l’aide des déclencheurs.
  • Malheureusement, vous ne pouvez pas créer de contraintes sur les entités dans les systèmes de base de données avec SQL Server, bien que vous puissiez émuler le fonctionnement des contraintes en utilisant des déclencheurs.
  • Les contraintes d’intégrité peuvent être mises en œuvre dans les bases de données à l’aide de déclencheurs.
  • Lorsque la validation de groupe est requise plutôt que la vérification ligne par ligne des données nouvellement saisies ou modifiées, les déclencheurs peuvent s’avérer utiles.

Inconvénients des déclencheurs

Les déclencheurs SQL peuvent ne pas être le meilleur choix dans certaines situations en raison de leurs limites.

  • Les déclencheurs doivent être documentés avec précision.
  • En raison de l’exécution simultanée de la base de données, qui peut ne pas être accessible aux composants de l’application, les déclencheurs peuvent être difficiles à déboguer.
  • Les instructions DML deviennent plus complexes lorsque des déclencheurs sont utilisés.
  • Même un problème mineur au niveau des déclencheurs peut entraîner des erreurs logiques dans l’instruction.

Conclusion

Les déclencheurs sont des composants très utiles de Transact-SQL et de SQL, et vous pouvez également les utiliser dans Oracle. L’utilisation des déclencheurs est cruciale lors de l’appel de méthodes stockées. Ces déclencheurs SQL nous permettent d’analyser les délais d’activité et de déterminer comment y répondre si nécessaire. Nous pouvons également rechercher une certaine table qui est connectée à un déclencheur pour acquérir des données.

La récursivité peut être activée par les déclencheurs. Lorsqu’un déclencheur sur une table exécute une commande sur la table parente, la deuxième itération du déclencheur est déclenchée, ce que l’on appelle un déclencheur récursif. Cela permet de résoudre les problèmes de corrélation d’identité.

En outre, les déclencheurs régulent le modèle de mise à jour que la base de données est autorisée à accepter. Il est très utile de conserver les contraintes d’intégrité des données dans le système de base de données si les clés de contrainte SQL n’existent pas, principalement la clé primaire et la clé étrangère.

J’espère que cet article vous a été utile dans votre apprentissage des déclencheurs SQL.

Si vous souhaitez apprendre les bases de données en profondeur, voici d’excellentes ressources pour apprendre SQL et NoSQL.