Применение DML-триггеров

Создание и оптимизация триггеров в SQL Server

Florin Angelescu

Instructor

Зачем использовать DML-триггеры?

Инициирование действий при изменении данных
Предотвращение манипуляций с данными
Отслеживание изменений данных или объектов базы данных
Аудит пользователей и безопасность базы данных

Выбор между AFTER и INSTEAD OF

CREATE TRIGGER MyFirstAfterTrigger
ON Table1
-- Triggered after 
-- the firing event (UPDATE)
AFTER UPDATE
AS
{trigger_actions_section};

CREATE TRIGGER MyFirstInsteadOfTrigger
ON Table2
-- Triggered instead of 
-- the firing event (UPDATE)
INSTEAD OF UPDATE
AS
{trigger_actions_section};

Выбор между AFTER и INSTEAD OF

Триггер AFTER

Исходное событие активирует триггер

Исходное событие выполняется

Выполняются действия триггера

Принцип работы триггера AFTER

Триггер INSTEAD OF

Исходное событие активирует триггер

Исходное событие не выполняется

Выполняются действия триггера

Принцип работы триггера INSTEAD OF

Пример использования триггера AFTER

Данные вставляются в таблицу продаж
Запуск процедуры очистки данных
Формирование отчёта по результатам процедуры
Уведомление администратора базы данных

CREATE TRIGGER SalesNewInfoTrigger
ON Sales
AFTER INSERT
AS

EXEC sp_cleansing @Table = 'Sales';

EXEC sp_generateSalesReport;

EXEC sp_sendnotification;

Пример использования триггера INSTEAD OF

| Brand | Model    | Power | Stock |
|-------|----------|-------|-------|
| Ecco  | Standard | 30W   | 30    |
| Miry  | Buma     | 45W   | 0     |
| Lume  | Ultra    | 50W   | 0     |

Мощность некоторых моделей изменяется

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

Пример использования триггера INSTEAD OF

| Brand | Model    | Power | Stock |
|-------|----------|-------|-------|
| Ecco  | Standard | 30W   | 30    |
| Miry  | Buma     | 50W   | 100   |
| Lume  | Ultra    | 52W   | 100   |

Мощность некоторых моделей изменяется
Обновлять только товары без остатка на складе

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

IF EXISTS (SELECT * FROM Bulbs AS b
INNER JOIN inserted AS i
           ON b.Brand = i.Brand
          AND b.Model = i.Model
WHERE b.Stock = 0)
BEGIN
    UPDATE b
    SET b.Power = i.Power,
        b.Stock = i.Stock
    FROM Bulbs AS b
    INNER JOIN inserted AS i
                ON b.Brand = i.Brand
                  AND b.Model = i.Model
    WHERE b.Stock = 0
END

Пример использования триггера INSTEAD OF

| Brand | Model    | Power | Stock |
|-------|----------|-------|-------|
| Ecco  | Standard | 30W   | 30    |
| Miry  | Buma     | 50W   | 100   |
| Lume  | Ultra    | 52W   | 100   |
| Ecco  | Standard | 35W   | 100   |

Мощность некоторых моделей изменяется
Обновлять только товары без остатка на складе
Добавлять новые строки для товаров с ненулевым остатком

-- First part was truncated for spacing reasons
IF EXISTS (SELECT * FROM Bulbs AS b
INNER JOIN inserted AS i
           ON b.Brand = i.Brand
          AND b.Model = i.Model
WHERE b.Stock = 0)
BEGIN
    UPDATE b
    SET b.Power = i.Power,
        b.Stock = i.Stock
    FROM Bulbs AS b
    INNER JOIN inserted AS i
                ON b.Brand = i.Brand
                  AND b.Model = i.Model
    WHERE b.Stock = 0
END
ELSE
    INSERT INTO Bulbs
    SELECT * FROM inserted;

Практические задания

Создание и оптимизация триггеров в SQL Server