Partager via

Déclencheurs DDL

  • Article

Les déclencheurs DDL sont activés en réponse à différents événements DDL (Data Definition Language). Ces événements correspondent principalement aux instructions Transact-SQL qui commencent par les mots clés CREATE, ALTER, DROP, 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 dans les cas suivants :

  • 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.

Important

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 procédure stockée sp_addtype activeront toutes deux un déclencheur DDL créé sur un événement CREATE_TYPE.

Types de déclencheurs DDL

Déclencheur Transact-SQL DDL
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 étendu au serveur ou à la base de données. Par exemple, un déclencheur DDL peut être activé si une instruction comme ALTER SERVER CONFIGURATION est exécutée ou si une table est supprimée à l'aide de DROP TABLE.

Déclencheur DDL CLR
Au lieu d’exécuter une procédure stockée Transact-SQL, un déclencheur CLR exécute une ou plusieurs méthodes écrites en code managé qui sont membres d’un assembly créé dans le .NET Framework et chargé 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 inserted et deleted spéciales.

Les informations sur un événement déclenché par un déclencheur DDL, ainsi que les modifications qui s'ensuivent, sont capturées au moyen de la fonction EVENTDATA.

Plusieurs déclencheurs à créer pour chaque événement DDL.

À la différence des déclencheurs DML, le champ d'action des déclencheurs DDL ne correspond pas aux schémas. Par conséquent, les fonctions OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY et OBJECTPROPERTYEX ne sont pas utilisables pour effectuer des requêtes de métadonnées à propos de déclencheurs DDL. Utilisez plutôt les affichages catalogue.

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.

Important

Un code malveillant présent dans des déclencheurs peut s'exécuter sous des privilèges promus. Pour plus d’informations sur les moyens de lutte contre ces malveillances, 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 active 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 activé en réponse à un événement CREATE_TABLE se déclenchera à chaque fois qu'un événement CREATE_TABLE se produira dans la base de données ou sur l’instance de serveur. Un déclencheur DDL créé pour être activé en réponse à un événement CREATE_LOGIN ne se déclenchera que si un événement CREATE_LOGIN se produit dans l'instance de serveur.

Dans l'exemple suivant, le déclencheur DDL safety est activé chaque fois qu'un événement DROP_TABLE ou ALTER_TABLE 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 EVENTDATA fonction pour récupérer le texte de l’instruction Transact-SQL correspondante. Pour plus d’informations sur l’utilisation de 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;  
GO

Les listes qui mappent les instructions Transact-SQL aux étendues qui peuvent être spécifiées pour elles sont disponibles via les liens fournis dans la section « Sélection d’une instruction DDL particulière pour déclencher un déclencheur DDL », plus loin dans cette rubrique.

Les déclencheurs DDL dont l'étendue est la base de données sont stockés sous forme d'objets dans la base de données où ils sont créés. Des déclencheurs DDL peuvent être créés dans la base de données master ; ils se comportent exactement comme ceux créés dans les bases de données conçues par l’utilisateur. Vous pouvez obtenir des informations concernant les déclencheurs DDL en interrogeant l’affichage catalogue sys.server_triggers . Vous pouvez interroger sys.triggers dans le contexte de base de données dans lequel ils sont créés, ou en spécifiant le nom de la base de données comme identificateur, par exemple master.sys.triggers.

Les déclencheurs DDL dont l’étendue est le serveur sont stockés sous forme d’objets dans la base de données master . Toutefois, vous pouvez obtenir des informations sur les déclencheurs DDL dont l’étendue est limitée au serveur en interrogeant l’affichage catalogue sys.server_triggers dans n’importe quel contexte de base de données.

Spécification d'une instruction ou d'un groupe d'instructions Transact-SQL

Sélection d'une instruction DDL particulière pour activer un déclencheur DDL

Les déclencheurs DDL peuvent être conçus pour être déclenchés après l’exécution d’une ou de 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 qui peuvent être spécifiées pour déclencher un déclencheur DDL et l’étendue au niveau de laquelle le déclencheur peut se déclencher, consultez Événements DDL.

Sélection d'un groupe prédéfini d'instructions DDL pour activer un déclencheur DDL

Un déclencheur DDL peut se déclencher après l’exécution de tout événement Transact-SQL qui appartient à un regroupement prédéfini d’événements similaires. Par exemple, si vous souhaitez activer un déclencheur DDL après l'exécution d'une instruction CREATE TABLE, ALTER TABLE ou DROP TABLE quelconque, vous pouvez spécifier FOR DDL_TABLE_EVENTS dans l'instruction CREATE TRIGGER. Après l’exécution de CREATE TRIGGER, les événements qui sont couverts par un groupe d’événements sont ajoutés à l’affichage catalogue sys.trigger_events .

Dans SQL Server 2005, 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 uniquement des informations sur les événements individuels couverts par ce groupe. Dans SQL Server 2008 et version ultérieure, sys.trigger_events rend persistantes les métadonnées relatives au groupe d’événement sur lequel les déclencheurs sont créés, et également celles qui concernent chacun des événements couverts par le groupe. Par conséquent, les modifications apportées aux événements couverts par les groupes d’événements dans SQL Server 2008 et versions ultérieures ne s’appliquent pas aux déclencheurs DDL créés sur ces groupes d’événements dans SQL Server 2005.

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âche Rubrique
Décrit comment créer, modifier, supprimer ou désactiver les 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 lance un déclencheur DDL à l'aide de la fonction EVENTDATA. Utiliser la fonction EVENTDATA
Décrit comment gérer la sécurité du déclencheur. Gérer la sécurité des déclencheurs

Voir aussi

Déclencheurs DML
Déclencheurs de connexion
CREATE TRIGGER (Transact-SQL)