CREATE TRIGGER (Transact-SQL)CREATE TRIGGER (Transact-SQL)

CETTE RUBRIQUE S’APPLIQUE À :ouiSQL Server (à partir de la version 2008)ouiAzure SQL DatabasenonAzure SQL Data Warehouse nonParallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Crée un déclencheur DML, DDL ou de connexion.Creates a DML, DDL, or logon trigger. 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.A trigger is a special type of stored procedure that automatically executes when an event occurs in the database server. Les déclencheurs DML s'exécutent lorsqu'un utilisateur essaie de modifier des données via un événement DML (Data Manipulation Language).DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. Les événements DML sont des instructions INSERT, UPDATE ou DELETE exécutées sur une table ou une vue.DML events are INSERT, UPDATE, or DELETE statements on a table or view. Ces déclencheurs s'activent au déclenchement d'un événement valide, que des lignes de table soient affectées ou non.These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected. Pour plus d'informations, consultez DML Triggers.For more information, see DML Triggers.

Les déclencheurs DDL s'exécutent en réponse à différents événements DDL (Data Definition Language).DDL triggers execute in response to a variety of data definition language (DDL) events. Ces événements correspondent essentiellement aux instructions Transact-SQLTransact-SQL CREATE, ALTER et DROP et à certaines procédures stockées système qui effectuent des opérations de type DDL.These events primarily correspond to Transact-SQLTransact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations. Les déclencheurs LOGON sont activés en réponse à l’événement LOGON qui est levé quand une session utilisateur est établie.Logon triggers fire in response to the LOGON event that is raised when a user's session is being established. Les déclencheurs peuvent être créés directement à partir d’instructions Transact-SQLTransact-SQL ou de méthodes d’assembly créées dans le CLR (Common Language Runtime) MicrosoftMicrosoft .NET Framework.NET Framework et chargées dans une instance de SQL ServerSQL Server.Triggers can be created directly from Transact-SQLTransact-SQL statements or from methods of assemblies that are created in the MicrosoftMicrosoft .NET Framework.NET Framework common language runtime (CLR) and uploaded to an instance of SQL ServerSQL Server. SQL ServerSQL Server permet de créer plusieurs déclencheurs pour toute instruction spécifique. allows for creating multiple triggers for any specific statement.

Important

Un code malveillant présent dans des déclencheurs peut s'exécuter sous des privilèges promus.Malicious code inside triggers can run under escalated privileges. Pour plus d’informations sur la façon de réduire cette menace, consultez Gérer la sécurité des déclencheurs.For more information on how to mitigate this threat, see Manage Trigger Security.

Note

L’intégration du CLR .NET Framework à SQL Server est décrite dans cette rubrique.The integration of .NET Framework CLR into SQL Server is discussed in this topic. L’intégration du CLR ne s’applique pas à Azure SQL Database.CLR integration does not apply to Azure SQL Database.

Icône de lien de rubrique Conventions de la syntaxe Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SyntaxeSyntax

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  

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  
-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a 
-- table (DML Trigger on memory-optimized tables)  

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 ]  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, 
-- REVOKE or UPDATE statement (DDL Trigger)  

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 ]  
-- Trigger on a LOGON event (Logon Trigger)  

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 ]  

SyntaxeSyntax

-- Azure SQL Database Syntax   
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  

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 ]  
-- Azure SQL Database Syntax  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, 
-- REVOKE, or UPDATE STATISTICS statement (DDL Trigger)   

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 ]  

ArgumentsArguments

OR ALTEROR ALTER
S’applique à : Azure SQL DatabaseSQL Database, SQL ServerSQL Server (depuis SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1).Applies to: Azure SQL DatabaseSQL Database, SQL ServerSQL Server (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1).

Modifie, de manière conditionnelle, le déclencheur uniquement s’il existe déjà.Conditionally alters the trigger only if it already exists.

schema_nameschema_name
Nom du schéma auquel appartient le déclencheur DML.Is the name of the schema to which a DML trigger belongs. 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.DML triggers are scoped to the schema of the table or view on which they are created. Vous ne pouvez pas spécifier schema_name pour des déclencheurs DDL ou de connexion.schema_name cannot be specified for DDL or logon triggers.

trigger_nametrigger_name
Nom du déclencheur.Is the name of the trigger. Un trigger_name doit respecter les règles applicables aux identificateurs, excepté que trigger_name ne peut pas commencer par # ou par ##.A trigger_name must comply with the rules for identifiers, except that trigger_name cannot start with # or ##.

table | viewtable | 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.Is the table or view on which the DML trigger is executed and is sometimes referred to as the trigger table or trigger view. La spécification du nom complet de la table ou de la vue est facultative.Specifying the fully qualified name of the table or view is optional. Une vue peut être référencée seulement par un déclencheur INSTEAD OF.A view can be referenced only by an INSTEAD OF trigger. Vous ne pouvez pas définir des déclencheurs DML sur des tables temporaires locales ou globales.DML triggers cannot be defined on local or global temporary tables.

DATABASEDATABASE
Applique l'étendue d'un déclencheur DDL à la base de données active.Applies the scope of a DDL trigger to the current database. 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.If specified, the trigger fires whenever event_type or event_group occurs in the current database.

ALL SERVERALL SERVER
S'applique à: SQL Server 2008SQL Server 2008 jusqu'à SQL Server 2017SQL Server 2017.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

Applique l'étendue d'un déclencheur DDL ou de connexion au serveur actif.Applies the scope of a DDL or logon trigger to the current server. 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.If specified, the trigger fires whenever event_type or event_group occurs anywhere in the current server.

