Déclencheurs DML

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de données SQL dans Microsoft Fabric

Le déclencheur DML est 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 garantir l’intégrité des données, interroger d’autres tables et inclure des instructions Transact-SQL complexes. Le déclencheur et l'instruction qui le déclenche sont traités comme une unique transaction qui peut être annulée (par une opération de restauration) à partir du déclencheur. Si une erreur grave est détectée (par exemple un espace disque insuffisant), toute la transaction est automatiquement annulée.

Avantages

Les déclencheurs DML sont semblables aux contraintes en ce sens qu'ils peuvent appliquer l'intégrité d'entité ou l'intégrité de domaine. En général, l’intégrité de l’entité doit toujours être appliquée au niveau le plus élémentaire par les index qui font partie des contraintes PRIMARY KEY et UNIQUE ou qui sont créés indépendamment des règles de contrainte. L’intégrité du domaine doit être appliquée par le biais CHECK de contraintes, et l’intégrité référentielle (RI) doit être appliquée par le biais FOREIGN KEY de contraintes. 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 aux contraintes et identifie les situations dans lesquelles les déclencheurs DML présentent plus d’avantages que de contraintes.

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

• Ils peuvent se protéger contre les opérations malveillantes ou incorrectesINSERT, UPDATE, et DELETE et imposer d'autres restrictions plus complexes que les restrictions définies avec des contraintesCHECK.

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

• Ils peuvent déterminer l'état d'une table avant et après une modification de données, et entreprendre des actions en fonction de cette différence d'état.

• Plusieurs déclencheurs DML du même type (INSERTou UPDATEDELETE) d’une table permettent à plusieurs actions différentes de se produire en réponse à la même instruction de modification.

• Les contraintes ne peuvent donner d'informations sur les erreurs que par l'intermédiaire des messages d'erreur système standard. Si votre application a besoin (ou peut tirer parti) de messages personnalisés et d'une 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. Les contraintes FOREIGN KEY sont généralement utilisées à cet effet toutefois.

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

Types de déclencheur DML

Déclencheur AFTER

AFTER Les déclencheurs sont exécutés après l’action de l’instruction INSERT, UPDATE, MERGE ou DELETE. AFTER les déclencheurs 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 INSERT, ou UPDATEDELETE action spécifiée dans une MERGE instruction, le déclencheur correspondant est déclenché pour chaque opération DML.

Déclencheur INSTEAD OF

INSTEAD OF les déclencheurs remplacent les actions standard de l’instruction de déclenchement. 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 d’autres actions 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 dans une table de registre du personnel dépasse une valeur spécifiée, il est possible de définir un déclencheur qui soit produit un message d'erreur et annule la transaction, soit insère un nouvel enregistrement dans un journal d'audit avant d'insérer l'enregistrement dans la table de registre du personnel. L’avantage principal des INSTEAD OF déclencheurs 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 INSTEAD OF déclencheur pour prendre en charge les insertions, mises à jour et suppressions qui référencent des données dans plusieurs tables. Un autre avantage des INSTEAD OF déclencheurs est qu’ils vous permettent de coder la logique qui peut rejeter des parties d’un lot tout en permettant à d’autres parties d’un lot de réussir.

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

Déclencheur CLR

Un déclencheur CLR (Common Language Runtime) peut être un AFTER ou un INSTEAD OF déclencheur. Un déclencheur CLR peut également être un déclencheur DDL (Data Definition Language). Au lieu d’exécuter une procédure stockée Transact-SQL, un déclencheur CLR exécute une ou plusieurs méthodes écrites en code managé qui sont membres d’un assembly créé dans le framework XXX.NET et chargé dans SQL Server.

Tâches associées

Contenu connexe

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