SQL Server СОЗДАТЬ ТРИГГЕР

В этом руководстве вы узнаете, как использовать оператор SQL Server CREATE TRIGGER для создания нового триггера.

Введение в оператор SQL Server CREATE TRIGGER

Оператор CREATE TRIGGER позволяет создать новый триггер, который автоматически срабатывает всякий раз, когда в таблице происходит такое событие, как INSERT, DELETE или UPDATE.

Ниже показан синтаксис оператора CREATE TRIGGER:

CREATE TRIGGER [schema_name.]trigger_name
ON table_name
AFTER  {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION]
AS
{sql_statements}

В этом синтаксисе:

• schema_name — имя схемы, к которой принадлежит новый триггер. Имя схемы необязательно.
• trigger_name — это определяемое пользователем имя нового триггера.
• table_name — это таблица, к которой применяется триггер.
• Событие указано в предложении AFTER. Событие может быть INSERT, UPDATE или DELETE. Один триггер может сработать в ответ на одно или несколько действий с таблицей.
• Параметр NOT FOR REPLICATION указывает SQL Server не активировать триггер, если изменение данных выполняется в рамках процесса репликации.
• sql_statements — это один или несколько операторов Transact-SQL, используемых для выполнения действий после возникновения события.

«Виртуальные» таблицы для триггеров: INSERTED и DELETED

SQL Server предоставляет две виртуальные таблицы, которые доступны специально для триггеров, называемые таблицами INSERTED и DELETED. SQL Server использует эти таблицы для сбора данных измененной строки до и после возникновения события.

В следующей таблице показано содержимое таблиц INSERTED и DELETED до и после каждого события:

событие DML	ВСТАВЛЕННАЯ таблица удерживает	УДАЛЕННАЯ таблица удерживает
ВСТАВЛЯТЬ	строки для вставки	пустой
ОБНОВЛЯТЬ	новые строки, измененные обновлением	существующие строки изменены обновлением
УДАЛИТЬ	пустой	строки для удаления

Пример SQL Server CREATE TRIGGER

Давайте рассмотрим пример создания нового триггера. Для демонстрации мы будем использовать таблицу production.products из примера базы данных.

1) Создать таблицу для регистрации изменений

Следующий оператор создает таблицу с именем production.product_audits для записи информации при возникновении события INSERT или DELETE в таблице production.products:

CREATE TABLE production.product_audits(
    change_id INT IDENTITY PRIMARY KEY,
    product_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    brand_id INT NOT NULL,
    category_id INT NOT NULL,
    model_year SMALLINT NOT NULL,
    list_price DEC(10,2) NOT NULL,
    updated_at DATETIME NOT NULL,
    operation CHAR(3) NOT NULL,
    CHECK(operation = 'INS' or operation='DEL')
);

Таблица production.product_audits содержит все столбцы из таблицы production.products. Кроме того, в ней есть еще несколько столбцов для записи изменений, например, updated_at, operation и change_id.

2) Создание триггера after DML

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

CREATE TRIGGER production.trg_product_audit

Далее в предложении ON указывается имя таблицы, которую триггер будет активировать при возникновении события:

ON production.products

Затем в предложении AFTER перечислите одно или несколько событий, которые вызовут триггер:

AFTER INSERT, DELETE

Тело триггера начинается с ключевого слова AS:

AS
BEGIN

После этого внутри тела триггера вы устанавливаете SET NOCOUNT в значение ON, чтобы подавить возврат сообщений о количестве затронутых строк при каждом срабатывании триггера.

SET NOCOUNT ON;

Триггер будет вставлять строку в таблицу production.product_audits всякий раз, когда строка вставляется или удаляется из таблицы production.products. Данные для вставки подаются из таблиц INSERTED и DELETED через оператор UNION ALL :

INSERT INTO
    production.product_audits
       (
            product_id,
            product_name,
            brand_id,
            category_id,
            model_year,
            list_price,
            updated_at,
            operation
        )
SELECT
    i.product_id,
    product_name,
    brand_id,
    category_id,
    model_year,
    i.list_price,
    GETDATE(),
    'INS'
FROM
    inserted AS i
UNION ALL
    SELECT
        d.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        d.list_price,
        getdate(),
        'DEL'
    FROM
        deleted AS d;

Ниже приведены все части, объединенные вместе:

CREATE TRIGGER production.trg_product_audit
ON production.products
AFTER INSERT, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO production.product_audits(
        product_id, 
        product_name,
        brand_id,
        category_id,
        model_year,
        list_price, 
        updated_at, 
        operation
    )
    SELECT
        i.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        i.list_price,
        GETDATE(),
        'INS'
    FROM
        inserted i
    UNION ALL
    SELECT
        d.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        d.list_price,
        GETDATE(),
        'DEL'
    FROM
        deleted d;
END

Наконец, вы выполняете весь оператор для создания триггера. После создания триггера вы можете найти его в папке триггеров таблицы, как показано на следующем рисунке:

3) Тестирование триггера

Следующий оператор вставляет новую строку в таблицу production.products:

INSERT INTO production.products(
    product_name, 
    brand_id, 
    category_id, 
    model_year, 
    list_price
)
VALUES(
    'Test product',
    1,
    1,
    2018,
    599
);

Из-за события INSERT сработал триггер production.trg_product_audit таблицы production.products.

Давайте рассмотрим содержимое таблицы production.product_audits:

SELECT 
    * 
FROM 
    production.product_audits;

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

Следующий оператор удаляет строку из таблицы production.products:

DELETE FROM 
    production.products
WHERE 
    product_id = 322;

Как и ожидалось, триггер сработал и вставил удаленную строку в таблицу production.product_audits:

SELECT 
    * 
FROM 
    production.product_audits;

На следующем рисунке показан результат:

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