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.
Des informations sur un événement qui déclenche un déclencheur DDL sont capturées à l’aide de la fonction EVENTDATA. Cette fonction retourne une xml valeur. Le schéma XML inclut des informations sur les éléments suivants :
l'heure de l'événement ;
Identifiant du processus système (SPID) de la connexion au moment où le déclencheur est exécuté.
Type d’événement qui a déclenché le déclencheur.
Selon le type d’événement, le schéma inclut ensuite des informations supplémentaires telles que la base de données dans laquelle l’événement s’est produit, l’objet sur lequel l’événement s’est produit et l’instruction Transact-SQL de l’événement. Pour plus d’informations, consultez Déclencheurs DDL.
Par exemple, le déclencheur DDL suivant est créé dans l’exemple de base de données AdventureWorks2012 :
CREATE TRIGGER safety
ON DATABASE
FOR CREATE_TABLE
AS
PRINT 'CREATE TABLE Issued.'
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
RAISERROR ('New tables cannot be created in this database.', 16, 1)
ROLLBACK
;
L’instruction suivante CREATE TABLE est ensuite exécutée :
CREATE TABLE NewTable (Column1 int);
L’instruction EVENTDATA() du déclencheur DDL capture le texte de l’instruction CREATE TABLE qui n’est pas autorisé. Pour ce faire, utilisez une instruction XQuery sur les xml données générées par EVENTDATA et récupérez l’élément <CommandText> . Pour plus d’informations, consultez référence du langage XQuery (SQL Server).
Avertissement
EVENTDATA capture les données des événements CREATE_SCHEMA ainsi que les <schema_element> de la définition CREATE SCHEMA correspondante, le cas échéant. En outre, EVENTDATA reconnaît la <définition de schema_element> en tant qu’événement distinct. Par conséquent, un déclencheur DDL créé à la fois sur un événement CREATE_SCHEMA et un événement représenté par l’schema_element <> de la définition CREATE SCHEMA, peut retourner les mêmes données d’événement deux fois, telles que les TSQLCommand données. Par exemple, considérez un déclencheur DDL créé à la fois sur les événements CREATE_SCHEMA et CREATE_TABLE et le lot suivant est exécuté :
CREATE SCHEMA s
CREATE TABLE t1 (col1 int)
Si l’application récupère les TSQLCommand données de l’événement de CREATE_TABLE, sachez que ces données peuvent apparaître deux fois : une fois lorsque l’événement CREATE_SCHEMA se produit, et à nouveau lorsque l’événement CREATE_TABLE se produit. Évitez de créer des déclencheurs DDL sur les événements CREATE_SCHEMA et les <textes schema_element> de toutes les définitions CREATE SCHEMA correspondantes, ou créez une logique dans votre application afin que le même événement ne soit pas traité deux fois.
ALTER TABLE et ALTER DATABASE : événements
Les données d’événement pour les événements ALTER_TABLE et ALTER_DATABASE incluent également les noms et les types d’autres objets affectés par l’instruction DDL et l’action effectuée sur ces objets. Les données d’événement ALTER_TABLE incluent les noms des colonnes, contraintes ou déclencheurs affectés par l’instruction ALTER TABLE et l’action (créer, modifier, supprimer, activer ou désactiver) effectuées sur les objets affectés. Les données d’événement ALTER_DATABASE incluent les noms des fichiers ou groupes de fichiers affectés par l’instruction ALTER DATABASE et l’action (créer, modifier ou supprimer) effectuée sur les objets affectés.
Par exemple, créez le déclencheur DDL suivant dans l’exemple de base de données AdventureWorks :
CREATE TRIGGER ColumnChanges
ON DATABASE
FOR ALTER_TABLE
AS
-- Detect whether a column was created/altered/dropped.
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
RAISERROR ('Table schema cannot be modified in this database.', 16, 1);
ROLLBACK;
Exécutez ensuite l’instruction ALTER TABLE suivante qui enfreint une contrainte :
ALTER TABLE Person.Address ALTER COLUMN ModifiedDate date;
L’instruction EVENTDATA() dans le déclencheur DDL capture le texte de l’instruction ALTER TABLE qui n’est pas autorisé.
Exemple :
Vous pouvez utiliser la fonction EVENTDATA pour créer un journal des événements. Dans l’exemple suivant, une table est créée pour stocker les informations d’événement. Un déclencheur DDL est ensuite créé sur la base de données active qui remplit la table avec les informations suivantes chaque fois qu’un événement DDL au niveau de la base de données se produit :
Heure de l’événement (à l’aide de la fonction GETDATE).
Utilisateur de base de données sur lequel l’événement s’est produit (à l’aide de la fonction CURRENT_USER).
le type de l'événement ;
La déclaration Transact-SQL qui constituait l’événement.
Là encore, les deux derniers éléments sont capturés à l’aide de XQuery sur les xml données générées par EVENTDATA.
USE AdventureWorks2012;
GO
CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));
GO
CREATE TRIGGER log
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT ddl_log
(PostTime, DB_User, Event, TSQL)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), CURRENT_USER),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
GO
--Test the trigger
CREATE TABLE TestTable (a int)
DROP TABLE TestTable ;
GO
SELECT * FROM ddl_log ;
GO
Remarque
Pour retourner des données d’événement, nous vous recommandons d’utiliser la méthode XQuery value() au lieu de la query() méthode. La query() méthode retourne des instances de retour chariot xml et d’échappement et de saut de ligne (CRLF) dans la sortie, tandis que la value() méthode restitue les instances CRLF invisibles dans la sortie.
Un exemple de déclencheur DDL similaire est fourni avec l’exemple de base de données AdventureWorks2012 . Pour obtenir l’exemple, recherchez le dossier Déclencheurs de base de données à l’aide de SQL Server Management Studio. Ce dossier se trouve sous le dossier Programmability de la base de données AdventureWorks2012. Cliquez avec le bouton droit sur ddlDatabaseTriggerLog , puis sélectionnez Déclencheur de base de données de script en tant que. Par défaut, le déclencheur DDL ddlDatabaseTriggerLog est désactivé.