Comment utiliser les déclencheurs DML

Création et optimisation de déclencheurs dans SQL Server

Florin Angelescu

Instructor

Pourquoi utiliser des déclencheurs DML ?

Déclencher des actions lors de la manipulation des données
Empêcher une manipulation de données
Suivre les changements de données ou d'objets
Vérifier l'activité et renforcer la sécurité

Choisir entre AFTER et 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};

Choisir entre AFTER et INSTEAD OF

Déclencheur AFTER

L'événement initial déclenche le déclencheur

L'événement initial s'exécute

Les actions du déclencheur s'exécutent

Fonctionnement d'un déclencheur AFTER

Déclencheur INSTEAD OF

L'événement initial déclenche le déclencheur

L'événement initial ne s'exécute plus

Les actions du déclencheur s'exécutent

Fonctionnement d'un déclencheur INSTEAD OF

Exemple d'utilisation d'un déclencheur AFTER

Insertion de données dans une table des ventes
Lancer une procédure de nettoyage des données
Générer un rapport de table avec les résultats
Aviser l'administrateur de la base de données

CREATE TRIGGER SalesNewInfoTrigger
ON Sales
AFTER INSERT
AS

EXEC sp_cleansing @Table = 'Sales';

EXEC sp_generateSalesReport;

EXEC sp_sendnotification;

Exemple d'utilisation d'un déclencheur INSTEAD OF

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

La puissance change pour certains modèles

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

Exemple d'utilisation d'un déclencheur INSTEAD OF

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

La puissance change pour certains modèles
Mettre à jour seulement les produits sans 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

Exemple d'utilisation d'un déclencheur INSTEAD OF

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

La puissance change pour certains modèles
Mettre à jour seulement les produits sans stock
Ajouter de nouvelles lignes pour les produits en 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;

Questions de pratique

Création et optimisation de déclencheurs dans SQL Server