Utilisation de la fonction EVENTDATA

Mis à jour : 5 décembre 2005

Les informations sur un événement qui lance un déclencheur DDL sont capturées à l'aide de la fonction EVENTDATA. Cette fonction retourne une valeur xml. Le schéma XML inclut des informations sur les éléments suivants :

• l'heure de l'événement ;
• le SPID (System Process ID) de la connexion lorsque le déclencheur s'est exécuté ;
• le type d'événement qui a lancé le déclencheur.

Selon le type d'événement, le schéma inclut ensuite des informations complémentaires, telles que la base de données dans laquelle s'est produit l'événement, l'objet sur lequel il s'est produit et l'instruction Transact-SQL de l'événement. Pour plus d'informations, consultez EVENTDATA (Transact-SQL).

Par exemple, le déclencheur DDL ci-dessous est créé dans l'exemple de base de données AdventureWorks :

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 CREATE TABLE suivante 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 cela, une instruction XQuery est appliquée aux données xml générées par EVENTDATA et l'élément <CommandText> est récupéré. Pour plus d'informations, consultez Requêtes XQuery impliquant le type de données xml.

Attention :

EVENTDATA capture les données des événements CREATE_SCHEMA ainsi que l'<élément_de_schéma> de la définition CREATE SCHEMA correspondante, s'il existe. En outre, EVENTDATA reconnaît la définition <élément_de_schéma> en tant qu'événement distinct. En conséquence, un déclencheur DDL créé à la fois sur un événement CREATE_SCHEMA et sur un événement représenté par l'<élément_de_schéma> de la définition CREATE SCHEMA, peut retourner deux fois les mêmes données d'événement, telles que les données TSQLCommand. Par exemple, considérez qu'un déclencheur DDL est créé sur les deux événements CREATE_SCHEMA et CREATE_TABLE et que le lot suivant est exécuté : CREATE SCHEMA s CREATE TABLE t1 (col1 int) Si l'application récupère les données TSQLCommand de l'événement CREATE_TABLE, soyez conscient que ces données peuvent apparaître deux fois : une fois lorsque l'événement CREATE_SCHEMA se produit et de nouveau lorsque l'événement CREATE_TABLE a lieu. Évitez de créer des déclencheurs DDL sur les événements CREATE_SCHEMA et sur les textes <élément_de_schéma> de toute définition CREATE SCHEMA correspondante, ou construisez la logique dans votre application de sorte que le même événement ne soit pas traité deux fois.

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 des informations sur l'événement. Un déclencheur DDL est ensuite créé sur la base de données active qui remplit la table avec les informations ci-dessous, chaque fois qu'un événement DDL se produit au niveau de la base de données :

• l'heure de l'événement (avec la fonction GETDATE) ;
• l'utilisateur de la base de données sur la session de qui l'événement s'est produit (avec la fonction CURRENT_USER) ;
• le type de l'événement ;
• l'instruction Transact-SQL comprenant l'événement.

Là encore, les deux derniers éléments sont capturés en utilisant XQuery sur les données xml générées par EVENTDATA.

USE AdventureWorks;
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() à la place de la méthode query(). La méthode query() retourne des instances XML et CR/LF (retour chariot/saut de ligne) à échappement & dans les résultats, alors que la méthode value() rend les instances CR/LF invisibles dans le résultat.

L'exemple de base de données AdventureWorks fournit un exemple similaire de déclencheur DDL. Pour trouver cet exemple, accédez au dossier des déclencheurs de base de données à l'aide de SQL Server Management Studio. Ce dossier se trouve sous le dossier Programmabilité de la base de données AdventureWorks. Cliquez avec le bouton droit sur ddlDatabseTriggerLog et sélectionnez Générer un script du déclencheur de la base de données en tant que. Par défaut, le déclencheur DDL ddlDatabseTriggerLog est désactivé.

Voir aussi

Concepts

Conception des déclencheurs DDL

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications