Remarque
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de vous connecter ou de modifier des répertoires.
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de modifier des répertoires.
S’applique à :
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Les déclencheurs DDL se déclenchent en réponse à divers événements du langage de définition de données (DDL). Ces événements correspondent principalement à des instructions Transact-SQL qui commencent par les mots clés CREATE, , DROPALTER, GRANT, DENY, REVOKE, ou UPDATE STATISTICS. Certaines procédures stockées système qui effectuent des opérations de type DDL peuvent également activer des déclencheurs DDL.
Utilisez des déclencheurs DDL lorsque vous souhaitez effectuer les tâches suivantes :
- Empêchez certaines modifications sur votre schéma de base de données.
- Faites en sorte qu'un événement se produise dans la base de données en réponse à une modification du schéma.
- Enregistrez des modifications ou des événements dans le schéma de la base de données.
Importante
Testez vos déclencheurs DDL afin de déterminer leurs réponses aux procédures stockées système qui sont exécutées. Par exemple, l’instruction CREATE TYPE et la sp_addtype procédure stockée déclenchent tous deux un déclencheur DDL créé sur un CREATE_TYPE événement.
Types de déclencheur DDL
Déclencheur Transact-SQL
Type spécial de procédure stockée Transact-SQL qui exécute une ou plusieurs instructions Transact-SQL en réponse à un événement au niveau du serveur ou de la base de données. Par exemple, un déclencheur DDL peut se déclencher si une instruction telle que ALTER SERVER CONFIGURATION est exécutée ou si une table est supprimée à l'aide de DROP TABLE.
Déclencheur CLR
Au lieu d’exécuter une procédure stockée Transact-SQL, un déclencheur CLR (Common Language Runtime) exécute une ou plusieurs méthodes écrites dans le code managé qui sont membres d’un assembly créé dans le .NET Framework et chargés dans SQL Server.
Les déclencheurs DDL ne s'activent qu'après l'exécution des instructions DDL de déclenchement. Les déclencheurs DDL ne peuvent pas être utilisés comme déclencheurs INSTEAD OF. 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.
Les déclencheurs DDL ne créent pas les tables spéciales inserted et deleted.
Les informations relatives à un événement qui déclenche un déclencheur DDL et les modifications suivantes provoquées par le déclencheur sont capturées à l’aide de la EVENTDATA fonction.
Plusieurs déclencheurs à créer pour chaque événement DDL.
Contrairement aux déclencheurs DML (Data Manipulation Language), les déclencheurs DDL ne sont pas limités aux schémas. Par conséquent, les fonctions telles que OBJECT_ID, OBJECT_NAME, OBJECTPROPERTYet OBJECTPROPERTYEX ne peuvent pas être utilisées pour interroger des métadonnées sur les déclencheurs DDL. Utilisez plutôt les affichages catalogue.
Les déclencheurs DDL dont l’étendue est le serveur figurent dans le dossier Déclencheurs de l’Explorateur d’objets SQL Server Management Studio. Ce dossier se trouve dans le dossier Objets serveur . Les déclencheurs DDL au niveau de la base de données figurent dans le dossier Database Triggers . Ce dossier se trouve dans le dossier Programmabilité de la base de données correspondante.
Importante
Un code malveillant présent dans des déclencheurs peut s'exécuter sous des privilèges promus. Pour plus d’informations sur la façon de réduire cette menace, consultez Gérer la sécurité des déclencheurs.
Étendue du déclencheur DDL
Les déclencheurs DDL peuvent se déclencher en réponse à un événement Transact-SQL traité dans la base de données actuelle ou sur le serveur actuel. L'étendue du déclencheur dépend de l'événement. Par exemple, un déclencheur DDL créé pour être déclenché en réponse à un CREATE_TABLE événement peut le faire chaque fois qu’un CREATE_TABLE événement se produit dans la base de données ou sur l’instance de serveur. Un déclencheur DDL créé pour être déclenché en réponse à un CREATE_LOGIN événement ne peut le faire que lorsqu’un CREATE_LOGIN événement se produit dans l’instance de serveur.
Dans l’exemple suivant, le déclencheur safety DDL se déclenche chaque fois qu’un DROP_TABLE ou ALTER_TABLE événement se produit dans la base de données.
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS PRINT 'You must disable trigger "safety" to drop or alter tables!';
ROLLBACK;
Dans l'exemple suivant, un déclencheur DDL affiche un message si un événement CREATE_DATABASE se produit sur l'instance de serveur actuelle. L’exemple utilise la fonction EVENTDATA pour récupérer le texte de l’instruction Transact-SQL correspondante. Pour plus d’informations sur l’utilisation EVENTDATA avec des déclencheurs DDL, consultez Utiliser 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;
Les listes qui mappent les instructions Transact-SQL aux étendues qui peuvent être spécifiées sont disponibles via les liens fournis dans la section Sélectionner une instruction DDL particulière pour déclencher un déclencheur DDL plus loin dans cet article.
Les déclencheurs DDL délimités à la base de données sont stockés en tant qu’objets dans la base de données dans laquelle ils sont créés. Les déclencheurs DDL peuvent être créés dans la master base de données et se comporter comme ceux créés dans les bases de données conçues par l’utilisateur. Vous pouvez obtenir des informations sur les déclencheurs DDL en interrogeant la vue de catalogue sys.triggers. Vous pouvez interroger sys.triggers dans le contexte de base de données dans lequel les déclencheurs sont créés ou en spécifiant le nom de la base de données en tant qu’identificateur, tel que master.sys.triggers.
Les déclencheurs DDL à l'échelle du serveur sont stockés en tant qu'objets dans la base de données master. Toutefois, vous pouvez obtenir des informations sur les déclencheurs DDL à l'échelle du serveur en interrogeant la vue du catalogue sys.server_triggers dans n’importe quel contexte de base de données.
Spécifier une instruction Transact-SQL ou un groupe d’instructions
Sélectionnez une instruction DDL particulière pour déclencher un déclencheur DDL
Les déclencheurs DDL peuvent être conçus pour se déclencher après l’exécution d’une ou plusieurs instructions Transact-SQL particulières. Dans l'exemple précédent, le déclencheur safety est activé après n’importe quel événement DROP_TABLE ou ALTER_TABLE . Pour obtenir la liste des instructions Transact-SQL pouvant être spécifiées pour déclencher un déclencheur DDL, ainsi que l’étendue dans laquelle le déclencheur peut se déclencher, consultez Événements DDL.
Sélectionnez un groupe prédéfini d’instructions DDL pour déclencher un déclencheur DDL
Un déclencheur DDL peut se déclencher après l’exécution de tout événement Transact-SQL appartenant à un groupe prédéfini d’événements similaires. Par exemple, si vous souhaitez qu'un déclencheur DDL se lance après l'exécution de toute instruction CREATE TABLE, ALTER TABLE ou DROP TABLE, vous pouvez préciser FOR DDL_TABLE_EVENTS dans l'instruction CREATE TRIGGER. Après l'exécution de CREATE TRIGGER, les événements d'un groupe d'événements sont ajoutés à la vue du catalogue sys.trigger_events.
Dans SQL Server 2005 (9.x), si un déclencheur est créé sur un groupe d’événements, sys.trigger_events n’inclut pas d’informations sur le groupe d’événements, sys.trigger_events inclut des informations uniquement sur les événements individuels couverts par ce groupe. sys.trigger_events conserve les métadonnées sur le groupe d’événements sur lequel le déclencheur est créé, ainsi que sur les événements individuels couverts par le groupe d’événements. Par conséquent, les modifications apportées aux événements couverts par les groupes d’événements ne s’appliquent pas aux déclencheurs DDL dans les versions récentes de SQL Server créées sur ces groupes d’événements dans SQL Server 2005 (9.x).
Pour obtenir la liste des groupes d’instructions DDL prédéfinis disponibles pour les déclencheurs DDL, connaître les instructions particulières qu’ils couvrent et les étendues selon lesquelles ces groupes d’événements peuvent être programmés, consultez Groupes d’événements DDL.
Tâches associées
| Tâche | Article |
|---|---|
| Décrit comment créer, modifier, supprimer ou désactiver des déclencheurs DDL. | Implémenter des déclencheurs DDL |
| Décrit comment créer un déclencheur DDL CLR. | Créer des déclencheurs CLR |
| Décrit comment retourner des informations sur les déclencheurs DDL. | Obtenir des informations sur les déclencheurs DDL |
Décrit comment retourner des informations sur un événement qui déclenche un déclencheur DDL à l’aide de la EVENTDATA fonction. |
Utiliser la fonction EVENTDATA |
| Décrit comment gérer la sécurité du déclencheur. | Gérer la sécurité des déclencheurs |