WITH ENCRYPTIONWITH ENCRYPTION
S'applique à: SQL Server 2008SQL Server 2008 jusqu'à SQL Server 2017SQL Server 2017.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

Code le texte de l'instruction CREATE TRIGGER.Obfuscates the text of the CREATE TRIGGER statement. L'utilisation de l'argument WITH ENCRYPTION évite la publication du déclencheur dans le cadre de la réplication SQL ServerSQL Server.Using WITH ENCRYPTION prevents the trigger from being published as part of SQL ServerSQL Server replication. Il n'est pas possible de spécifier WITH ENCRYPTION pour les déclencheurs CLR.WITH ENCRYPTION cannot be specified for CLR triggers.

EXECUTE ASEXECUTE AS
Spécifie le contexte de sécurité dans lequel le déclencheur est exécuté.Specifies the security context under which the trigger is executed. Cet argument permet de contrôler le compte d'utilisateur que l'instance SQL ServerSQL Server utilise pour valider les autorisations sur n'importe quel objet de la base de données référencé par le déclencheur.Enables you to control which user account the instance of SQL ServerSQL Server uses to validate permissions on any database objects that are referenced by the trigger.

Cette option est obligatoire pour les déclencheurs sur les tables optimisées en mémoire.This option is required for triggers on memory-optimized tables.

Pour plus d’informations, consultez Clause EXECUTE AS (Transact-SQL).For more information, seeEXECUTE AS Clause (Transact-SQL).

NATIVE_COMPILATIONNATIVE_COMPILATION
Indique que le déclencheur est compilé en mode natif.Indicates that the trigger is natively compiled.

Cette option est obligatoire pour les déclencheurs sur les tables optimisées en mémoire.This option is required for triggers on memory-optimized tables.

SCHEMABINDINGSCHEMABINDING
Garantit que les tables référencées par un déclencheur ne peuvent pas être supprimées ou modifiées.Ensures that tables that are referenced by a trigger cannot be dropped or altered.

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.This option is required for triggers on memory-optimized tables and is not supported for triggers on traditional tables.

FOR | AFTERFOR | 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.AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. Toutes les actions référentielles en cascade et les vérifications de contraintes doivent être effectuées avec succès pour que ce déclencheur soit activé.All referential cascade actions and constraint checks also must succeed before this trigger fires.

AFTER est la valeur par défaut lorsque FOR est le seul mot clé spécifié.AFTER is the default when FOR is the only keyword specified.

Il n'est pas possible de définir des déclencheurs AFTER sur des vues.AFTER triggers cannot be defined on views.

INSTEAD OFINSTEAD 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 des instructions de déclenchement.Specifies that the DML trigger is executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. Il n'est pas possible de spécifier INSTEAD OF pour des déclencheurs DDL ou de connexion.INSTEAD OF cannot be specified for DDL or logon triggers.

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.At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. Vous pouvez cependant définir des vues sur des vues, où chaque vue a son propre déclencheur INSTEAD OF.However, you can define views on views where each view has its own INSTEAD OF trigger.

