Skip to content

Latest commit

 

History

History
149 lines (127 loc) · 5.77 KB

eventdata-transact-sql.md

File metadata and controls

149 lines (127 loc) · 5.77 KB
titledescriptionauthorms.authorms.datems.servicems.subservicems.topicf1_keywordshelpviewer_keywordsdev_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( )

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 CREATETRIGGERsafetyON 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. CREATETABLENewTable (Column1 INT); GO --Drop the trigger. DROPTRIGGER 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 CREATETABLEddl_log (PostTime DATETIME, DB_User NVARCHAR(100), Event NVARCHAR(100), TSQL NVARCHAR(2000)); GO CREATETRIGGERlogON 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. CREATETABLETestTable (a INT); DROPTABLE TestTable ; GO SELECT*FROM ddl_log ; GO --Drop the trigger. DROPTRIGGER log ON DATABASE; GO --Drop table ddl_log. DROPTABLE ddl_log; GO

See Also

Use the EVENTDATA Function
DDL Triggers
Event Notifications
Logon Triggers

close