Jak se používají DML triggery

Tvorba a optimalizace triggerů v SQL Serveru

Florin Angelescu

Instructor

Proč používat DML triggery?

Spouštění akcí při manipulaci s daty
Prevence manipulace s daty
Sledování změn dat nebo databázových objektů
Audit uživatelů a zabezpečení databáze

Volba mezi 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};

Volba mezi AFTER a INSTEAD OF

Trigger AFTER

Počáteční událost aktivuje trigger

Počáteční událost se provede

Akce triggeru se provedou

Jak funguje trigger AFTER

Trigger INSTEAD OF

Počáteční událost aktivuje trigger

Počáteční událost se již neprovede

Akce triggeru se provedou

Jak funguje trigger INSTEAD OF

Příklad použití triggeru AFTER

Data jsou vložena do tabulky prodejů
Spuštění procedury čištění dat
Generování reportu tabulky s výsledky procedury
Upozornění správce databáze

CREATE TRIGGER SalesNewInfoTrigger
ON Sales
AFTER INSERT
AS

EXEC sp_cleansing @Table = 'Sales';

EXEC sp_generateSalesReport;

EXEC sp_sendnotification;

Příklad použití triggeru INSTEAD OF

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

U některých modelů se mění výkon

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

Příklad použití triggeru INSTEAD OF

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

U některých modelů se mění výkon
Aktualizovat pouze produkty bez skladu

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

Příklad použití triggeru INSTEAD OF

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

U některých modelů se mění výkon
Aktualizovat pouze produkty bez skladu
Přidat nové řádky pro produkty se skladem

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

Procvičovací otázky

Tvorba a optimalizace triggerů v SQL Serveru