Première chose, un petit historique des déclencheurs. Bien que souvent décriés, ils peuvent être très utiles dans certaines circonstances… une fois que l’on apprend à les connaître. Par exemple, l’envoi d’un email à partir de SQL Server pourrait être fait avec un déclencheur basé sur un certain événement anticipé.
Dans cet article, nous allons nous concentrer sur un cas de modification de données T-SQL lorsqu’une instruction Insert, Update ou Delete frappe les données de notre table. Comme son nom l’indique, le déclencheur se déclenchera et nous pourrons alors prendre des décisions sur la suite en fonction des nouvelles données qui arrivent ou des anciennes données qui sont remplacées. C’est ce à quoi les déclencheurs sont le plus utilisés.
L’inconvénient des déclencheurs est que les gens peuvent en quelque sorte s’attirer des ennuis avec des déclencheurs récursifs et des déclencheurs en boucle imbriqués qui pourraient conduire à certaines implications en termes de performances, mais nous nous en tiendrons à des déclencheurs simples dans cet article en gardant à l’esprit qu’il est important de savoir quand, où et comment les utiliser.
En regardant du côté des performances, il est préférable de garder les déclencheurs petits et simples. Tout comme la programmation, lors de la création d’un déclencheur aller par la logique que le moins est plus.
Dans cet article, nous allons passer en revue trois exemples d’utilisation d’un déclencheur en utilisant différentes combinaisons d’opérations par exemple l’insertion et les types de déclencheurs par exemple INSTEAD OF, y compris:
- Envoi d’un e-mail, basé sur une opération d’insertion pour une table particulière. La table est rarement mise à jour et nous avons une partie prenante qui veut savoir quand toute nouvelle donnée y est ajoutée
- Suivre toutes les modifications de la table en écrivant les modifications dans une table d’audit
- Prévenir les suppressions de données dans la table avec une garde d’exécution
Synthèse des déclencheurs DML
Nous devons mentionner qu’il existe également des déclencheurs DLL et CLR qui sont aussi un type spécial d’objets T-SQL, mais l’accent est mis ici sur le DML (langage de manipulation des données) tel qu’une instruction d’insertion, de mise à jour et de suppression.
Types de déclencheurs
- Après – les déclencheurs se déclenchent lorsque les déclarations DML se terminent
Après indique que le déclencheur DML est déclenché uniquement lorsque toutes les opérations spécifiées dans la déclaration SQL ont été exécutées avec succès. Toutes les actions de cascade référentielle et les vérifications de contraintes doivent également réussir avant que ce déclencheur ne se déclenche. Les déclencheurs AFTER ne peuvent pas être définis sur des vues. - INSTEAD OF – les déclencheurs se déclenchent avant les instructions DML
INSTEAD OF indique que le déclencheur DML est exécuté avant que l’instruction DML aille jusqu’au bout en remplaçant les actions des instructions de déclenchement. Les déclencheurs INSTEAD OF ne peuvent pas être spécifiés pour la DDL ou la connexion.
Assez parlé, voyons comment créer des déclencheurs DML simples pour les instructions Insert et Update, puis un autre déclencheur pour une instruction Delete sur une table importante qui agira comme une garde d’action fatale et empêchera l’utilisateur de supprimer des données.
Trigger de suivi/auditing des changements (update)
Nous allons commencer par le déclencheur Update. La condition préalable ici est que nous avons une base de données avec certaines informations sur les produits stockées dans des tables. L’une des tables possède une colonne contenant des informations sur le prix des produits. Disons que nous voulons surveiller la table des produits et qu’à chaque fois qu’une mise à jour frappe la table, nous voulons que le déclencheur se déclenche parce que nous voulons suivre les informations sur l’historique des prix. En outre, nous avons besoin d’une table supplémentaire dans laquelle nous allons stocker les informations surveillées recueillies à partir des modifications apportées à une table existante.
Vous pouvez créer une table simple qui répond à vos besoins, mais ci-dessous est un exemple de la table utilisée dans ce cas:
USE AdventureWorks2014;GOIF OBJECT_ID('Purchasing.StandardPriceHistory', 'U') IS NOT NULL DROP TABLE Purchasing.StandardPriceHistory;GOCREATE TABLE Purchasing.StandardPriceHistory(PriceHistoryID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, ProductID INT NOT NULL, PreviousPrice DECIMAL(19, 4), NewPrice DECIMAL(19, 4), PriceChangeDate DATETIME NOT NULL)ON ;GO
Maintenant que la table est créée, nous pouvons procéder à la création du déclencheur. Un exemple du déclencheur décrit ci-dessus est présenté ci-dessous :
USE AdventureWorks2014;GOIF OBJECT_ID('Purchasing.uStandardPriceHistory', 'TR') IS NOT NULL DROP TRIGGER Purchasing.uStandardPriceHistory;GOCREATE TRIGGER uStandardPriceHistory ON Purchasing.ProductVendorFOR UPDATEAS INSERT INTO Purchasing.StandardPriceHistory(PriceHistoryID, ProductID, PreviousPrice, NewPrice, PriceChangeDate) SELECT NEWID(), pv.ProductID, d.StandardPrice, i.StandardPrice, GETDATE() FROM Purchasing.ProductVendor pv JOIN INSERTED i ON pv.ProductID = i.ProductID JOIN DELETED d ON pv.ProductID = d.ProductID;GO
Ce déclencheur est créé sur la table Purchasing.ProductsVendor pour l’instruction Update et il va essentiellement prendre un instantané de la colonne Price de l’ancien prix et du nouveau prix et le placer à l’intérieur de la table ProductPriceHistory précédemment créée et nécessaire pour que cela se produise.
Après avoir exécuté le code de ce qui précède, si nous naviguons dans la table ProductsVendor et développons le dossier Triggers, il devrait y avoir notre déclencheur nouvellement créé. Lorsqu’il se déclenche, il enregistre les anciennes données qu’il remplace par de nouvelles données et insère ces informations dans la table ProductPriceHistory :
Maintenant, tout ce que nous avons à faire est d’émettre une instruction Update contre la table ProductsVendor pour voir comment cela fonctionne. Plutôt que d’écrire du code et parce que c’est plus rapide, utilisez l’option Editer les 200 premières lignes en cliquant avec le bouton droit de la souris sur la table dans l’Explorateur d’objets, ce qui ouvrira un nouvel onglet dans l’Éditeur :
Ici nous avons un produit avec l’ID 1 qui a une valeur de 47,87. Disons que l’article est en solde et modifions-le en entrant la nouvelle valeur de 39,99 et en appuyant sur la touche Entrée :
Cette action revient à écrire une déclaration de mise à jour. Elle émet l’instruction en arrière-plan et si rien ne cloche avec les données saisies, elle met à jour les enregistrements. Dans le cas contraire, elle fait apparaître un message d’information sur ce qui n’a pas fonctionné.
L’instruction est passée et un rapide Select de la table utilisée pour stocker les données surveillées montre des changements :
Nouvel enregistrement notifié (déclencheur d’insertion)
Vient ensuite le déclencheur d’insertion. Utilisons l’instruction Insert en combinaison avec un déclencheur comme mécanisme de notification. Nous allons en fait envoyer un email à partir d’un trigger Insert. L’idée derrière celui-ci est par exemple de faire savoir aux clients quand un nouveau produit est disponible. La condition préalable ici est que le Database Mail soit mis en place et configuré correctement.
Note : Si vous êtes nouveau dans le Database Mail, il s’agit d’une solution d’entreprise pour envoyer des messages électroniques à partir du moteur de base de données de SQL Server et il est fortement recommandé de consulter l’article détaillé suivant sur la façon de le configurer et de dépanner les problèmes connus qui peuvent survenir : Comment configurer le courrier de base de données dans SQL Server
Lorsque cette condition préalable est remplie, un exemple de déclencheur Insert pouvant être utilisé est présenté ci-dessous :
USE AdventureWorks2014;GOIF OBJECT_ID('Production.iProductNotification', 'TR') IS NOT NULL DROP TRIGGER Purchasing.iProductNotification;GOCREATE TRIGGER iProductNotification ON Production.ProductFOR INSERTAS DECLARE @ProductInformation NVARCHAR(255); SELECT @ProductInformation = 'A new product, '+Name+', is now available for $'+CAST(StandardCost AS NVARCHAR(20))+'!' FROM INSERTED i; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Notifications', @recipients = 'Use a valid e-mail address', @body = @ProductInformation, @subject = 'New product notification';GO
Cette fois, nous attachons le déclencheur à la table Production.Product. Notez que cette fois le déclencheur est FOR INSERT statement. Vous pouvez également utiliser AFTER INSERT, car for et after sont fondamentalement la même chose. C’est l’une de ces choses de rétrocompatibilité. Il y en a un depuis le tout début, puis ils en ont ajouté un autre.
Donc, dès qu’un nouveau produit est inséré, cela déclenchera le déclencheur qui rassemblera ensuite quelques informations de base, enveloppera ces informations sous la forme d’un message électronique et exécutera la procédure stockée pour l’envoi de l’email qui placera finalement le message dans la file d’attente Database Mail. C’est un excellent exemple de la façon dont un déclencheur peut être utilisé dans une réaction en chaîne.
Un résumé rapide de ces deux-là est que vous devez garder à l’esprit que les déclencheurs Update et Insert appartiennent au groupe des déclencheurs AFTER. Dans les deux exemples ci-dessus, ils se déclenchent lorsque les instructions DML se terminent.
Garde d’action (déclencheur delete)
Passons au déclencheur Delete, c’est là que nous pouvons montrer un exemple de déclencheurs INSTEAD OF qui se déclenchent avant les instructions DML. Voyons ce que nous pouvons faire avec ce type de déclencheur.
Bien que ce déclencheur puisse empêcher l’exécution d’une instruction DML, pourquoi ne pas l’utiliser comme un » garde-action » contre la perte accidentelle de données pour des informations importantes. Un exemple est décrit et montré dans un autre article qui explore également d’autres moyens d’empêcher la perte accidentelle de données : Comment prévenir la perte accidentelle de données lors de l’exécution d’une requête dans SQL Server alias « Practicing safe coding »
Alrightty alors, nous recommandons fortement de vérifier le code de l’article ci-dessus, mais montrons également un autre exemple ici. Microsoft a en fait un très bon déclencheur à l’intérieur de la base de données AdventureWorks2014. Naviguez dans l’Explorateur d’objets et localisez le déclencheur dEployee sous la table HumanResources.Employee ou utilisez une fonction astucieuse ‘Go to object’ dans ApexSQL Complete pour trouver rapidement l’objet et le mettre en évidence à l’intérieur de l’Explorateur d’objets :
Cliquez avec le bouton droit de la souris sur le déclencheur dEmployee et naviguez vers Script Trigger en tant que > CREATE To, puis cliquez sur la commande Nouvelle fenêtre d’édition de requête :
Cette action ouvrira un nouvel onglet dans Editor avec le code suivant:
USE ;GO/****** Object: Trigger . Script Date: 12/18/2017 12:27:41 PM ******/SET ANSI_NULLS ON;GOSET QUOTED_IDENTIFIER ON;GOCREATE TRIGGER . ON .INSTEAD OF DELETENOT FOR REPLICATIONAS BEGIN DECLARE @Count INT; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN RAISERROR(N'Employees cannot be deleted. They can only be marked as not current.', -- Message 10, -- Severity. 1); -- State. -- Rollback any active or uncommittable transactions IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END; END; END;GOALTER TABLE . ENABLE TRIGGER ;GOEXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'INSTEAD OF DELETE trigger which keeps Employees from being deleted.', @level0type = N'SCHEMA', @level0name = N'HumanResources', @level1type = N'TABLE', @level1name = N'Employee', @level2type = N'TRIGGER', @level2name = N'dEmployee';GO
Maintenant, nous avons plusieurs choses qui se passent ici. Tout d’abord, tout le code n’est pas nécessaire, cependant, c’est un exemple parfait d’un déclencheur qui a tout. Nous avons un déclencheur INSTEAD OF pour une instruction Delete qui signifie que lorsqu’un Delete frappe les données de notre table, le déclencheur exécute d’abord le code qui vérifie les données avant qu’il ne soit réellement exécuté et nous donne une chance de faire marche arrière.
La fonction @@ROWCOUNT est utile dans ce cas parce qu’elle vérifie le nombre de lignes affectées par l’instruction Delete. Elle dit essentiellement que si le nombre de lignes est égal à zéro, ne déclenchez pas du tout le déclencheur. C’est génial du point de vue des performances.
Si nous sommes arrivés au point où l’instruction Delete est effectivement passée, et que des rangs réels ont été affectés, la fonction RAISERROR affichera un message défini par l’utilisateur. Pour voir cela en action, utilisez à nouveau la commande Editer les 200 premières lignes sur la table HumanResources.Employee:
Sélectionnez n’importe quelle ligne comme indiqué ci-dessous et appuyez sur la touche Suppr du clavier. Une boîte de dialogue de confirmation d’avertissement apparaît. Cliquez sur le bouton Oui pour poursuivre l’action :
Une autre boîte de dialogue d’information s’affiche avec le message d’erreur indiquant que la transaction s’est terminée dans le déclencheur. En outre, il affiche la chaîne de caractères qui a été spécifiée par l’utilisateur lui-même :
C’est ici que la fonction ROLLBACK TRANSACTION intervient pour effacer toutes les modifications de données effectuées depuis le début de la transaction.
La même chose se produira à partir de l’éditeur également. Rappelez-vous, l’exemple ci-dessus et le code ci-dessous font exactement la même chose :
Potential gotchas
« With great power comes great responsibility » est souvent cité à partir des mots d’Oncle Ben dans le film Spider-Man. Maintenant que nous avons vu la véritable puissance des triggers DML, revenons au tout début de l’article où nous avons mentionné le trigger récursif et les triggers à boucles imbriquées. Nous passerons sur les exemples car le but de cet article est de mieux comprendre l’utilité globale des triggers et de voir quand, où et comment les utiliser, et non de montrer les inconvénients. Mais il est également important de décrire au moins brièvement les inconvénients possibles également.
Trigger imbriqués
Les triggers imbriqués sont des cas dans lesquels un trigger d’une table effectue une action qui initie un autre trigger dans une autre table, etc. De plus, si le déclencheur de la deuxième table rappelle notre table et met à jour les données, alors nous avons un processus de bouclage infini. Heureusement pour nous, SQL Server dispose d’un garde-fou et les déclencheurs peuvent être imbriqués jusqu’à une limite de 32 niveaux (les déclencheurs INSTEAD OF peuvent être imbriqués quel que soit le réglage de cette option). Ce paramètre est activé par défaut dans SQL Server et ce que cela signifie, c’est que si cette limite d’imbrication est dépassée, le déclencheur se termine.
Juste pour vérifier l’option des triggers imbriqués, dans l’Explorateur d’objets, faites un clic droit sur une base de données, puis sélectionnez Propriétés. Sous la page Options, il y a l’option Nested Triggers Enabled et elle doit être définie sur True:
Notez que cette option est grisée, ce qui signifie qu’elle n’est pas configurable dans l’interface graphique. Si pour une raison quelconque, cette option est désactivée, ce qui n’est pas recommandé, sauf dans quelques rares cas, exécutez le code suivant pour l’activer :
USE AdventureWorks2014GOEXEC sp_configure 'nested triggers', 1; GO RECONFIGURE; GO
Le paramètre prend effet immédiatement sans redémarrer le serveur. Si vous rencontrez une erreur, c’est très probablement parce qu’il s’agit d’une option avancée. Ajoutez le code ci-dessous avant la procédure stockée du système pour résoudre ce problème :
USE AdventureWorks2014 ; GO EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE ; GO
Déclencheurs récursifs
Les déclencheurs récursifs sont des cas dans lesquels un déclencheur s’appelle lui-même de manière répétée. Cela se produit généralement lorsqu’il n’y a aucune logique à l’intérieur du déclencheur qui peut rompre la récursion. Ceci est également connu comme une boucle infinie. Heureusement, les déclencheurs récursifs dans SQL Server sont désactivés par défaut. Comme auparavant, vous pouvez vérifier cette option dans l’Explorateur d’objets, en faisant un clic droit sur une base de données, puis en sélectionnant Propriétés. Sous la page Options, il y a l’option Recursive Triggers Enabled et elle est réglée sur False:
Lorsqu’elle est réglée sur False, seule la récursion directe est empêchée. Cette option peut être modifiée directement depuis l’interface graphique:
N’oubliez pas que les déclencheurs sont attachés à des tables. Sur les grandes bases de données comportant de nombreuses tables, l’écriture de déclencheurs pour chaque table peut prendre beaucoup de temps. Maintenant que nous avons couvert chaque instruction DML, voyons une solution prête à l’emploi pour la création automatique de déclencheurs afin de répondre aux objectifs de ce qui précède.
Création automatique de déclencheurs basée sur des modèles
ApexSQL Trigger est un outil d’audit pour les bases de données SQL Server qui capture les changements DML qui se sont produits dans une base de données. Ainsi, voyons comment mettre en œuvre un audit SQL basé sur un déclencheur, en utilisant une version plus complexe de notre déclencheur Update pour le suivi/audit des changements, avec cet outil en quelques étapes.
Démarrez ApexSQL Trigger, cliquez sur le bouton Nouveau pour créer un nouveau projet. Cela ouvrira une boîte de dialogue Nouveau projet où nous sélectionnerons le serveur, la méthode d’authentification et la base de données sur laquelle nous voulons auditer les changements. Une fois définis, cliquez sur le bouton OK pour passer à l’étape suivante :
L’application affichera ensuite une boîte de dialogue concernant l’architecture manquante. C’est normal car c’est la première fois que l’application est connectée à cette base de données. C’est une étape essentielle car l’application nécessite une architecture spécifique d’objets SQL installés avant de commencer l’audit (similaire à la création de notre table avant). Sélectionnez l’option par défaut et cliquez sur le bouton Oui pour installer l’architecture :
Étape suivante, laissez les paramètres par défaut et cliquez sur le bouton Installer, puis confirmez l’exécution du script en cliquant sur le bouton Oui pour installer l’architecture d’audit sur la base de données :
L’exécution du script ne prend que quelques secondes et une fois terminé avec succès, cliquez sur le bouton OK pour fermer la fenêtre et revenir à la fenêtre Gérer l’architecture :
Fermez cette fenêtre car nous n’en avons plus besoin et vous verrez la fenêtre d’accueil de l’application :
Recherchez la table Achats.ProductsVendor, cochez l’option sur la gauche et vous verrez toutes les colonnes sous le volet Colonnes. Vérifiez la colonne StandardPrice, puis cliquez sur le bouton Créer pour créer le déclencheur :
Cliquez sur le bouton Exécuter dans la fenêtre Script pour créer le déclencheur :
Puis, vous devriez voir le message indiquant que toutes les opérations sont terminées et que le script s’est exécuté avec succès :
De retour à l’écran d’accueil, notez que la table a maintenant une coche verte, ce qui signifie que cette table est auditée :
Si nous retournons à l’Explorateur d’objets et rafraîchissons le dossier Triggers, il y aura trois nouveaux triggers créés par ApexSQL Trigger :
Chaque déclencheur correspond à une instruction DML. Pourquoi ? Parce que pourquoi pas, ce n’est pas comme si on avait dû taper le code. Et cela fournit plus de granularité, au cas où nous voudrions faire des mods spécifiques à une opération dans l’un des trois modèles. Cependant, c’est à vous de décider quelles modifications vous voulez auditer et vous pouvez le décider en un seul clic.
Nous pouvons maintenant supprimer le déclencheur uStandardPriceHistory car nous n’en avons plus besoin. N’oubliez pas non plus que le fait d’avoir plusieurs déclencheurs sur une table peut entraîner des répercussions sur les performances (surtout dans ce cas, lorsque deux déclencheurs font exactement la même chose). Nous devrions également supprimer la table Purchasing.StandardPriceHistory créée précédemment, car elle est inutile sans le déclencheur.
Disons que l’article n’est plus en vente, changez le prix en ancienne valeur en utilisant les étapes de tout à l’heure, afin que nous puissions voir comment visualiser les changements. Contrairement à l’écriture de l’instruction Select et à l’extraction manuelle des données d’une table, ApexSQL Trigger le fait en générant des rapports personnalisables par l’utilisateur.
Cliquez sur le bouton Standard sous le groupe Rapports :
Dans la fenêtre suivante, il suffit de cliquer sur le bouton Appliquer et la modification s’affiche ci-dessous :
Maintenant, l’envoi d’un courriel peut se faire de plusieurs façons. L’une des façons est déjà décrite dans un article sur la façon de personnaliser l’architecture de ApexSQL Trigger, de sorte que des alertes par courriel soient envoyées lorsque des modifications sont apportées à un enregistrement de la base de données : Comment envoyer des alertes par email lorsqu’une modification est apportée à un enregistrement de base de données
Si vous appliquez la méthode de l’article ci-dessus, Database Mail enverra des emails pour toutes les modifications DML sur une table. Dans notre cas, nous n’en avons besoin que pour l’instruction Insert. L’utilisateur peut librement éditer et modifier les modèles, ce qui signifie que l’insertion de votre code personnalisé pour le déclencheur est possible. Dans ce cas, nous pouvons simplement créer un nouveau déclencheur Insert et éditer le modèle existant en mettant notre morceau de code, ainsi le déclencheur va non seulement enregistrer les changements mais aussi exécuter la procédure stockée sp_send_dbmail et envoyer un email.
Il en va de même pour une instruction Delete. Juste en ajoutant quelques lignes de code, vous pouvez créer un mécanisme de prévention qui gardera vos données en ne vous laissant pas supprimer accidentellement toutes les données d’une table. Tout ce que nous avons fait dans ce cas est d’ajouter le code suivant pour atteindre l’objectif:
DECLARE @Count int SET @Count = @@ROWCOUNT; IF @Count >= (SELECT SUM(row_count) FROM sys.dm_db_partition_stats WHERE OBJECT_ID = OBJECT_ID('Sales.Customer') AND index_id = 1) BEGIN RAISERROR('Cannot delete all rows',16,1) ROLLBACK TRANSACTION RETURN;END
Le modèle édité ressemble à ceci :
L’exécution du code édité, crée un trigger qui empêchera l’effacement de la table entière :
Enregistrer le modèle peut être utile pour une utilisation future et créer ceci sur plusieurs tables simultanément.
J’espère que vous trouverez cet article utile. Pour plus d’informations sur ApexSQL Trigger, veuillez consulter la page du produit
Liens utiles
- Comment éviter la perte accidentelle de données lors de l’exécution d’une requête dans SQL Server alias « Pratiquer un codage sûr »
- Comment définir différentes couleurs de connexion dans SSMS
- Garde d’action fatale : protection contre l’exécution par inadvertance d’un code susceptible d’endommager ou de détruire des données dans SQL Server
.