Les déclencheurs INSTEAD OF ne sont pas autorisés sur les vues pouvant être mises à jour qui utilisent l'option WITH CHECK OPTION.INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION. SQL ServerSQL Server signale une erreur lorsqu'un déclencheur INSTEAD OF est ajouté à une telle vue avec l'option WITH CHECK OPTION spécifiée. raises an error when an INSTEAD OF trigger is added to an updatable view WITH CHECK OPTION specified. L'utilisateur doit supprimer cette option à l'aide de l'instruction ALTER VIEW avant de définir le déclencheur INSTEAD OF.The user must remove that option by using ALTER VIEW before defining the INSTEAD OF trigger.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } { [ 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.Specifies the data modification statements that activate the DML trigger when it is tried against this table or view. Vous devez spécifier au moins une option.At least one option must be specified. Vous pouvez combiner dans la définition du déclencheur toutes ces options, dans n'importe quel ordre.Any combination of these options in any order is allowed in the trigger definition.

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.For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action 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.Similarly, the UPDATE option is not allowed on tables that have a referential relationship specifying a cascade action ON UPDATE.

WITH APPENDWITH APPEND
S'applique à: SQL Server 2008SQL Server 2008 jusqu'à SQL Server 2008 R2SQL Server 2008 R2.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2008 R2SQL Server 2008 R2.

Spécifie qu'il faut ajouter un déclencheur supplémentaire d'un type existant.Specifies that an additional trigger of an existing type should be added. 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.WITH APPEND cannot be used with INSTEAD OF triggers or if AFTER trigger is explicitly stated. Elle peut s'utiliser seulement lorsque FOR est spécifié (sans INSTEAD OF ou AFTER) pour des raisons de compatibilité descendante.WITH APPEND can be used only when FOR is specified, without INSTEAD OF or AFTER, for backward compatibility reasons. 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).WITH APPEND cannot be specified if EXTERNAL NAME is specified (that is, if the trigger is a CLR trigger).

event_typeevent_type
Nom de l'événement du langage Transact-SQLTransact-SQL qui, après l'exécution, provoque l'exécution d'un déclencheur DDL.Is the name of a Transact-SQLTransact-SQL language event that, after execution, causes a DDL trigger to fire. Les événements valides pour les déclencheurs DDL sont répertoriés dans Événements DDL.Valid events for DDL triggers are listed in DDL Events.

event_groupevent_group
Nom d'un groupe prédéfini d'événements du langage Transact-SQLTransact-SQL.Is the name of a predefined grouping of Transact-SQLTransact-SQL language events. Le déclencheur DDL est activé après l’exécution de n’importe quel événement du langage Transact-SQLTransact-SQL appartenant à event_group.The DDL trigger fires after execution of any Transact-SQLTransact-SQL language event that belongs to event_group. Les groupes d’événements valides pour les déclencheurs DDL sont répertoriés dans Groupes d’événements DDL.Valid event groups for DDL triggers are listed in DDL Event Groups.

Une fois l’exécution de CREATE TRIGGER terminée, event_group fait aussi office de macro en ajoutant les types d’événements qu’il couvre à la vue de catalogue sys.trigger_events.After the CREATE TRIGGER has finished running, event_group also acts as a macro by adding the event types it covers to the sys.trigger_events catalog view.

NOT FOR REPLICATIONNOT FOR REPLICATION
S'applique à: SQL Server 2008SQL Server 2008 jusqu'à SQL Server 2017SQL Server 2017.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

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.Indicates that the trigger should not be executed when a replication agent modifies the table that is involved in the trigger.

sql_statementsql_statement
Conditions et actions du déclencheur.Is the trigger conditions and actions. 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.Trigger conditions specify additional criteria that determine whether the tried DML, DDL, or logon events cause the trigger actions to be performed.

Les actions du déclencheur spécifiées dans les instructions Transact-SQLTransact-SQL prennent effet lors de la tentative d'opération.The trigger actions specified in the Transact-SQLTransact-SQL statements go into effect when the operation is tried.

Les déclencheurs peuvent comprendre n’importe quel type et n’importe quel nombre d’instructions Transact-SQLTransact-SQL. Il existe toutefois des exceptions.Triggers can include any number and type of Transact-SQLTransact-SQL statements, with exceptions. Pour plus d'informations, consultez la section Notes.For more information, see Remarks. 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.A trigger is designed to check or change data based on a data modification or definition statement; it should not return data to the user. Les instructions Transact-SQLTransact-SQL dans un déclencheur comportent souvent un langage de contrôle de flux.The Transact-SQLTransact-SQL statements in a trigger frequently include control-of-flow language.

Les déclencheurs DML utilisent les tables logiques (conceptuelles) supprimées ou insérées.DML triggers use the deleted and inserted logical (conceptual) tables. 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.They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is tried. 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.The deleted and inserted tables hold the old values or new values of the rows that may be changed by the user action. Par exemple, pour extraire toutes les valeurs de la table deleted, utilisez :For example, to retrieve all values in the deleted table, use:

SELECT * FROM deleted;  

Pour plus d’informations, consultez Utiliser les tables inserted et deleted.For more information, see Use the inserted and deleted Tables.

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).DDL and logon triggers capture information about the triggering event by using the EVENTDATA (Transact-SQL) function. Pour plus d’informations, consultez Utiliser la fonction EVENTDATA.For more information, see Use the EVENTDATA Function.

SQL ServerSQL Server autorise la mise à jour des colonnes text, ntext, ou image via le déclencheur INSTEAD OF sur des tables ou des vues. allows for the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.

Important

Les types de donnéesntext, text et image seront supprimés dans une version ultérieure de MicrosoftMicrosoft SQL ServerSQL Server.ntext, text, and image data types will be removed in a future version of MicrosoftMicrosoft SQL ServerSQL Server. Évitez d'utiliser ces types de données dans un nouveau développement. Prévoyez de modifier les applications qui les utilisent actuellement.Avoid using these data types in new development work, and plan to modify applications that currently use them. Utilisez plutôt les types de données nvarchar(max), varchar(max)et varbinary(max) .Use nvarchar(max), varchar(max), and varbinary(max) instead. Les déclencheurs AFTER et INSTEAD OF prennent tous les deux en charge les données varchar(MAX), nvarchar(MAX) et varbinary(MAX) dans les tables inserted et deleted.Both AFTER and INSTEAD OF triggers support varchar(MAX), nvarchar(MAX), and varbinary(MAX) data in the inserted and deleted tables.

Pour les déclencheurs sur les tables optimisées en mémoire, la seule instruction sql_statement autorisée au niveau supérieur est un bloc ATOMIC.For triggers on memory-optimized tables, the only sql_statement allowed at the top level is an ATOMIC block. Le code T-SQL autorisé dans le bloc ATOMIC est limité par le code T-SQL autorisé dans les procédures natives.The T-SQL allowed inside the ATOMIC block is limited by the T-SQL allowed inside native procs.

< method_specifier > S’applique à : SQL Server 2008SQL Server 2008 à SQL Server 2017SQL Server 2017.< method_specifier > Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

Pour un déclencheur CLR, spécifie la méthode de liaison d'un assembly avec le déclencheur.For a CLR trigger, specifies the method of an assembly to bind with the trigger. La méthode ne doit prendre aucun argument et retourner une valeur vide.The method must take no arguments and return void. class_name doit être un identificateur SQL ServerSQL Server valide et doit exister comme classe dans l’assembly avec une visibilité de l’assembly.class_name must be a valid SQL ServerSQL Server identifier and must exist as a class in the assembly with assembly visibility. 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 (" ").If the class has a namespace-qualified name that uses '.' to separate namespace parts, the class name must be delimited by using [ ] or " " delimiters. La classe ne peut pas être imbriquée.The class cannot be a nested class.

Note

Par défaut, la possibilité de SQL ServerSQL Server d'exécuter du code CLR est désactivée.By default, the ability of SQL ServerSQL Server to run CLR code is off. Vous pouvez créer, modifier et supprimer des objets de base de données qui font référence à des modules de code managé. Cependant, ces références ne s’exécutent dans une instance de SQL ServerSQL Server que si l’option clr enabled est activée à l’aide de sp_configure.You can create, modify, and drop database objects that reference managed code modules, but these references will not execute in an instance of SQL ServerSQL Server unless the clr enabled Option is enabled by using sp_configure.

Remarques sur les déclencheurs DMLRemarks for DML Triggers

Les déclencheurs DML s'utilisent souvent pour imposer des règles de gestion et l'intégrité des données.DML triggers are frequently used for enforcing business rules and data integrity. SQL ServerSQL Server fournit l'intégrité référentielle déclarative (DRI) via des instructions ALTER TABLE et CREATE TABLE. provides declarative referential integrity (DRI) through the ALTER TABLE and CREATE TABLE statements. Cependant, la fonctionnalité DRI ne gère pas l'intégrité référentielle entre bases de données.However, DRI does not provide cross-database referential integrity. 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.Referential integrity refers to the rules about the relationships between the primary and foreign keys of 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.To enforce referential integrity, use the PRIMARY KEY and FOREIGN KEY constraints in ALTER TABLE and 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.If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and before the AFTER trigger execution. 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é.If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not fired.

Vous pouvez spécifier le premier et le dernier déclencheur AFTER à exécuter sur une table à l'aide de sp_settriggerorder.The first and last AFTER triggers to be executed on a table can be specified by using 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.Only one first and one last AFTER trigger for each INSERT, UPDATE, and DELETE operation can be specified on a table. S'il y a d'autres déclencheurs AFTER sur la même table, ils sont exécutés de manière aléatoire.If there are other AFTER triggers on the same table, they are randomly executed.

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.If an ALTER TRIGGER statement changes a first or last trigger, the first or last attribute set on the modified trigger is dropped, and the order value must be reset by using 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.An AFTER trigger is executed only after the triggering SQL statement has executed successfully. 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é.This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted. Un déclencheur AFTER ne déclenche pas de manière récursive un déclencheur INSTEAD OF sur la même table.An AFTER trigger will not recursively fire an INSTEAD OF trigger on the same table.

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.If an INSTEAD OF trigger defined on a table executes a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called recursively. 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.Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. 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.For example, if a trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. 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.The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the 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.If an INSTEAD OF trigger defined on a view executes a statement against the view that would ordinarily fire the INSTEAD OF trigger again, it is not called recursively. Au lieu de cela, l'instruction est résolue sous forme de modifications apportées aux tables de base sous-jacentes de la vue.Instead, the statement is resolved as modifications against the base tables underlying the view. Dans ce cas, la définition de la vue doit respecter toutes les restrictions applicables à une vue pouvant être mise à jour.In this case, the view definition must meet all the restrictions for an updatable view. Pour obtenir une définition des vues pouvant être mises à jour, consultez Modifier les données par l’intermédiaire d’une vue.For a definition of updatable views, see Modify Data Through a View.

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.For example, if a trigger is defined as an INSTEAD OF UPDATE trigger for a view, and the trigger executes an UPDATE statement referencing the same view, the UPDATE statement executed by the INSTEAD OF trigger does not call the trigger again. Elle est appliquée à la vue comme si celle-ci ne comportait pas de déclencheur INSTEAD OF.The UPDATE executed by the trigger is processed against the view as if the view did not have an INSTEAD OF trigger. Les colonnes modifiées par l'instruction UPDATE doivent être résolues en une seule table de base.The columns changed by the UPDATE must be resolved to a single base table. 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.Each modification to an underlying base table starts the chain of applying constraints and firing AFTER triggers defined for the table.

Test des actions UPDATE ou INSERT sur des colonnes spécifiquesTesting for UPDATE or INSERT Actions to Specific Columns

Vous pouvez créer un déclencheur Transact-SQLTransact-SQL qui exécute certaines actions en fonction de modifications des instructions UPDATE ou INSERT sur des colonnes particulières.You can design a Transact-SQLTransact-SQL trigger to perform certain actions based on UPDATE or INSERT modifications to specific columns. Pour cela, utilisez UPDATE() ou COLUMNS_UPDATED dans le corps du déclencheur.Use UPDATE() or COLUMNS_UPDATED in the body of the trigger for this purpose. UPDATE() teste les tentatives UPDATE ou INSERT sur une colonne.UPDATE() tests for UPDATE or INSERT attempts on one column. 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.COLUMNS_UPDATED tests for UPDATE or INSERT actions that are performed on multiple columns and returns a bit pattern that indicates which columns were inserted or updated.

Limitations des déclencheursTrigger Limitations

CREATE TRIGGER doit être la première instruction du traitement et ne peut s'appliquer qu'à une seule table.CREATE TRIGGER must be the first statement in the batch and can apply to only one 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.A trigger is created only in the current database; however, a trigger can reference objects outside the current database.

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.If the trigger schema name is specified to qualify the trigger, qualify the table name in the same way.

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.The same trigger action can be defined for more than one user action (for example, INSERT and UPDATE) in the same CREATE TRIGGER statement.

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.INSTEAD OF DELETE/UPDATE triggers cannot be defined on a table that has a foreign key with a cascade on DELETE/UPDATE action defined.

Vous pouvez spécifier n'importe quelle instruction SET dans le déclencheur.Any SET statement can be specified inside a trigger. L'option SET sélectionnée reste active pendant l'exécution du déclencheur, puis retrouve sa valeur d'origine.The SET option selected remains in effect during the execution of the trigger and then reverts to its former setting.

Lorsqu'un déclencheur est activé, les résultats sont retournés à l'application appelante, comme pour les procédures stockées.When a trigger fires, results are returned to the calling application, just like with stored procedures. 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.To prevent having results returned to an application because of a trigger firing, do not include either SELECT statements that return results or statements that perform variable assignment in a trigger. 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.A trigger that includes either SELECT statements that return results to the user or statements that perform variable assignment requires special handling; these returned results would have to be written into every application in which modifications to the trigger table are allowed. 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 jeu de résultats.If variable assignment must occur in a trigger, use a SET NOCOUNT statement at the start of the trigger to prevent the return of any result sets.

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.Although a TRUNCATE TABLE statement is in effect a DELETE statement, it does not activate a trigger because the operation does not log individual row deletions. 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.However, only those users with permissions to execute a TRUNCATE TABLE statement need be concerned about inadvertently circumventing a DELETE trigger this way.

L'instruction WRITETEXT, enregistrée ou non dans le journal, n'active pas un déclencheur.The WRITETEXT statement, whether logged or unlogged, does not activate a trigger.

Les instructions Transact-SQLTransact-SQL suivantes ne sont pas autorisées dans un déclencheur DML :The following Transact-SQLTransact-SQL statements are not allowed in a DML trigger:

ALTER DATABASEALTER DATABASE CREATE DATABASECREATE DATABASE DROP DATABASEDROP DATABASE
RESTORE DATABASERESTORE DATABASE RESTORE LOGRESTORE LOG RECONFIGURERECONFIGURE

De plus, les instructions Transact-SQLTransact-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.Additionally, the following Transact-SQLTransact-SQL statements are not allowed inside the body of a DML trigger when it is used against the table or view that is the target of the triggering action.

CREATE INDEX (y compris CREATE SPATIAL INDEX et CREATE XML INDEX)CREATE INDEX (including CREATE SPATIAL INDEX and CREATE XML INDEX) ALTER INDEXALTER INDEX DROP INDEXDROP INDEX
DBCC DBREINDEXDBCC DBREINDEX ALTER PARTITION FUNCTIONALTER PARTITION FUNCTION DROP TABLEDROP TABLE
ALTER TABLE quand elle est utilisée pour effectuer les actions suivantes :ALTER TABLE when used to do the following:

ajout, modification ou suppression de colonnes ;Add, modify, or drop columns.

changement de partitions ;Switch partitions.

ajout ou suppression de contraintes PRIMARY KEY ou UNIQUE.Add or drop PRIMARY KEY or UNIQUE constraints.

Note

SQL ServerSQL 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.Because SQL ServerSQL Server does not support user-defined triggers on system tables, we recommend that you do not create user-defined triggers on system tables.

Optimisation des déclencheurs DMLOptimizing DML Triggers

Les déclencheurs fonctionnent dans des transactions (implicites ou autres). Quand ils sont ouverts, ils verrouillent les ressources.Triggers work in transactions (implied, or otherwise) and while they are open, they lock resources. Le verrou reste en place jusqu’à ce que la transaction soit confirmée (avec COMMIT) ou rejetée (avec ROLLBACK).The lock will remain in place until the transaction is confirmed (with COMMIT) or rejected (with a ROLLBACK). Plus la durée d’exécution d’un déclencheur est longue, plus le risque de blocage d’un autre processus augmente.The longer a trigger runs, the higher the probability that another process will be blocked. Vous devez donc essayer d’écrire les déclencheurs de façon à réduire leur durée d’exécution.Therefore, triggers should be written in a way to decrease their duration whenever possible. Une façon d’y parvenir est de libérer un déclencheur quand une instruction DML ne change aucune ligne.One way to achieve this is to release a trigger when a DML statement changes 0 rows.

Pour libérer un déclencheur dans une commande qui ne change aucune ligne, utilisez la variable système ROWCOUNT_BIG.To release the trigger for a command that that does not change any rows, employ the system variable ROWCOUNT_BIG.

L’extrait de code T-SQL suivant produit le résultat attendu. Il doit être présent au début de chaque déclencheur DML :The following T-SQL code snippet will achieve this, and should be present at the beginning of each DML trigger:

IF (@@ROWCOUNT_BIG = 0)
RETURN;

Remarques sur les déclencheurs DDLRemarks for DDL Triggers

Les déclencheurs DDL, tout comme les déclencheurs standard, exécutent des procédures stockées en réponse à un événement.DDL triggers, like standard triggers, execute stored procedures in response to an event. 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.But unlike standard triggers, they do not execute in response to UPDATE, INSERT, or DELETE statements on a table or view. Au lieu de cela, ils s'exécutent essentiellement en réponse aux instructions DDL (Data Definition Language).Instead, they primarily execute in response to data definition language (DDL) statements. Il s'agit des instructions CREATE, ALTER, DROP, GRANT, DENY, REVOKE et UPDATE STATISTICS.These include CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements. Certaines procédures stockées système qui effectuent des opérations de type DDL peuvent également activer des déclencheurs DDL.Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.

Important

Testez vos déclencheurs DDL afin de déterminer leurs réponses à l'exécution des procédures stockées système.Test your DDL triggers to determine their responses to system stored procedure execution. 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.For example, the CREATE TYPE statement and the sp_addtype and sp_rename stored procedures will fire a DDL trigger that is created on a CREATE_TYPE event.

Pour plus d’informations sur les déclencheurs DDL, consultez Déclencheurs DDL.For more information about DDL triggers, see DDL Triggers.

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.DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures.

À la différence des déclencheurs DML, le champ d'action des déclencheurs DDL ne correspond pas aux schémas.Unlike DML triggers, DDL triggers are not scoped to schemas. 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.Therefore, functions such as OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY, and OBJECTPROPERTYEX cannot be used for querying metadata about DDL triggers. Utilisez plutôt les affichages catalogue.Use the catalog views instead. Pour plus d’informations, consultez Obtenir des informations sur les déclencheurs DDL.For more information, see Get Information About DDL Triggers.

Note

Les déclencheurs DDL avec étendue au niveau serveur figurent dans le dossier Déclencheurs de l’Explorateur d’objets SQL Server Management StudioSQL Server Management Studio.Server-scoped DDL triggers appear in the SQL Server Management StudioSQL Server Management Studio Object Explorer in the Triggers folder. Ce dossier se trouve dans le dossier Objets serveur .This folder is located under the Server Objects folder. Les déclencheurs DDL délimités à la base de données figurent dans le dossier Déclencheurs de base de données.Database-scoped DDL Triggers appear in the Database Triggers folder. Ce dossier se trouve dans le dossier Programmabilité de la base de données correspondante.This folder is located under the Programmability folder of the corresponding database.

Déclencheurs de connexionLogon Triggers

Les déclencheurs de connexion exécutent des procédures stockées en réponse à un événement LOGON.Logon triggers execute stored procedures in response to a LOGON event. Cet événement est déclenché lorsqu'une session utilisateur est établie avec une instance de SQL ServerSQL Server.This event is raised when a user session is established with an instance of SQL ServerSQL 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.Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. 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 des erreurs SQL ServerSQL Server .Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL ServerSQL Server error log. Pour plus d’informations, consultez Déclencheurs de connexion.For more information, see Logon Triggers.

Les déclencheurs de connexion ne sont pas activés si l'authentification échoue.Logon triggers do not fire if authentication fails.

Les transactions distribuées ne sont pas compatibles avec un déclencheur de connexion.Distributed transactions are not supported in a logon trigger. Le système affiche le message d'erreur 3969 lorsqu'un déclencheur de connexion contenant une transaction distribuée est activé.Error 3969 is returned when a logon trigger containing a distributed transaction is fired.

Désactivation d'un déclencheur de connexionDisabling a Logon Trigger

Un déclencheur de connexion peut empêcher les connexions au Moteur de base de donnéesDatabase Engine pour tous les utilisateurs, notamment les membres du rôle serveur fixe sysadmin .A logon trigger can effectively prevent successful connections to the Moteur de base de donnéesDatabase Engine for all users, including members of the sysadmin fixed server role. Quand un déclencheur de connexion 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éesDatabase Engine en mode de configuration minimale (-f).When a logon trigger is preventing connections, members of the sysadmin fixed server role can connect by using the dedicated administrator connection, or by starting the Moteur de base de donnéesDatabase Engine in minimal configuration mode (-f). Pour plus d’informations, consultez Options de démarrage du service moteur de base de données.For more information, see Database Engine Service Startup Options.

Considérations générales sur les déclencheursGeneral Trigger Considerations

Retour de résultatsReturning Results

Cette possibilité d'obtenir des résultats via des déclencheurs sera supprimée dans les prochaines versions de Microsoft SQL Server.The ability to return results from triggers will be removed in a future version of SQL Server. Les déclencheurs qui renvoient des jeux de résultats peuvent provoquer un comportement inattendu des applications qui ne sont pas conçues pour interagir avec eux.Triggers that return result sets may cause unexpected behavior in applications that are not designed to work with them. É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.Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do this. 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.To prevent triggers from returning result sets, set the disallow results from triggers option to 1.

Les déclencheurs de connexion interdisent toujours le renvoi de jeux de résultats et ce comportement n'est pas configurable.,Logon triggers always disallow results sets to be returned and this behavior is not 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.If a logon trigger does generate a result set, the trigger fails to execute and the login attempt that fired the trigger is denied.

Déclencheurs multiplesMultiple Triggers

SQL ServerSQL Server autorise la création de plusieurs déclencheurs pour chaque événement DML, DDL ou LOGON. allows for multiple triggers to be created for each DML, DDL, or LOGON event. 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éé.For example, if CREATE TRIGGER FOR UPDATE is executed for a table that already has an UPDATE trigger, an additional update trigger is created. Dans les versions antérieures de SQL ServerSQL 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.In earlier versions of SQL ServerSQL Server, only one trigger for each INSERT, UPDATE, or DELETE data modification event is allowed for each table.

Déclencheurs récursifsRecursive Triggers

SQL ServerSQL Server permet l'appel récursif de déclencheurs lorsque le paramètre RECURSIVE_TRIGGERS est activé au moyen de l'instruction ALTER DATABASE. also allows for recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE.

Les déclencheurs récursifs permettent les types de récurrence suivants :Recursive triggers enable the following types of recursion to occur:

  • Récurrence indirecte.Indirect recursion

    Avec la récurrence indirecte, une application met à jour la table T1.With indirect recursion, an application updates table T1. Cela active le déclencheur TR1, avec pour conséquence la mise à jour de la table T2.This fires trigger TR1, updating table T2. Dans ce scénario, le déclencheur T2 est activé et met à jour la table T1.In this scenario, trigger T2 then fires and updates table T1.

  • Récurrence directe.Direct recursion

    Avec la récurrence directe, l'application met à jour la table T1.With direct recursion, the application updates table T1. Cela active le déclencheur TR1, avec pour conséquence la mise à jour de la table T1.This fires trigger TR1, updating table T1. La table T1 ayant été mise à jour, le déclencheur TR1 est réactivé, etc.Because table T1 was updated, trigger TR1 fires again, and so on.

    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.The following example uses both indirect and direct trigger recursion Assume that two update triggers, TR1 and TR2, are defined on table T1. Le déclencheur TR1 met à jour la table T1 de manière récursive.Trigger TR1 updates table T1 recursively. Une instruction UPDATE exécute chaque déclencheur TR1 et TR2 une fois.An UPDATE statement executes each TR1 and TR2 one time. De plus, l'exécution du déclencheur TR1 entraîne l'exécution de TR1 (de manière récursive) et de TR2.Additionally, the execution of TR1 triggers the execution of TR1 (recursively) and 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.The inserted and deleted tables for a specific trigger contain rows that correspond only to the UPDATE statement that invoked the trigger.

Note

Le comportement précédent se produit uniquement si le paramètre RECURSIVE_TRIGGERS est activé au moyen de l'instruction ALTER DATABASE.The previous behavior occurs only if the RECURSIVE_TRIGGERS setting is enabled by using 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.There is no defined order in which multiple triggers defined for a specific event are executed. Chaque déclencheur doit être indépendant.Each trigger should be self-contained.

La désactivation du paramètre RECURSIVE_TRIGGERS empêche uniquement les récurrences directes.Disabling the RECURSIVE_TRIGGERS setting only prevents direct recursions. Pour désactiver également la récurrence indirecte, affectez la valeur 0 à l'option de serveur nested triggers à l'aide de sp_configure.To disable indirect recursion also, set the nested triggers server option to 0 by using 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é.If any one of the triggers performs a ROLLBACK TRANSACTION, regardless of the nesting level, no more triggers are executed.

Déclencheurs imbriquésNested Triggers

Les déclencheurs peuvent compter jusqu'à 32 niveaux d'imbrication.Triggers can be nested to a maximum of 32 levels. 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.If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. 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é.If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled. Lorsqu'un déclencheur Transact-SQLTransact-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.When a Transact-SQLTransact-SQL trigger executes managed code by referencing a CLR routine, type, or aggregate, this reference counts as one level against the 32-level nesting limit. Les méthodes appelées à partir du code managé n'entrent pas en compte dans cette limiteMethods invoked from within managed code do not count against this limit

Pour désactiver les déclencheurs imbriqués, attribuez la valeur 0 (off) à l'option nested triggers de sp_configure.To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). La configuration par défaut permet les déclencheurs imbriqués.The default configuration allows for nested triggers. 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.If nested triggers are off, recursive triggers are also disabled, regardless of the RECURSIVE_TRIGGERS setting set by using ALTER DATABASE.

Le premier déclencheur AFTER imbriqué dans un déclencheur INSTEAD OF se déclenche même si l’option de configuration de serveur nested triggers est définie sur 0.The first AFTER trigger nested inside an INSTEAD OF trigger fires even if the nested triggers server configuration option is set to 0. Toutefois, les déclencheurs AFTER suivants ne se déclenchent pas avec ce paramètre.However, under this setting, later AFTER triggers do not fire. Nous vous recommandons de contrôlez les déclencheurs imbriqués de vos applications afin de déterminer si ces applications respectent vos règles métier relatives à ce nouveau comportement quand l’option de configuration de serveur nested triggers est définie sur 0, puis effectuez les modifications appropriées.We recommend that you review your applications for nested triggers to determine whether the applications comply with your business rules with regard to this behavior when the nested triggers server configuration option is set to 0, and then make appropriate modifications.

Résolution de noms différéeDeferred Name Resolution

SQL ServerSQL Server permet aux procédures stockées, aux déclencheurs et aux lots d'instructions Transact-SQLTransact-SQL de faire référence à des tables qui n'existent pas au moment de la compilation. allows for Transact-SQLTransact-SQL stored procedures, triggers, and batches to refer to tables that do not exist at compile time. Cette fonction s'appelle la résolution différée des noms.This ability is called deferred name resolution.

PermissionsPermissions

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éé.To create a DML trigger requires ALTER permission on the table or view on which the trigger is being created.

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.To create a DDL trigger with server scope (ON ALL SERVER) or a logon trigger requires CONTROL SERVER permission on the server. 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.To create a DDL trigger with database scope (ON DATABASE) requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.

ExemplesExamples

A.A. Utilisation d'un déclencheur DML avec un message de rappelUsing a DML trigger with a reminder message

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 de la base de données AdventureWorks2012AdventureWorks2012.The following DML trigger prints a message to the client when anyone tries to add or change data in the Customer table in the AdventureWorks2012AdventureWorks2012 database.

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

B.B. Utilisation d'un déclencheur DML avec un message de rappel par courrier électroniqueUsing a DML trigger with a reminder e-mail message

L'exemple suivant envoie un message électronique à une personne spécifiée (MaryM) lorsque la table Customer est modifiée.The following example sends an e-mail message to a specified person (MaryM) when the Customer table changes.

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

C.C. Utilisation d'un déclencheur DML AFTER pour imposer une règle de gestion entre les tables PurchaseOrderHeader et VendorUsing a DML AFTER trigger to enforce a business rule between the PurchaseOrderHeader and Vendor tables

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.Because CHECK constraints can reference only the columns on which the column-level or table-level constraint is defined, any cross-table constraints (in this case, business rules) must be defined as triggers.

L'exemple suivant crée un déclencheur DML dans la base de données AdventureWorks2012.The following example creates a DML trigger in the AdventureWorks2012 database. 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.This trigger checks to make sure the credit rating for the vendor is good (not 5) when an attempt is made to insert a new purchase order into the PurchaseOrderHeader table. Pour obtenir les informations de conditions de crédit du fournisseur, la table Vendor doit être référencée.To obtain the credit rating of the vendor, the Vendor table must be referenced. Si les conditions de crédit sont trop faibles, un message s'affiche et l'insertion n'a pas lieu.If the credit rating is too low, a message is displayed and the insertion does not execute.

-- 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 *  
           FROM Purchasing.PurchaseOrderHeader AS p   
           JOIN inserted AS i   
           ON p.PurchaseOrderID = i.PurchaseOrderID   
           JOIN Purchasing.Vendor AS v   
           ON v.BusinessEntityID = p.VendorID  
           WHERE v.CreditRating = 5  
          )  
