Partager via

CRÉER TRIGGER (Transact-SQL)

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

Crée un déclencheur DML, DDL ou de connexion. Un déclencheur est un type particulier de procédure stockée qui s’exécute automatiquement quand un événement se produit sur le serveur de base de données. Les déclencheurs DML s'exécutent lorsqu'un utilisateur essaie de modifier des données via un événement DML (Data Manipulation Language). Les événements DML sont INSERT, UPDATEou DELETE des instructions sur une table ou une vue. Ces déclencheurs s'activent au déclenchement d'un événement valide, que des lignes de table soient affectées ou non. Pour plus d'informations, consultez DML Triggers.

Les déclencheurs DDL s’exécutent en réponse à différents événements DDL (Data Definition Language). Ces événements correspondent principalement à Transact-SQL CREATE, ALTERet aux DROP instructions et à certaines procédures stockées système qui effectuent des opérations de type DDL.

Les déclencheurs d’ouverture de session se déclenchent en réponse à l’événement LOGON déclenché lors de l’établissement de la session d’un utilisateur. Vous pouvez créer des déclencheurs directement à partir d’instructions Transact-SQL ou de méthodes d’assembly créées dans le CLR (Common Language Runtime) Microsoft .NET Framework et chargées dans une instance de SQL Server. SQL Server vous permet de créer plusieurs déclencheurs pour toute instruction spécifique.

Important

Un code malveillant présent dans des déclencheurs peut s'exécuter sous des privilèges promus. Pour plus d’informations sur la façon d’atténuer cette menace, consultez Gérer la sécurité des déclencheurs.

Notes

L’intégration du CLR .NET Framework à SQL Server est décrite dans cet article. L’intégration CLR ne s’applique pas à Azure SQL Database ou SQL Database dans Microsoft Fabric.

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe SQL Server

Déclencheur sur une INSERTtable UPDATEou DELETE une instruction (déclencheur DML) :

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement  [ ; ] [ , ...n ] | EXTERNAL NAME <method_specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Déclencheur sur une INSERTtable , UPDATEou DELETE instruction (déclencheur DML sur des tables mémoire optimisées) :

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table }
[ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement  [ ; ] [ , ...n ] }

<dml_trigger_option> ::=
    [ NATIVE_COMPILATION ]
    [ SCHEMABINDING ]
    [ EXECUTE AS Clause ]

Déclencheur sur un CREATEdéclencheur , , ALTERDROPGRANT, DENY, , REVOKEou UPDATE instruction (déclencheur DDL) :

