Як використовують тригери 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