Problem Reading EVENT_INSTANCE in DDL Trigger

  • 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!

  • 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

  • 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!

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

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