Zastosowanie wyzwalaczy DML

Tworzenie i optymalizacja triggerów w SQL Server

Florin Angelescu

Instructor

Dlaczego warto używać wyzwalaczy DML?

Inicjowanie akcji przy manipulacji danymi
Zapobieganie manipulacji danymi
Śledzenie zmian danych lub obiektów bazy danych
Audyt użytkowników i bezpieczeństwo bazy danych

Wybór między AFTER a 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};

Wybór między AFTER a INSTEAD OF

Wyzwalacz AFTER

Zdarzenie inicjujące wyzwala wyzwalacz

Zdarzenie inicjujące wykonuje się

Akcje wyzwalacza są wykonywane

Jak działa wyzwalacz AFTER

Wyzwalacz INSTEAD OF

Zdarzenie inicjujące wyzwala wyzwalacz

Zdarzenie inicjujące nie jest już wykonywane

Akcje wyzwalacza są wykonywane

Jak działa wyzwalacz INSTEAD OF

Przykład użycia wyzwalacza AFTER

Dane są wstawiane do tabeli sprzedaży
Uruchomienie procedury czyszczenia danych
Generowanie raportu z wynikami procedury
Powiadomienie administratora bazy danych

CREATE TRIGGER SalesNewInfoTrigger
ON Sales
AFTER INSERT
AS

EXEC sp_cleansing @Table = 'Sales';

EXEC sp_generateSalesReport;

EXEC sp_sendnotification;

Przykład użycia wyzwalacza INSTEAD OF

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

Moc zmienia się dla niektórych modeli

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

Przykład użycia wyzwalacza INSTEAD OF

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

Moc zmienia się dla niektórych modeli
Aktualizacja tylko produktów bez stanu magazynowego

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

Przykład użycia wyzwalacza INSTEAD OF

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

Moc zmienia się dla niektórych modeli
Aktualizacja tylko produktów bez stanu magazynowego
Dodanie nowych wierszy dla produktów z zapasem

-- 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;

Pytania praktyczne

Tworzenie i optymalizacja triggerów w SQL Server