Problem Reading EVENT_INSTANCE in DDL Trigger

Question

Post reply

Problem Reading EVENT_INSTANCE in DDL Trigger

Jason Bunn

SSCommitted

Points: 1713
More actions
March 31, 2011 at 1:50 pm

#104057

Hello,
I'd like to determine the columns that were affected by a DDL change in a DDL trigger. I get to the point of saving the EVENT_INSTANCE data in an XML variable. The returned data, for the example of dropping two columns, is
<EVENT_INSTANCE>
<EventType>ALTER_TABLE</EventType>
<PostTime>2011-03-31T14:09:57.840</PostTime>
<SPID>57</SPID>
<ServerName>XXXXXX</ServerName>
<LoginName>XXXXXX</LoginName>
<UserName>dbo</UserName>
<DatabaseName>testDB</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>testTable</ObjectName>
<ObjectType>TABLE</ObjectType>
<AlterTableActionList>
<Drop>
<Columns>
<Name>col5</Name>
<Name>col6</Name>
</Columns>
</Drop>
</AlterTableActionList>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>alter table testTable drop column col5, col6</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
But, from there, I can't seem to construct an XML query that will put these two names into a table. I tried:
SELECT EVENTDATA().value('(/EVENT_INSTANCE/AlterTableActionList/Drop/Columns)[1]','nvarchar(max)') as Result
where EVENTDATA().exist('(/EVENT_INSTANCE/AlterTableActionList/Drop/Columns)[1]') = 1
but it returns
Result
col5col6
I don't need the names slammed together in one output. I'd like to be able to have two rows, one with "col5" and one with "col6".
Any ideas would be appreciated. Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

Try:

SELECT x.c.value('text()[1]','nvarchar(max)') as Result

from EVENTDATA().nodes ('/EVENT_INSTANCE/AlterTableActionList/Drop/Columns/Name') x (c)

Russel Loski, MCSE Business Intelligence, Data Platform

Jason Bunn SSCommitted Points: 1713 More actions · Answer 2

Russel,

That resulting in a syntax error around the EVENTDATA().node construct.

We were able to get around this by switching to an openxml command:

DECLARE @data XML

SET @data = EVENTDATA()

declare @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @data

SELECT textFROM OPENXML

(@idoc, '/EVENT_INSTANCE/AlterTableActionList/Drop/Columns/Name',2)

where [text] is not null

Thanks!