Partager via

Déclencheurs DML

Les déclencheurs DML sont un type spécial de procédure stockée qui prend effet automatiquement lorsqu’un événement DML (Data Manipulation Language) a lieu qui affecte la table ou la vue définie dans le déclencheur. Les événements DML incluent des instructions INSERT, UPDATE ou DELETE. Les déclencheurs DML peuvent être utilisés pour appliquer des règles métier et l’intégrité des données, interroger d’autres tables et inclure des instructions de Transact-SQL complexes. Le déclencheur et l'instruction qui l'active sont traités comme une transaction unique, qui peut être annulée à partir du déclencheur. Si une erreur grave est détectée (par exemple, un espace disque insuffisant), la transaction entière est automatiquement rétablie.

Avantages du déclencheur DML

Les déclencheurs DML sont similaires aux contraintes dans lesquelles ils peuvent appliquer l’intégrité de l’entité ou l’intégrité du domaine. En général, l’intégrité de l’entité doit toujours être appliquée au niveau le plus bas par les index qui font partie des contraintes PRIMARY KEY et UNIQUE ou qui sont créées indépendamment des contraintes. L’intégrité du domaine doit être appliquée via des contraintes CHECK, et l’intégrité référentielle (RI) doit être appliquée via des contraintes FOREIGN KEY. Les déclencheurs DML sont les plus utiles lorsque les fonctionnalités prises en charge par les contraintes ne peuvent pas répondre aux besoins fonctionnels de l’application.

La liste suivante compare les déclencheurs DML avec des contraintes et identifie quand les déclencheurs DML ont des avantages par rapport à .

  • Les déclencheurs DML peuvent cascader des modifications par le biais de tables associées dans la base de données ; Toutefois, ces modifications peuvent être exécutées plus efficacement à l’aide de contraintes d’intégrité référentielles en cascade. Les contraintes FOREIGN KEY peuvent valider une valeur de colonne uniquement avec une correspondance exacte avec une valeur dans une autre colonne, sauf si la clause REFERENCES définit une action référentielle en cascade.

  • Ils peuvent se protéger contre les opérations INSERT, UPDATE et DELETE malveillantes ou incorrectes et appliquer d’autres restrictions plus complexes que celles définies avec des contraintes CHECK.

    Contrairement aux contraintes CHECK, les déclencheurs DML peuvent référencer des colonnes dans d’autres tables. Par exemple, un déclencheur peut utiliser un SELECT à partir d’une autre table pour comparer aux données insérées ou mises à jour et effectuer des actions supplémentaires, telles que la modification des données ou l’affichage d’un message d’erreur défini par l’utilisateur.

  • Ils peuvent évaluer l’état d’une table avant et après une modification des données et prendre des mesures en fonction de cette différence.

  • Plusieurs déclencheurs DML du même type (INSERT, UPDATE ou DELETE) sur une table autorisent plusieurs actions différentes à effectuer en réponse à la même instruction de modification.

  • Les contraintes ne peuvent communiquer les erreurs que par le biais de messages d’erreur système standardisés. Si votre application nécessite ou peut tirer parti des messages personnalisés et de la gestion des erreurs plus complexe, vous devez utiliser un déclencheur.

  • Les déclencheurs DML peuvent interdire ou restaurer les modifications qui violent l’intégrité référentielle, ce qui annule la tentative de modification des données. Un tel déclencheur peut entrer en vigueur lorsque vous modifiez une clé étrangère et que la nouvelle valeur ne correspond pas à sa clé primaire. Toutefois, les contraintes FOREIGN KEY sont généralement utilisées à cet effet.

  • Si des contraintes existent sur la table de déclencheurs, elles sont vérifiées après l’exécution du déclencheur INSTEAD OF, mais avant l’exécution du déclencheur AFTER. Si les contraintes sont violées, les actions du déclencheur INSTEAD OF sont annulées et le déclencheur AFTER n’est pas exécuté.

Types de déclencheurs DML

Déclencheur APRÈS
Les déclencheurs AFTER sont exécutés après l’exécution de l’action de l’instruction INSERT, UPDATE, MERGE ou DELETE. Les déclencheurs AFTER ne sont jamais exécutés si une violation de contrainte se produit ; par conséquent, ces déclencheurs ne peuvent pas être utilisés pour tout traitement susceptible d’empêcher les violations de contrainte. Pour chaque action INSERT, UPDATE ou DELETE spécifiée dans une instruction MERGE, le déclencheur correspondant est déclenché pour chaque opération DML.

