Comment utiliser SQL Server Triggers

Les déclencheurs sont un type spécial de procédures stockées qui s'exécutent en réponse aux activités dans les objets SQL Server. Les déclencheurs sont stockés dans la mémoire du système et ne sont exécutés que lorsqu'un événement spécifique se produit. Contrairement aux procédures stockées normales, les déclencheurs n'acceptent pas les paramètres ni exécutés manuellement.

Dans cet article, nous explorerons comment créer et travailler avec des déclencheurs dans SQL Server.

Il existe trois principaux types de déclencheurs dans SQL Server:

1. DML déclenche
2. DDL déclenche
3. Déclencheurs de connexion

Explorons ces déclencheurs dans ce guide.

SQL Server DML déclenche

Les déclencheurs du langage DML ou de manipulation de données sont le type de déclencheurs qui tirent en réponse à une opération d'insertion, de mise à jour et de suppression sur une table ou une vue. Les déclencheurs DML s'exécuteront lorsqu'une opération valide sera exécutée, si des lignes sont affectées.

Créer un DML après le déclencheur

Dans SQL Server, vous pouvez créer un déclencheur DML à l'aide de l'instruction Create Trigger.

Créer un schéma de déclenchement_.Trigger_name
Sur table_name
Après [insérer, mettre à jour, supprimer]
COMME
-- déclencher le code pour s'exécuter

Décomposons la syntaxe ci-dessus:

1. schéma_name - le nom du schéma où le déclencheur est stocké.
2. Trigger_name - Nom du déclencheur.
3. table_name - le nom du tableau où le déclencheur spécifié s'appliquera.
4. Après - une clause après définir dans laquelle les conditions que le déclencheur s'appliquera.

Pour apprendre à créer et à utiliser un déclencheur DML, prenons un exemple pratique.

Créez un exemple de base de données et insérez les données fournies dans l'ensemble des requêtes ci-dessous:

-- Créer une exemple de base de données
Créer des ventes de bases de données;
ALLER
-- commutateur db;
Utiliser les ventes;
-- créer une table
Créer des ventes de tables (
ID INT Identité (1,1) Pas de clé primaire nul,
Product_name Varchar (50),
prix,
quantité int
));
-- insérer des données d'échantillons
Insérer dans les ventes (Product_name, prix, quantité)
Valeurs ('iPhone Charger', 9 $.99, 10),
('Google Chromecast', 59 $.25, 5),
('PlayStation DualSense Wireless Controller', 69 $.00, 100),
('Xbox Series S', 322 $.00, 3),
('Oculus Quest 2', 299 $.50, 7),
('Netgear Nighthawk', 236 $.30, 40),
('Redragon S101', 35 $.98, 100),
('Star Wars Action Figure', 17 $.50, 10),
('Mario Kart 8 Deluxe', 57 $.00, 5);

Une fois que nous avons les exemples de données, nous pouvons procéder et créer un déclencheur DML pour tirer sur une opération de mise à jour sur la table.

Considérez l'exemple ci-dessous:

-- Créer un tableau pour stocker l'historique de la mise à jour
Créer la table ModifiedDate (ID INT, DATE_ DateTime)
ALLER
-- Créer un déclencheur
Créer un déclencheur DBO.update_trigger
En vente
Après la mise à jour
Pas pour la réplication
COMME
COMMENCER
Insérer dans ModifiedDate
Sélectionnez ID, getDate ()
De inséré
FIN

La requête ci-dessus créera un déclencheur qui tire lorsque nous effectuons une mise à jour sur la table. Pour tester le déclencheur, nous pouvons exécuter une mise à jour comme:

-- table de mise à jour
Mettre à jour le prix défini des ventes = 10 $.dix
Où id = 1;

Après l'exécution, nous pouvons vérifier si le déclencheur a fonctionné en sélectionnant les colonnes dans la table modifiée.

-- Vérifiez le tableau ModifiedDate
SELECT * dans ModifiedDate;

Dans SSMS, vous pouvez afficher les déclencheurs sur une table en élargissant l'option Triggers:

Créer au lieu de déclencheurs

L'autre type de déclencheurs DML dans SQL Server est au lieu de déclencher. Ce sont des types de déclencheurs qui s'exécutent au lieu de l'instruction DML. Par exemple, si nous spécifions une instruction de suppression, nous pouvons utiliser les déclencheurs au lieu de s'exécuter avant l'opération.

La syntaxe pour la création d'un déclencheur au lieu est comme indiqué:

Créer un schéma de déclenchement_.Trigger_name
Sur table_name
Au lieu de [insérer, mettre à jour, supprimer]
COMME
-- instructions de déclenchement

Par exemple, la requête ci-dessous crée un déclencheur qui affiche un message lorsqu'une opération d'insertion est effectuée sur la table.

