Come si usano i trigger DML

Creare e ottimizzare i trigger in SQL Server

Florin Angelescu

Instructor

Perché usare i trigger DML?

Avviare azioni durante la manipolazione dei dati
Impedire la manipolazione dei dati
Tracciare cambiamenti di dati o oggetti del database
Audit utenti e sicurezza del database

Scegliere tra AFTER e 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};

Scegliere tra AFTER e INSTEAD OF

Trigger AFTER

L'evento iniziale attiva il trigger

L'evento iniziale si esegue

Si eseguono le azioni del trigger

Come funziona un trigger AFTER

Trigger INSTEAD OF

L'evento iniziale attiva il trigger

L'evento iniziale non si esegue più

Si eseguono le azioni del trigger

Come funziona un trigger INSTEAD OF

Esempio d'uso di un trigger AFTER

Dati inseriti in una tabella vendite
Avvia una procedura di pulizia dati
Genera un report tabellare con i risultati
Notifica l'amministratore del database

CREATE TRIGGER SalesNewInfoTrigger
ON Sales
AFTER INSERT
AS

EXEC sp_cleansing @Table = 'Sales';

EXEC sp_generateSalesReport;

EXEC sp_sendnotification;

Esempio d'uso di un trigger INSTEAD OF

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

La potenza cambia per alcuni modelli

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

Esempio d'uso di un trigger INSTEAD OF

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

La potenza cambia per alcuni modelli
Aggiorna solo i prodotti senza stock

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

Esempio d'uso di un trigger INSTEAD OF

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

La potenza cambia per alcuni modelli
Aggiorna solo i prodotti senza stock
Aggiungi nuove righe per i prodotti con stock

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

Domande di pratica

Creare e ottimizzare i trigger in SQL Server