CREATE TRIGGER (Transact-SQL)
Crée un déclencheur DML, DDL ou de connexion. Un déclencheur est une procédure stockée spéciale qui s'exécute automatiquement lorsqu'un événement se produit dans 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 des instructions INSERT, UPDATE ou DELETE exécutées sur une table ou une vue.
[!REMARQUE]
Ces déclencheurs s'activent au déclenchement d'un événement valide, que des lignes de table soient affectées ou non.
Les déclencheurs DDL s'exécutent en réponse à différents événements DDL (Data Definition Language). Ces événements correspondent essentiellement aux instructions Transact-SQL CREATE, ALTER et DROP et à certaines procédures stockées système qui effectuent des opérations de type DDL. Les déclencheurs de connexion sont activés en réponse à l'événement LOGON qui est levé lorsqu'une session utilisateur est établie. Il est possible de créer des déclencheurs directement à partir d'instructions Transact-SQL ou de méthodes d'assemblys créées dans le CLR (Common Language Runtime) Microsoft.NET Framework et téléchargées vers une instance de SQL Server. SQL Server permet de créer plusieurs déclencheurs pour toute instruction spécifique.
.gif "Remarque relative à la sécurité") Remarque relative à la sécurité |
|---|
Un code malveillant présent dans des déclencheurs peut s'exécuter sous des privilèges promus. Pour plus d'informations sur la manière de réduire cette menace, consultez Gestion de la sécurité liée aux déclencheurs. |
.gif "Icône Lien de rubrique")
Syntaxe
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE 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
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE 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 ]
<method_specifier> ::=
assembly_name.class_name.method_name
Trigger on a LOGON event (Logon Trigger)
CREATE 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 ]
<method_specifier> ::=
assembly_name.class_name.method_name
Arguments
schema_name
Nom du schéma associé au déclencheur DML. Les déclencheurs DML sont limités au schéma de la table ou à la vue sur laquelle ils sont créés. schema_name ne peut pas être spécifié pour des connexions DDL ou de connexion.trigger_name
Nom du déclencheur. Un trigger_name doit respecter les règles propres aux identificateurs, si ce n'est que trigger_name ne peut pas commencer par # ni ##.table | view
Table ou vue sur laquelle le déclencheur DML est exécuté. Elle est parfois appelée table de déclencheur ou vue de déclencheur. La spécification du nom complet de la table ou de la vue est facultative. Une vue peut être référencée seulement par un déclencheur INSTEAD OF. Vous ne pouvez pas définir des déclencheurs DML sur des tables temporaires locales ou globales.DATABASE
Applique l'étendue d'un déclencheur DDL à la base de données active. S'il est spécifié, le déclencheur est activé lorsque event_type ou event_group se produit dans la base de données active.ALL SERVER
Applique l'étendue d'un déclencheur DDL ou de connexion au serveur actif. S'il est spécifié, le déclencheur est activé lorsque event_type ou event_group se produit à un endroit quelconque dans le serveur actif.WITH ENCRYPTION
Code le texte de l'instruction CREATE TRIGGER. L'utilisation de l'argument WITH ENCRYPTION évite la publication du déclencheur dans le cadre de la réplication SQL Server. Il n'est pas possible de spécifier WITH ENCRYPTION pour les déclencheurs CLR.EXECUTE AS
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.Pour plus d'informations, consultez Clause EXECUTE AS (Transact-SQL).
FOR | AFTER
AFTER spécifie que le déclencheur DML est exécuté seulement lorsque toutes les opérations spécifiées dans l'instruction SQL de déclenchement ont été exécutées correctement. Toutes les actions d'intégrité référentielle en cascade et les vérifications des contraintes doivent être effectuées avec succès pour que ce déclencheur s'exécute.AFTER est la valeur par défaut lorsque FOR est le seul mot clé spécifié.
Il n'est pas possible de définir des déclencheurs AFTER sur des vues.
INSTEAD OF
Spécifie que le déclencheur DML est exécuté à la place de l'instruction SQL de déclenchement, remplaçant ainsi les actions de celle-ci. Il n'est pas possible de spécifier INSTEAD OF pour des déclencheurs DDL ou de connexion.Il est possible de définir au plus un déclencheur INSTEAD OF par instruction INSERT, UPDATE ou DELETE sur une table ou une vue. Vous pouvez cependant définir des vues sur des vues, où chaque vue a son propre déclencheur INSTEAD OF.
Les déclencheurs INSTEAD OF ne sont pas autorisés sur les vues pouvant être mises à jour qui utilisent l'option WITH CHECK OPTION. SQL Server signale une erreur lorsqu'un déclencheur INSTEAD OF est ajouté à une telle vue avec l'option WITH CHECK OPTION spécifiée. L'utilisateur doit supprimer cette option à l'aide de l'instruction ALTER VIEW avant de définir le déclencheur INSTEAD OF.
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
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. Vous devez spécifier au moins une option. Vous pouvez combiner dans la définition du déclencheur toutes ces options, dans n'importe quel ordre.Dans le cas des déclencheurs INSTEAD OF, l'option DELETE n'est pas autorisée sur les tables dont la relation référentielle spécifie une action en cascade ON DELETE. De même, l'option UPDATE n'est pas autorisée sur les tables dont la relation référentielle spécifie une action en cascade ON UPDATE.
event_type
Nom de l'événement du langage Transact-SQL qui, après l'exécution, provoque l'exécution 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 groupe prédéfini d'événements du langage Transact-SQL. Le déclencheur DDL est activé après l'exécution de n'importe quel événement du langage Transact-SQL qui appartient à 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 terminée l'exécution de l'instruction CREATE TRIGGER, event_group se comporte également comme une macro en ajoutant au catalogue sys.trigger_events les types d'événements traités.
WITH APPEND
Spécifie qu'il faut ajouter un déclencheur supplémentaire d'un type existant. La clause WITH APPEND ne peut pas s'utiliser avec des déclencheurs INSTEAD OF ou si le déclencheur AFTER est stipulé de manière explicite. Elle peut s'utiliser seulement lorsque FOR est spécifié (sans INSTEAD OF ou AFTER) pour des raisons de compatibilité descendante. Vous ne pouvez pas spécifier WITH APPEND si EXTERNAL NAME est spécifié (c'est-à-dire si le déclencheur est un déclencheur CLR).Important
WITH APPEND sera supprimé dans la version suivante de MicrosoftSQL Server. N'utilisez pas WITH APPEND dans un nouveau travail de développement et envisagez de modifier les applications qui s'en servent actuellement.
NOT FOR REPLICATION
Indique que le déclencheur ne doit pas être exécuté lorsqu'un Agent de réplication modifie la table impliquée dans le déclencheur. Pour plus d'informations, consultez Contrôle des contraintes, des identités et des déclencheurs avec l'option NOT FOR REPLICATION.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 des exceptions. Pour plus d'informations, consultez la section « Remarques ». Un déclencheur sert à vérifier ou à modifier des données suite à une instruction de modification ou de définition. Il ne doit pas retourner des données à l'utilisateur. Les instructions Transact-SQL dans un déclencheur comportent souvent une séquence de 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 ou les nouvelles valeurs des lignes que l'action de l'utilisateur peut modifier. Par exemple, pour extraire toutes les valeurs de la table deleted, utilisez :
SELECT * FROM deletedPour plus d'informations, consultez Utilisation des tables inserted et deleted.
Les déclencheurs DDL et de connexion capturent des informations sur l'événement de déclenchement à l'aide de la fonction EVENTDATA (Transact-SQL). Pour plus d'informations, cliquez sur le lien Utilisation de la fonction EVENTDATA.
SQL Server permet d'actualiser les colonnes text, ntext ou image via le déclencheur INSTEAD OF dans les tables ou dans les vues.
Important
Les types de données ntext, text et image seront supprimés dans une version future de MicrosoftSQL Server. Évitez d'utiliser ces types de données dans un nouveau développement. Prévoyez de modifier les applications qui les utilisent actuellement. Au lieu de cela, utilisez les types de données nvarchar(max), varchar(max) et varbinary(max). Les déclencheurs AFTER et INSTEAD OF sont compatibles avec les données varchar(MAX), nvarchar(MAX) et varbinary(MAX) dans les tables insérées ou supprimées.
< 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 exister en tant que classe dans l'assembly avec une visibilité d'assembly. Si la classe possède un nom qualifié par un espace de noms utilisant un point '.' pour séparer les parties de l'espace de noms, le nom de la classe doit être délimité par des crochets ([ ]) ou des guillemets doubles (" "). La classe ne peut pas être imbriquée.[!REMARQUE]
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 d'une base de données qui font référence à des modules de code managé. Cependant, ces références ne s'exécuteront pas dans une instance de SQL Server à moins que l'option clr enabled soit activée à l'aide de sp_configure.
Notes
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 l'intégrité référentielle déclarative (DRI) via des instructions ALTER TABLE et CREATE TABLE. 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 PRIMARY KEY et FOREIGN KEY dans les instructions ALTER TABLE et CREATE TABLE. S'il existe des contraintes sur la table des déclencheurs, elles sont vérifiées après l'exécution du déclencheur INSTEAD OF et avant celle 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é.
Vous pouvez spécifier le premier et le dernier déclencheur AFTER à exécuter sur une table à l'aide de sp_settriggerorder. Sur une table, il ne peut y avoir qu'un seul premier et un seul dernier déclencheur AFTER pour chaque instruction INSERT, UPDATE ou DELETE. S'il y a d'autres déclencheurs AFTER sur la même table, ils sont exécutés de manière aléatoire.
Si une instruction ALTER TRIGGER modifie un premier ou un dernier déclencheur, le premier ou le dernier attribut défini sur le déclencheur modifié est supprimé et la valeur du rang d'exécution doit être réinitialisée avec sp_settriggerorder.
Un déclencheur AFTER est exécuté seulement après que l'instruction SQL de déclenchement se soit exécuté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é.
Si un déclencheur INSTEAD OF défini sur une table exécute une instruction portant sur cette table et qui est susceptible de l'activer de nouveau, il n'est pas appelé de façon récurrente. L'instruction est traitée comme si la table n'avait aucun déclencheur INSTEAD OF et démarre la chaîne des opérations de contrainte et des exécutions du déclencheur AFTER. Par exemple, si un déclencheur est défini sur une table comme déclencheur INSTEAD OF INSERT et qu'il exécute une instruction INSERT sur cette table, cette instruction INSERT ne l'appelle pas une seconde fois. L'instruction INSERT exécutée par le déclencheur démarre le processus d'exécution des actions de contrainte et d'activation de tout déclencheur AFTER INSERT défini pour la table.
Si un déclencheur INSTEAD OF défini sur une vue exécute une instruction portant sur cette vue et qui est susceptible de l'activer de nouveau, il n'est pas appelé de façon récurrente. 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 la définition des vues pouvant être mises à jour, consultez Modification de données par l'intermédiaire d'une vue.
Par exemple, si un déclencheur est défini comme déclencheur INSTEAD OF UPDATE sur une vue et qu'il exécute une instruction UPDATE faisant référence à la même vue, cette instruction UPDATE n'appelle pas à nouveau le déclencheur. Elle est appliquée à la vue comme si celle-ci ne comportait pas de déclencheur INSTEAD OF. Les colonnes modifiées par l'instruction UPDATE doivent être résolues en une seule table de base. Chaque modification d'une table de base sous-jacente démarre la chaîne d'application des contraintes et d'activation des déclencheurs AFTER définis sur la table.
Test des actions UPDATE ou INSERT sur des colonnes spécifiques
Vous pouvez créer un déclencheur Transact-SQL qui exécute certaines actions en fonction de modifications des instructions UPDATE ou INSERT sur des colonnes particulières. Pour cela, utilisez UPDATE() ou COLUMNS_UPDATED dans le corps du déclencheur. UPDATE() teste les tentatives UPDATE ou INSERT sur une colonne. COLUMNS_UPDATED teste les actions UPDATE ou INSERT exécutées sur plusieurs colonnes et retourne un modèle binaire qui indique les colonnes insérées ou mises à jour.
Limitations des déclencheurs
CREATE TRIGGER doit être la première instruction du traitement et ne peut s'appliquer qu'à 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 de l'utilisateur (par exemple, INSERT et UPDATE) dans la même instruction CREATE TRIGGER.
Les déclencheurs INSTEAD OF DELETE/UPDATE ne peuvent pas être définis sur une table ayant une clé étrangère pour laquelle une action DELETE/UPDATE en cascade est 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 éviter le retour de résultats à une application parce qu'un déclencheur est activé, n'incluez pas d'instructions SELECT qui retournent des résultats, ni d'instructions affectant des variables dans un déclencheur. Un déclencheur qui inclut soit des instructions SELECT qui retournent des résultats à l'utilisateur, soit des instructions exécutant des affectations de variables, doit être traité de manière particulière. Les résultats retournés doivent être écrits dans chaque application dans laquelle des modifications de la table du déclencheur sont autorisées. Si une affectation de variable doit avoir lieu dans un déclencheur, utilisez l'instruction SET NOCOUNT au début du déclencheur, pour éviter tout retour d'un ensemble de résultats.
Bien qu'une instruction TRUNCATE TABLE soit appliquée dans une instruction DELETE, elle n'active pas de déclencheur parce que l'opération n'enregistre pas les suppressions de lignes individuelles. Toutefois, seuls les utilisateurs disposant d'autorisations permettant d'exécuter une instruction TRUNCATE TABLE doivent se soucier de contourner par inadvertance un déclencheur DELETE de cette façon.
L'instruction WRITETEXT, enregistrée ou non dans le journal, n'active pas un déclencheur.
Les instructions Transact-SQL suivantes ne sont pas autorisées dans un déclencheur DML :
ALTER DATABASE |
CREATE DATABASE |
DROP DATABASE |
LOAD DATABASE |
LOAD LOG |
RECONFIGURE |
RESTORE DATABASE |
RESTORE LOG |
|
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 |
DBCC DBREINDEX |
ALTER PARTITION FUNCTION |
DROP TABLE |
ALTER TABLE quand elle est utilisée pour effectuer les actions suivantes :
|
|
|
[!REMARQUE]
SQL Server ne prenant pas en charge les déclencheurs définis par l'utilisateur sur des tables système, nous recommandons de ne pas en créer.
Déclencheurs DDL
Les déclencheurs DDL, tout comme les déclencheurs standard, exécutent des procédures stockées en réponse à un événement. Cependant, à la différence des déclencheurs standard, ils ne s'exécutent pas en réponse aux instructions UPDATE, INSERT ou DELETE sur une table ou sur une vue. Au lieu de cela, ils s'exécutent essentiellement en réponse aux instructions DDL (Data Definition Language). Il s'agit des instructions CREATE, ALTER, DROP, GRANT, 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 procédures stockées sp_addtype et sp_rename activeront un déclencheur DDL créé à l'occasion d'un événement CREATE_TYPE.
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, les fonctions OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY et OBJECTPROPERTYEX ne sont pas utilisables pour effectuer des requêtes de métadonnées à propos de déclencheurs DDL. Utilisez plutôt les affichages catalogue. Pour plus d'informations, consultez Obtention d'informations sur les déclencheurs DDL.
[!REMARQUE]
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, situé sous le dossier Objets serveur. Les déclencheurs DDL dont l'étendue est la base de données figurent 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 de connexion
Les déclencheurs de connexion exécutent des procédures stockées en réponse à un événement LOGON. Cet événement est déclenché 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. Par conséquent, tous les messages provenant du corps du déclencheur et habituellement destinés à l'utilisateur, (les messages et les messages d'erreur de l'instruction PRINT, par exemple), sont dirigés vers le journal d'erreurs SQL Server. Pour plus d'informations, consultez Déclencheurs de connexion.
Les déclencheurs de connexion ne sont pas activés si l'authentification échoue.
Les transactions distribuées ne sont pas compatibles avec un déclencheur de connexion. Le système affiche le message d'erreur 3969 lorsqu'un déclencheur de connexion contenant une transaction distribuée est activé.
Considérations générales sur les déclencheurs
Retour de 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 ensembles de résultats sont susceptibles d'entraîner un comportement inattendu au niveau des applications qui ne sont pas conçues pour fonctionner avec eux. Évitez de retourner des ensembles de résultats à partir de déclencheurs dans toute nouvelle tâche de développement et envisagez de modifier les applications qui utilisent actuellement cette possibilité. 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 de connexion interdisent toujours le renvoi de jeux de résultats et ce comportement n'est pas configurable., Si un déclencheur de connexion génère un jeu de résultats, le déclencheur échoue et la tentative de connexion qui a exécuté le déclencheur est refusée.
Déclencheurs multiples
SQL Server autorise la création de plusieurs déclencheurs pour chaque événement DML, DDL ou LOGON. Par exemple, si la commande CREATE TRIGGER FOR UPDATE est exécutée pour une table qui comporte déjà un déclencheur UPDATE, un déclencheur de mise à jour supplémentaire est créé. Dans les versions antérieures de SQL Server, il n'était possible de créer qu'un seul déclencheur par événement de modification de données INSERT, UPDATE ou DELETE pour chaque table.
Déclencheurs récursifs
SQL Server permet l'appel récursif de déclencheurs lorsque le paramètre RECURSIVE_TRIGGERS est activé au moyen de l'instruction ALTER DATABASE.
Les déclencheurs récursifs permettent les types de récurrence suivants :
Récurrence indirecte.
Avec la récurrence indirecte, une application met à jour la table T1. Cela active le déclencheur TR1, avec pour conséquence la mise à jour de la table T2. Dans ce scénario, le déclencheur T2 est activé et met à jour la table T1.
Récurrence directe.
Avec la récurrence directe, l'application met à jour la table T1. Cela active le déclencheur TR1, avec pour conséquence la mise à jour de la table T1. La table T1 ayant été mise à jour, le déclencheur TR1 est réactivé, etc.
L'exemple suivant illustre l'utilisation de la récurrence directe et indirecte. Supposons que deux déclencheurs de mise à jour, TR1 et TR2, soient définis sur la table T1. Le déclencheur TR1 met à jour la table T1 de manière récursive. Une instruction UPDATE exécute chaque déclencheur TR1 et TR2 une fois. De plus, l'exécution du déclencheur TR1 entraîne l'exécution de TR1 (de manière récursive) et de TR2. Les tables inserted et deleted d'un déclencheur donné contiennent des lignes qui ne correspondent qu'à l'instruction UPDATE qui a appelé le déclencheur.
[!REMARQUE]
Le comportement précédent se produit uniquement si le paramètre RECURSIVE_TRIGGERS est activé au moyen de l'instruction ALTER DATABASE. 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 paramètre RECURSIVE_TRIGGERS empêche uniquement les récurrences directes. Pour désactiver également la récurrence indirecte, affectez la valeur 0 à l'option de serveur nested triggers à l'aide de sp_configure.
Si un des déclencheurs effectue une opération ROLLBACK TRANSACTION, quel que soit le niveau d'imbrication, aucun autre déclencheur n'est exécuté.
Déclencheurs imbriqués
Les déclencheurs peuvent compter jusqu'à 32 niveaux d'imbrication. 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 exécute 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é n'entrent pas en compte dans cette limite
Pour désactiver les déclencheurs imbriqués, attribuez la valeur 0 (off) à l'option nested triggers de sp_configure. La configuration par défaut permet les déclencheurs imbriqués. Si l'option des déclencheurs imbriqués est désactivée, les déclencheurs récursifs le sont également, quel que soit le paramètre RECURSIVE_TRIGGERS défini avec l'instruction ALTER DATABASE.
[!REMARQUE]
Dans SQL Server 2000, aucun déclencheur AFTER imbriqué dans un déclencheur INSTEAD OF ne s'exécute lorsque l'option de configuration du serveur de nested triggers est désactivée (OFF). Dans SQL Server 2005 ou version ultérieure, le premier déclencheur AFTER imbriqué dans un déclencheur INSTEAD OF se déclenche même si l'option de configuration du serveur de nested triggers est définie à 0. Toutefois, les déclencheurs AFTER suivants ne se déclenchent pas avec ce paramètre. Contrôlez les déclencheurs imbriqués de vos applications afin de déterminer si ces applications sont toujours conformes aux règles d'entreprise relatives à ce nouveau comportement lorsque l'option de configuration du serveur de nested triggers est définie à 0, puis effectuez les modifications nécessaires.
Résolution de noms différée
SQL Server permet aux procédures stockées, aux déclencheurs et aux traitements d'instructions Transact-SQL de faire référence à des tables qui n'existent pas au moment de la compilation. Cette fonction s'appelle la résolution différée des noms. Cependant, si la procédure stockée, le déclencheur ou le lot d'instructions Transact-SQL fait référence à une table définie dans la procédure stockée ou le déclencheur, le système envoie un avertissement au moment de la création uniquement, si le paramètre du niveau de compatibilité a la valeur 65. Un message d'avertissement est généré au moment de la compilation si un lot est utilisé. Un message d'erreur est retourné au moment de l'exécution si la table référencée n'existe pas. Pour plus d'informations, consultez Résolution de noms différée et compilation.
Autorisations
La création d'un déclencheur DML nécessite l'autorisation ALTER sur la table ou la vue sur laquelle le déclencheur est créé.
La création d'un déclencheur DDL avec une étendue de serveur (ON ALL SERVER) ou d'un déclencheur de connexion nécessite l'autorisation CONTROL SERVER sur le serveur. La création d'un déclencheur DDL avec l'étendue de la base de données (ON DATABASE) nécessite l'autorisation ALTER ANY DATABASE DDL TRIGGER sur la base de données active.
Exemples
A. Utilisation d'un déclencheur DML avec un message de rappel
Le déclencheur DML suivant affiche un message à destination du client lorsque quelqu'un essaye d'ajouter ou de modifier des données dans la table Customer.
USE AdventureWorks;
GO
IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL
DROP TRIGGER Sales.reminder1;
GO
-- This trigger raises a message whenever a row is inserted or modified in Sales.Customer.
CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10);
GO
B. Utilisation d'un déclencheur DML avec un message de rappel par courrier électronique
L'exemple suivant envoie un message électronique à une personne spécifiée (MaryM) lorsque la table Customer est modifiée.
USE AdventureWorks;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
DROP TRIGGER Sales.reminder2;
GO
-- This trigger sends an e-mail message to a specified account whenever
-- a row is inserted, updated or deleted from the Sales.Customer table.
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorks Administrator',
@recipients = 'danw@Adventure-Works.com',
@body = 'Don''t forget to print a report for the sales force.',
@subject = 'Reminder';
GO
C. Utilisation d'un déclencheur DML AFTER pour imposer une règle de gestion entre les tables PurchaseOrderHeader et Vendor
Les contraintes CHECK pouvant référencer uniquement les colonnes sur lesquelles des contraintes de niveau table ou colonne sont définies, toutes les contraintes entre tables (dans ce cas, des règles de gestion) doivent être définies sous la forme de déclencheurs.
L'exemple suivant crée un déclencheur DML. Ce déclencheur vérifie que les informations de conditions de crédit du fournisseur sont correctes 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.
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 EXISTS (SELECT *
FROM Purchasing.PurchaseOrderHeader p
JOIN inserted AS i
ON p.PurchaseOrderID = i.PurchaseOrderID
JOIN Purchasing.Vendor AS v
ON v.VendorID = p.VendorID
WHERE v.CreditRating = 5
)
BEGIN
RAISERROR ('This vendor''s credit rating is too low to accept new
purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;
-- 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. Utilisation de la résolution différée des noms
L'exemple suivant crée deux déclencheurs DML afin d'illustrer la résolution différée des noms.
USE AdventureWorks;
GO
IF OBJECT_ID ('HumanResources.trig1','TR') IS NOT NULL
DROP TRIGGER HumanResources.trig1;
GO
-- Creating a trigger on a nonexistent table.
CREATE TRIGGER HumanResources.trig1
on HumanResources.Employee
AFTER INSERT, UPDATE, DELETE
AS
SELECT e.EmployeeID, e.BirthDate, x.info
FROM HumanResources.Employee AS e INNER JOIN does_not_exist AS x
ON e.EmployeeID = x.xID
GO
-- This statement displays the text of the trigger.
SELECT t.object_id, m.definition
FROM sys.triggers AS t INNER JOIN sys.sql_modules AS m
ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig1'
AND t.parent_class = 1
GO
-- Creating a trigger on an existing table, but with a nonexistent
-- column.
USE AdventureWorks;
GO
IF OBJECT_ID ('HumanResources.trig2','TR') IS NOT NULL
DROP TRIGGER HumanResources.trig2
GO
CREATE TRIGGER HumanResources.trig2
ON HumanResources.Employee
AFTER INSERT, UPDATE
AS
DECLARE @fax varchar(12)
SELECT @fax = 'AltPhone'
FROM HumanResources.Employee
GO
-- This statement displays the text of the trigger.
SELECT t.object_id, m.definition
FROM sys.triggers AS t INNER JOIN sys.sql_modules AS m
ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig2'
AND t.parent_class = 1
GO
E. Utilisation d'un déclencheur DDL avec une étendue de 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.
USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class = 0 AND name = 'safety')
DROP TRIGGER safety
ON DATABASE;
GO
CREATE TRIGGER safety
ON DATABASE
FOR DROP_SYNONYM
AS
RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
ROLLBACK
GO
DROP TRIGGER safety
ON DATABASE;
GO
F. Utilisation d'un déclencheur DDL avec une étendue de serveur
L'exemple suivant utilise un déclencheur DDL pour imprimer un message si un événement CREATE DATABASE se produit sur l'instance de serveur active. Il utilise la fonction EVENTDATA pour récupérer le texte de l'instruction Transact-SQL correspondante.
[!REMARQUE]
Pour davantage d'exemples d'utilisation de EVENTDATA dans les déclencheurs DDL, consultez Utilisation de la fonction EVENTDATA.
IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
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
G. Utilisation d'un déclencheur de connexion
L'exemple de déclencheur de connexion suivant refuse une tentative de connexion à SQL Server en tant que membre de la connexion login_test si trois sessions utilisateur sont déjà en cours sous cette connexion.
USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' 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;
H. Affichage des événements qui provoquent l'activation d'un déclencheur
L'exemple suivant effectue une requête sur les affichages 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 safety est créé dans l'exemple précédent.
SELECT TE.*
FROM sys.trigger_events AS TE
JOIN sys.triggers AS T
ON T.object_id = TE.object_id
WHERE T.parent_class = 0
AND T.name = 'safety'
GO
Historique des modifications
Mise à jour du contenu |
|---|
Insertion d'une note dans la section intitulée 'Nested Triggers' (déclenchements imbriqués). La rubrique Remarques indique une modification du comportement de SQL Server 2005 lorsque vous importez des déclencheurs AFTER dans le déclencheur INSTEAD OF. |
Informations insérées dans la rubrique 'Logon Triggers'. Elles indiquent que ces déclencheurs ne sont pas compatibles avec les transactions distribuées. |
Suppression de l'instruction stockée sp_rename qui n'active pas de déclencheur DDL. |
Voir aussi