Déclencheurs DDL

Les déclencheurs DDL sont activés en réponse à différents événements DDL (Data Definition Language). Ces événements correspondent principalement à des instructions Transact-SQL commençant 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 lancer 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.

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 d'étendue de serveur ou de 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é que les membres d'un assembly ont créées dans .NET Framework et téléchargées 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 est situé sous le dossier Objets serveur. Les déclencheurs DDL au niveau de la base de données figurent dans le dossier Database Triggers. Ce dossier est situé sous le dossier Programmabilité de la base de données correspondante.

Remarque relative à la sécurité
Un code malveillant présent dans des déclencheurs peut s'exécuter sous des privilèges promus. Pour plus d'informations sur 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 être activés 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 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. Cet exemple utilise la fonction EVENTDATA pour extraire 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

La liste qui mappe les instructions Transact-SQL aux étendues qui peuvent leur être spécifiées sont disponibles par l'intermédiaire des liens fournis dans la section « Sélection d'une instruction DDL particulière pour activer 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 au sein du 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 activés après l'exécution d'une ou de plusieurs instructions Transact-SQL. 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 à spécifier pour activer un déclencheur et connaître l'étendue selon laquelle ce déclencheur peut être activé, 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 être activé après l'exécution de n'importe quel événement Transact-SQL appartenant à un groupe prédéfini d'événements comparables. 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 à la vue de 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 concernant le groupe d'événements mais uniquement des informations relatives à chacun des événements couverts par ce groupe. Dans SQL Server 2008 et version ultérieure, sys.trigger_events rend persiste les métadonnées relatives au groupe d'événement sur lequel les déclencheurs sont créés, et également celles qui concernent chacune des événements couverts par le groupe. Par conséquent, les modifications apportées aux événements couverts par les groupes d'événement dans SQL Server 2008 et versions supérieures ne s'appliquent pas aux déclencheurs DDL créés sur ces groupes d'événement 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âches associées

Voir aussi

Référence

CREATE TRIGGER (Transact-SQL)

Concepts

Déclencheurs DML

Déclencheurs de connexion