EVENTDATA (Transact-SQL)

Articol
09/03/2024

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( )

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

Partajați prin