BEGIN  
RAISERROR ('A vendor''s credit rating is too low to accept new  
purchase orders.', 16, 1);  
ROLLBACK TRANSACTION;  
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.D. Utilisation d'un déclencheur DDL avec une étendue de base de donnéesUsing a database-scoped DDL trigger

L'exemple suivant utilise un déclencheur DDL pour empêcher la suppression d'un synonyme dans une base de données.The following example uses a DDL trigger to prevent any synonym in a database from being dropped.

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

E.E. Utilisation d'un déclencheur DDL avec une étendue de serveurUsing a server-scoped DDL trigger

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-SQLTransact-SQL correspondante.The following example uses a DDL trigger to print a message if any CREATE DATABASE event occurs on the current server instance, and uses the EVENTDATA function to retrieve the text of the corresponding Transact-SQLTransact-SQL statement. Pour obtenir d’autres exemples d’utilisation d’EVENTDATA dans des déclencheurs DDL, consultez Utiliser la fonction EVENTDATA.For more examples that use EVENTDATA in DDL triggers, see Use the EVENTDATA Function.

S'applique à: SQL Server 2008SQL Server 2008 jusqu'à SQL Server 2017SQL Server 2017.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

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.F. Utilisation d'un déclencheur de connexionUsing a logon trigger

L’exemple de déclencheur de connexion suivant refuse une tentative de connexion à SQL ServerSQL Server en tant que membre de la connexion login_test si trois sessions utilisateur sont déjà en cours d’exécution sous cette connexion.The following logon trigger example denies an attempt to log in to SQL ServerSQL Server as a member of the login_test login if there are already three user sessions running under that login.

S'applique à: SQL Server 2008SQL Server 2008 jusqu'à SQL Server 2017SQL Server 2017.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

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;  

G.G. Affichage des événements qui provoquent l'activation d'un déclencheurViewing the events that cause a trigger to fire

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-SQLTransact-SQL qui provoquent l'activation du déclencheur safety.The following example queries the sys.triggers and sys.trigger_events catalog views to determine which Transact-SQLTransact-SQL language events cause trigger safety to fire. safety est créé dans l'exemple précédent.safety is created in the previous example.

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 aussiSee Also

ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
ALTER TRIGGER (Transact-SQL) ALTER TRIGGER (Transact-SQL)
COLUMNS_UPDATED (Transact-SQL) COLUMNS_UPDATED (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
DROP TRIGGER (Transact-SQL) DROP TRIGGER (Transact-SQL)
ENABLE TRIGGER (Transact-SQL) ENABLE TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL) DISABLE TRIGGER (Transact-SQL)
TRIGGER_NESTLEVEL (Transact-SQL) TRIGGER_NESTLEVEL (Transact-SQL)
EVENTDATA (Transact-SQL) EVENTDATA (Transact-SQL)
sys.dm_sql_referenced_entities (Transact-SQL) sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL) sys.dm_sql_referencing_entities (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL) sys.sql_expression_dependencies (Transact-SQL)
sp_help (Transact-SQL) sp_help (Transact-SQL)
sp_helptrigger (Transact-SQL) sp_helptrigger (Transact-SQL)
sp_helptext (Transact-SQL) sp_helptext (Transact-SQL)
sp_rename (Transact-SQL) sp_rename (Transact-SQL)
sp_settriggerorder (Transact-SQL) sp_settriggerorder (Transact-SQL)
UPDATE() (Transact-SQL) UPDATE() (Transact-SQL)
Obtenir des informations sur les déclencheurs DML Get Information About DML Triggers
Obtenir des informations sur les déclencheurs DDL Get Information About DDL Triggers
sys.triggers (Transact-SQL) sys.triggers (Transact-SQL)
sys.trigger_events (Transact-SQL) sys.trigger_events (Transact-SQL)
sys.sql_modules (Transact-SQL) sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL) sys.assembly_modules (Transact-SQL)
sys.server_triggers (Transact-SQL) sys.server_triggers (Transact-SQL)
sys.server_trigger_events (Transact-SQL) sys.server_trigger_events (Transact-SQL)
sys.server_sql_modules (Transact-SQL) sys.server_sql_modules (Transact-SQL)
sys.server_assembly_modules (Transact-SQL)sys.server_assembly_modules (Transact-SQL)