Conception des déclencheurs DDL
Avant de pouvoir concevoir un déclencheur DDL, les conditions suivantes sont requises :
Vous devez comprendre la portée du déclencheur DDL.
Vous devez déterminer l'instruction ou le groupe d'instructions Transact-SQL qui active le déclencheur.
.gif "Remarque relative à la sécurité") Remarque relative à la sécurité |
|---|
Du code malveillant dans les déclencheurs peut s'exécuter sous des privilèges promus. Pour plus d'informations sur les moyens de lutte contre ces malveillances, consultez Gestion de la sécurité liée aux déclencheurs. |
[!REMARQUE]
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.
Présentation de l'étendue des déclencheurs
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 sur le 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 Utilisation de 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.
Pour plus d'informations sur l'extraction de métadonnées pour des déclencheurs DDL, consultez Obtention d'informations sur les déclencheurs DDL.
Spécification d'une instruction ou d'un groupe d'instructions Transact-SQL
Des déclencheurs DDL peuvent être créés pour être activés après l'exécution d'une ou de plusieurs instructions DDL ou d’un groupe prédéfini d’instructions DDL .
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.
[!REMARQUE]
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, 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 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.