CREATE TRIGGER (Transact-SQL)

Article
12/15/2008

Mis à jour : 17 juillet 2006

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 :
Ils s'activent au déclenchement d'un événement valide, que des lignes de la table en soient affectées ou non. Ce mode de fonctionnement est intentionnel.

Les déclencheurs DDL s'exécutent en réponse à différents événements du langage de définition de données (DDL). 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 dans le moteur de base de données SQL Server 2005 directement à partir d'instructions Transact-SQL ou de méthodes d'assemblys créées dans le langage Microsoft .NET Framework CLR (Common Language Runtime) et téléchargées vers une instance de SQL Server. SQL Server permet de créer plusieurs déclencheurs pour n'importe quelle instruction spécifique.

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.

Icône Lien de rubrique Conventions de syntaxe Transact-SQL

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 auquel appartient le 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. L'argument trigger_name doit respecter les règles des identificateurs, à ceci près qu'il ne peut pas commencer par # ou ##.

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 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 dont l'utilisation est valide dans les déclencheurs DDL sont répertoriés dans Événements DDL à utiliser avec des déclencheurs DLL.

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 dont l'utilisation est valide dans les déclencheurs DDL sont répertoriés dans Groupes d'événements à utiliser avec des déclencheurs DDL.

Lorsque l'instruction CREATE TRIGGER est exécutée, event_group agit également comme une macro en ajoutant les types d'événements qu'il traite à l'affichage catalogue sys.trigger_events.

WITH APPEND
Spécifie qu'il faut ajouter un déclencheur supplémentaire d'un type existant. L'utilisation de cette clause facultative est requise seulement lorsque le niveau de compatibilité est inférieur ou égal à 65. Si le niveau de compatibilité est supérieur ou égal à 70, la clause WITH APPEND n'est pas requise pour ajouter un déclencheur supplémentaire à un type existant. Il s'agit du comportement par défaut de l'instruction CREATE TRIGGER avec un niveau de compatibilité supérieur ou égal à 70. Pour plus d'informations, consultez sp_dbcmptlevel (Transact-SQL).

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 :
La fonction WITH APPEND sera supprimée dans une version future de Microsoft SQL Server. Évitez d'utiliser cette fonction dans un nouveau développement. Prévoyez de modifier les applications qui l'utilisent 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) deleted et inserted. 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 deleted et inserted contiennent les anciennes ou les nouvelles valeurs que l'action de l'utilisateur peut modifier. Par exemple, pour extraire toutes les valeurs de la table deleted, utilisez :

SELECT *
FROM deleted

Pour 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 fonctionEVENTDATA (Transact-SQL). Pour plus d'informations, consultez Utilisation de la fonction EVENTDATA.

Dans un déclencheur DELETE, INSERT ou UPDATE, SQL Server n'autorise pas les références de colonnes text, ntext ou image dans les tables inserted et deleted lorsque le niveau de compatibilité a la valeur 70. Les valeurs text, ntext et image qui figurent dans les tables inserted et deleted sont inaccessibles. Pour extraire la nouvelle valeur dans un déclencheur INSERT ou UPDATE, effectuez une jointure entre la table inserted et la table de mise à jour d'origine. Lorsque le niveau de compatibilité est inférieur ou égal à 65, des valeurs NULL sont retournées pour les colonnes text, ntext ou image de inserted ou deleted qui acceptent les valeurs NULL. Des chaînes de longueur nulle sont retournées si les colonnes n'acceptent pas les valeurs NULL.

Si le niveau de compatibilité est supérieur ou égal à 80, SQL Server autorise la mise à jour de colonnes text, ntext ou image au moyen du déclencheur INSTEAD OF sur des tables ou des vues.

Important :
Les types de données ntext, text et image seront supprimés dans une version future 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. Au lieu de cela, utilisez les types de données nvarchar(max), varchar(max) et varbinary(max). Les déclencheurs AFTER et INSTEAD OF prennent en charge les données varchar(MAX), nvarchar(MAX) et varbinary(MAX) dans les tables inserted et deleted.

Les types de données ntext, text et image seront supprimés dans une version future 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. Au lieu de cela, utilisez les types de données nvarchar(max), varchar(max) et varbinary(max). Les déclencheurs AFTER et INSTEAD OF prennent en charge les données varchar(MAX), nvarchar(MAX) et varbinary(MAX) dans les tables inserted et deleted.

< 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 a un nom qualifié par un espace de noms qui utilise '.' 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 lot 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 en fait une instruction DELETE, elle ne peut pas activer de déclencheur car l'opération n'enregistre pas les suppressions de lignes individuelles dans le journal. Toutefois, seuls les utilisateurs dotés d'autorisations sur une table leur permettant d'exécuter une instruction TRUNCATE TABLE doivent veiller à ne pas faire échouer par inadvertance un déclencheur DELETE avec une instruction TRUNCATE TABLE.

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.

Important :
Bien que cette restriction soit introduite dans SQL Server 2005, elle est également appliquée lorsque le mode de compatibilité descendante est défini à 80.

CREATE INDEX

ALTER INDEX

