Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
Informationen zu einem Ereignis, das einen DDL-Trigger auslöst, werden mithilfe der EVENTDATA-Funktion erfasst. Diese Funktion gibt einen xml Wert zurück. Das XML-Schema enthält Informationen zu folgenden Themen:
Zeitpunkt des Ereignisses.
Die Systemprozess-ID (SPID) der Verbindung, wenn der Trigger ausgeführt wird.
Der Ereignistyp, der den Trigger ausgelöst hat.
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-Trigger.
Beispielsweise wird der folgende DDL-Trigger in der AdventureWorks2012-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 nicht zulässigen Anweisung. Dies wird erreicht, indem eine XQuery-Anweisung gegen die von EVENTDATA generierten xml Daten ausgeführt und das <CommandText>-Element abgerufen wird. Weitere Informationen finden Sie unter XQuery Language Reference (SQL Server).For more information, see XQuery Language Reference (SQL Server).
Vorsicht
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. Ziehen Sie beispielsweise einen DDL-Trigger in Betracht, der sowohl für die Ereignisse CREATE_SCHEMA als auch für CREATE_TABLE erstellt wird, und führen Sie anschließend den folgenden Batch aus:
CREATE SCHEMA s
CREATE TABLE t1 (col1 int)
Wenn die Anwendung die TSQLCommand Daten des CREATE_TABLE-Ereignisses abruft, beachten Sie, dass diese Daten zweimal angezeigt werden können: einmal, wenn das CREATE_SCHEMA-Ereignis auftritt, und erneut, wenn das CREATE_TABLE-Ereignis auftritt. 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- und ALTER DATABASE-Ereignisse
Die Ereignisdaten für die Ereignisse ALTER_TABLE und ALTER_DATABASE enthalten auch die Namen und Typen anderer Objekte, die von der DDL-Anweisung betroffen sind, und die aktion, die für diese Objekte ausgeführt wird. Die ALTER_TABLE Ereignisdaten umfassen die Namen der Spalten, Einschränkungen oder Trigger, die von der ALTER TABLE-Anweisung betroffen sind, und die Aktion (Erstellen, Ändern, Ablegen, Aktivieren oder Deaktivieren), die für die betroffenen Objekte ausgeführt werden. Die ALTER_DATABASE Ereignisdaten enthalten die Namen aller Dateien oder Dateigruppen, die von der ALTER DATABASE-Anweisung betroffen sind, und die Aktion (Erstellen, Ändern oder Ablegen), die für die betroffenen Objekte ausgeführt werden.
Erstellen Sie beispielsweise den folgenden DDL-Trigger in der AdventureWorks-Beispieldatenbank:
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, die nicht zulässig ist.
Beispiel
Mit der EVENTDATA-Funktion können Sie ein Ereignisprotokoll erstellen. Im folgenden Beispiel wird eine Tabelle zum Speichern von Ereignisinformationen erstellt. Ein DDL-Trigger wird dann in der aktuellen Datenbank erstellt, die die Tabelle mit den folgenden Informationen auffüllt, wenn ein DDL-Ereignis auf Datenbankebene auftritt:
Der Zeitpunkt des Ereignisses (mithilfe der GETDATE-Funktion).
Der Datenbankbenutzer, für dessen Sitzung das Ereignis aufgetreten ist (mithilfe der CURRENT_USER-Funktion).
Typ des Ereignisses
Die Transact-SQL Anweisung, die Bestandteil des Ereignisses war.
Erneut werden die letzten beiden Elemente erfasst, indem XQuery gegen die von EVENTDATA generierten xml Daten verwendet wird.
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
Hinweis
Um Ereignisdaten zurückzugeben, wird empfohlen, die XQuery-Methode value() anstelle der query() Methode zu verwenden. Die Methode query() gibt XML- und Ampersand-escapete Wagenrücklauf- und Zeilenvorschubinstanzen (CRLF) in der Ausgabe zurück, während die Methode value() CRLF-Instanzen in der Ausgabe unsichtbar macht.
Ein ähnliches DDL-Triggerbeispiel wird mit der AdventureWorks2012-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 AdventureWorks2012-Datenbank. Klicken Sie mit der rechten Maustaste auf "ddlDatabaseTriggerLog ", und wählen Sie "Script Database Trigger" aus. Standardmäßig ist der DDL-Trigger ddlDatabaseTriggerLog deaktiviert.