Déclencheurs SQL : le guide essentiel

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 fonction qui répond à des opérations de base de données spécifiques.

Ce guide essentiel vous donnera des informations détaillées sur SQL Des déclencheurs qui peuvent être très utiles dans votre métier. Commençons!

What are SQL Triggers?

Le mot « déclencheur » décrit une déclaration indiquant qu'un serveur exécute 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 qui sont stockés 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 de base de données se produit. Chaque déclencheur a une table qui lui est assignée.

Par exemple, un déclencheur peut être activé chaque fois qu'une nouvelle colonne est ajoutée à une table particulière ou si des enregistrements spécifiques sont modifiés.

Selon Microsoft Developer Network, les déclencheurs sont une classe particulière de procédures stockées. Dans une instruction de déclencheur, nous définissons initialement le moment où le déclencheur doit être exécuté, puis nous fournissons l'action à entreprendre après l'activation du déclencheur.

syntaxe:

CREATE TRIGGER trigger_name
BEFORE/AFTER
INSERT/UPDATE/DELETE
ON tableName
FOR EACH ROW SET operation [trigger_body];

Explication de chaque paramètre

• CREATE TRIGGER nom_trigger – Il est utilisé pour construire un déclencheur ou pour changer le nom d'un déclencheur existant.
• AVANT APRÈS – Cette requête est utilisée pour définir le temps d'exécution du déclencheur (avant ou après un certain événement).
• INSÉRER/MISE À JOUR/SUPPRIMER – Ceci décrit l'action que nous souhaitons effectuer sur les tables.
• ON tableName – Ici, nous définissons le nom de la table pour configurer un déclencheur.
• POUR CHAQUE ROW – Cette instruction concerne le déclencheur de ligne, ce qui signifie que les déclencheurs seront exécutés chaque fois qu'une ligne est modifiée.
• trigger_body – Il spécifie l'action à entreprendre 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 déclencheurs sont principalement utilisés pour réguler le déploiement de code chaque fois qu'un événement se produit. En d'autres termes, l'utilisation de déclencheurs est le choix idéal si vous avez besoin qu'un fragment de code spécifique soit constamment exécuté en réponse à un événement spécifié.

Voici quelques avantages de l'utilisation de 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 de 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 sur des bases de données ou des systèmes distincts, il n'existe aucun moyen d'assurer la validation des données qu'elles contiennent à l'aide de valeurs de contrainte. Dans une telle situation, les déclencheurs sont la seule option d'exécution.

Combination of Trigger Arguments

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

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

APRÈS INSERTION: Il exécute l'action sur les lignes suivant immédiatement toute activité INSERT de la base de données.

AVANT LA MISE À JOUR: Avec ces triggers, une fonction sur les lignes est effectuée avant qu'une action UPDATE ne soit effectuée sur la base.

APRÈS LA MISE À JOUR: Il exécute l'action sur les lignes suivant immédiatement toute activité UPDATE de base de données ou de table spécifique.

AVANT DE SUPPRIMER: Il exécute une certaine opération sur les lignes avant même que la base de données ou la table ne soit soumise à une action DELETE.

APRÈS SUPPRIMER: Ces déclencheurs exécutent l'action sur les lignes suivant chaque transaction DELETE.

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. Cela ressemble à la planification événementielle. Les situations suivantes peuvent déclencher l'exécution des déclencheurs.

Déclencheurs DML – DML signifie Data Manipulation Language. L'exécution de 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. Ceux-ci sont également appelés "déclencheurs au niveau de la table".

Déclencheurs DDL – DDL signifie Data Definition Language. Les déclencheurs DDL nous permettent d'exécuter du code en réaction aux modifications du schéma de la base de données, telles que l'ajout ou la suppression de tables, ou des événements de serveur, comme lorsqu'un utilisateur s'enregistre. Ceux-ci sont appelés «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. Ceux-ci peuvent également être utilisés pour surveiller et gérer les activités qui sont exécutées.

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 effectué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 LOGON ne seront pas déclenchés si l'autorisation échoue.

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, entre autres fonctions d'audit et de gestion des identités pour les connexions au serveur.

Déclencheurs CLR – CLR signifie 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 doit être lié à une entité autre que SQL.

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

Sample SQL Server Trigger

Comprenons ces concepts de déclencheur avec un exemple.

Commençons par créer une base de données à l'aide d'instructions SQL.

