CREATE TRIGGER (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 associé au déclencheur DML. Les déclencheurs DML sont limités au schéma de la table ou à la vue sur laquelle ils sont créés. schema_name ne peut pas être spécifié pour des connexions DDL ou de connexion.

• trigger_name
  Nom du déclencheur. Un trigger_name doit respecter les règles propres aux identificateurs, si ce n'est que trigger_name ne peut pas commencer par # ni ##.

• table | view
  Table ou vue sur laquelle le déclencheur DML est exécuté. Elle est parfois appelée table de déclencheur ou vue de déclencheur. La spécification du nom complet de la table ou de la vue est facultative. Une vue peut être référencée seulement par un déclencheur INSTEAD OF. Vous ne pouvez pas définir des déclencheurs DML sur des tables temporaires locales ou globales.

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

• ALL SERVER
  Applique l'étendue d'un déclencheur DDL ou de connexion au serveur actif. S'il est spécifié, le déclencheur est activé lorsque event_type ou event_group se produit à un endroit quelconque dans le serveur actif.

• WITH ENCRYPTION
  Code le texte de l'instruction CREATE TRIGGER. L'utilisation de l'argument WITH ENCRYPTION évite la publication du déclencheur dans le cadre de la réplication SQL Server. Il n'est pas possible de spécifier WITH ENCRYPTION pour les déclencheurs CLR.

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

  Pour plus d'informations, consultez Clause EXECUTE AS (Transact-SQL).

• FOR | AFTER
  AFTER spécifie que le déclencheur DML est exécuté seulement lorsque toutes les opérations spécifiées dans l'instruction SQL de déclenchement ont été exécutées correctement. Toutes les actions d'intégrité référentielle en cascade et les vérifications des contraintes doivent être effectuées avec succès pour que ce déclencheur s'exécute.

  AFTER est la valeur par défaut lorsque FOR est le seul mot clé spécifié.

  Il n'est pas possible de définir des déclencheurs AFTER sur des vues.

• INSTEAD OF
  Spécifie que le déclencheur DML est exécuté à la place de l'instruction SQL de déclenchement, remplaçant ainsi les actions de celle-ci. Il n'est pas possible de spécifier INSTEAD OF pour des déclencheurs DDL ou de connexion.

  Il est possible de définir au plus un déclencheur INSTEAD OF par instruction INSERT, UPDATE ou DELETE sur une table ou une vue. Vous pouvez cependant définir des vues sur des vues, où chaque vue a son propre déclencheur INSTEAD OF.

  Les déclencheurs INSTEAD OF ne sont pas autorisés sur les vues pouvant être mises à jour qui utilisent l'option WITH CHECK OPTION. SQL Server signale une erreur lorsqu'un déclencheur INSTEAD OF est ajouté à une telle vue avec l'option WITH CHECK OPTION spécifiée. L'utilisateur doit supprimer cette option à l'aide de l'instruction ALTER VIEW avant de définir le déclencheur INSTEAD OF.

• { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
  Spécifie les instructions de modification des données qui activent le déclencheur DML lorsqu'une tentative a lieu pour l'appliquer à cette table ou à cette vue. Vous devez spécifier au moins une option. Vous pouvez combiner dans la définition du déclencheur toutes ces options, dans n'importe quel ordre.

  Dans le cas des déclencheurs INSTEAD OF, l'option DELETE n'est pas autorisée sur les tables dont la relation référentielle spécifie une action en cascade ON DELETE. De même, l'option UPDATE n'est pas autorisée sur les tables dont la relation référentielle spécifie une action en cascade ON UPDATE.

• event_type
  Nom de l'événement du langage Transact-SQL qui, après l'exécution, provoque l'exécution d'un déclencheur DDL. Les événements valides pour les déclencheurs DDL sont répertoriés dans Événements DDL.

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

  Une fois terminée l'exécution de l'instruction CREATE TRIGGER, event_group se comporte également comme une macro en ajoutant au catalogue sys.trigger_events les types d'événements traités.

• WITH APPEND
  Spécifie qu'il faut ajouter un déclencheur supplémentaire d'un type existant. La clause WITH APPEND ne peut pas s'utiliser avec des déclencheurs INSTEAD OF ou si le déclencheur AFTER est stipulé de manière explicite. Elle peut s'utiliser seulement lorsque FOR est spécifié (sans INSTEAD OF ou AFTER) pour des raisons de compatibilité descendante. Vous ne pouvez pas spécifier WITH APPEND si EXTERNAL NAME est spécifié (c'est-à-dire si le déclencheur est un déclencheur CLR).

  Important

  WITH APPEND sera supprimé dans la version suivante de MicrosoftSQL Server. N'utilisez pas WITH APPEND dans un nouveau travail de développement et envisagez de modifier les applications qui s'en servent actuellement.

• NOT FOR REPLICATION
  Indique que le déclencheur ne doit pas être exécuté lorsqu'un Agent de réplication modifie la table impliquée dans le déclencheur. Pour plus d'informations, consultez Contrôle des contraintes, des identités et des déclencheurs avec l'option NOT FOR REPLICATION.

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

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

  Les déclencheurs peuvent comprendre n'importe quel type et n'importe quel nombre d'instructions Transact-SQL. Il existe des exceptions. Pour plus d'informations, consultez la section « Remarques ». Un déclencheur sert à vérifier ou à modifier des données suite à une instruction de modification ou de définition. Il ne doit pas retourner des données à l'utilisateur. Les instructions Transact-SQL dans un déclencheur comportent souvent une séquence de langage de contrôle de flux.

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

  SELECT *
  FROM 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 fonction EVENTDATA (Transact-SQL). Pour plus d'informations, cliquez sur le lien Utilisation de la fonction EVENTDATA.

  SQL Server permet d'actualiser les colonnes text, ntext ou image via le déclencheur INSTEAD OF dans les tables ou dans les vues.

  Important

  Les types de données ntext, text et image seront supprimés dans une version future de MicrosoftSQL Server. Évitez d'utiliser ces types de données dans un nouveau développement. Prévoyez de modifier les applications qui les utilisent actuellement. Au lieu de cela, utilisez les types de données nvarchar(max), varchar(max) et varbinary(max). Les déclencheurs AFTER et INSTEAD OF sont compatibles avec les données varchar(MAX), nvarchar(MAX) et varbinary(MAX) dans les tables insérées ou supprimées.

• < method_specifier >
  Pour un déclencheur CLR, spécifie la méthode de liaison d'un assembly avec le déclencheur. La méthode ne doit prendre aucun argument et retourner une valeur vide. class_name doit être un identificateur SQL Server valide et exister en tant que classe dans l'assembly avec une visibilité d'assembly. Si la classe possède un nom qualifié par un espace de noms utilisant un point '.' pour séparer les parties de l'espace de noms, le nom de la classe doit être délimité par des crochets ([ ]) ou des guillemets doubles (" "). La classe ne peut pas être imbriquée.

  [!REMARQUE]

  Par défaut, la possibilité de SQL Server d'exécuter du code CLR est désactivée. Vous pouvez créer, modifier et supprimer des objets d'une base de données qui font référence à des modules de code managé. Cependant, ces références ne s'exécuteront pas dans une instance de SQL Server à moins que l'option clr enabled soit activée à l'aide de sp_configure.

Notes

Déclencheurs DML

Déclencheurs DDL

Déclencheurs de connexion

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

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

USE AdventureWorks;
GO
IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL
   DROP TRIGGER Sales.reminder1;
GO
-- This trigger raises a message whenever a row is inserted or modified in Sales.Customer.

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

B. Utilisation d'un déclencheur DML avec un message de rappel par courrier électronique

USE AdventureWorks;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
    DROP TRIGGER Sales.reminder2;
GO
-- This trigger sends an e-mail message to a specified account whenever
-- a row is inserted, updated or deleted from the Sales.Customer table.

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

C. Utilisation d'un déclencheur DML AFTER pour imposer une règle de gestion entre les tables PurchaseOrderHeader et Vendor

IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).

CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
IF EXISTS (SELECT *
           FROM Purchasing.PurchaseOrderHeader p 
           JOIN inserted AS i 
           ON p.PurchaseOrderID = i.PurchaseOrderID 
           JOIN Purchasing.Vendor AS v 
           ON v.VendorID = p.VendorID
           WHERE v.CreditRating = 5
          )
BEGIN
RAISERROR ('This vendor''s credit rating is too low to accept new
purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;

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

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

D. Utilisation de la résolution différée des noms

USE AdventureWorks;
GO
IF OBJECT_ID ('HumanResources.trig1','TR') IS NOT NULL
   DROP TRIGGER HumanResources.trig1;
GO
-- Creating a trigger on a nonexistent table.
CREATE TRIGGER HumanResources.trig1
on HumanResources.Employee
AFTER INSERT, UPDATE, DELETE
AS 
   SELECT e.EmployeeID, e.BirthDate, x.info 
   FROM HumanResources.Employee AS e INNER JOIN does_not_exist AS x 
      ON e.EmployeeID = x.xID
GO

-- This statement displays the text of the trigger.
SELECT t.object_id, m.definition
FROM sys.triggers AS t INNER JOIN sys.sql_modules AS m 
   ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig1'
AND t.parent_class = 1
GO

-- Creating a trigger on an existing table, but with a nonexistent 
-- column.
USE AdventureWorks;
GO
IF OBJECT_ID ('HumanResources.trig2','TR') IS NOT NULL
   DROP TRIGGER HumanResources.trig2
GO

CREATE TRIGGER HumanResources.trig2 
ON HumanResources.Employee
AFTER INSERT, UPDATE
AS 
   DECLARE @fax varchar(12)
   SELECT @fax = 'AltPhone'
   FROM HumanResources.Employee
GO

-- This statement displays the text of the trigger.

SELECT t.object_id, m.definition
FROM sys.triggers AS t INNER JOIN sys.sql_modules AS m 
   ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig2'
AND t.parent_class = 1
GO

E. Utilisation d'un déclencheur DDL avec une étendue de base de données

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

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

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

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

Historique des modifications

Voir aussi

Référence

Concepts