CREATE [ OR ALTER ] TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ , ...n ] ]
{ FOR | AFTER } { event_type | event_group } [ , ...n ]
AS { sql_statement  [ ; ] [ , ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

Déclencheur sur un événement (déclencheur d’ouverture LOGON de session) :

CREATE [ OR ALTER ] TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ , ...n ] ]
{ FOR | AFTER } LOGON
AS { sql_statement  [ ; ] [ , ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<logon_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

Base de données Azure SQL ou base de données SQL dans la syntaxe Fabric

Déclencheur sur une INSERTtable UPDATEou DELETE une instruction (déclencheur DML) :

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table | view }
 [ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
  AS { sql_statement  [ ; ] [ , ...n ] [ ; ] > }

<dml_trigger_option> ::=
        [ EXECUTE AS Clause ]

Déclencheur sur un CREATEdéclencheur , , ALTERDROPGRANT, DENY, , REVOKEou UPDATE STATISTICS instruction (déclencheur DDL) :

CREATE [ OR ALTER ] TRIGGER trigger_name
ON { DATABASE }
 [ WITH <ddl_trigger_option> [ , ...n ] ]
{ FOR | AFTER } { event_type | event_group } [ , ...n ]
AS { sql_statement  [ ; ] [ , ...n ]  [ ; ] }

<ddl_trigger_option> ::=
    [ EXECUTE AS Clause ]

Les arguments

OU ALTER

S’applique à : SQL Server 2016 (13.x) SP1 et versions ultérieures, Azure SQL Database, et SQL database dans Microsoft Fabric.

Modifie, de manière conditionnelle, le déclencheur uniquement s’il existe déjà.

schema_name

Nom du schéma auquel appartient le déclencheur DML. La portée des déclencheurs DML se limite au schéma de la table ou de la vue sur laquelle ils sont créés. Vous ne pouvez pas spécifier schema_name pour des déclencheurs DDL ou de connexion.

trigger_name

Nom du déclencheur. Une trigger_name doit suivre les règles pour les identificateurs, sauf que trigger_name ne peut pas commencer # ou ##.

table | Vue

Table ou vue sur laquelle le déclencheur DML est exécuté. Cette table ou cette vue sont parfois appelées table de déclencheur ou vue de déclencheur. La spécification du nom complet de la table ou de la vue est facultative. Vous ne pouvez référencer qu’une vue par un INSTEAD OF déclencheur. Vous ne pouvez pas définir de déclencheurs DML sur des tables temporaires locales ou globales.

BASE DE DONNÉES

Applique l'étendue d'un déclencheur DDL à la base de données active. S’il est spécifié, le déclencheur est activé chaque fois qu’event_type ou event_group se produit dans la base de données active.

TOUS LES SERVEURS

Applique l'étendue d'un déclencheur DDL ou de connexion au serveur actif. S’il est spécifié, le déclencheur est activé chaque fois qu’event_type ou event_group se produit à un endroit quelconque sur le serveur actif.

AVEC CHIFFREMENT

Masque le texte de l’instruction CREATE TRIGGER . L’utilisation WITH ENCRYPTION empêche la publication du déclencheur dans le cadre de la réplication SQL Server. WITH ENCRYPTION ne peut pas être spécifié pour les déclencheurs CLR.

EXECUTER EN TANT QUE

Spécifie le contexte de sécurité dans lequel le déclencheur est exécuté. Cet argument permet de contrôler le compte d'utilisateur que l'instance SQL Server utilise pour valider les autorisations sur n'importe quel objet de la base de données référencé par le déclencheur.

Cette option est obligatoire pour les déclencheurs sur les tables optimisées en mémoire.

Pour plus d’informations, consultez la clause EXECUTE AS.

NATIVE_COMPILATION

Indique que le déclencheur est compilé en mode natif.

Cette option est obligatoire pour les déclencheurs sur les tables optimisées en mémoire.

SCHEMABINDING

Garantit que les tables référencées par un déclencheur ne peuvent pas être supprimées ou modifiées.

Cette option est obligatoire pour les déclencheurs sur les tables optimisées en mémoire et n’est pas prise en charge pour les déclencheurs sur des tables traditionnelles.

FOR | APRÈS

FOR ou AFTER spécifie que le déclencheur DML se déclenche uniquement lorsque toutes les opérations spécifiées dans l’instruction SQL de déclenchement ont été lancées avec succès. Toutes les actions référentielles en cascade et les vérifications de contraintes doivent également être effectuées avec succès pour que ce déclencheur soit activé.

Vous ne pouvez pas définir AFTER de déclencheurs sur les vues.

AU LIEU DE

Spécifie que le déclencheur DML est lancé à la place de l’instruction SQL de déclenchement, remplaçant ainsi les actions des instructions de déclenchement. Vous ne pouvez pas spécifier INSTEAD OF les déclencheurs DDL ou d’ouverture de session.

Au maximum, vous pouvez définir un INSTEAD OF déclencheur par INSERT, UPDATEou DELETE une instruction sur une table ou une vue. Vous pouvez également définir des vues sur des vues où chaque vue a son propre INSTEAD OF déclencheur.

Vous ne pouvez pas définir INSTEAD OF de déclencheurs sur des vues pouvant être mises à jour qui utilisent WITH CHECK OPTION. Cela entraîne une erreur lorsqu’un INSTEAD OF déclencheur est ajouté à une vue WITH CHECK OPTION pouvant être mise à jour spécifiée. Vous supprimez cette option ALTER VIEW avant de définir le INSTEAD OF déclencheur.

{ [ SUPPRIMER ] [ , ] [ INSÉRER ] [ , ] [ MISE À JOUR ] }

Spécifie les instructions de modification des données qui activent le déclencheur DML lorsqu'une tentative a lieu pour l'appliquer à cette table ou à cette vue. Spécifier au moins une option. Utilisez une combinaison de ces options dans un ordre quelconque dans la définition du déclencheur.

Pour INSTEAD OF les déclencheurs, vous ne pouvez pas utiliser l’option DELETE sur les tables qui ont une relation référentielle, en spécifiant une action ON DELETEen cascade. De même, l’option UPDATE n’est pas autorisée sur les tables qui ont une relation référentielle, en spécifiant une action ON UPDATEen cascade.

AVEC APPEND

S’applique à : SQL Server 2008 (10.0.x) à SQL Server 2008 R2 (10.50.x).

Spécifie qu'il faut ajouter un déclencheur supplémentaire d'un type existant. WITH APPEND ne peut pas être utilisé avec INSTEAD OF des déclencheurs ou si un AFTER déclencheur est explicitement déclaré. Pour la compatibilité descendante, utilisez uniquement WITH APPEND une fois FOR spécifié, sans INSTEAD OF ou AFTER. Vous ne pouvez pas spécifier WITH APPEND si vous utilisez EXTERNAL NAME (autrement dit, si le déclencheur est un déclencheur CLR).

event_type

Nom d’un événement de langage Transact-SQL qui, après le lancement, provoque l'activation d'un déclencheur DDL. Les événements valides pour les déclencheurs DDL sont répertoriés dans Événements DDL.

event_group

Nom d'un regroupement prédéfini d'événements de langage Transact-SQL. Le déclencheur DDL est activé après l’exécution de n’importe quel événement de langage Transact-SQL appartenant à event_group. Les groupes d’événements valides pour les déclencheurs DDL sont répertoriés dans Groupes d’événements DDL.

Une fois l’exécution CREATE TRIGGER terminée, event_group agit également en tant que macro en ajoutant les types d’événements qu’il couvre à l’affichage sys.trigger_events catalogue.

NON POUR LA RÉPLICATION

Indique que le déclencheur ne doit pas être exécuté lorsqu'un aent de réplication modifie la table impliquée dans le déclencheur.

sql_statement

Conditions et actions du déclencheur. Les conditions du déclencheur spécifient des critères supplémentaires qui déterminent si les instructions DML, DDL ou de connexion tentées vont provoquer l'exécution des actions du déclencheur.

Les actions du déclencheur spécifiées dans les instructions Transact-SQL prennent effet lors de la tentative d'opération.

Les déclencheurs peuvent comprendre n’importe quel type et n’importe quel nombre d’instructions Transact-SQL. Il existe toutefois des exceptions. Pour plus d'informations, consultez la section Notes. Un déclencheur est conçu pour vérifier ou modifier des données en fonction d’une instruction de modification ou de définition de données. Le déclencheur ne doit pas retourner de données à l’utilisateur. Les instructions Transact-SQL dans un déclencheur comportent souvent un langage de contrôle de flux.

Les déclencheurs DML utilisent les tables logiques (conceptuelles) supprimées ou insérées. Leur structure est similaire à celle de la table sur laquelle le déclencheur est défini, c'est-à-dire la table sur laquelle l'action de l'utilisateur est tentée. Les tables supprimées et insérées contiennent les anciennes valeurs ou les nouvelles valeurs des lignes susceptibles d’être modifiées par l’action de l’utilisateur. Par exemple, pour extraire toutes les valeurs de la table deleted, utilisez :

SELECT * FROM deleted;

Pour plus d’informations, consultez Utiliser les tables insérées et supprimées.

DDL et déclencheurs d’ouverture de session capturent des informations sur l’événement déclencheur à l’aide de la fonction EVENTDATA . Pour plus d’informations, consultez Utiliser la fonction EVENTDATA.

SQL Server permet la mise à jour de colonnes de texte, de ntext ou d’image par le biais du INSTEAD OF déclencheur sur des tables ou des vues.

Important

Les types de données ntext, text et image seront supprimés dans une future version de Microsoft SQL Server. Évitez d'utiliser ces types de données dans un nouveau développement. Prévoyez de modifier les applications qui les utilisent actuellement. Utilisez nvarchar(max), varchar(max)et varbinary(max) à la place. Les déclencheurs et AFTER les INSTEAD OF déclencheurs prennent en charge les données varchar(max), nvarchar(max)et varbinary(max) dans les tables insérées et supprimées.

Pour les déclencheurs sur des tables mémoire optimisées, la seule sql_statement autorisée au niveau supérieur est un ATOMIC bloc. Le T-SQL autorisé à l’intérieur du ATOMIC bloc est limité par le T-SQL autorisé dans les processus natifs.

<method_specifier>

Pour un déclencheur CLR, spécifie la méthode de liaison d'un assembly avec le déclencheur. La méthode ne doit prendre aucun argument et retourner une valeur vide. class_name doit être un identificateur SQL Server valide et doit exister comme classe dans l’assembly avec une visibilité de l’assembly. Si la classe a un nom qualifié d’espace de noms qui utilise . pour séparer les parties d’espace de noms, le nom de la classe doit être délimité à l’aide de [ ] ou de « délimiteurs ». La classe ne peut pas être imbriquée.

Notes

Par défaut, la possibilité de SQL Server d'exécuter du code CLR est désactivée. Vous pouvez créer, modifier et supprimer des objets de base de données qui référencent des modules de code managé, mais ces références ne s’exécutent pas dans une instance de SQL Server, sauf si l’option clr activée est activée avec sp_configure.

Remarques relatives aux déclencheurs DML

Les déclencheurs DML s'utilisent souvent pour imposer des règles de gestion et l'intégrité des données. SQL Server fournit une intégrité référentielle déclarative (DRI) via les instructions et ALTER TABLE les CREATE TABLE instructions. Cependant, la fonctionnalité DRI ne gère pas l'intégrité référentielle entre bases de données. L'intégrité référentielle se réfère aux règles appliquées aux relations entre les clés primaires et les clés étrangères des tables. Pour appliquer l’intégrité référentielle, utilisez les contraintes et les PRIMARY KEYFOREIGN KEY contraintes dans ALTER TABLE et CREATE TABLE. 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 et avant l’exécution du AFTER déclencheur. Si les contraintes sont violées, les actions de INSTEAD OF déclencheur sont restaurées et le AFTER déclencheur n’est pas déclenché.

Vous pouvez spécifier les premiers et derniers AFTER déclencheurs à exécuter sur une table à l’aide sp_settriggerorderde . Vous ne pouvez spécifier qu’un seul et un dernier AFTER déclencheur pour chaque INSERTtable UPDATE, et DELETE l’opération sur une table. S’il existe d’autres AFTER déclencheurs sur la même table, ils sont exécutés de façon aléatoire.

Si une ALTER TRIGGER instruction modifie un premier ou dernier déclencheur, le premier ou le dernier attribut défini sur le déclencheur modifié est supprimé et vous devez réinitialiser la valeur de commande à l’aide sp_settriggerorderde .

Un AFTER déclencheur est exécuté uniquement une fois l’instruction SQL déclenchée correctement. Cette exécution réussie inclut toutes les actions d'intégrité référentielle en cascade et les vérifications des contraintes associées à l'objet mis à jour ou supprimé. Un AFTER déclencheur ne déclenche pas de manière récursive un INSTEAD OF déclencheur sur la même table.

Si un INSTEAD OF déclencheur défini sur une table exécute une instruction sur la table qui déclenche généralement à nouveau le INSTEAD OF déclencheur, le déclencheur n’est pas appelé de manière récursive. Au lieu de cela, l’instruction traite comme si la table n’avait aucun INSTEAD OF déclencheur et démarre la chaîne d’opérations de contrainte et AFTER d’exécutions de déclencheur. Par exemple, si un déclencheur est défini comme déclencheur INSTEAD OF INSERT pour une table. Et, si le déclencheur exécute une INSERT instruction sur la même table, l’instruction INSERT lancée par le INSTEAD OF déclencheur n’appelle pas à nouveau le déclencheur. Le INSERT déclencheur lancé démarre le processus d’exécution d’actions de contrainte et déclenche tous les AFTER INSERT déclencheurs définis pour la table.

Lorsqu’un INSTEAD OF déclencheur défini sur une vue exécute une instruction sur la vue qui déclenche généralement à nouveau le INSTEAD OF déclencheur, il n’est pas appelé de manière récursive. Au lieu de cela, l'instruction est résolue sous forme de modifications apportées aux tables de base sous-jacentes de la vue. Dans ce cas, la définition de la vue doit respecter toutes les restrictions applicables à une vue pouvant être mise à jour. Pour obtenir une définition des vues pouvant être mises à jour, consultez Modifier les données par l’intermédiaire d’une vue.

Par exemple, si un déclencheur est défini comme déclencheur INSTEAD OF UPDATE pour une vue. Et, le déclencheur exécute une UPDATE instruction référençant la même vue, l’instruction UPDATE lancée par le INSTEAD OF déclencheur n’appelle pas à nouveau le déclencheur. Le UPDATE déclencheur lancé par le déclencheur est traité par rapport à la vue comme si la vue n’avait pas de INSTEAD OF déclencheur. Les colonnes modifiées par la UPDATE table de base doivent être résolues en une seule table de base. Chaque modification apportée à une table de base sous-jacente démarre la chaîne d’application de contraintes et de déclencheurs de déclenchement AFTER définis pour la table.

Tester les actions UPDATE ou INSERT sur des colonnes spécifiques

Vous pouvez concevoir un déclencheur Transact-SQL pour effectuer certaines actions en fonction UPDATE ou INSERT des modifications apportées à des colonnes spécifiques. Utilisez UPDATE ou COLUMNS_UPDATED dans le corps du déclencheur à cet effet. UPDATE() UPDATE teste ou INSERT tente sur une colonne. COLUMNS_UPDATED teste UPDATE ou INSERT exécute des actions qui s’exécutent sur plusieurs colonnes. Cette fonction retourne un modèle binaire qui indique quelles colonnes ont été insérées ou mises à jour.

Limitations du déclencheur

CREATE TRIGGER doit être la première instruction du lot et peut s’appliquer à une seule table.

Un déclencheur n'est créé que dans la base de données active. Cependant, il peut faire référence à des objets qui se trouvent hors de la base de données active.

Si le nom du schéma du déclencheur est spécifié pour qualifier le déclencheur, qualifiez le nom de la table de la même façon.

La même action de déclencheur peut être définie pour plusieurs actions utilisateur (par exemple, INSERT et UPDATE) dans la même CREATE TRIGGER instruction.

INSTEAD OF DELETE / INSTEAD OF UPDATE Les déclencheurs ne peuvent pas être définis sur une table qui a une clé étrangère avec une cascade sur DELETE/UPDATE l’action définie.

Vous pouvez spécifier n'importe quelle instruction SET dans le déclencheur. L'option SET sélectionnée reste active pendant l'exécution du déclencheur, puis retrouve sa valeur d'origine.

Lorsqu'un déclencheur est activé, les résultats sont retournés à l'application appelante, comme pour les procédures stockées. Pour empêcher les résultats retournés à une application en raison d’un déclenchement de déclencheur, n’incluez SELECT pas d’instructions qui retournent des résultats ou des instructions qui effectuent une affectation de variable dans un déclencheur. Un déclencheur qui inclut des SELECT instructions qui retournent des résultats à l’utilisateur ou des instructions qui effectuent une affectation de variable, nécessite une gestion spéciale. Vous devriez écrire les résultats renvoyés dans chacune des applications autorisant des modifications de la table du déclencheur. Si l’attribution de variable doit se produire dans un déclencheur, utilisez une SET NOCOUNT instruction au début du déclencheur pour empêcher le retour des jeux de résultats.

Bien qu’une TRUNCATE TABLE instruction soit en vigueur DELETE , elle n’active pas de déclencheur, car l’opération ne journalise pas les suppressions de lignes individuelles. Toutefois, seuls les utilisateurs disposant d’autorisations pour exécuter une TRUNCATE TABLE instruction doivent être préoccupés par le contournement par inadvertance d’un DELETE déclencheur de cette façon.

L’instruction WRITETEXT , qu’elle soit journalisée ou non journalisée, n’active pas de déclencheur.

Les instructions Transact-SQL suivantes ne sont pas autorisées dans un déclencheur DML :

  • ALTER DATABASE
  • CREATE DATABASE
  • DROP DATABASE
  • RESTORE DATABASE
  • RESTORE LOG
  • RECONFIGURE

De plus, les instructions Transact-SQL suivantes ne sont pas autorisées dans le corps d'un déclencheur DML lorsque celui-ci est utilisé sur la table ou la vue cible de l'action de déclenchement.

  • CREATE INDEX (y compris CREATE SPATIAL INDEX et CREATE XML INDEX)
  • ALTER INDEX
  • DROP INDEX
  • DROP TABLE
  • DBCC DBREINDEX
  • ALTER PARTITION FUNCTION
  • ALTER TABLE lorsqu’il est utilisé pour effectuer les actions suivantes :
    • ajout, modification ou suppression de colonnes ;
    • changement de partitions ;
    • Ajoutez ou supprimez ou PRIMARY KEY supprimez UNIQUE des contraintes.

Notes

Étant donné que SQL Server ne prend pas en charge les déclencheurs définis par l’utilisateur sur les tables système, nous vous recommandons de ne pas créer de déclencheurs définis par l’utilisateur sur les tables système.

Optimiser les déclencheurs DML

Les déclencheurs fonctionnent dans des transactions (implicites ou autres). Quand ils sont ouverts, ils verrouillent les ressources. Le verrou reste en place jusqu’à ce que la transaction soit confirmée (avec COMMIT) ou rejetée (avec un ROLLBACK). Plus la durée d’exécution d’un déclencheur est longue, plus le risque de blocage d’un autre processus augmente. Par conséquent, écrivez les déclencheurs afin de réduire leur durée autant que possible. Une façon d’y parvenir est de libérer un déclencheur quand une instruction DML ne change aucune ligne.

Pour libérer un déclencheur dans une commande qui ne change aucune ligne, utilisez la variable système ROWCOUNT_BIG.

L’extrait de code T-SQL suivant montre comment libérer le déclencheur pour une commande qui ne modifie aucune ligne. Ce code doit se trouver au début de chaque déclencheur DML :

IF (ROWCOUNT_BIG() = 0)
RETURN;

Remarques relatives aux déclencheurs DDL

Les déclencheurs DDL, tout comme les déclencheurs standard, lancent des procédures stockées en réponse à un événement. Mais, contrairement aux déclencheurs standard, ils ne s’exécutent pas en réponse à UPDATE, INSERTou DELETE des instructions sur une table ou une vue. Au lieu de cela, ils s'exécutent essentiellement en réponse aux instructions DDL (Data Definition Language). Les types d’instructions incluent CREATE, , ALTERDROPGRANT, DENY, , REVOKE, et .UPDATE STATISTICS Certaines procédures stockées système qui effectuent des opérations de type DDL peuvent également activer des déclencheurs DDL.

Important

Testez vos déclencheurs DDL afin de déterminer leurs réponses à l'exécution des procédures stockées système. Par exemple, l’instruction CREATE TYPE et les sp_addtypesp_rename procédures stockées déclenchent un déclencheur DDL créé sur un CREATE_TYPE événement.

Pour plus d’informations sur les déclencheurs DDL, consultez déclencheurs DDL.

Les déclencheurs DDL ne sont pas activés en réponse à des événements qui concernent les tables et les procédures stockées temporaires locales ou globales.

À la différence des déclencheurs DML, le champ d'action des déclencheurs DDL ne correspond pas aux schémas. Par conséquent, vous ne pouvez pas utiliser de fonctions telles que OBJECT_ID, OBJECT_NAME, OBJECTPROPERTYet OBJECTPROPERTYEX pour interroger des métadonnées sur les déclencheurs DDL. Utilisez plutôt les affichages catalogue. Pour plus d’informations, consultez Obtenir des informations sur les déclencheurs DDL.

Notes

Les déclencheurs DDL dont l’étendue est le serveur figurent dans le dossier Déclencheurs de l’Explorateur d’objets SQL Server Management Studio. Ce dossier se trouve dans le dossier Objets serveur . Les déclencheurs DDL à l'étendue de la base de données apparaissent dans le dossier Déclencheurs de base de données. Ce dossier se trouve dans le dossier Programmabilité de la base de données correspondante.

Déclencheurs d’ouverture de session

Les déclencheurs d’ouverture de session exécutent des procédures stockées en réponse à un LOGON événement. Cet événement survient lorsqu'une session utilisateur est établie avec une instance de SQL Server. Les déclencheurs de connexion sont activés au terme de la phase d'authentification de connexion, mais avant l'établissement de la session utilisateur. Ainsi, tous les messages provenant du déclencheur qui atteindreaient généralement l’utilisateur, tels que les messages d’erreur et les messages de l’instruction PRINT , sont redirigés vers le journal des erreurs SQL Server. Pour plus d’informations, consultez déclencheurs d’ouverture de session.

Les déclencheurs de connexion ne sont pas activés si l'authentification échoue.

Les transactions distribuées ne sont pas prises en charge dans un déclencheur ouverture de session. L’erreur 3969 est retournée quand un déclencheur d’ouverture de session qui contient une transaction distribuée est activé.

Désactiver un déclencheur d’ouverture de session

Un déclencheur de connexion peut empêcher les connexions au Moteur de base de données pour tous les utilisateurs, notamment les membres du rôle serveur fixe sysadmin . Lorsqu’un déclencheur d’ouverture de session empêche les connexions, les membres du rôle serveur fixe sysadmin peuvent se connecter à l’aide de la connexion administrateur dédiée ou en démarrant le moteur de base de données en mode de configuration minimal (-f). Pour plus d'informations, consultez les options de démarrage du service Moteur de base de données .

Considérations générales relatives au déclencheur

Renvoyer les résultats

Cette possibilité d'obtenir des résultats via des déclencheurs sera supprimée dans les prochaines versions de Microsoft SQL Server. Les déclencheurs qui retournent des jeux de résultats peuvent entraîner un comportement inattendu dans les applications qui ne sont pas conçues pour les utiliser. Évitez de renvoyer des jeux de résultats provenant de déclencheurs dans un nouveau travail de développement et prévoyez la modification des applications qui y recourent actuellement. Pour empêcher les déclencheurs de retourner des jeux de résultats, attribuez la valeur 1 à l’option Interdire les résultats à partir des déclencheurs.

Les déclencheurs d’ouverture de session interdisent toujours le renvoi de jeux de résultats et ce comportement n'est pas configurable. Si un déclencheur d’ouverture de session génère un jeu de résultats, le lancement du déclencheur échoue et la tentative de connexion qui a activé le déclencheur est refusée.

Déclencheurs multiples

SQL Server vous permet de créer plusieurs déclencheurs pour chaque DML, DDL ou LOGON événement. Par exemple, si CREATE TRIGGER FOR UPDATE elle est exécutée pour une table qui a déjà un UPDATE déclencheur, un déclencheur de mise à jour supplémentaire est créé. Dans les versions antérieures de SQL Server, un seul déclencheur pour chaque INSERTévénement de modification de données ou UPDATE de DELETEmodification de données est autorisé pour chaque table.

Déclencheurs récursifs

SQL Server prend également en charge l’appel récursif des déclencheurs lorsque le paramètre est activé à l’aide RECURSIVE_TRIGGERSALTER DATABASEde .

Les déclencheurs récursifs permettent les types de récurrence suivants :

  • Récursivité indirecte : avec récursivité indirecte, une table T1de mises à jour d’application . Cela déclenche le déclencheur TR1, mise à jour de la table T2. Le déclencheur T2 déclenche ensuite et met à jour la table T1.

  • Récursivité directe : en récursivité directe, la table T1de mises à jour de l’application . Cela déclenche le déclencheur TR1, mise à jour de la table T1. Étant donné que la table T1 a été mise à jour, le déclencheur TR1 se déclenche à nouveau, et ainsi de suite.

L’exemple suivant utilise à la fois la récursivité des déclencheurs indirects et directs Supposez que deux déclencheurs de mise à jour et TR1TR2, sont définis sur la table T1. Déclenchez des TR1 mises à jour de la table T1 de manière récursive. Une UPDATE instruction s’exécute chaque TR1 fois TR2 . En outre, le lancement de TR1 déclenche l’exécution ( TR1 récursivement) et TR2. Les tables insérées et supprimées d’un déclencheur spécifique contiennent des lignes qui correspondent uniquement à l’instruction UPDATE qui a appelé le déclencheur.

Notes

Le comportement précédent se produit uniquement si le RECURSIVE_TRIGGERS paramètre est activé à l’aide ALTER DATABASEde . Les différents déclencheurs définis pour un événement donné ne sont pas exécutés dans un ordre défini. Chaque déclencheur doit être indépendant.

La désactivation du RECURSIVE_TRIGGERS paramètre empêche uniquement les récursivités directes. Pour désactiver également la récursivité indirecte, définissez l’option de serveur des déclencheurs imbriqués sur 0 à l’aide sp_configurede .

Si l’un des déclencheurs exécute un ROLLBACK TRANSACTIONdéclencheur, quel que soit le niveau d’imbrication, aucun autre déclencheur n’est exécuté.

Déclencheurs imbriqués

Vous pouvez imbriquer des déclencheurs sur 32 niveaux maximum. Si un déclencheur modifie une table dans laquelle il y a un autre déclencheur, le second déclencheur est activé et peut en appeler un troisième, etc. Si un des déclencheurs de la chaîne provoque une boucle infinie, le niveau d'imbrication maximal est dépassé et le déclencheur est annulé. Lorsqu'un déclencheur Transact-SQL lance du code managé en référençant une routine, un type ou un agrégat CLR, cette référence compte comme un seul niveau pour le calcul de la limite des 32 niveaux d'imbrication. Les méthodes appelées à partir du code managé ne comptent pas par rapport à cette limite.

Pour désactiver les déclencheurs imbriqués, définissez l’option de déclencheurs imbriqués sur sp_configure 0 (désactivé). La configuration par défaut prend en charge les déclencheurs imbriqués. Si les déclencheurs imbriqués sont désactivés, les déclencheurs récursifs sont également désactivés, malgré le paramètre défini à l’aide RECURSIVE_TRIGGERSALTER DATABASEde .

Le premier AFTER déclencheur imbriqué à l’intérieur d’un INSTEAD OF déclencheur se déclenche même si l’option de configuration de serveur des déclencheurs imbriqués est 0. Mais, sous ce paramètre, les déclencheurs ultérieurs AFTER ne se déclenchent pas. Vérifiez si vos applications contiennent des déclencheurs imbriqués pour déterminer si elles sont conformes aux règles de votre entreprise lorsque la configuration du serveur déclencheurs imbriqués est définie sur 0. Si ce n’est pas le cas, apportez les modifications appropriées.

Résolution de noms différée

SQL Server permet de Transact-SQL procédures stockées, déclencheurs, fonctions et lots pour faire référence aux tables qui n’existent pas au moment de la compilation. Cette fonction s'appelle la résolution différée des noms.

Autorisations

Pour créer un déclencheur DML, il nécessite ALTER une autorisation sur la table ou la vue sur laquelle le déclencheur est créé.

Pour créer un déclencheur DDL avec l’étendue du serveur (ON ALL SERVER) ou un déclencheur d’ouverture de session, vous devez CONTROL SERVER disposer d’autorisations sur le serveur. Pour créer un déclencheur DDL avec l’étendue de la base de données (ON DATABASE), nécessite ALTER ANY DATABASE DDL TRIGGER une autorisation dans la base de données active.

Exemples

R. Utiliser un déclencheur DML avec un message de rappel

Le déclencheur DML suivant affiche un message au client lorsque quelqu’un tente d’ajouter ou de modifier des données dans la Customer table de la base de données AdventureWorks2025.

CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10);
GO

B. Utiliser un déclencheur DML avec un message électronique de rappel

L'exemple suivant envoie un message électronique à une personne spécifiée (MaryM) lorsque la table Customer est modifiée.

CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
    EXECUTE msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2022 Administrator',
        @recipients = 'danw@Adventure-Works.com',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';
GO

Chapitre C. Utiliser un déclencheur DML AFTER pour appliquer une règle métier entre les tables PurchaseOrderHeader et Vendor

Étant donné que CHECK les contraintes référencent uniquement les colonnes sur lesquelles la contrainte au niveau des colonnes ou au niveau de la table est définie, vous devez définir toutes les contraintes inter-tables (dans ce cas, les règles métier) en tant que déclencheurs.

L'exemple suivant crée un déclencheur DML dans la base de données AdventureWorks2025. Ce déclencheur vérifie que les informations de conditions de crédit du fournisseur sont correctes (pas 5) lors d’une tentative d’insertion d’un nouveau bon de commande dans la table PurchaseOrderHeader. Pour obtenir les informations de conditions de crédit du fournisseur, la table Vendor doit être référencée. Si les conditions de crédit sont trop faibles, un message s'affiche et l'insertion n'a pas lieu.

USE AdventureWorks2022;
GO

IF OBJECT_ID('Purchasing.LowCredit', 'TR') IS NOT NULL
    DROP TRIGGER Purchasing.LowCredit;
GO

-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).
CREATE TRIGGER Purchasing.LowCredit
ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
    IF (ROWCOUNT_BIG() = 0)
    RETURN;
    IF EXISTS (SELECT 1
        FROM inserted AS i
        INNER JOIN Purchasing.Vendor AS v
            ON v.BusinessEntityID = i.VendorID
            WHERE v.CreditRating = 5)
