EVENTDATA (Transact-SQL)

EVENTDATA (Transact-SQL)

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (June 2009)

EVENTDATA (Transact-SQL)

Returns information about server or database events. EVENTDATA is called when an event notification fires, and the results are returned to the specified service broker. EVENTDATA can also be used inside the body of a DDL or logon trigger.

Topic link icon Transact-SQL Syntax Conventions

Syntax

EVENTDATA( )

Remarks

EVENTDATA returns data only when referenced directly inside of a DDL or logon trigger. EVENTDATA returns null if it is called by other routines, even if those routines are called by a DDL or logon trigger.

Data returned by EVENTDATA is not valid after a transaction that called EVENTDATA, either implicitly or explicitly, commits or is rolled back.

Caution:
EVENTDATA returns XML data. This data is sent to the client as Unicode that uses 2 bytes for each character. The following Unicode code points can be represented in the XML that is returned by EVENTDATA: `0x0009` `0x000A` `0x000D` `>= 0x0020 && <= 0xD7FF` `>= 0xE000 && <= 0xFFFD` Some characters that can appear in Transact-SQL identifiers and data are not expressible or permissible in XML. Characters or data that have code points not shown in the previous list are mapped to a question mark (?).

Caution:

EVENTDATA returns XML data. This data is sent to the client as Unicode that uses 2 bytes for each character. The following Unicode code points can be represented in the XML that is returned by EVENTDATA:

0x0009

0x000A

0x000D

>= 0x0020 && <= 0xD7FF

>= 0xE000 && <= 0xFFFD

Some characters that can appear in Transact-SQL identifiers and data are not expressible or permissible in XML. Characters or data that have code points not shown in the previous list are mapped to a question mark (?).

Schemas Returned

EVENTDATA returns a value of type xml. By default, the schema definition for all events is installed in the following directory: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd.

Alternatively, the event schema is published at the Microsoft SQL Server XML Schemas Web page.

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

The following example creates a DDL trigger to prevent new tables from being created in the database. The Transact-SQL statement that fires the trigger is captured by using XQuery against the XML data that is generated by EVENTDATA. For more information, see XQuery Language Reference (Database Engine).

Note:
When you query the `<TSQLCommand>` element by using Results to Grid in SQL Server Management Studio, line breaks in the command text do not appear. Use Results to Text instead.

USE AdventureWorks;
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:
When you want to return event data, we recommend that you 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

The following example creates a table to store information about all database level events, and populates the table with a DDL trigger. The event type and Transact-SQL statement are captured by using XQuery against the XML data generated by EVENTDATA.

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
--Drop the trigger.
DROP TRIGGER log
ON DATABASE
GO
--Drop table ddl_log.
DROP TABLE ddl_log
GO

C. Validating an event instance against part of the schema

The following example validates a DROP_TABLE event against the schema returned by EVENTDATA.

IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name='EventsXML')
DROP XML SCHEMA COLLECTION EventsXML ;
GO

DECLARE @x xml
SET @x = (SELECT * FROM OPENROWSET(BULK 'c:\Program Files\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd', single_clob) AS x)
CREATE XML SCHEMA COLLECTION EventsXML AS @x ;
GO

DECLARE @x xml(XSDEVENTS)
DECLARE @y xml
DECLARE @n nvarchar(max)

SELECT @y = EVENTDATA()
SELECT @n = @y
SELECT @n = REPLACE(@n, '<EVENT_INSTANCE', '<EVENT_INSTANCE xmlns=''http://schemas.microsoft.com/sqlserver/2006/eventdata'' xmlns:xsi=''http://www.w3.org/2001/XMLSchema-instance'' xsi:type=''EVENT_INSTANCE_DROP_TABLE''')

SELECT @x = @n -- This causes the validation.

Other Resources

Using the EVENTDATA Function
Designing DDL Triggers
Designing Event Notifications
Logon Triggers

Help and Information

Getting SQL Server 2008 Assistance