CREATE DATABASE testdb;
use testdb;

Ici, j'ai donné un "testdb" comme nom de la base de données. Et la prochaine étape consiste à créer 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)
 );

J'ai créé une table pour stocker les détails de l'étudiant. Et voici la commande pour décrire la structure de la table. Ici "étudiant" est le nom de table que j'ai donné.

DESC student;

Vous trouverez ci-dessous la structure du tableau que j'ai créé.

+---------+-------------+------+-----+---------+-------+
| 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 avoir créé une table, l'étape suivante consiste à configurer un déclencheur. Essayons d'utiliser le AVANT INSÉRER argument.

Le nom du déclencheur que j'ai créé est "marques". Dès que le tableau est modifié avec les notes de l'élève, le déclencheur ci-dessous tente de déterminer automatiquement la note globale 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;

Étant donné que 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. Maintenant, nous allons ajouter des valeurs à chaque ligne et voir les résultats. Initialement, la note totale est 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 sera automatiquement déclenchée lorsque les données seront insérées dans la table des étudiants dans ce cas. Le total des notes de chaque élève sera calculé par le déclencheur. Voyons maintenant si le déclencheur est appelé ou non à l'aide d'un SELECT déclaration.

SELECT * FROM table_name;

Et voici la sortie finale.

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 |
+--------+------+-------+---------+---------+--------+-------+
5 rows in set (0.00 sec)

Dans le résultat ci-dessus, vous pouvez voir que toutes les notes de matière sont automatiquement ajoutées pour chaque élève. Ainsi, nous pouvons conclure que le déclencheur a été appelé avec succès.

Additional Trigger Operations

Nous pouvons effectuer de nombreuses opérations à l'aide de déclencheurs. Certains peuvent être simples et d'autres peuvent être un peu complexes, mais une fois que nous avons parcouru les requêtes, il est facile à 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 là ou non

Cette commande recherche le déclencheur spécifié dans toute la base de données.

SELECT * FROM [sys].[triggers] WHERE [name] = 'Trigger_name'

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 le déclencheur

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

DISABLE TRIGGER trigger_name ON DATABASE;

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

DISABLE TRIGGER trigger_name ON table_name;

Requête pour activer le déclencheur

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

ALTER TABLE table_name DISABLE TRIGGER trigger_name

ALTER TABLE table_name ENABLE TRIGGER trigger_name

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

À l'aide de l'instruction SQL ci-dessus, nous pouvons désactiver ou activer tous les déclencheurs de table à la fois en remplaçant "ALL" à la place d'un nom de déclencheur spécifique.

ALTER TABLE table_name DISABLE TRIGGER ALL 

ALTER TABLE table_name ENABLE TRIGGER ALL

Requête pour supprimer ou supprimer un déclencheur

Un déclencheur peut être éliminé en le supprimant ou en supprimant tout le tableau. Chaque déclencheur associé est également supprimé lorsqu'une table est supprimée.

DROP TRIGGER [trigger_name];

Chaque fois qu'un déclencheur est supprimé, les données associées sont supprimées de la table de données sys.objects.

Advantages of Triggers

• Il est simple 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 implémenter un audit simple à l'aide de déclencheurs.
• Tragiquement, vous ne pouvez pas créer de contraintes entre 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 implémentées dans les bases de données à l'aide de déclencheurs.
• Lorsqu'une validation de groupe est requise plutôt qu'une vérification ligne par ligne des données nouvellement saisies ou modifiées, les déclencheurs peuvent être utiles.

Disadvantages of Triggers

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 de déclencheur mineur peut entraîner des erreurs logiques dans l'instruction.

Conclusion

Les déclencheurs sont des composants très utiles de Transact-SQL et SQL, et vous pouvez également les utiliser dans Oracle. L'utilisation de déclencheurs est cruciale lors de l'appel de méthodes stockées. Ces déclencheurs SQL nous permettent d'analyser les chronologies 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 des déclencheurs. Chaque fois qu'un déclencheur sur une table exécute une commande sur la table parent, la deuxième itération du déclencheur est déclenchée, et c'est ce qu'on appelle un déclencheur récursif. Cela aide tout en essayant de résoudre une corrélation d'identité.

De plus, 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 avantageux 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 vous avez trouvé cet article utile pour en savoir plus sur les déclencheurs SQL.

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