It’s Dangerous to Use XML in Triggers

There are a few examples of DDL triggers available on the web, which use XML to retrieve the EventData() of the trigger.  EventData() retrieves an XML object containing properties about the event.  For example, Books Online describes ALTER TABLE event as:

<EVENT_INSTANCE>
    <EventType>type</EventType>
    <PostTime>date-time</PostTime>
    <SPID>spid</SPID>
    <ServerName>name</ServerName>
    <LoginName>name</LoginName>
    <UserName>name</UserName>
    <DatabaseName>name</DatabaseName>
    <SchemaName>name</SchemaName>
    <ObjectName>name</ObjectName>
    <ObjectType>type</ObjectType>
    <TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>

By using XML query methods, we can extract data out of this XML structure.  For example:

DECLARE @eventdata xml
SELECT @eventdata = EventData()
SELECT @eventdata.value('(/EVENT_INSTANCE/EventType)[1]'
    , 'nvarchar(100)')

This works well in most cases, but there is one little gotcha that occasionally springs up.  To use the XML methods, you need to have a specific set of SET options applied to your session.  If you have a DDL trigger that uses XML methods to parse out the EventData, and you have incompatible SET options set in your session, your trigger will generate an error, and rollback the entire transaction.

Now, although you can change set options inside a trigger, and they will automatically roll back to their previous values once the trigger ends, you may not be aware that you need to do this, and once you deploy the trigger into production, you start getting errors from some clients.  The easiest way around this is to not use the XML methods in your trigger, and just retrieve EventData() and store it in an XML column.  You can then use a separate process which has the correct session options set to process the XML in greater detail.

The alternative option is to ensure you set the set options correctly in your trigger.  The correct settings to use are:

SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET NUMERIC_ROUNDABORT OFF
SET ANSI_WARNINGS ON

If you change any of these and attempt to run an XML method, you’ll get an error message telling you which option needs changing:

DECLARE @xml xml
SET @xml = '<Root><Product ID="1" Name="Widget, 25cm">
</Product></Root>'
SELECT @xml.query('/Root/Product')

Msg 1934, Level 16, State 1, Line 4
SELECT failed because the following SET options have incorrect settings: ‘ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or
query notifications and/or xml data type methods.

Applying a DDL trigger into production without first checking that the session values are compatible could be disastrous!

Special thanks to Jon Kehayias for technical reviewing of this post.