This function returns information about server or database events. When an event notification fires, and the specified service broker receives the results, EVENTDATA
is called. A DDL or logon trigger also support internal use of EVENTDATA
.
Transact-SQL Syntax Conventions
EVENTDATA( )
EVENTDATA
returns data only when referenced directly inside of a DDL or logon trigger. EVENTDATA
returns null if other routines call it, even if a DDL or logon trigger calls those routines.
Data returned by EVENTDATA
is invalid after a transaction that
EVENTDATA
explicitlyEVENTDATA
implicitly[!CAUTION]
EVENTDATA
returns XML data, sent to the client as Unicode that uses 2 bytes for each character.EVENTDATA
returns XML that can represent these Unicode code points:
0x0009
0x000A
0x000D
>= 0x0020 && <= 0xD7FF
>= 0xE000 && <= 0xFFFD
XML cannot express, and will not permit, some characters that can appear in Transact\-SQL identifiers and data. Characters or data that have code points not shown in the previous list are mapped to a question mark (?).
Passwords do not display when CREATE LOGIN
or ALTER LOGIN
statements execute. This protects login security.
EVENTDATA returns a value of data type xml. By default, the schema definition for all events installs in this directory: C:\Program Files\Microsoft SQL Server\\*nnn*\ \2006\11.xsd.
The Microsoft SQL Server XML Schemas web page also has the event schema.
To extract the schema for any particular event, search the schema for the Complex Type EVENT_INSTANCE_<event_type>
. For example, to extract the schema for the DROP_TABLE
event, search the schema for EVENT_INSTANCE_DROP_TABLE
.
This example creates a DDL trigger that prevents creation of new database tables. Use of XQuery against the XML data generated by EVENTDATA
captures the Transact\-SQL statement that fires the trigger. See XQuery Language Reference (SQL Server) for more information.
[!NOTE]
When using Results to Grid in SQL Server Management Studio to query the<TSQLCommand>
element, line breaks in the command text do not appear. Use Results to Text instead.
USE AdventureWorks2012;
GO
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
;
GO
--Test the trigger.
CREATE TABLE NewTable (Column1 int);
GO
--Drop the trigger.
DROP TRIGGER safety
ON DATABASE;
GO
[!NOTE]
To return event data, use the XQuery value() method instead of the query() method. The query() method returns XML and ampersand-escaped carriage return and line feed (CR/LF) instances in the output, while the value() method renders CR/LF instances invisible in the output.
This example creates a table for information storage about all database level events, and populates that table with a DDL trigger. Use of XQuery against the XML data generated by EVENTDATA
captures the event type and the Transact\-SQL statement.
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
--Drop the trigger.
DROP TRIGGER log
ON DATABASE;
GO
--Drop table ddl_log.
DROP TABLE ddl_log;
GO
Use the EVENTDATA Function
DDL Triggers
Event Notifications
Logon Triggers