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.

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 trigger.

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

Schemas Returned

EVENTDATA returns a value of type xml. The schema returned for each event is comprised of elements nested inside an <EVENT_INSTANCE> element. To obtain the schema returned for an event, use Index or Search to locate the topic for the event in SQL Server Books Online. The property names listed for the event correspond to elements returned in the schema, with the following exceptions:

• EVENTDATA returns a <ServerName> element, instead of <SQLInstance> and <ComputerName>.
• EVENTDATA returns an additional <EventType> element.

For example, the ALTER_TABLE event returns the following schema:

<EVENT_INSTANCE>
    <EventType>type</EventType>
    <PostTime>date-time</PostTime>
    <SPID>spid</SPID>
    <ServerName>name</ServerName>
    <LoginName>name</LoginName>
    <UserName>name</UserName>
    <DatabaseName>name</DatabaseName>
    <SchemaName>name</SchemaName>
    <ObjectName>name</ObjectName>
    <ObjectType>type</ObjectType>
    <TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>

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 Against the xml Data Type.

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

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

See Also

Other Resources

Using the EVENTDATA Function
Designing DDL Triggers
Designing Event Notifications

Help and Information

Getting SQL Server 2005 Assistance

Change History