BEGIN
    RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
    ROLLBACK;
    RETURN;
END
GO

-- This statement attempts to insert a row into the PurchaseOrderHeader table  
-- for a vendor that has a below average credit rating.  
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.  

INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,
    VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
VALUES (2, 3, 261, 1652, 4, GETDATE(), GETDATE(), 44594.55, 3567.564, 1114.8638);
GO

D. Utiliser un déclencheur DDL dans l’étendue de la base de données

L'exemple suivant utilise un déclencheur DDL pour empêcher la suppression d'un synonyme dans une base de données.

CREATE TRIGGER safety
    ON DATABASE
    FOR DROP_SYNONYM
    AS IF (@@ROWCOUNT = 0)
           RETURN;
       RAISERROR ('You must disable Trigger "safety" to remove synonyms!', 10, 1);
       ROLLBACK;
GO

DROP TRIGGER safety
    ON DATABASE;
GO

E. Utiliser un déclencheur DDL dans l’étendue du serveur

L’exemple suivant utilise un déclencheur DDL pour imprimer un message si un CREATE DATABASE événement se produit sur l’instance de serveur actuelle et utilise la EVENTDATA fonction pour récupérer le texte de l’instruction Transact-SQL correspondante. Pour plus d’exemples qui utilisent EVENTDATA dans les déclencheurs DDL, consultez Utiliser la fonction EVENTDATA.

