Hur DML-triggers används

Bygga och optimera triggers i SQL Server

Florin Angelescu

Instructor

Varför ska vi använda DML-triggers?

Initiera åtgärder vid datamanipulation
Förhindra datamanipulation
Spåra ändringar i data eller databasobjekt
Användargranskning och databassäkerhet

Välja mellan AFTER och 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};

Välja mellan AFTER och INSTEAD OF

AFTER-trigger

Den inledande händelsen aktiverar triggern

Den inledande händelsen körs

Triggeråtgärderna körs

Hur en AFTER-trigger fungerar

INSTEAD OF-trigger

Den inledande händelsen aktiverar triggern

Den inledande händelsen körs inte längre

Triggeråtgärderna körs

Hur en INSTEAD OF-trigger fungerar

Exempel på AFTER-trigger

Data infogas i en försäljningstabell
Starta en datarensningsprocedur
Generera en tabellrapport med procedurresultaten
Meddela databasadministratören

CREATE TRIGGER SalesNewInfoTrigger
ON Sales
AFTER INSERT
AS

EXEC sp_cleansing @Table = 'Sales';

EXEC sp_generateSalesReport;

EXEC sp_sendnotification;

Exempel på INSTEAD OF-trigger

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

Effekten ändras för vissa modeller

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

Exempel på INSTEAD OF-trigger

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

Effekten ändras för vissa modeller
Uppdatera bara produkter utan lagersaldo

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

Exempel på INSTEAD OF-trigger

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

Effekten ändras för vissa modeller
Uppdatera bara produkter utan lagersaldo
Lägg till nya rader för produkter med lagersaldo

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

Övningsfrågor

Bygga och optimera triggers i SQL Server