Utilizarea triggerelor DML

Crearea și optimizarea triggerelor în SQL Server

Florin Angelescu

Instructor

De ce să utilizăm triggere DML?

Inițierea acțiunilor la manipularea datelor
Prevenirea manipulării datelor
Urmărirea modificărilor datelor sau obiectelor din baza de date
Auditarea utilizatorilor și securitatea bazei de date

Alegerea între AFTER și 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};

Alegerea între AFTER și INSTEAD OF

Trigger AFTER

Evenimentul inițial declanșează triggerul

Evenimentul inițial se execută

Acțiunile triggerului se execută

Cum funcționează un trigger AFTER

Trigger INSTEAD OF

Evenimentul inițial declanșează triggerul

Evenimentul inițial nu mai este executat

Acțiunile triggerului se execută

Cum funcționează un trigger INSTEAD OF

Exemplu de utilizare a triggerului AFTER

Date inserate într-un tabel de vânzări
Pornirea unei proceduri de curățare a datelor
Generarea unui raport din rezultatele procedurii
Notificarea administratorului bazei de date

CREATE TRIGGER SalesNewInfoTrigger
ON Sales
AFTER INSERT
AS

EXEC sp_cleansing @Table = 'Sales';

EXEC sp_generateSalesReport;

EXEC sp_sendnotification;

Exemplu de utilizare a triggerului INSTEAD OF

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

Puterea se modifică pentru unele modele

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

Exemplu de utilizare a triggerului INSTEAD OF

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

Puterea se modifică pentru unele modele
Actualizați doar produsele fără stoc

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

Exemplu de utilizare a triggerului INSTEAD OF

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

Puterea se modifică pentru unele modele
Actualizați doar produsele fără stoc
Adăugați rânduri noi pentru produsele cu stoc

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

Întrebări de practică

Crearea și optimizarea triggerelor în SQL Server