CREATE TRIGGER ddl_trig_database
    ON ALL SERVER
    FOR CREATE_DATABASE
    AS PRINT 'Database Created.';
       SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)');
GO

DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
GO

F. Utiliser un déclencheur d’ouverture de session

L’exemple de déclencheur d’ouverture de session suivant refuse une tentative de connexion à SQL Server en tant que membre de la login_test connexion s’il existe déjà trois sessions utilisateur s’exécutant sous cette connexion. Passez <password> à un mot de passe fort.

USE master;
GO

CREATE LOGIN login_test
    WITH PASSWORD = '<password>' MUST_CHANGE, CHECK_EXPIRATION = ON;
GO

GRANT VIEW SERVER STATE TO login_test;
GO

CREATE TRIGGER connection_limit_trigger
    ON ALL SERVER
    WITH EXECUTE AS 'login_test'
    FOR LOGON
    AS BEGIN
           IF ORIGINAL_LOGIN() = 'login_test'
              AND (SELECT COUNT(*)
                   FROM sys.dm_exec_sessions
                   WHERE is_user_process = 1
                         AND original_login_name = 'login_test') > 3
               ROLLBACK;
       END

G. Afficher les événements qui provoquent le déclenchement d’un déclencheur

L'exemple suivant effectue une requête sur les vues de catalogue sys.triggers et sys.trigger_events pour déterminer les événements de langage Transact-SQL qui provoquent l'activation du déclencheur safety. Le déclencheur, est safetycréé dans l’exemple D. Utilisez un déclencheur DDL dans l’étendue de la base de données.

SELECT TE.*
FROM sys.trigger_events AS TE
     INNER JOIN sys.triggers AS T
         ON T.object_id = TE.object_id
WHERE T.parent_class = 0
      AND T.name = 'safety';
GO

Contenu connexe

  • Last updated on