data / sql

Procédures stockées et triggers

Leçon 141 exercice

Explication

Ce que vous allez apprendre

Écrire une fonction PL/pgSQL réutilisable qui retourne une valeur calculée
Écrire une procédure stockée qui exécute des actions avec effets de bord via CALL
Distinguer précisément une fonction (RETURNS) d'une procédure (CALL)
Créer un trigger qui réagit automatiquement à un INSERT/UPDATE/DELETE
Utiliser NEW et OLD pour accéder à la ligne avant/après modification

Dans quel contexte ?

Une boutique en ligne veut automatiquement décrémenter le stock de la table produits à chaque nouvelle ligne insérée dans lignes_commande, et bloquer la vente si le stock devient négatif. Faire ça côté application obligerait chaque service qui écrit dans la base à réimplémenter la même règle. Un trigger centralise cette logique directement en base, comme le montre cette leçon.

D'abord, où vit la logique aujourd'hui

Jusqu'ici, la logique métier — calculs, conditions, boucles — vit dans le code de l'application, qui appelle la base uniquement pour lire ou écrire. Une autre approche existe : écrire cette logique directement dans la base, avec un vrai langage de programmation (plpgsql pour PostgreSQL).

L'intérêt de déplacer la logique

Cette logique s'exécute alors au plus près des données, sans aller-retour réseau, et devient partagée par tous les programmes qui se connectent à la base, quel que soit leur langage.

Une première distinction à faire : fonction ou procédure

Une fonction calcule et retourne toujours une valeur (RETURNS), comme une fonction mathématique. Une procédure exécute des actions avec effets de bord et s'appelle avec CALL plutôt qu'en résultat d'un SELECT.

Une fois ça posé, un nouveau besoin : réagir automatiquement

Parfois, on veut qu'une action se déclenche toute seule dès qu'un événement précis se produit sur une table, sans que personne n'ait à l'appeler explicitement.

La réponse : le trigger

Un trigger attache une fonction à un événement (INSERT, UPDATE, DELETE). NEW et OLD donnent accès respectivement à la nouvelle et à l'ancienne version de la ligne concernée — très pratique pour maintenir un stock à jour ou tracer un historique.

Concept	Déclenché par	Retourne	Exemple
Fonction	Un appel `SELECT fonction(...)`	Une valeur (`RETURNS`)	`calculer_remise(prix, pourcentage)`
Procédure	Un appel `CALL procedure(...)`	Rien, effets de bord	`archiver_vieilles_commandes(365)`
Trigger	Un `INSERT`/`UPDATE`/`DELETE`	`NEW`/`OLD` (ligne concernée)	`maj_stock_apres_commande()`

Un piège à garder en tête

Un trigger est une logique "cachée" : un développeur qui lit le code applicatif ne verra jamais qu'un INSERT déclenche en réalité trois autres opérations en coulisses. C'est puissant mais dangereux si mal documenté.

Piège fréquent

Un trigger AFTER INSERT qui lève une exception (RAISE EXCEPTION 'Stock insuffisant') fait échouer silencieusement, du point de vue de l'application, un INSERT qui semblait pourtant correct dans le code appelant. Sans documentation claire du trigger, un développeur peut perdre un temps considérable à comprendre pourquoi son insertion échoue.

La sagesse à retenir

Beaucoup d'équipes limitent volontairement l'usage des triggers aux cas vraiment nécessaires — audit, intégrité — pour garder le système compréhensible par tous.

Bonne pratique

Réserve les triggers aux cas où la règle DOIT s'appliquer quel que soit le programme qui écrit dans la table (audit, intégrité critique). Pour la logique métier "normale", préfère la garder visible dans le code applicatif ou dans une procédure appelée explicitement.

Commandes & code

Procédures stockées et triggers

-- Fonction PostgreSQL (plpgsql) : logique réutilisable côté base
CREATE OR REPLACE FUNCTION calculer_remise(prix NUMERIC, pourcentage NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN prix - (prix * pourcentage / 100);
END;
$$ LANGUAGE plpgsql;

SELECT calculer_remise(100, 20);  -- -> 80

-- Procédure stockée (avec effets de bord, appelée via CALL)
CREATE OR REPLACE PROCEDURE archiver_vieilles_commandes(seuil_jours INTEGER)
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO commandes_archive
    SELECT * FROM commandes WHERE cree_le < CURRENT_DATE - seuil_jours;

    DELETE FROM commandes WHERE cree_le < CURRENT_DATE - seuil_jours;

    COMMIT;  -- une procédure peut gérer ses propres transactions
END;
$$;

CALL archiver_vieilles_commandes(365);

-- Fonction avec logique conditionnelle et boucle
CREATE OR REPLACE FUNCTION niveau_fidelite(client_id_param INTEGER)
RETURNS TEXT AS $$
DECLARE
    total NUMERIC;
BEGIN
    SELECT COALESCE(SUM(montant), 0) INTO total
    FROM commandes WHERE client_id = client_id_param;

    IF total > 10000 THEN
        RETURN 'platine';
    ELSIF total > 5000 THEN
        RETURN 'or';
    ELSIF total > 1000 THEN
        RETURN 'argent';
    ELSE
        RETURN 'standard';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Trigger : exécute une fonction automatiquement sur INSERT/UPDATE/DELETE
CREATE OR REPLACE FUNCTION maj_stock_apres_commande()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE produits
    SET stock = stock - NEW.quantite
    WHERE id = NEW.produit_id;

    IF (SELECT stock FROM produits WHERE id = NEW.produit_id) < 0 THEN
        RAISE EXCEPTION 'Stock insuffisant pour le produit %', NEW.produit_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_maj_stock
AFTER INSERT ON lignes_commande
FOR EACH ROW
EXECUTE FUNCTION maj_stock_apres_commande();

-- Trigger d'audit : trace tout changement sur une table sensible
CREATE OR REPLACE FUNCTION auditer_changement()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, ancienne_valeur, nouvelle_valeur, modifie_le)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        row_to_json(OLD),
        row_to_json(NEW),
        CURRENT_TIMESTAMP
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_utilisateurs
AFTER UPDATE OR DELETE ON utilisateurs
FOR EACH ROW
EXECUTE FUNCTION auditer_changement();

-- Désactiver / supprimer un trigger
ALTER TABLE lignes_commande DISABLE TRIGGER trg_maj_stock;
DROP TRIGGER IF EXISTS trg_maj_stock ON lignes_commande;

Résumé

Une fonction retourne une valeur (RETURNS), une procédure exécute des effets de bord (CALL, peut gérer ses transactions).
NEW/OLD donnent accès aux valeurs avant/après dans un trigger.
BEFORE/AFTER + INSERT/UPDATE/DELETE définissent le moment de déclenchement.
Les triggers sont puissants mais opaques : à documenter soigneusement (logique cachée hors de l'application).

Exercices pratiques

1 disponible

Mission : automatiser la décrémentation du stock sans casser les commandes

Objectif : Écrire un trigger qui maintient le stock à jour et bloque les ventes impossibles, puis diagnostiquer un échec d'INSERT inexpliqué.

Contexte

La boutique en ligne veut que chaque insertion dans lignes_commande décrémente automatiquement le stock du produit concerné dans produits, et bloque l'opération si le stock devient négatif. Un développeur qui découvre ce trigger plus tard se plaint qu'un INSERT "pourtant correct" échoue sans qu'il comprenne pourquoi.

Résoudre l’exercice →