DÉCLENCHEUR AU LIEU DE
Les déclencheurs INSTEAD OF remplacent les actions standard de l’énoncé déclencheur. Par conséquent, ils peuvent être utilisés pour effectuer une vérification d’erreur ou de valeur sur une ou plusieurs colonnes et effectuer des actions supplémentaires avant d’insérer, mettre à jour ou supprimer la ligne ou les lignes. Par exemple, lorsque la valeur mise à jour dans une colonne de salaire horaire d’une table de paie dépasse une valeur spécifiée, un déclencheur peut être défini pour produire un message d’erreur et restaurer la transaction, ou insérer un nouvel enregistrement dans une piste d’audit avant d’insérer l’enregistrement dans la table de paie. L’avantage principal des déclencheurs INSTEAD OF est qu’ils activent des vues qui ne seraient pas modifiables pour prendre en charge les mises à jour. Par exemple, une vue basée sur plusieurs tables de base doit utiliser un déclencheur INSTEAD OF pour prendre en charge les insertions, mises à jour et suppressions qui référencent des données dans plusieurs tables. Un autre avantage des déclencheurs INSTEAD OF est qu’ils vous permettent de coder la logique qui peut rejeter des parties d’un lot tout en laissant d’autres parties d’un lot réussir.

Ce tableau compare les fonctionnalités des déclencheurs AFTER et INSTEAD OF.

Fonction Déclencheur APRÈS DÉCLENCHEUR INSTEAD OF
Applicabilité Tableaux Tables et vues
Quantité par table ou vue Plusieurs par action de déclenchement (UPDATE, DELETE et INSERT) Une par action de déclenchement (UPDATE, DELETE et INSERT)
Références en cascade Aucune restriction ne s’applique Les déclencheurs INSTEAD OF UPDATE et DELETE ne sont pas autorisés sur les tables qui sont des cibles de contraintes d’intégrité référentielle en cascade.
Exécution Après :

Traitement des contraintes
Actions référentielles déclaratives
création de tables insérées et tableaux supprimés
Action de déclenchement		 Avant : traitement des contraintes

À la place de l’action déclencheuse

Après : création de tables insertion et suppression
Ordre d’exécution La première et la dernière exécution peuvent être spécifiées Sans objet
varchar(max), nvarchar(max), et varbinary(max) références de colonne dans les tables insérées et supprimées Autorisé Autorisé
text, ntext et image références de colonne dans les tables insérées et supprimées Non autorisé Autorisé

Déclencheurs CLR
Un déclencheur CLR peut être un déclencheur de type APRÈS ou À LA PLACE DE. Un déclencheur CLR peut également être un déclencheur DDL. Au lieu d’exécuter une procédure stockée Transact-SQL, un déclencheur CLR exécute une ou plusieurs méthodes écrites dans le code managé qui sont membres d’un assembly créé dans le .NET Framework et chargées dans SQL Server.

Tâche Sujet
Décrit comment créer un déclencheur DML. Créer des déclencheurs DML
Décrit comment créer un déclencheur CLR. Créer des déclencheurs CLR
Décrit comment créer un déclencheur DML pour gérer les modifications de données à une seule ligne et à plusieurs lignes. Créer des déclencheurs DML pour gérer plusieurs lignes de données
Décrit comment imbriquer des déclencheurs. Créer des déclencheurs imbriqués
Décrit comment spécifier l’ordre dans lequel les déclencheurs AFTER sont déclenchés. Spécifier les premiers et les derniers déclencheurs
Décrit comment utiliser les tables insérées et supprimées spéciales dans le code du déclencheur. Utiliser les tables insérées et supprimées
Décrit comment modifier ou renommer un déclencheur DML. Modifier ou renommer des déclencheurs DML
Décrit comment afficher des informations sur les déclencheurs DML. Obtenir des informations sur les déclencheurs DML
Décrit comment supprimer ou désactiver des déclencheurs DML. Supprimer ou désactiver des déclencheurs DML
Décrit comment gérer la sécurité des déclencheurs. Gérer la sécurité des déclencheurs

Voir aussi

CRÉER UN DÉCLENCHEUR (Transact-SQL)
ALTER TRIGGER (Transact-SQL)
DROP TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
Fonctions de déclencheur (Transact-SQL)

  • Last updated on