Microsoft SQL Server 2012 Bible

Developing DDL Triggers

In some way, a DDL trigger is easier to write than a DML trigger. Because DDL triggers always fire for a single event, they avoid dealing with multiple rows involving the base table the inserted and deleted virtual tables exposed in DML triggers. The complexity of the DDL trigger results from the fact that the data about the event is in XML.

EventData()

DDL triggers can respond to so many different events that they need some method of capturing data about the event that caused them to fire. DML triggers have the inserted 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 ...