Hoe DML-triggers worden gebruikt

Triggers in SQL Server bouwen en optimaliseren

Florin Angelescu

Instructor

Waarom DML-triggers gebruiken?

Acties starten bij datamanipulatie
Datamanipulatie voorkomen
Wijzigingen in data of databaseobjecten bijhouden
Gebruikersaudits en databasesecurity

Kiezen tussen AFTER en INSTEAD OF

CREATE TRIGGER MyFirstAfterTrigger
ON Table1
-- Geactiveerd na
-- de triggergebeurtenis (UPDATE)
AFTER UPDATE
AS
{trigger_actions_section};

CREATE TRIGGER MyFirstInsteadOfTrigger
ON Table2
-- Geactiveerd in plaats van
-- de triggergebeurtenis (UPDATE)
INSTEAD OF UPDATE
AS
{trigger_actions_section};

Kiezen tussen AFTER en INSTEAD OF

AFTER-trigger

Initiële gebeurtenis activeert de trigger

Initiële gebeurtenis voert uit

De triggeracties voeren uit

Hoe een AFTER-trigger werkt

INSTEAD OF-trigger

Initiële gebeurtenis activeert de trigger

Initiële gebeurtenis wordt niet meer uitgevoerd

De triggeracties voeren uit

Hoe een INSTEAD OF-trigger werkt

Voorbeeld gebruik AFTER-trigger

Data wordt ingevoegd in een verkoopstabel
Start een opschoningsprocedure
Genereer een tabelrapport met de resultaten
Stel de databasebeheerder op de hoogte

CREATE TRIGGER SalesNewInfoTrigger
ON Sales
AFTER INSERT
AS

EXEC sp_cleansing @Table = 'Sales';

EXEC sp_generateSalesReport;

EXEC sp_sendnotification;

Voorbeeld gebruik INSTEAD OF-trigger

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

Het vermogen wijzigt voor sommige modellen

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

Voorbeeld gebruik INSTEAD OF-trigger

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

Het vermogen wijzigt voor sommige modellen
Update alleen producten zonder voorraad

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

Voorbeeld gebruik INSTEAD OF-trigger

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

Het vermogen wijzigt voor sommige modellen
Update alleen producten zonder voorraad
Voeg nieuwe rijen toe voor producten met voorraad

-- Eerste deel ingekort wegens ruimte
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;

Oefenvragen

Triggers in SQL Server bouwen en optimaliseren