Verwenden der EVENTDATA-Funktion
Gilt für:
SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance
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.
Je nach Ereignistyp enthält das Schema dann zusätzliche Informationen wie die Datenbank, in der das Ereignis aufgetreten ist, das Objekt, für das das Ereignis aufgetreten ist, und die Transact-SQL-Anweisung des Ereignisses. Weitere Informationen finden Sie unter DDL Triggers.
Der folgende DDL-Trigger wird z. B. in der AdventureWorks2022 -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 wird mithilfe einer XQuery-Anweisung für die xml-Daten erreicht, die von EVENTDATA generiert und das <CommandText-Element> abgerufen werden. Weitere Informationen finden Sie unter XQuery Language Reference (SQL Server).For more information, see XQuery Language Reference (SQL Server).
Achtung
EVENTDATA erfasst die Daten von CREATE_SCHEMA Ereignissen sowie die <schema_element> der entsprechenden CREATE SCHEMA-Definition, sofern vorhanden. Darüber hinaus erkennt EVENTDATA die <schema_element> Definition als separates Ereignis. Daher kann ein DDL-Trigger, der sowohl für ein CREATE_SCHEMA-Ereignis als auch für ein durch die <schema_element> der CREATE SCHEMA-Definition dargestelltes Ereignis erstellt wird, die gleichen Ereignisdaten zweimal zurückgeben, 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 für die <schema_element> Texte entsprechender CREATE SCHEMA-Definitionen, oder erstellen Sie Logik in Ihrer Anwendung, sodass dasselbe Ereignis nicht zweimal verarbeitet wird.
ALTER TABLE-Ereignis und ALTER DATABASE-Ereignis
Die Ereignisdaten für das ALTER_TABLE-Ereignis und das ALTER DATABASE-Ereignis umfassen auch die Namen und Typen anderer Objekte, die durch die DDL-Anweisung betroffen sind, sowie die Aktionen, die für diese Objekte ausgeführt werden. Die Daten für das ALTER_TABLE-Ereignis umfassen die Namen der Spalten, Einschränkungen oder Trigger, die durch die ALTER TABLE-Anweisung betroffen sind, sowie die Aktion (Erstellen, Ändern, Löschen, Aktivieren oder Deaktivieren), die für die betroffenen Objekte ausgeführt wurde. Die Daten für das ALTER_DATABASE-Ereignis umfassen die Namen aller Dateien oder Dateigruppen, die durch die ALTER_DATABASE-Anweisung betroffen sind, sowie die Aktion (Erstellen, Ändern oder Löschen), die für die betroffenen Objekte ausgeführt wurde.
Der folgende DDL-Trigger wird beispielsweise in der AdventureWorks-Beispieldatenbank erstellt:
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;
Führen Sie dann die folgende ALTER TABLE-Anweisung aus, die gegen eine Einschränkung verstößt:
ALTER TABLE Person.Address ALTER COLUMN ModifiedDate date;
Die EVENTDATA()-Anweisung im DDL-Trigger erfasst den Text der ALTER TABLE -Anweisung, der nicht zulässig ist.
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 umfasste.
Die beiden letzten Elemente werden wiederum erfasst, indem XQuery für die von EVENTDATA generierten xml -Daten verwendet wird.
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
Hinweis
Zum Zurückgeben von Ereignisdaten wird die Verwendung der XQuery value() -Methode anstelle der query() -Methode empfohlen. Bei der query() -Methode werden XML-Daten und durch das kaufmännische Und-Zeichen geschützte CR/LF-Instanzen (Wagenrücklauf/Zeilenvorschub) in der Ausgabe zurückgegeben, während bei der value() -Methode CR/LF-Instanzen zurückgegeben werden, die in der Ausgabe nicht sichtbar sind.
Ein ähnliches Beispiel für einen DDL-Trigger wird mit der AdventureWorks2022 -Beispieldatenbank bereitgestellt. Um das Beispiel abzurufen, suchen Sie den Ordner "Datenbanktrigger" mithilfe von SQL Server Management Studio. Dieser Ordner befindet sich unter dem Ordner "Programmierbarkeit " der AdventureWorks2022-Datenbank. Klicken Sie mit der rechten Maustaste auf ddlDatabaseTriggerLog, und wählen Sie Skript für Datenbanktrigger als aus. Standardmäßig ist der DDL-Trigger ddlDatabaseTriggerLog deaktiviert.
Weitere Informationen
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für