Verwenden der EVENTDATA-Funktion

Artikel
12/15/2008

Aktualisiert: 05. Dezember 2005

Informationen zu einem Ereignis, das einen DDL-Trigger auslöst, werden mit der EVENTDATA-Funktion erfasst. Diese Funktion gibt einen xml-Wert zurück. Das XML-Schema schließt Informationen zu folgenden Punkten ein:

Zeitpunkt des Ereignisses.
Die SPID (System Process ID) der Verbindung, als der Trigger ausgeführt wurde.
Der Typ des Ereignisses, die den Trigger ausgelöst haben.

Abhängig vom Ereignistyp schließt das Schema dann weitere Informationen ein, z. B. die Datenbank, in der das Ereignis aufgetreten ist, das Objekt, für das das Ereignis erfolgte, und die Transact-SQL-Anweisung des Ereignisses. Weitere Informationen finden Sie unter EVENTDATA (Transact-SQL).

Der folgende DDL-Trigger wird z. B. in der AdventureWorks-Beispieldatenbank erstellt:

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
;

Anschließend wird die folgende CREATE TABLE-Anweisung ausgeführt:

CREATE TABLE NewTable (Column1 int);

Die EVENTDATA()-Anweisung im DDL-Trigger erfasst den Text der CREATE TABLE -Anweisung, die nicht zulässig ist. Dies erfolgt mithilfe einer XQuery-Anweisung für die von EVENTDATA generierten xml-Daten und durch Abrufen des <CommandText>-Elements. Weitere Informationen finden Sie unter XQuery für den xml-Datentyp.

Vorsicht:
EVENTDATA erfasst die Daten von CREATE_SCHEMA-Ereignissen sowie den <schema_element>-Text der entsprechenden CREATE SCHEMA-Definition, sofern vorhanden. Darüber hinaus erkennt EVENTDATA die <schema_element>-Definition als ein gesondertes Ereignis. Deshalb ist es möglich, dass ein DDL-Trigger erstellt wurde, der für ein CREATE_SCHEMA-Ereignis sowie für ein Ereignis, das durch den <schema_element>-Text der CREATE SCHEMA-Definition dargestellt wird, möglicherweise dieselben Ereignisdaten doppelt zurückgibt, wie z. B. die `TSQLCommand`-Daten. Angenommen, ein DDL-Trigger wird für die Ereignisse CREATE_SCHEMA und CREATE_TABLE erstellt, und der folgende Batch wird ausgeführt: `CREATE SCHEMA s` `CREATE TABLE t1 (col1 int)` Ruft die Anwendung die `TSQLCommand`-Daten des CREATE_TABLE-Ereignisses ab, sollten Sie beachten, dass diese Daten möglicherweise doppelt angezeigt werden: einmal, wenn das CREATE_SCHEMA-Ereignis stattfindet, und ein zweites Mal, wenn das CREATE_TABLE-Ereignis stattfindet. Vermeiden Sie das Erstellen von DDL-Triggern sowohl für die CREATE_SCHEMA-Ereignisse als auch die <schema_element>-Texte entsprechender CREATE SCHEMA-Definitionen, oder integrieren Sie eine Logik in die Anwendung, damit dasselbe Ereignis nicht doppelt verarbeitet wird.

Vorsicht:

EVENTDATA erfasst die Daten von CREATE_SCHEMA-Ereignissen sowie den <schema_element>-Text der entsprechenden CREATE SCHEMA-Definition, sofern vorhanden. Darüber hinaus erkennt EVENTDATA die <schema_element>-Definition als ein gesondertes Ereignis. Deshalb ist es möglich, dass ein DDL-Trigger erstellt wurde, der für ein CREATE_SCHEMA-Ereignis sowie für ein Ereignis, das durch den <schema_element>-Text der CREATE SCHEMA-Definition dargestellt wird, möglicherweise dieselben Ereignisdaten doppelt zurückgibt, wie z. B. die TSQLCommand-Daten. Angenommen, ein DDL-Trigger wird für die Ereignisse CREATE_SCHEMA und CREATE_TABLE erstellt, und der folgende Batch wird ausgeführt: CREATE SCHEMA s CREATE TABLE t1 (col1 int) Ruft die Anwendung die TSQLCommand-Daten des CREATE_TABLE-Ereignisses ab, sollten Sie beachten, dass diese Daten möglicherweise doppelt angezeigt werden: einmal, wenn das CREATE_SCHEMA-Ereignis stattfindet, und ein zweites Mal, wenn das CREATE_TABLE-Ereignis stattfindet. Vermeiden Sie das Erstellen von DDL-Triggern sowohl für die CREATE_SCHEMA-Ereignisse als auch die <schema_element>-Texte entsprechender CREATE SCHEMA-Definitionen, oder integrieren Sie eine Logik in die Anwendung, damit dasselbe Ereignis nicht doppelt verarbeitet wird.

Beispiel

Mithilfe der EVENTDATA-Funktion können Sie ein Ereignisprotokoll erstellen. Im folgenden Beispiel wird eine Tabelle zum Speichern von Ereignisinformationen erstellt. Anschließend wird ein DDL-Trigger für die aktuelle Datenbank erstellt, die bei jedem Auftreten eines DDL-Ereignisses auf Datenbankebene die Tabelle mit den folgenden Informationen auffüllt:

Zeitpunkt des Ereignisses (mithilfe der GETDATE-Funktion)
Datenbankbenutzer, für dessen Sitzung das Ereignis aufgetreten ist (mithilfe der CURRENT_USER-Funktion)
Typ des Ereignisses
Die Transact-SQL-Anweisung, die das Ereignis enthalten hat.

Die beiden letzten Elemente werden wiederum erfasst, indem XQuery für die von EVENTDATA generierten xml-Daten verwendet wird.

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

Hinweis:
Zur Rückgabe von Ereignisdaten sollten Sie die XQuery-value()-Methode anstelle der query()-Methode verwenden. Die query()-Methode gibt in der Ausgabe XML und Ampersand-Escape-CRLF-Instanzen (Carriage Return and Line Feed) zurück, wogegen die value()-Methode CRLF-Instanzen zurückgibt, die in der Ausgabe unsichtbar sind.

Ein ähnliches Beispiel für einen DDL-Trigger wird mit der AdventureWorks-Beispieldatenbank bereitgestellt. Auf das Beispiel können Sie mithilfe von SQL Server Management Studio im Ordner "Datenbanktrigger" zugreifen. Dieser Ordner befindet sich unterhalb des Ordners Programmierbarkeit in der AdventureWorks-Datenbank. Klicken Sie mit der rechten Maustaste auf ddlDatabseTriggerLog, und wählen Sie Skript für Datenbanktrigger als aus. Standardmäßig ist der DDL-Trigger ddlDatabseTriggerLog deaktiviert.

Siehe auch

Konzepte

Entwerfen von DDL-Triggern

Hilfe und Informationen

Informationsquellen für SQL Server 2005

Änderungsverlauf

Version	Verlauf
05. Dezember 2005	Neuer Inhalt: Ein Warnhinweis über das Erstellen von DDL-Triggern sowohl für die CREATE_SCHEMA-Ereignisse als auch die <schema_element>-Texte entsprechender CREATE SCHEMA-Definitionen wurde hinzugefügt.