Utiliser la fonction EVENTDATA
S’applique à :
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
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 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 DDL Triggers.
Par exemple, le déclencheur DDL ci-dessous est créé dans l'exemple de base de données AdventureWorks2022 :
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 ce faire, utilisez une instruction XQuery sur les données xml 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).
Attention
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 qu'un déclencheur DDL est créé sur les deux événements CREATE_SCHEMA et CREATE_TABLE et que le traitement 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 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.
Événements ALTER TABLE et ALTER DATABASE
Les données d'événements pour les événements ALTER_TABLE et ALTER_DATABASE incluent également les noms et types d'autres objets affectés par l'instruction DDL et l'action effectuée sur ces objets. Les données d'événements ALTER_TABLE incluent les noms des colonnes, contraintes ou déclencheurs affectés par l'instruction TABLE ALTER et l'action (créer, modifier, supprimer, activer ou désactiver) effectuée sur les objets affectés. Les données d'événements 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 ci-dessous 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;
Ensuite, exécutez l'instruction ALTER TABLE suivante qui enfreint une contrainte :
ALTER TABLE Person.Address ALTER COLUMN ModifiedDate date;
L’instruction EVENTDATA() du 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 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 ;
Instruction Transact-SQL qui comprenait 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 AdventureWorks2022;
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
Note
Pour retourner des données d’événement, nous vous recommandons d’utiliser la méthode XQuery value() plutôt que la méthode query() . La méthode query() retourne des instances XML et CRLF (retour chariot et saut de ligne) à séquence d’échappement perluète dans la sortie, tandis que la méthode value() retourne des instances CRLF invisibles à la sortie.
L'exemple de base de données AdventureWorks2022 fournit un exemple similaire de déclencheur DDL. 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 AdventureWorks2022. Cliquez avec le bouton droit sur ddlDatabaseTriggerLog 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 ddlDatabaseTriggerLog est désactivé.
Voir aussi
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour