EVENTDATA (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
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
Syntax
EVENTDATA( )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Remarks
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
- called
EVENTDATA
explicitly - called
EVENTDATA
implicitly - commits
- is rolled back
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.
Schemas Returned
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\Tools\Binn\schemas\sqlserver\2006\11\events\events.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
.
Examples
A. Querying event data in a DDL trigger
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 AdventureWorks2022;
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.
B. Creating a log table with event data in a DDL trigger
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 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
--Drop the trigger.
DROP TRIGGER log
ON DATABASE;
GO
--Drop table ddl_log.
DROP TABLE ddl_log;
GO
See Also
Use the EVENTDATA Function
DDL Triggers
Event Notifications
Logon Triggers