Microsoft® SQL Server® 2012 Bible

905

Chapter 36: Creating Triggers

36

and deleted virtual tables; DDL triggers have the EventData() function. This function

returns XML-formatted data about the event. The XML schema varies according to the type

of event captured. Note that parts of the XML schema are case-sensitive.

Using the EventData() function to populate an XML variable, the trigger can use

XQuery to investigate the values. Use the XQuery Value() method to extract the data

from the XML.

The XML schema for event data is published at http://schemas.microsoft.com/

sqlserver.

The following code example creates a DDL trigger that populates EventData() into an XML

variable and then selects from the variable to display the data:

CREATE TRIGGER DDLTrigger

ON DATABASE

FOR CREATE_TABLE

AS

Set NoCount ON

DECLARE @EventData XML =

EventData()

SELECT

@EventData.value

('data(/EVENT_INSTANCE/SchemaName)[1]','VARCHAR(50)') as

'Schema',

@EventData.value

('data(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(50)') as

'Object',

@EventData.value

('data(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(50)') as

'EventType'

With the DDL triggers in place, the next command creates a table, which fi res the trigger,

which examines EventData’s XML, and returns the values to the client:

CREATE TABLE Test (Col1 INT)

Result:

Schema Object EventType

------------------- ----------- ---------------------

dbo Test CREATE_TABLE

For more on XML and working with XQuery, see Chapter 14, “Using XML Data.”

c36.indd 905c36.indd 905 7/31/2012 10:03:10 AM7/31/2012 10:03:10 AM

http://www.it-ebooks.info