-- Créer au lieu du déclencheur
Créer un déclencheur à la place_insert
En vente
au lieu d'insérer
COMME
COMMENCER
Sélectionnez «vous ne pouvez pas insérer dans ce tableau» comme erreur
FIN
-- Exécutez à la place_insert Trigger
Insérer dans les ventes (Product_name, prix, quantité)
Valeurs ('iPhone Charger', 9 $.99, 10);

Une fois que nous avons exécuté la requête ci-dessus, nous devons recevoir un message indiquant que nous ne pouvons pas effectuer un insert sur la table.

Erreur
-------------------------------

Vous ne pouvez pas insérer dans ce tableau

SQL DDL déclenche

DDL ou le langage de définition des données sont des déclencheurs qui répondent aux événements au serveur ou à la base de données au lieu d'un tableau. Les déclencheurs de DDL répondront à des événements tels que Drop, Grant, Deny, Revok, Mettez à jour les statistiques, créez et modifiez.

Créer des déclencheurs DDL

La syntaxe pour créer un déclencheur DDL est comme indiqué:

Créer Trigger Trigger_name
Sur la base de données | Tous les serveurs
Avec ddl_trigger_parameters
Pour event_type | event_group
COMME
-- instructions de déclenchement

Nous pouvons décomposer la syntaxe comme:

1. Trigger_name - Nom unique du déclencheur.
2. base de données ou tout serveur - Spécifiez où le déclencheur est exécuté. Base de données si elle s'applique à la base de données ou à tous les serveurs s'il s'applique à la portée du serveur.
3. DDL_TRIGGER_PARAMETER - Paramètres DDL tels que Exécuter AS, ou crypter AS.
4. event_type - L'événement DDL qui déclenche la déclencheur.

L'exemple de requête ci-dessous crée un déclencheur DDL qui tire lorsqu'une déclaration de table de dépôt est publiée.

-- Créer un déclencheur DDL
Créer un déclencheur drop_ddl_trigger
Sur la base de données
Pour Drop_Table
COMME
COMMENCER
Sélectionnez EventData ();
FIN

Une fois que nous aurons exécuté un événement de dépôt dans la base de données, le déclencheur affichera des informations sur l'événement à l'aide de la fonction EventData ().

Nous pouvons tester le déclencheur:

-- déclencheur de test
Ventes de table de dépôt;

La requête doit retourner des informations XML sur l'événement en tant que:

Dans SSMS, vous pouvez afficher les déclencheurs en élargissant les déclencheurs de la base de données sous programmabilité dans votre base de données cible.

Activer / désactiver les déclencheurs

SQL Server vous permet d'activer et de désactiver les déclencheurs. Pour activer un déclencheur sur une table, utilisez la requête comme:

Activer Trigger Update_trigger sur les ventes;

Où update_trigger représente le nom et les ventes du déclencheur représentent le nom du tableau.

Vous pouvez également activer tous les déclencheurs sur une table en tant que:

Activer le déclencheur sur table_name;

Pour activer le déclencheur de la base de données, utilisez la requête:

Activer le déclenchement drop_ddl_trigger sur les ventes;

Ici, drop_ddl_trigger représente le nom de déclencheur et les ventes représentent la base de données.

Pour activer tous les déclencheurs de la base de données, utilisez la requête:

Activer le déclenchement des ventes;

Pour désactiver une table ou un déclencheur de base de données, remplacez le mot-clé Activer par Disable ☺️.

Supprimer la gâchette

Pour supprimer un déclencheur, vous pouvez utiliser l'instruction DROP comme indiqué:

Drop Trigger si existe Trigger_name;

SQL Server montre tous les déclencheurs

Pour afficher tous les déclencheurs dans une instance SQL Server, utilisez la requête comme indiqué:

Sélectionnez Nom, Type_desc, is_disabled, is_instead_of_trigger à partir de sys.déclenche où type = 'tr'

La requête doit renvoyer tous les déclencheurs de l'instance SQL Server comme:

SQL Server Logon déclenche

Les déclencheurs de connexion sont un type de déclencheurs qui s'exécutent lorsqu'une activité de connexion se produit sur le serveur. Ces types de déclencheurs s'exécutent après une authentification réussie mais avant de créer une session utilisateur. Puisqu'ils sont utilisés pour gérer l'activité de connexion, nous les créons au niveau du serveur, comme indiqué dans l'exemple ci-dessous:

AVERTIR: Le déclencheur ci-dessous peut empêcher les connexions futures du serveur. Assurez-vous de supprimer avant de vous connecter.

AVERTIR - ☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️.

-- Créer un déclencheur de connexion
Créer un déclencheur Login_tg
Sur tous les serveurs
Pour la connexion comme
COMMENCER
Sélectionnez «Un déclencheur après la connexion» comme [Message]
FIN

Le déclencheur affichera un message lorsque l'utilisateur se connecte dans le serveur.

Conclusion

Dans ce guide, vous avez compris divers types de déclencheurs, comment créer, activer, désactiver, supprimer et afficher les déclencheurs dans SQL Server.