В предыдущем совете мы обсудили, как использовать триггеры DDL SQL Server для отслеживания всех изменений в базе данных. Это вызвало много обсуждений и вопросов о способах сделать предоставленный триггер DDL более полезным. В этом последующем совете мы рассмотрим некоторые из этих вопросов и предоставим решения для расширения функциональности триггера DDL.

Предоставление разрешений для триггера DDL

Один из заданных вопросов был о возможности сбоя триггера DDL, если у пользователя нет разрешений на вставку в таблицу аудита. Это обоснованная озабоченность, так как триггер DDL выполняется в контексте вызывающего. Чтобы гарантировать, что триггер DDL никогда не сбоит, одно из решений – предоставить права на чтение/запись роли public для таблицы аудита. Это можно сделать с помощью следующей команды:

GRANT SELECT, INSERT ON OBJECT::dbo.DDLEvents TO [public];

Конечно, вы также можете предоставить более детализированные разрешения для конкретных логинов/пользователей, если это необходимо.

Получение последней и предыдущей версий процедуры

Другой заданный вопрос был о том, как получить как последнюю версию процедуры, так и версию, которая существовала непосредственно перед ней. Исходный запрос, предоставленный в предыдущем совете, возвращал только исходную и последнюю версии, игнорируя любые изменения между ними. Чтобы решить эту проблему, мы можем использовать общую таблицу выражений (CTE) и функцию ROW_NUMBER() для получения нужной информации. Вот пример:

;WITH e AS
(
    SELECT
        EventDate,
        DatabaseName,
        SchemaName,
        ObjectName,
        LoginName,
        EventDDL,
        rn = ROW_NUMBER() OVER 
        (
            PARTITION BY DatabaseName, SchemaName, ObjectName
            ORDER BY     EventDate DESC
        )        
    FROM
        AuditDB.dbo.DDLEvents
)
SELECT cur.DatabaseName, 
  cur.SchemaName, 
  cur.ObjectName,
  cur.EventDate, 
  cur.LoginName,
  [PreviousVersion] = prev.EventDDL, 
  [CurrentVersion]  = cur.EventDDL
FROM e AS cur
INNER JOIN e AS prev
ON cur.DatabaseName = prev.DatabaseName
 AND cur.SchemaName = prev.SchemaName
 AND cur.ObjectName = prev.ObjectName
WHERE cur.rn = 1
AND prev.rn = 2;

Если вы используете SQL Server 2012 или более позднюю версию, вы можете достичь того же результата, используя функцию LAG():

;WITH e AS
(
    SELECT
        DatabaseName,
        SchemaName,
        ObjectName,
        EventDate,
        LoginName,
        PreviousVersion = LAG(EventDDL, 1) OVER 
        (
          PARTITION BY DatabaseName, SchemaName, ObjectName 
          ORDER BY     EventDate
        ),
        CurrentVersion = EventDDL,
        rn = ROW_NUMBER() OVER 
        (
          PARTITION BY DatabaseName, SchemaName, ObjectName
          ORDER BY     EventDate DESC
        )        
    FROM
        AuditDB.dbo.DDLEvents
)
SELECT DatabaseName, 
  SchemaName, 
  ObjectName,
  EventDate,
  LoginName, 
  [PreviousVersion], 
  [CurrentVersion]
FROM e WHERE rn = 1 AND PreviousVersion IS NOT NULL;

Создание зеркальной таблицы с другим именем

Один из заданных вопросов был о том, как создать зеркальную таблицу с той же структурой, но немного другим именем. Это может быть полезно для архивных целей или как пустая таблица для переключения разделов. Один из подходов – использовать отдельный триггер DDL, который заменяет имя таблицы в исходной команде T-SQL. Вот пример:

CREATE TRIGGER DDL_CopyNewTable
    ON DATABASE
    FOR CREATE_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EventData XML = EVENTDATA();
 DECLARE
   @sql NVARCHAR(MAX) = @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
   @t   SYSNAME       = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)');
 SELECT @sql = REPLACE(@sql, @t, N'Prefix' + @t);
 EXEC sp_executesql @sql;
END
GO

Важно отметить, что этот подход предполагает простую структуру таблицы без ограничений внешнего ключа или других внешних зависимостей. Использование REPLACE() также делает некоторые предположения о используемом имени таблицы. Если возникнут ошибки, такие как явно указанные ограничения с именем без имени таблицы или таблица с префиксом уже существует, триггер откатит оператор CREATE TABLE. Обертывание триггера в блок TRY/CATCH в этом случае не помогает.

В качестве альтернативы вы можете использовать оператор SELECT INTO для создания зеркальной таблицы:

ALTER TRIGGER DDL_CopyNewTable
    ON DATABASE
    FOR CREATE_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EventData XML = EVENTDATA();
 DECLARE @s SYSNAME = @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),
         @t SYSNAME = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)');
 DECLARE @sql NVARCHAR(MAX) = N'SELECT * INTO '
   + QUOTENAME(@s) + '.' + QUOTENAME(N'Prefix' + @t)
   + ' FROM ' + QUOTENAME(@s) + '.' + QUOTENAME(@t);
    PRINT @sql;
 EXEC sp_executesql @sql;
END
GO

Следует отметить, что этот подход может не учитывать индексы или ограничения, добавленные после исходного оператора CREATE TABLE. Он выполняет только исходный оператор CREATE TABLE. Если вы хотите зарегистрировать второй оператор CREATE TABLE, вы можете объединить логику в один триггер.

Заключение

Предыдущий совет вызвал живую беседу и возникло несколько вопросов. В этом последующем совете мы рассмотрели некоторые из этих вопросов и предоставили решения для расширения функциональности триггеров DDL SQL Server. Мы надеемся, что эти решения будут полезными в вашей работе по управлению базами данных.