Les déclencheurs sous SQL Server peuvent prendre deux formes distinctes selon l'inclusion de l'instruction DELETE.
La première forme permet de construire des déclencheurs des trois types possibles.
CREATE TRIGGER nom_déclencheur ON { nom_table | nom_vue } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [,] [ UPDATE ] [,] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS } instruction_SQL... } } |
La syntaxe ci-dessous ne permet la création que de deux types de déclencheur. Néanmoins, des structures conditionnelles donnent la possibilité de tester les modifications apportées aux colonnes spécifiées.
CREATE TRIGGER nom_déclencheur ON { nom_table | nom_vue } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [,] [ UPDATE ] [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ { IF UPDATE ( nom_colonne ) [ { AND | OR } UPDATE ( nom_colonne ) ] [ ...n ] | IF ( COLUMNS_UPDATED() { Operateur_Niveau_Bit } Mise_Jour_Masque_Bit ) { = | > } Colonne_Masque_Bit [ ...n ] } ] instruction_SQL... } } |
La clause ON désigne la table ou la vue concernées par le déclencheur.
La clause WITH ENCRYPTION indique que SQL Server crypte les entrées syscomments qui contiennent le texte de l'instruction CREATE TRIGGER.
La clause for indique le déclenchement du Trigger pour un type d'événement.
La clause AFTER indique le déclenchement du Trigger suite à un type d'événement.
La clause INSTEAD OF indique un exécution des instructions du Trigger à la place de celles normalement produites par l'événement déclencheur.
Les instructions INSERT, UPDATE et DELETE représentent chacun un type de déclencheur respectivement sur une insertion, une mise à jour et une suppression.
La clause WITH APPEND indique la nécesité d'ajouter un déclencheur supplémentaire d'un type existant.
La clause NOT FOR REPLICATION indique que le déclencheur ne doit pas être exécuté lors de la modification de la table concernée par un processus de réplication.
La clause IF UPDATE exécute des tests pour une opération INSERT ou UPDATE sur une ou plusieurs colonnes spécifiées à la suite de l'instruction. Les colonnes à tester, peuvent être articulées autour d'un opérateur AND ou OR afin d'effectuer un test conditionnel sur l'une ou/et l'autre des colonnes. La clause retourne une valeur booléenne.
La clause colUMNS_UPDATED retourne un modèle de bit de type varbinary indiquant les colonnes ayant subi des modifications suite à un test dans un déclencheur de type INSERT ou UPDATE uniquement.
IF (COLUMNS_UPDATED() & 15) = 15 IF (COLUMNS_UPDATED() & 15) > 0
La valeur 15 suivant l'opérateur de niveau de bit & représente les cinq premières colonnes d'un tableau. Le signe égal = permet de vérifier dans ce cas si les cinq colonnes ont été modifiées. Dans l'autre exemple, le signe supérieur à > est spécifié, il s'agit ici d'une vérification sur l'une ou l'autre des cinq colonnes indiquées.
Exemple-- Premier exemple CREATE TABLE tbl_produit ( nom VARCHAR(20), identificateur INTEGER ) GO INSERT tbl_produit SELECT 'dentifrice', 1 UNION SELECT 'savon', 2 UNION SELECT 'savon', 0 UNION SELECT 'brosse à dent', 3 UNION SELECT 'brosse à dent', 0 UNION SELECT 'rasoir', 4 UNION SELECT 'mousse à raser', 5 UNION SELECT 'serviette', 6 UNION SELECT 'serviette', 0 UNION SELECT 'peigne', 7 UNION SELECT 'brosse à cheveux' 8 GO CREATE TRIGGER declencheur_suppression ON tbl_produit FOR DELETE AS SELECT nom AS "Lignes à supprimer" FROM deleted GO CREATE TRIGGER declencheur_insteadof ON tbl_produit INSTEAD OF DELETE AS DELETE tbl_produit FROM tbl_produit INNER JOIN deleted ON tbl_produit.nom = deleted.nom WHERE tbl_produit.identificateur = 0 GO DELETE tbl_produit WHERE nom IN ('brosse à dent', 'serviette', 'savon') GO SELECT * FROM tbl_produit GO DROP TABLE tbl_produit -- Second exemple CREATE TRIGGER envoi_email ON nom_table FOR INSERT, UPDATE, DELETE AS EXEC master.dbo.xp_sendmail @recipients = 'Administrateur', @message = 'Attention une modification a été effectuée dans la table : nom_table.', @subject = 'Modification sur une table.' GO -- Troisième exemple CREATE TRIGGER avertissement ON nom_table FOR INSERT, UPDATE, DELETE AS RAISERROR ('Attention une modification a été effectuée dans la table', 16, 10) GO |
Le premier exemple met en exergue le processus permettant de faire appel à différents types de déclencheurs au sein d'une table. Le premier déclencheur FOR DELETE s'exécute lors d'une suppression en sélectionnant les lignes supprimées à partir de la table temporaire deleted. Le second Trigger se déclenche et exécute ses propres instructions à la place de la commande de déclenchement DELETE afin d'effectuer une comparaison entre les noms de produit sur les deux tables tbl_produit et deleted pour ensuite supprimer les enregistrements possédant un identificateur égal à zéro.
Les deux derniers exemples démontrent qu'il est possible d'exécuter diverses instructions telles que respectivement, l'envoi d'un courrier électronique notifiant une modification à l'administrateur de la base de données et du retour d'un message d'erreur par l'instruction RAISERROR.