Триггер DDL SQL Server

В этом руководстве вы узнаете, как использовать триггер языка определения данных(DDL) SQL Server для отслеживания изменений, вносимых в объекты базы данных.

Введение в триггеры DDL SQL Server

Триггеры DDL SQL Server реагируют на события сервера или базы данных, а не на изменения данных таблицы. Эти события создаются оператором Transact-SQL, который обычно начинается с одного из следующих ключевых слов CREATE, ALTER, DROP, GRANT, DENY, REVOKE или UPDATE STATISTICS.

Например, вы можете написать триггер DDL для регистрации каждого случая, когда пользователь выполняет оператор CREATE TABLE или ALTER TABLE.

Триггеры DDL полезны в следующих случаях:

• Запишите изменения в схему базы данных.
• Предотвратить некоторые конкретные изменения в схеме базы данных.
• Реагировать на изменение схемы базы данных.

Ниже показан синтаксис создания триггера DDL:

CREATE TRIGGER trigger_name
ON { DATABASE |  ALL SERVER}
[WITH ddl_trigger_option]
FOR {event_type | event_group }
AS {sql_statement}

имя_триггера

Укажите пользовательское имя триггера после ключевых слов CREATE TRIGGER. Обратите внимание, что вам не нужно указывать схему для триггера DDL, поскольку он не связан с фактической таблицей или представлением базы данных.

БАЗА ДАННЫХ | ВСЕ СЕРВЕРА

Используйте DATABASE, если триггер реагирует на события, относящиеся к базе данных, или ALL SERVER, если триггер реагирует на события, относящиеся к серверу.

ddl_trigger_option

Параметр ddl_trigger_option определяет условие ENCRYPTION и/или EXECUTE AS. ENCRYPTION шифрует определение триггера. EXECUTE AS определяет контекст безопасности, в котором выполняется триггер.

тип_события | группа_событий

event_type указывает событие DDL, которое вызывает срабатывание триггера, например, CREATE_TABLE, ALTER_TABLE и т. д.

Event_group — это группа событий event_type, например DDL_TABLE_EVENTS.

Триггер может подписываться на одно или несколько событий или групп событий.

Пример создания триггера DDL SQL Server

Предположим, вы хотите зафиксировать все изменения, внесенные в индекс базы данных, чтобы иметь возможность лучше контролировать производительность сервера базы данных, связанную с этими изменениями индекса.

Сначала создайте новую таблицу с именем index_logs для регистрации изменений индекса:

CREATE TABLE index_logs(
    log_id INT IDENTITY PRIMARY KEY,
    event_data XML NOT NULL,
    changed_by SYSNAME NOT NULL
);
GO

Далее создайте триггер DDL для отслеживания изменений индекса и вставьте данные событий в таблицу index_logs:

CREATE TRIGGER trg_index_changes
ON DATABASE
FOR 
    CREATE_INDEX,
    ALTER_INDEX, 
    DROP_INDEX
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO index_logs(
        event_data,
        changed_by
    )
    VALUES(
        EVENTDATA(),
        USER
    );
END;
GO

В теле триггера мы использовали функцию EVENTDATA(), которая возвращает информацию о событиях сервера или базы данных. Функция доступна только внутри DDL или триггера входа.

Затем создайте индексы для столбцов first_name и last_name таблицы sales.customers:

CREATE NONCLUSTERED INDEX nidx_fname
ON sales.customers(first_name);
GO
CREATE NONCLUSTERED INDEX nidx_lname
ON sales.customers(last_name);
GO

После этого запросите данные из таблицы index_changes, чтобы проверить, было ли событие создания индекса правильно зафиксировано триггером:

SELECT 
    *
FROM
    index_logs;

Вот что получилось:

Если щелкнуть ячейку столбца event_data, можно просмотреть XML-данные события следующим образом:

В этом руководстве вы узнали, как создать триггер DDL SQL Server, который реагирует на одно или несколько событий DDL.