Share via

Using the EVENTDATA Function

  • Article

Information about an event that fires a DDL trigger is captured by using the EVENTDATA function. This function returns an xml value. The XML schema includes information about the following:

  • The time of the event.
  • The System Process ID (SPID) of the connection when the trigger executed.
  • The type of event that fired the trigger.

Depending on the event type, the schema then includes additional information such as the database in which the event occurred, the object against which the event occurred, and the Transact-SQL statement of the event. For more information, see EVENTDATA (Transact-SQL).

For example, the following DDL trigger is created in the AdventureWorks sample database:

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
;

The following CREATE TABLE statement is then run:

    CREATE TABLE NewTable (Column1 int);

The EVENTDATA() statement in the DDL trigger captures the text of the CREATE TABLE statement that is not allowed. This is achieved by using an XQuery statement against the xml data that is generated by EVENTDATA and retrieving the <CommandText> element. For more information, see XQuery Against the xml Data Type.

Warning

EVENTDATA captures the data of CREATE_SCHEMA events as well as the <schema_element> of the corresponding CREATE SCHEMA definition, if any exists. Additionally, EVENTDATA recognizes the <schema_element> definition as a separate event. Therefore, a DDL trigger created on both a CREATE_SCHEMA event, and an event represented by the <schema_element> of the CREATE SCHEMA definition, may return the same event data twice, such as the TSQLCommand data. For example, consider a DDL trigger that is created on both the CREATE_SCHEMA and CREATE_TABLE events and the following batch is run: CREATE SCHEMA s CREATE TABLE t1 (col1 int) If the application retrieves the TSQLCommand data of the CREATE_TABLE event, be aware that this data may appear two times: once when the CREATE_SCHEMA event occurs, and again when the CREATE_TABLE event occurs. Avoid creating DDL triggers on both the CREATE_SCHEMA events and the <schema_element> texts of any corresponding CREATE SCHEMA definitions, or build logic into your application so that the same event is not processed twice.

Example

You can use the EVENTDATA function to create a log of events. In the following example, a table is created to store event information. A DDL trigger is then created on the current database that populates the table with the following information whenever any database-level DDL event occurs:

  • The time of the event (using the GETDATE function).
  • The database user against whose session the event occurred (using the CURRENT_USER function).
  • The type of the event.
  • The Transact-SQL statement that comprised the event.

Again, the last two items are captured by using XQuery against the xml data that is 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

Note

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 (CRLF) instances in the output, while the value() method renders CRLF instances invisible in the output.

A similar DDL trigger example is provided with the AdventureWorks sample database. To obtain the example, locate the Database Triggers folder by using SQL Server Management Studio. This folder is located under the Programmability folder of the AdventureWorks database. Right-click ddlDatabseTriggerLog and select Script Database Trigger as. By default, DDL trigger ddlDatabseTriggerLog is disabled.

See Also

Concepts

Designing DDL Triggers

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

5 December 2005
New content:
  • Added a Caution note about creating DDL triggers on both the CREATE_SCHEMA events and the <schema_element> texts of any corresponding CREATE SCHEMA definitions.