Skip to content

Latest commit

 

History

History
151 lines (128 loc) · 5.89 KB

eventdata-transact-sql.md

File metadata and controls

151 lines (128 loc) · 5.89 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
EVENTDATA (Transact-SQL)
EVENTDATA (Transact-SQL)
markingmyname
maghan
03/14/2017
sql
t-sql
reference
EVENTDATA
fn_event_data
EVENTDATA_TSQL
fn_event_data_TSQL
server instance event data [SQL Server]
event notifications [SQL Server], event status
events [SQL Server], status information
EVENTDATA function
status information [SQL Server], events
DDL triggers, returning event data
TSQL

EVENTDATA (Transact-SQL)

[!INCLUDE 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.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

EVENTDATA( )

[!INCLUDEsql-server-tsql-previous-offline-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 [!INCLUDEtsql] 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: [!INCLUDEssInstallPath]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 [!INCLUDEtsql] statement that fires the trigger. See XQuery Language Reference (SQL Server) for more information.

Note

When using Results to Grid in [!INCLUDEssManStudioFull] 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 [!INCLUDEtsql] 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