Выделите определенные части EVENTDATA для использования в триггере DDL?

Question

Текущий триггер выглядит следующим образом:

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

Затем я запускаю следующую инструкцию SQL:

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

И это выводит следующее:

Следующая инструкция SQL была выполнена:

ALTER TABLE dbo.TABLE

ДОБАВИТЬ TestColumn varchar(50)

Следующий шаг - выяснить, как выделить имя добавляемого столбца.

Msg 3609, уровень 16, состояние 2, строка 2

Транзакция завершилась в триггере. Пакет был прерван.

Конечная цель состоит в том, чтобы выделить "TestColumn", чтобы убедиться, что он не содержит определенных символов, которые могут сломать внутренние программы. "TestColumn" будет допустимым именем, но "Test.Column" не будет, например.

Каков наилучший способ выделить его и передать в новую переменную ("@ColumnName"?) Для проверки на наличие нежелательных символов?

Answer 1

Вы должны иметь в виду, что оператор alter может добавить более одного столбца. Если вы посмотрите на XML, сгенерированный для оператора alter column, вы получите лучшее представление:

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>

Так как вы можете добавить несколько столбцов, вам нужно проверить несколько столбцов, чтобы получить списки столбцов, которые вы можете использовать:

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

Тогда вы могли бы просто использовать EXISTS проверить список зарезервированных имен и т. д. и проверить наличие нежелательных символов:

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

Еще одна вещь, которую следует учитывать, это то, что sp_rename будет запускать другое событие, и вы, вероятно, хотите отслеживать это тоже. XML здесь выглядит так:

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

Так что здесь вам нужно отслеживать его немного по-другому, поскольку он может быть только одним столбцом за раз, вам не нужно беспокоиться об узлах:

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';