Les déclencheurs SQL sont un autre objet de base de données puissant que nous avons à notre disposition. Dans les articles précédents, nous avons abordé les fonctions définies par l’utilisateur, les procédures définies par l’utilisateur et les SQL Views. Aujourd’hui, nous allons parler des déclencheurs SQL et de la façon de les utiliser pour obtenir le comportement souhaité.
Le modèle
Avant de passer au sujet de cet article, jetons un rapide coup d’œil au modèle que nous utilisons dans cet article mais aussi tout au long de cette série.
Dans cet article, nous nous concentrerons sur les déclencheurs DML (langage de manipulation des données) et montrerons comment ils fonctionnent lorsque nous apportons des modifications à une seule table.
Que sont les déclencheurs SQL ?
Dans SQL Server, les déclencheurs sont des objets de base de données, en fait, un type spécial de procédure stockée, qui « réagit » à certaines actions que nous faisons dans la base de données. L’idée principale derrière les déclencheurs est qu’ils exécutent toujours une action au cas où un certain événement se produit. Si nous parlons de déclencheurs DML, ces changements seront des changements dans nos données. Examinons quelques situations intéressantes :
- Dans le cas où vous effectuez une insertion dans la table des appels, vous voulez mettre à jour que le client lié a 1 appel de plus (dans ce cas, nous devrions avoir l’attribut integer dans la table des clients)
- Lorsque vous terminez un appel (mise à jour de call.end_time valeur de l’attribut), vous voulez augmenter le compteur des appels effectués par cet employé au cours de cette journée (encore une fois, nous devrions avoir un tel attribut dans la table employee)
- Lorsque vous essayez de supprimer un employé, vous voulez vérifier s’il a des appels liés. Si c’est le cas, vous empêcherez cette suppression et soulèverez une exception personnalisée
À partir des exemples, vous pouvez remarquer que les déclencheurs DML sont des actions liées aux commandes SQL définies dans ces déclencheurs. Comme ils sont similaires aux procédures stockées, vous pouvez tester des valeurs en utilisant l’instruction IF, etc. Cela offre beaucoup de flexibilité.
La bonne raison d’utiliser les déclencheurs SQL DML est le cas où vous voulez assurer qu’un certain contrôle sera effectué avant ou après l’instruction définie sur la table définie. Cela peut être le cas lorsque votre code se trouve un peu partout, par exemple la base de données est utilisée par différentes applications, le code est écrit directement dans les applications et vous ne l’avez pas bien documenté.
Types de déclencheurs SQL
Dans SQL Server, nous avons 3 groupes de déclencheurs :
- DML (langage de manipulation des données) triggers – Nous les avons déjà mentionnés, et ils réagissent aux commandes DML. Il s’agit de – INSERT, UPDATE et DELETE
- Trigger DDL (langage de définition des données) – Comme prévu, les triggers de ce type réagissent aux commandes DDL comme – CREATE, ALTER et DROP
- Trigger de connexion – Le nom dit tout. Ce type réagit aux événements de LOGON
Dans cet article, nous nous concentrerons sur les déclencheurs DML, car ils sont les plus couramment utilisés. Nous couvrirons les deux autres types de déclencheurs dans les prochains articles de cette série.
DML Triggers – Syntaxe
La syntaxe SQL simplifiée pour définir le déclencheur est la suivante .
|
.
1
2
3
. 4
5
|
CREATE TRIGGER trigger_name
ON table_name
{FOR | AFTER | INSTEAD OF} { }
AS
{sql_statements}
|
La plupart de la syntaxe devrait être auto-explicative. L’idée principale est de définir :
- Un ensemble de {sql_statements} qui doivent être exécutés lorsque le déclencheur est déclenché (défini par les paramètres restants)
- Nous devons définir quand le déclencheur est déclenché. C’est ce que fait la partie {FOR | AFTER | INSTEAD OF}. Si notre déclencheur est défini comme un déclencheur FOR | AFTER | INSTEAD OF, alors les instructions SQL dans le déclencheur seront exécutées après que toutes les actions qui ont déclenché ce déclencheur soient lancées avec succès. Le déclencheur INSTEAD OF doit effectuer des contrôles et remplacer l’action d’origine par l’action dans le déclencheur, tandis que le déclencheur FOR | AFTER (ils signifient la même chose) doit exécuter des commandes supplémentaires après que l’instruction d’origine a terminé
- La partie { } indique quelle commande déclenche réellement ce déclencheur. Nous devons spécifier au moins une option, mais nous pourrions en utiliser plusieurs si nous en avons besoin
Avec cela en tête, nous pouvons facilement écrire des déclencheurs qui vont :
- Vérifier (avant l’insertion) si tous les paramètres de l’instruction INSERT sont OK, en ajouter si nécessaire, et effectuer l’insertion
- Après l’insertion, effectuer des tâches supplémentaires, comme la mise à jour d’une valeur dans une autre table
- Avant la suppression, vérifier s’il y a des enregistrements connexes
- Mettre à jour certaines valeurs (par ex.g. fichier journal) après que la suppression soit faite
Si vous voulez déposer un déclencheur, vous utiliserez :
|
1
|
DROP TRIGGER trigger_name ;
|
Trigger SQL INSERT – Exemple
D’abord, nous allons créer un simple trigger SQL qui effectuera une vérification avant l’instruction INSERT.
|
1
2
3
4
5
.. 6
7
8
9
10
11
12
|
DROMP TRIGGER IF EXISTS t_country_insert ;
GO
CREATE TRIGGER t_country_insert ON country INSTEAD OF INSERT
AS BEGIN
DECLARE @country_name CHAR(128) ;
DECLARE @country_name_eng CHAR(128) ;
DECLARE @country_code CHAR(8) ;
SELECT @country_name = country_name, @country_name_eng = country_name_eng, @country_code = country_code FROM INSERTED ;
IF @country_name IS NULL SET @country_name = @country_name_eng ;
IF @country_name_eng IS NULL SET @country_name_eng = @country_name ;
INSERT INTO country (country_name, country_name_eng, country_code) VALUES (@country_name, @country_name_eng, @country_code) ;
END ;
|
Nous pouvons voir notre déclencheur dans l’explorateur d’objets, lorsque nous développons les données de la table liée (pays).
Je veux insister sur quelques points ici :
- L’instruction INSERT déclenche cette requête et est en fait remplacée (INSTEAD OF INSERT) par l’instruction dans ce déclencheur
- Nous avons défini un certain nombre de variables locales pour stocker les valeurs de l’enregistrement d’insertion original (INSERTED). Cet enregistrement est spécifique aux déclencheurs et il vous permet d’accéder à cet enregistrement unique et à ses’ valeurs
- Note : l’enregistrement INSERTED peut être utilisé dans les déclencheurs SQL d’insertion et de mise à jour.
- Avec les instructions IF, nous avons testé les valeurs et SET les valeurs si elles n’étaient pas définies auparavant
- À la fin de la requête, nous avons exécuté l’instruction INSERT (celle qui remplace l’instruction originale qui a déclenché ce déclencheur)
Exécutons maintenant une commande INSERT INTO et voyons ce qui se passe dans la base de données. Nous allons exécuter les instructions suivantes :
|
1
2
3
|
Sélectionner * du pays ;
INSERT INTO country (country_name_eng, country_code) VALUES (‘United Kingdom’, ‘UK’) ;
SELECT * FROM country ;
|
Le résultat est dans l’image ci-dessous.
Vous pouvez facilement remarquer que la ligne avec id = 10, avait été insérée. Nous n’avons pas spécifié le country_name, mais le trigger a fait son’ travail et a rempli cette valeur avec country_name_eng.
- Note : Si le trigger est défini sur une certaine table, pour une certaine action, il s’exécutera toujours lorsque cette action sera effectuée.
Déclencheur SQL DELETE – Exemple
Créons maintenant un déclencheur qui se déclenchera lors de l’instruction DELETE sur la table des pays.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
DUP TRIGGER IF EXISTS t_country_delete ;
GO
CREATE TRIGGER t_country_delete ON country INSTEAD OF DELETE
AS BEGIN
DECLARE @id INT ;
DECLARE @count INT ;
SELECT @id = id FROM DELETED ;
SELECT @count = COUNT(*) FROM city WHERE country_id = @id ;
IF @count = 0
DELETE FROM country WHERE id = @id ;
ELSE
THROW 51000, ‘can not delete – country is referenced in other tables’, 1 ;
END ;
|
Pour ce déclencheur, il convient de souligner les points suivants :
- Encore une fois, nous effectuons l’action avant (au lieu de) l’exécution réelle (INSTEAD OF DELETE)
- Nous avons utilisé l’enregistrement DELETED. Cet enregistrement peut être utilisé dans les déclencheurs liés à l’instruction DELETE
- Note : l’enregistrement DELETED peut être utilisé dans les déclencheurs SQL de suppression et de mise à jour.
- Nous avons utilisé l’instruction IF pour déterminer si la ligne doit ou non être supprimée. Si elle devrait, nous avons exécuté l’instruction DELETE, et si elle ne devrait pas, nous sommes lancés et exception
L’exécution de l’instruction ci-dessous s’est déroulée sans erreur car le pays avec id = 6 n’avait pas d’enregistrements liés.
|
1
|
DELETE FROM country WHERE id = 6 ;
|
Si nous exécutons cette déclaration, nous verrons un message d’erreur personnalisé, comme le montre l’image ci-dessous.
|
1
|
DELETE FROM country WHERE id = 1 ;
|
Un tel message est non seulement descriptif, mais nous permet de traiter cette erreur de manière agréable et de montrer un message plus significatif à l’utilisateur final.
Déclencheur UPDATE SQL
Je vous laisse celui-ci, à titre de pratique. Essayez donc d’écrire le déclencheur UPDATE. La chose importante que vous devez savoir est que dans le déclencheur de mise à jour, vous pouvez utiliser les deux – les enregistrements INSERTED (après la mise à jour) et DELETED (avant la mise à jour). Dans presque tous les cas, vous aurez besoin d’utiliser les deux.
Quand utiliser les déclencheurs SQL ?
Les déclencheurs ont beaucoup de points communs avec les procédures stockées. Pourtant, par rapport aux procédures stockées, ils sont limités dans ce que vous pouvez faire. Par conséquent, je préfère avoir une procédure stockée pour l’insertion/mise à jour/suppression et y effectuer toutes les vérifications et actions supplémentaires.
Pour autant, ce n’est pas toujours l’option. Si vous avez hérité d’un système ou si vous ne voulez tout simplement pas mettre toute la logique dans les procédures stockées, alors les déclencheurs pourraient être une solution pour de nombreux problèmes que vous pourriez avoir.
Table des matières
Apprendre SQL : CREATE DATABASE & CREATE TABLE Operations
Apprendre SQL : INSERT INTO TABLE
Apprendre SQL : Clé primaire
Apprendre SQL : Clé étrangère
Apprendre SQL : Instruction SELECT
Apprendre SQL : INNER JOIN vs LEFT JOIN
Apprendre SQL : Scripts SQL
Apprendre SQL : Types de relations
Apprendre SQL : Joindre plusieurs tables
Apprendre SQL : Fonctions d’agrégation
Apprendre SQL : Comment écrire une requête SELECT complexe ?
Apprendre SQL : La base de données INFORMATION_SCHEMA
Apprendre SQL : Types de données SQL
Apprendre SQL : Théorie des ensembles
Apprendre SQL : Fonctions définies par l’utilisateur
Apprendre SQL : Procédures stockées définies par l’utilisateur
Apprendre SQL : Vues SQL
Apprendre SQL : Triggers SQL
Apprendre SQL : Pratique des requêtes SQL
Apprendre SQL : Exemples de requêtes SQL
Apprendre SQL : Créer un rapport manuellement en utilisant des requêtes SQL
Apprendre SQL : Fonctions de date et d’heure du serveur SQL
Apprendre SQL : Créer des rapports SQL Server en utilisant les fonctions de date et d’heure
Apprendre SQL : Tableaux croisés dynamiques de SQL Server
Apprendre SQL : Exportation du serveur SQL vers Excel
Apprendre SQL : Intro aux boucles de SQL Server
Apprendre SQL : Curseurs SQL Server
Apprendre SQL : Meilleures pratiques SQL pour la suppression et la mise à jour des données
Apprendre SQL : Conventions de nommage
Apprendre SQL : Jobs liés à SQL
Apprendre SQL : Joints non équitables dans SQL Server
Apprendre SQL : Injection SQL
Apprendre SQL : SQL dynamique
- Auteur
- Messages récents
Ses engagements passés et présents varient de la conception et du codage de bases de données à l’enseignement, au conseil et à l’écriture sur les bases de données. Il ne faut pas non plus oublier, la BI, la création d’algorithmes, les échecs, la philatélie, 2 chiens, 2 chats, 1 femme, 1 bébé….
Vous pouvez le trouver sur LinkedIn
Voir tous les messages de Emil Drkusic
- Apprendre SQL : SQL dynamique – 3 mars 2021
- Apprendre SQL : Injection SQL – 2 novembre 2020
- Apprendre SQL : Joints non équivoques dans SQL Server – 29 septembre 2020
.