DROP INDEX

DBCC DBREINDEX

ALTER PARTITION FUNCTION

DROP TABLE

ALTER TABLE quand elle est utilisée pour effectuer les actions suivantes :

ajout, modification ou suppression de colonnes ;
changement de partitions ;
ajout ou suppression de contraintes PRIMARY KEY ou UNIQUE.

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 la procédure stockée sp_addtype activeront toutes deux un déclencheur DDL créé sur un événement CREATE_TYPE. Toutefois, la procédure stockée sp_rename n'active pas de déclencheur DDL.

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, l'étendue des déclencheurs DDL ne correspond pas aux schémas. Par conséquent, les propriétés 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, situé sous le dossier Programmabilité de la base de données correspondante.

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, situé sous le dossier Programmabilité de la base de données correspondante.

Déclencheurs de connexion

Les déclencheurs de connexion sont activés en réponse à l'événement LOGON. Cet événement est déclenché lorsqu'une session utilisateur est établie. Pour plus d'informations, consultez Déclencheurs de connexion.

Considérations générales sur les déclencheurs

Retour de résultats

Cette possibilité de retourner des résultats à partir 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 ensembles de résultats dans SQL Server 2005, définissez l'option d'interdiction des résultats des déclencheurs sur 1.

Les déclencheurs LOGON interdisent toujours les ensembles de résultats à retourner et ce comportement n'est pas configurable., Si un déclencheur LOGON ne génère pas un ensemble de résultats, l'exécution du déclencheur échoue et la tentative de connexion ayant activé 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.

Remarque :
Avec un niveau de compatibilité de 70, le comportement par défaut de CREATE TRIGGER ajoute des déclencheurs aux déclencheurs existants si les noms des déclencheurs sont différents. Si les noms des déclencheurs sont identiques, SQL Server retourne un message d'erreur. Cependant, si le niveau de compatibilité est inférieur ou égal à 65, tout nouveau déclencheur créé à l'aide de l'instruction CREATE TRIGGER remplace tous les déclencheurs existants du même type, même si les noms des déclencheurs sont différents. Pour plus d'informations, consultez sp_dbcmptlevel (Transact-SQL).

Avec un niveau de compatibilité de 70, le comportement par défaut de CREATE TRIGGER ajoute des déclencheurs aux déclencheurs existants si les noms des déclencheurs sont différents. Si les noms des déclencheurs sont identiques, SQL Server retourne un message d'erreur. Cependant, si le niveau de compatibilité est inférieur ou égal à 65, tout nouveau déclencheur créé à l'aide de l'instruction CREATE TRIGGER remplace tous les déclencheurs existants du même type, même si les noms des déclencheurs sont différents. Pour plus d'informations, consultez sp_dbcmptlevel (Transact-SQL).

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é. 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 :
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é ne rentrent pas en compte dans cette limite.

Résolution de noms différée

SQL Server permet aux procédures stockées, aux déclencheurs et aux lots 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
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
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.

Remarque :
Pour visualiser des exemples de déclencheurs DML AFTER qui mettent à jour plusieurs lignes, consultez Observations au sujet des lignes multiples pour les déclencheurs DML. Pour visualiser des exemples de déclencheurs DML INSTEAD OF INSERT, consultez Déclencheurs INSTEAD OF INSERT.

IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
CREATE TRIGGER LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
DECLARE @creditrating tinyint,
   @vendorid int
SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID
FROM Purchasing.PurchaseOrderHeader AS p 
    INNER JOIN inserted AS i ON p.PurchaseOrderID =
   i.PurchaseOrderID 
    JOIN Purchasing.Vendor AS v on v.VendorID = i.VendorID
IF @creditrating = 5
BEGIN
   RAISERROR ('This vendor''s credit rating is too low to accept new
      purchase orders.', 16, 1)
ROLLBACK TRANSACTION
END
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
-- Here is the statement to actually see 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
-- Here is the statement to actually see 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

Voir aussi

Historique des modifications

Version	Historique
12 décembre 2006	Nouveau contenu Ajout des informations dans la rubrique sur les déclencheurs de connexion qui sont introduits dans SQL Server 2005 Service Pack 2.
17 juillet 2006	Contenu modifié Mise à jour des exemples E et F.
14 avril 2006	Nouveau contenu : Dans la section Remarques, ajout d'une note importante qui recommande de tester les déclencheurs DDL afin d'évaluer leurs réponses à l'exécution des procédures stockées. Enrichissement de l'exemple C avec des liens vers des exemples de déclencheurs DML AFTER mettant à jour plusieurs lignes et de déclencheurs DML INSTEAD OF INSERT. Enrichissement de l'exemple F avec un lien vers davantage d'exemples d'utilisation de EVENTDATA dans les déclencheurs DDL. Contenu modifié Mise à jour de l'exemple B, afin d'illustrer l'utilisation de la messagerie de base de données.
5 décembre 2005	Nouveau contenu : Nouvelle information indiquant qu'il est impossible de définir des déclencheurs DML sur des tables temporaires locales ou globales. Nouvelle information indiquant que 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.