Single out specific parts of EVENTDATA for use in a DDL Trigger?

Question

Current Trigger is as follows:

CREATE TRIGGER TestTrigger
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
SET NOCOUNT ON;
    DECLARE @FULL_STATEMENT SYSNAME
    SELECT @FULL_STATEMENT = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
    print('The following SQL statement was executed:')
    print('')
    print (@FULL_STATEMENT)
    print('')
    print('Next step is to work out how to single out the added column name')
    ROLLBACK
END

I then run the following SQL Statement:

USE [a_Database]
ALTER TABLE dbo.TABLE
ADD TestColumn varchar(50)

And it outputs the following:

The following SQL statement was executed:

ALTER TABLE dbo.TABLE

ADD TestColumn varchar(50)

Next step is to work out how to single out the added column name

Msg 3609, Level 16, State 2, Line 2

The transaction ended in the trigger. The batch has been aborted.

The end goal is to single out"TestColumn" to make sure that it doesn't contain specific characters that can break internal programs. "TestColumn" would be a valid name, but "Test.Column" wouldn't, for example.

What would be the best way to single it out and pass it to a new variable ("@ColumnName"?) to check for unwanted characters?

Answer 1

You need to bear in mind that an alter statement can add more than one column. If you look at the XML generated for an alter column statement you will get a better idea:

ALTER TABLE dbo.EventTest ADD NewColumn1 INT, NewColumn2 INT;

----------------------------
<EVENT_INSTANCE>
  <EventType>ALTER_TABLE</EventType>
  <PostTime>2015-06-30T14:28:30.790</PostTime>
  <SPID>67</SPID>
  <ServerName>XXXXXX</ServerName>
  <LoginName>XXXXXX</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>XXXXXX</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>EventTest</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <AlterTableActionList>
    <Create>
      <Columns>
        <Name>NewColumn1</Name>
        <Name>NewColumn2</Name>
      </Columns>
    </Create>
  </AlterTableActionList>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>ALTER TABLE dbo.EventTest ADD NewColumn1 INT, NewColumn1 INT;</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

So since you can add multiple columns, you need to check multiple columns, to get your columns lists you can use:

SELECT  ColumnName = Cols.value('.', 'SYSNAME')
FROM    EVENTDATA().nodes('EVENT_INSTANCE/AlterTableActionList/Create/Columns') x (Cols);

Then you could simply use EXISTS to check against a list of reserved names etc, and check for unwanted characters:

IF EXISTS
(   SELECT  1
    FROM    (   SELECT  ColumnName = Cols.value('.', 'SYSNAME')
                FROM    EVENTDATA().nodes('EVENT_INSTANCE/AlterTableActionList/Create/Columns') x (Cols)
            ) AS t
    WHERE   PATINDEX('%[.@!-]%', t.ColumnName) > 0  -- USE PATTERN MATCH TO CHECK FOR UNWANTED CHARACTERS
    OR      EXISTS 
            (   SELECT  1
                FROM    dbo.ReservedColumnNames AS rc
                WHERE   rc.Name = t.ColumnName
            )
)
BEGIN
    RAISERROR(...)
    ROLLBACK;
END

Another thing to consider is that sp_rename will fire a different event, and you probably want to track this too. The XML here looks like:

<EVENT_INSTANCE>
  <EventType>RENAME</EventType>
    ....
  <SchemaName>dbo</SchemaName>
  <ObjectName>NewColumn</ObjectName>
  <ObjectType>COLUMN</ObjectType>
  <TargetObjectName>EventTest</TargetObjectName>
  <TargetObjectType>TABLE</TargetObjectType>
  <NewObjectName>NewColumn2</NewObjectName>
  <Parameters>
    <Param>dbo.EventTest.NewColumn</Param>
    <Param>NewColumn2</Param>
    <Param>COLUMN</Param>
  </Parameters>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>EXECUTE SP_RENAME 'dbo.EventTest.NewColumn', 'NewColumn2', 'COLUMN';</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

So here you would need to track it slightly differently, since it can only be one column at a time, you don't need to worry about nodes:

SELECT  EVENTDATA().value('EVENT_INSTANCE[1]/NewObjectName[1]', 'SYSNAME')
WHERE   EVENTDATA().value('EVENT_INSTANCE[1]/EventType[1]', 'VARCHAR(13)') = 'RENAME'
AND     EVENTDATA().value('EVENT_INSTANCE[1]/ObjectType[1]', 'VARCHAR(13)') = 'COLUMN';