Triggers et Curseurs

⚡

Qu'est-ce qu'un Trigger ?

Automatiser des actions sur ta base de données

Un trigger (déclencheur) est un bloc de code qui s'exécute automatiquement en réponse à un événement sur une table : INSERT, UPDATE, ou DELETE.

💡 Analogie

Imagine un détecteur de mouvement qui allume automatiquement la lumière quand tu entres dans une pièce. Le trigger fait pareil : il "détecte" une action sur la table et exécute du code automatiquement.

🎯 Cas d'usage courants

📝

Audit / Logging

Enregistrer qui a modifié quoi et quand

✅

Validation

Vérifier des règles métier avant insertion

🔄

Synchronisation

Mettre à jour automatiquement d'autres tables

📅

Timestamps

Remplir created_at, updated_at automatiquement

Le déclencheur en action

Lance une requête sur la table : le trigger se déclenche tout seul et remplit l'audit.

Choisis une opération : INSERT, UPDATE ou DELETE.

📜 Syntaxe d'un Trigger

Un trigger en PostgreSQL se compose de 2 parties :

1️⃣ La Fonction Trigger

Le code à exécuter (retourne TRIGGER)

2️⃣ Le Trigger lui-même

Lie la fonction à un événement sur une table

Étape 1 : Créer la fonction

CREATE OR REPLACE FUNCTION log_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- NEW = la nouvelle ligne (INSERT, UPDATE)
    -- OLD = l'ancienne ligne (UPDATE, DELETE)

    INSERT INTO audit_log (action, user_id, changed_at)
    VALUES (TG_OP, NEW.id, NOW());

    RETURN NEW;  -- Important pour BEFORE triggers
END;
$$ LANGUAGE plpgsql;

Étape 2 : Créer le trigger

CREATE TRIGGER trg_user_audit
    AFTER INSERT OR UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION log_user_changes();

⚠️ Variables spéciales dans un trigger

NEW → La nouvelle ligne (disponible pour INSERT, UPDATE)
OLD → L'ancienne ligne (disponible pour UPDATE, DELETE)
TG_OP → L'opération : 'INSERT', 'UPDATE', 'DELETE'
TG_TABLE_NAME → Nom de la table concernée

⏱️ BEFORE vs AFTER

BEFORE

S'exécute avant que la modification soit appliquée.

✅ Peut modifier les données (NEW.colonne = ...)
✅ Peut annuler l'opération (RETURN NULL)
📌 Idéal pour : validation, auto-remplissage

-- Auto-remplir created_at
CREATE FUNCTION set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.created_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

AFTER

S'exécute après que la modification soit appliquée.

❌ Ne peut PAS modifier les données
❌ Ne peut PAS annuler l'opération
📌 Idéal pour : audit, notifications, cascade

-- Logger après insertion
CREATE FUNCTION log_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO logs (msg)
    VALUES ('New user: ' || NEW.name);
    RETURN NULL; -- Ignoré pour AFTER
END;
$$ LANGUAGE plpgsql;

🎯 Règle simple

BEFORE = Tu veux modifier ou bloquer l'action
AFTER = Tu veux réagir à l'action (logging, sync)

La chronologie d'un UPDATE

Avance étape par étape (ou clique sur la frise) : suis OLD et NEW pendant UPDATE employes SET salaire = 2600.

Étape 1/4

🛠️ Exemples Pratiques

1. Auto-remplir updated_at

Mettre à jour automatiquement le timestamp à chaque modification :

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_column();

2. Empêcher la suppression

Bloquer les DELETE sur une table sensible :

CREATE OR REPLACE FUNCTION prevent_delete()
RETURNS TRIGGER AS $$
BEGIN
    RAISE EXCEPTION 'Suppression interdite sur cette table !';
    RETURN NULL; -- Jamais atteint
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER no_delete_users
    BEFORE DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION prevent_delete();

3. Audit complet

Enregistrer toutes les modifications dans une table d'audit :

CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (
        table_name, operation, old_data, new_data, changed_by, changed_at
    ) VALUES (
        TG_TABLE_NAME,
        TG_OP,
        row_to_json(OLD),
        row_to_json(NEW),
        current_user,
        NOW()
    );

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION audit_changes();

🔁 FOR EACH ROW vs FOR EACH STATEMENT

FOR EACH ROW

Exécuté une fois par ligne affectée.

UPDATE users SET active = true;
-- Si 100 users → trigger appelé 100x

✅ Accès à NEW et OLD

FOR EACH STATEMENT

Exécuté une seule fois par requête.

UPDATE users SET active = true;
-- Trigger appelé 1x seulement

❌ Pas d'accès à NEW/OLD