SCENE 8 : Procédures Stockées et Déclencheurs - Les Automates de l'Ombre | Faites entrer SQL

Certaines choses doivent se faire toutes seules. Quand un client paie, la commande passe en "payée". Quand un stock tombe à zéro, une alerte se lève. La ville a ses réflexes. Ses automatismes. On ne peut pas tout faire à la main.

Les Procédures Stockées – Le Script Préparé

Une procédure stockée, c'est un programme SQL enregistré dans la base.
Une séquence d'instructions nommée, paramétrée, qu'on appelle comme une fonction.
C'est la logique métier qui descend dans les caves de la base.

Pourquoi ? Quand la Logique Descend dans les Sous-Sols

Performance – Exécutée côté serveur, pas de va-et-vient réseau.
Centralisation – Une logique unique, pas dispersée dans 10 applications.
Sécurité – On donne l'accès à la procédure, pas aux tables.
Transaction native – Tout dans la procédure est une transaction.

`CREATE PROCEDURE` – Écrire le Script

PostgreSQL (PL/pgSQL) :

CREATE OR REPLACE PROCEDURE traiter_commande(
    p_commande_id INTEGER,
    p_nouveau_statut VARCHAR(20)
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Validation
    IF p_nouveau_statut NOT IN ('payee', 'expediee', 'annulee') THEN
        RAISE EXCEPTION 'Statut invalide';
    END IF;
    
    -- Mise à jour
    UPDATE commande 
    SET statut = p_nouveau_statut,
        date_modification = CURRENT_TIMESTAMP
    WHERE commande_id = p_commande_id;
    
    -- Log
    INSERT INTO audit_commande (commande_id, ancien_statut, nouveau_statut, date_changement)
    VALUES (p_commande_id, 
            (SELECT statut FROM commande WHERE commande_id = p_commande_id),
            p_nouveau_statut,
            CURRENT_TIMESTAMP);
    
    COMMIT;
END;
$$;

MySQL :

DELIMITER //

CREATE PROCEDURE traiter_commande(
    IN p_commande_id INT,
    IN p_nouveau_statut VARCHAR(20)
)
BEGIN
    DECLARE v_ancien_statut VARCHAR(20);
    
    -- Validation
    IF p_nouveau_statut NOT IN ('payee', 'expediee', 'annulee') THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Statut invalide';
    END IF;
    
    -- Récupération ancien statut
    SELECT statut INTO v_ancien_statut 
    FROM commande 
    WHERE commande_id = p_commande_id;
    
    -- Mise à jour
    UPDATE commande 
    SET statut = p_nouveau_statut,
        date_modification = NOW()
    WHERE commande_id = p_commande_id;
    
    -- Log
    INSERT INTO audit_commande (commande_id, ancien_statut, nouveau_statut, date_changement)
    VALUES (p_commande_id, v_ancien_statut, p_nouveau_statut, NOW());
    
    COMMIT;
END//

DELIMITER ;

Appel d'une Procédure – Lancer le Mécanisme

-- Appel simple
CALL traiter_commande(12345, 'payee');

-- Dans une transaction
BEGIN;
CALL traiter_commande(12345, 'expediee');
-- Autres opérations
COMMIT;

Les Fonctions – Les Procédures qui Retournent une Valeur

Une fonction ressemble à une procédure, mais retourne une valeur.
Utilisable dans les SELECT.

-- PostgreSQL
CREATE OR REPLACE FUNCTION calculer_commission(
    p_montant DECIMAL,
    p_taux DECIMAL DEFAULT 0.10
) RETURNS DECIMAL
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    RETURN p_montant * p_taux;
END;
$$;

-- Utilisation
SELECT commande_id, montant, calculer_commission(montant, 0.15) 
FROM commande;

Avantages/Inconvénients – Le Pacte avec le Démon

Pour :

Rapidité sur traitement batch
Cohérence garantie
Réduction du trafic réseau
Cache des plans d'exécution

Contre :

Vendor lock-in – Le code est spécifique au SGBD
Debugging difficile
Tests complexes
Risque de logique métier enterrée

Les Déclencheurs (Triggers) – Les Réflexes de la Ville

Un déclencheur, c'est un morceau de code qui s'exécute automatiquement
avant ou après un événement (INSERT, UPDATE, DELETE).
La ville qui réagit toute seule.

`CREATE TRIGGER` – Implanter le Réflexe

PostgreSQL :

-- Trigger pour mettre à jour date_modification
CREATE OR REPLACE FUNCTION update_date_modification()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.date_modification = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trig_update_date
    BEFORE UPDATE ON client
    FOR EACH ROW
    EXECUTE FUNCTION update_date_modification();

MySQL :

DELIMITER //

CREATE TRIGGER trig_update_date
BEFORE UPDATE ON client
FOR EACH ROW
BEGIN
    SET NEW.date_modification = NOW();
END//

DELIMITER ;

Types de Déclencheurs – Le Moment de l'Action

BEFORE – Avant l'opération. Peut modifier les valeurs.
AFTER – Après l'opération. Pour les logs, les effets de bord.
INSTEAD OF (PostgreSQL) – Pour les vues. Remplacer l'opération par autre chose.

Événements :

INSERT
UPDATE (éventuellement UPDATE OF colonne)
DELETE

Granularité :

FOR EACH ROW – Exécuté pour chaque ligne affectée.
FOR EACH STATEMENT – Exécuté une fois par instruction.

Les Variables Spéciales – Les Témoins de l'Action

Dans la fonction du trigger :

NEW – La nouvelle ligne (pour INSERT, UPDATE)
OLD – L'ancienne ligne (pour UPDATE, DELETE)

-- Trigger d'audit complet
CREATE OR REPLACE FUNCTION audit_client_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_client (
            operation, client_id, ancienne_valeur, nouvelle_valeur, utilisateur, date
        ) VALUES (
            'INSERT',
            NEW.client_id,
            NULL,
            row_to_json(NEW),
            CURRENT_USER,
            CURRENT_TIMESTAMP
        );
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_client (
            operation, client_id, ancienne_valeur, nouvelle_valeur, utilisateur, date
        ) VALUES (
            'UPDATE',
            NEW.client_id,
            row_to_json(OLD),
            row_to_json(NEW),
            CURRENT_USER,
            CURRENT_TIMESTAMP
        );
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_client (
            operation, client_id, ancienne_valeur, nouvelle_valeur, utilisateur, date
        ) VALUES (
            'DELETE',
            OLD.client_id,
            row_to_json(OLD),
            NULL,
            CURRENT_USER,
            CURRENT_TIMESTAMP
        );
    END IF;
    RETURN COALESCE(NEW, OLD);
END;
$$;

CREATE TRIGGER trig_audit_client
    AFTER INSERT OR UPDATE OR DELETE ON client
    FOR EACH ROW
    EXECUTE FUNCTION audit_client_changes();

Cas Pratiques – Les Réflexes Essentiels

1. Validation Complexe

-- Empêcher la baisse de salaire
CREATE OR REPLACE FUNCTION check_salaire()
RETURNS TRIGGER
AS $$
BEGIN
    IF NEW.salaire < OLD.salaire THEN
        RAISE EXCEPTION 'Le salaire ne peut pas diminuer';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_check_salaire
    BEFORE UPDATE ON employe
    FOR EACH ROW
    EXECUTE FUNCTION check_salaire();

2. Gestion de Stock Automatique

-- Décrémenter le stock à chaque commande
CREATE OR REPLACE FUNCTION update_stock()
RETURNS TRIGGER
AS $$
BEGIN
    UPDATE produit
    SET quantite_stock = quantite_stock - NEW.quantite
    WHERE produit_id = NEW.produit_id;
    
    -- Alerte si stock bas
    IF (SELECT quantite_stock FROM produit WHERE produit_id = NEW.produit_id) < 5 THEN
        INSERT INTO alertes_stock (produit_id, quantite_restante, date_alerte)
        VALUES (NEW.produit_id, 
                (SELECT quantite_stock FROM produit WHERE produit_id = NEW.produit_id),
                CURRENT_TIMESTAMP);
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_update_stock
    AFTER INSERT ON ligne_de_commande
    FOR EACH ROW
    EXECUTE FUNCTION update_stock();

3. Historisation des Modifications

-- Garder l'historique complet des prix
CREATE TABLE historique_prix (
    id SERIAL PRIMARY KEY,
    produit_id INTEGER REFERENCES produit(produit_id),
    ancien_prix DECIMAL(10,2),
    nouveau_prix DECIMAL(10,2),
    date_changement TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    utilisateur VARCHAR(100)
);

CREATE OR REPLACE FUNCTION historiser_prix()
RETURNS TRIGGER
AS $$
BEGIN
    IF NEW.prix <> OLD.prix THEN
        INSERT INTO historique_prix (produit_id, ancien_prix, nouveau_prix, utilisateur)
        VALUES (NEW.produit_id, OLD.prix, NEW.prix, CURRENT_USER);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_historiser_prix
    AFTER UPDATE OF prix ON produit
    FOR EACH ROW
    EXECUTE FUNCTION historiser_prix();

Pièges des Triggers – Les Réflexes Incontrôlés

Récursion infinie – Un trigger qui se déclenche lui-même.

-- DANGER : trigger sur table A qui update table B, qui a un trigger update sur table A...

Performance – FOR EACH ROW sur 1 million de lignes = 1 million d'exécutions.
Effets de bord cachés – Une simple UPDATE peut lancer 5 triggers invisibles.
Debugging infernal – "Pourquoi cette valeur a changé ?" → 3 heures à tracer les triggers.

Bonnes Pratiques – Le Code de Conduite des Automates

Garder les triggers simples – Une logique par trigger.
Documenter – Un commentaire sur chaque trigger explique POURQUOI il existe.
Éviter la récursion – Vérifier les dépendances circulaires.
Tester avec RAISE NOTICE – Pour le debugging.
Préférer les contraintes quand c'est possible. Un CHECK est plus simple qu'un trigger.

🔄 Procédures + Triggers – L'Orchestre Automatique

Le combo gagnant : une procédure pour l'action, des triggers pour les effets.

Exemple : Système Complet de Commande

-- 1. Trigger pour la date de modification
CREATE TRIGGER trig_commande_date
    BEFORE UPDATE ON commande
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp(); -- Fonction générique

-- 2. Trigger pour l'audit
CREATE TRIGGER trig_audit_commande
    AFTER INSERT OR UPDATE OR DELETE ON commande
    FOR EACH ROW
    EXECUTE FUNCTION audit_table('commande');

-- 3. Procédure pour créer une commande (cohérence métier)
CREATE OR REPLACE PROCEDURE creer_commande_complete(
    p_client_id INTEGER,
    p_produits JSON -- Format: [{"produit_id": 1, "quantite": 2}, ...]
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_commande_id INTEGER;
    v_produit RECORD;
BEGIN
    -- Validation client
    IF NOT EXISTS (SELECT 1 FROM client WHERE client_id = p_client_id AND actif = true) THEN
        RAISE EXCEPTION 'Client inactif ou inexistant';
    END IF;
    
    -- Création commande
    INSERT INTO commande (client_id, date_commande, statut)
    VALUES (p_client_id, CURRENT_TIMESTAMP, 'en_attente')
    RETURNING commande_id INTO v_commande_id;
    
    -- Ajout des lignes
    FOR v_produit IN SELECT * FROM json_to_recordset(p_produits) 
                     AS x(produit_id INTEGER, quantite INTEGER)
    LOOP
        -- Validation stock (le trigger fera la mise à jour)
        IF (SELECT quantite_stock FROM produit 
            WHERE produit_id = v_produit.produit_id) < v_produit.quantite THEN
            RAISE EXCEPTION 'Stock insuffisant pour produit %', v_produit.produit_id;
        END IF;
        
        INSERT INTO ligne_de_commande (commande_id, produit_id, quantite, prix_unitaire)
        VALUES (
            v_commande_id,
            v_produit.produit_id,
            v_produit.quantite,
            (SELECT prix FROM produit WHERE produit_id = v_produit.produit_id)
        );
    END LOOP;
    
    -- Calcul du total (fonction)
    UPDATE commande 
    SET montant_total = calculer_total_commande(v_commande_id)
    WHERE commande_id = v_commande_id;
    
    -- Log
    INSERT INTO logs_creation_commande (commande_id, client_id, date_creation)
    VALUES (v_commande_id, p_client_id, CURRENT_TIMESTAMP);
    
    COMMIT;
    
    RAISE NOTICE 'Commande % créée avec succès', v_commande_id;
END;
$$;

Gestion des Erreurs – Les Parachutes

CREATE OR REPLACE PROCEDURE operation_risquee()
LANGUAGE plpgsql
AS $$
BEGIN
    -- Début de transaction implicite
    
    -- Opération 1
    UPDATE ...;
    
    -- Opération 2 (peut échouer)
    BEGIN
        INSERT INTO ...;
    EXCEPTION 
        WHEN unique_violation THEN
            RAISE NOTICE 'Doublon ignoré';
            -- Continuer malgré l'erreur
        WHEN OTHERS THEN
            RAISE; -- Remonter l'erreur
    END;
    
    -- Opération 3
    DELETE ...;
    
    -- Si tout va bien, commit automatique
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE EXCEPTION 'Échec de l''opération: %', SQLERRM;
END;
$$;

🧪 Testing et Debugging – L'Autopsie des Automates

Debugging avec `RAISE`

CREATE OR REPLACE FUNCTION debug_trigger()
RETURNS TRIGGER
AS $$
BEGIN
    RAISE NOTICE 'Trigger % activé', TG_NAME;
    RAISE NOTICE 'Operation: %', TG_OP;
    RAISE NOTICE 'OLD: %', row_to_json(OLD);
    RAISE NOTICE 'NEW: %', row_to_json(NEW);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Voir les Triggers Existants

-- PostgreSQL
SELECT tgname, tgrelid::regclass, tgtype, tgisinternal 
FROM pg_trigger 
WHERE NOT tgisinternal;

-- MySQL
SHOW TRIGGERS FROM ma_base;

Désactiver/Activer Temporairement

-- PostgreSQL
ALTER TABLE client DISABLE TRIGGER trig_audit_client;
-- Import de données...
ALTER TABLE client ENABLE TRIGGER trig_audit_client;

-- MySQL
SET @old_triggers = @@SESSION.sql_mode;
SET SESSION sql_mode = '';
-- Opérations sans triggers
SET SESSION sql_mode = @old_triggers;

📜 Script de Déploiement Type

-- deploy_automation.sql
BEGIN;

-- 1. Fonctions utilitaires
CREATE OR REPLACE FUNCTION update_timestamp() ...;
CREATE OR REPLACE FUNCTION audit_table() ...;

-- 2. Triggers génériques
CREATE TRIGGER trig_timestamp_client ...;
CREATE TRIGGER trig_audit_client ...;
CREATE TRIGGER trig_timestamp_commande ...;
CREATE TRIGGER trig_audit_commande ...;

-- 3. Triggers métier
CREATE TRIGGER trig_gestion_stock ...;
CREATE TRIGGER trig_historique_prix ...;

-- 4. Procédures métier
CREATE OR REPLACE PROCEDURE creer_commande_complete(...) ...;
CREATE OR REPLACE PROCEDURE traiter_paiement(...) ...;

-- 5. Droits d'exécution
GRANT EXECUTE ON PROCEDURE creer_commande_complete TO role_commercial;
GRANT EXECUTE ON PROCEDURE traiter_paiement TO role_caisse;

-- 6. Tables de logs et d'audit (si inexistantes)
CREATE TABLE IF NOT EXISTS audit_client (...);
CREATE TABLE IF NOT EXISTS logs_commande (...);

COMMIT;

Conclusion – La Ville qui Dort d'un Œil

Les procédures stockées sont les scripts prêts à l'emploi.
Les déclencheurs sont les réflexes inconscients.
Ensemble, ils font de la base de données un système vivant, réactif.

Souvenez-vous :

Avec grand pouvoir vient grande responsabilité – Un trigger mal écrit peut corrompre les données.
Documentation obligatoire – Personne ne doit deviner pourquoi un trigger existe.
KISS – Keep It Simple, Stupid. Un trigger = une responsabilité.
Tester comme un fou – Les automatismes doivent être infaillibles.

La base de données n'est plus un entrepôt inerte.
C'est un système nerveux.
Qui réagit, qui valide, qui alerte, qui trace.
Même quand tout le monde dort.

La ville veille.

Prochain et dernier épisode : optimisation et maintenance.
Quand la ville vieillit, qu'elle ralentit.
Et qu'il faut la remettre en état.

SCENE 8 : Procédures Stockées et Déclencheurs - Les Automates de l'Ombre