Déclencheurs DML

Un déclencheur DML est un type spécial de procédure stockée qui entre automatiquement en vigueur lorsqu'un événement de langage de manipulation de données (DML, ou Data Manipulation Language) qui affecte la table ou la vue définie dans le déclencheur se produit. Les événements DML incluent les instructions INSERT, UPDATE ou DELETE. Les déclencheurs DML peuvent être utilisés pour appliquer des règles d'entreprise et 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 des déclencheurs DML

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é d'entité doit toujours être appliquée au niveau le plus bas par des index qui font partie des contraintes PRIMARY KEY et UNIQUE, ou qui sont créés indépendamment des contraintes. L'intégrité de domaine doit être appliquée au moyen de contraintes CHECK, et l'intégrité référentielle au moyen de contraintes FOREIGN KEY. Les déclencheurs DML sont particulièrement 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.

• 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. Les contraintes FOREIGN KEY peuvent valider une valeur de colonne uniquement si celle-ci correspond exactement à une valeur d'une autre colonne, à moins que la clause REFERENCES définisse une action d'intégrité référentielle en cascade.

• Ils peuvent empêcher les opérations INSERT, UPDATE et DELETE incorrectes et assurer l'application d'autres restrictions plus complexes que celles définies à l'aide de contraintes CHECK.

  Contrairement aux contraintes CHECK, les déclencheurs DML peuvent faire référence à des colonnes d'autres tables. Par exemple, un déclencheur peut utiliser une instruction SELECT à partir d'une autre table afin de comparer avec les données insérées ou mises à jour et d'effectuer des actions supplémentaires, comme modifier les données ou afficher 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 (INSERT, UPDATE ou DELETE) sur une table permettent que plusieurs actions différentes soient réalisées 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 servir à interdire ou à restaurer des modifications qui enfreignent l'intégrité référentielle, annulant ainsi la tentative de modification des données. Un déclencheur de ce type peut entrer en action si vous modifiez une clé étrangère et si la nouvelle valeur ne correspond pas à sa clé primaire. Toutefois, les contraintes FOREIGN KEY sont généralement utilisées à cette fin.

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

Types de déclencheurs DML

• Déclencheur AFTER
  Les déclencheurs AFTER sont exécutés après l'action associée à une instruction INSERT, UPDATE, MERGE ou DELETE. Les déclencheurs AFTER ne sont jamais exécutés en cas de violation de contrainte et ne peuvent donc pas être utilisés pour tout traitement susceptible d'éviter les violations de contrainte. Pour chaque instruction INSERT, UPDATE ou DELETE spécifiée dans une instruction MERGE, le déclencheur correspondant est exécuté pour chaque opération DML.

• Déclencheur INSTEAD OF
  Les déclencheurs INSTEAD OF 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, puis effectuer des actions supplémentaires avant l'insertion, la mise à jour ou la suppression des 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. Le principal avantage des déclencheurs INSTEAD OF est qu'ils autorisent des vues qui autrement ne pourraient pas être mises à jour en vue de la prise en charge des mises à jour. Par exemple, une vue basée sur plusieurs tables de base doit posséder un déclencheur INSTEAD OF pour prendre en charge les opérations d'insertion, de mise à jour et de suppression référençant des données dans plusieurs tables. Un autre avantage des déclencheurs INSTEAD OF est qu'ils permettent de programmer une logique capable de rejeter certaines parties d'un lot et d'en mener d'autres à terme.

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

  Fonction	Déclencheur AFTER	Déclencheur INSTEAD OF
  Applicabilité	Tables	Tables et vues
  Quantité par table ou vue	Plusieurs par action de déclenchement (UPDATE, DELETE et INSERT)	Un par action de déclenchement (UPDATE, DELETE et INSERT)
  Références en cascade	Aucune restriction	Les déclencheurs INSTEAD OF UPDATE et DELETE ne sont pas autorisés sur des 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 inserted et deleted L'action de déclenchement	Avant : Traitement des contraintes Au lieu de : L'action de déclenchement Après : Création de tables inserted et deleted
  Ordre d'exécution	La première et la dernière exécution peuvent être spécifiées	Non applicable
  Références de colonnes varchar(max), nvarchar(max) et varbinary(max) dans des tables inserted et deleted	Autorisées	Autorisées
  Références de colonnes text, ntext et image dans des tables inserted et deleted	Non autorisées	Autorisées

• Déclencheurs CLR
  Un déclencheur CLR peut être un déclencheur AFTER ou INSTEAD OF. Il peut également s'agir d'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 en code managé que les membres d'un assembly ont créées dans .NET Framework et téléchargées dans SQL Server.

Tâches associées

Voir aussi

Référence

CREATE TRIGGER (Transact-SQL)

ALTER TRIGGER (Transact-SQL)

DROP TRIGGER (Transact-SQL)

DISABLE TRIGGER (Transact-SQL)

Concepts

Fonctions de déclencheurs (Transact-SQL)