Jan 1, 0001

Correction détaillée — Bloc 5 : Triggers

Partie A

1. Identifier les événements à automatiser

On se place dans le modèle :

  • Client(idc, nom, age)
  • Village(idv, ville, activite, prix, capacite)
  • Sejour(ids, idc, idv, debut, fin)
  • ArchiveSejour(ids, idc, idv, debut, fin, date_archivage)

On liste table × opération × logique métier probable.

Table Client

  • INSERT

    • Inscription d’un nouveau client.
    • Possible automatisme : initialiser des compteurs dérivés (nb_sejours = 0), journaliser la création.

  • UPDATE

    • Changement de nom, age.
    • Peu de logique métier à automatiser côté trigger (les règles sont déjà des contraintes).

  • DELETE

    • Suppression d’un client.
    • Si la FK sejour.idc → client.idc est en ON DELETE CASCADE, pas besoin de trigger.
    • Si elle est en RESTRICT, pas de suppression possible si des séjours existent.
    • Un trigger pourrait servir uniquement à journaliser (log).

Conclusion : pas de trigger “critique” nécessaire sur client, éventuellement log / statistiques.

Table Village

  • INSERT

    • Création d’un nouveau village.
    • Automatisable : journalisation, initialisation de colonnes dérivées éventuelles.

  • UPDATE

    • Le cahier des charges impose :

      identifiant, ville et prix non modifiables.

    • Deux options :

      • soit on ne donne pas de formulaire qui modifie ces champs,
      • soit on met un trigger BEFORE UPDATE qui vérifie que idv, ville, prix ne changent pas et lève une exception sinon.

  • DELETE

    • Suppression d’un village.
    • Logique métier : interdire tant qu’il existe des séjours associés.
    • → Trigger BEFORE DELETE qui compte les séjours et lève une erreur si count(*) > 0.

Conclusion : Village est un bon candidat à triggers sur UPDATE (protection des champs non modifiables) et DELETE (protection contre les suppressions dangereuses).

Table Sejour

C’est la table la plus “vivante”.

  • INSERT

    • Réservation d’un séjour.

    • Automatisable par triggers :

      • contrôle de cohérence des dates (ordre, même année, etc.),
      • éventuellement contrôle métier supplémentaire (ex : capacité du village, au-delà de ce qui est déjà géré par contraintes / requêtes),
      • mise à jour de compteurs (client.nb_sejours),
      • journalisation.

  • UPDATE

    • Modifications de dates, changement de client, de village.

    • Triggers possibles :

      • contrôle des dates (mêmes règles que pour INSERT),
      • si idc change → ajustement des compteurs par client (−1 / +1),
      • éventuellement recalculs / logs.

  • DELETE

    • Fonctionnalité 9 et 10 (purge / annulation) entraînent des suppressions.
    • Spécification 11 : archivage automatique des séjours supprimés.
    • → Trigger AFTER DELETE qui copie la ligne dans ArchiveSejour + log + mise à jour des compteurs.

Conclusion : Sejour est la table centrale pour les triggers d’archivage, de contrôle et de maintenance de statistiques.

Table ArchiveSejour

  • INSERT

    • Toujours déclenché par la logique système (trigger ou procédure).
    • En général, aucun trigger (l’archive est un “sink” append-only).

  • UPDATE / DELETE

    • À éviter ; pas de trigger.

Conclusion : pas de trigger utile sur ArchiveSejour dans ce scénario.

2. Tracer la chaîne d’actions

On répond en termes de “séquence d’événements” pour les principaux cas.

Cas 1 : purge des séjours obsolètes (fonctionnalité 9 + archivage 11)

  1. L’employé appelle CALL purge_sejours(:date_limite); (procédure Bloc 3).

  2. La procédure exécute un DELETE FROM sejour WHERE fin < :date_limite RETURNING ...;.

  3. Pour chaque ligne supprimée :

    • Trigger AFTER DELETE ON sejour :

      • insère la ligne dans ArchiveSejour,
      • écrit éventuellement un message NOTICE,
      • met à jour client.nb_sejours si ce trigger existe.

  4. Si une insertion dans ArchiveSejour échoue, toute la transaction est annulée (suppression comprise).

Chaîne : Procédure applicative → DELETE sejour → trigger(s) AFTER DELETE (archivage, stats, log).

Cas 2 : annulation d’un séjour (fonctionnalité 10 + archivage 11)

Version “procédure” + triggers :

  1. L’employé appelle CALL annuler_sejour(p_ids, p_motif);.

  2. La procédure :

    • peut éventuellement journaliser le motif via NOTICE ou dans une table dédiée,
    • exécute un DELETE FROM sejour WHERE ids = p_ids;.

  3. Le DELETE déclenche :

    • AFTER DELETE ON sejour → insertion dans ArchiveSejour,
    • log éventuel,
    • mise à jour des compteurs.

Chaîne : Procédure métier → DELETE sejour → triggers de niveau table (archivage + cohérence).

Cas 3 : tentative de suppression d’un village occupé

  1. Quelqu’un exécute DELETE FROM village WHERE idv = :idv;.

  2. Trigger BEFORE DELETE ON village :

    • compte le nombre de séjours associés à OLD.idv,
    • si count > 0, RAISE EXCEPTION → annule la commande.

  3. Rien n’est supprimé, aucune ligne Sejour n’est touchée.

Chaîne : DELETE village → trigger BEFORE DELETE → éventuelle exception → rollback de l’ordre.

Cas 4 : insertion d’un séjour incohérent

  1. Appel direct ou via procédure de réservation : INSERT INTO sejour(...) VALUES (...);

  2. Trigger BEFORE INSERT ON sejour :

    • vérifie que debut et fin sont dans la même année,
    • éventuellement vérifie la longueur du séjour, etc.

  3. Si incohérence → RAISE EXCEPTION → rollback de l’ordre INSERT.

Chaîne : INSERT sejour → trigger BEFORE → validation ou exception.

3. Erreurs à lever (RAISE EXCEPTION)

On liste les cas où une exception est légitime (on veut bloquer la transaction) et ceux où un simple message (NOTICE/WARNING) suffit.

Erreurs bloquantes (EXCEPTION)

  1. Séjour multi-année

    • Règle : Les séjours sont limités à une année calendaire.
    • Trigger BEFORE INSERT/UPDATE ON sejour → RAISE EXCEPTION si EXTRACT(YEAR FROM debut) <> EXTRACT(YEAR FROM fin).

  2. Suppression d’un village occupé

    • Règle implicite de cohérence : ne pas perdre des séjours vivants.
    • Trigger BEFORE DELETE ON village → RAISE EXCEPTION si count(sejour.idv = OLD.idv) > 0.

  3. Violation explicite d’une règle métier non couverte par contraintes Exemples possibles :

    • si on décide de forcer une capacité maximale par trigger (même si déjà gérée par contraintes/exclusion) ;
    • si on veut interdire la modification de ville ou prix dans village par un BEFORE UPDATE qui compare OLD et NEW et lève une exception en cas de changement.

  4. Séjour introuvable lors d’une annulation (version plus stricte de Bloc 3)

    • Dans annuler_sejour, si aucun DELETE n’a lieu (IF NOT FOUND), on peut RAISE EXCEPTION 'Séjour introuvable'.

Dans tous ces cas : on veut empêcher la persistance de la commande → exception.

Messages non bloquants (NOTICE / WARNING)

  1. Journalisation des opérations normales

    • Archivage d’un séjour : RAISE NOTICE 'Séjour % archivé', OLD.ids;
    • Insertion/suppression d’un séjour : log dans log_evenement + NOTICE éventuel.

  2. Avertissement sur une situation anormale mais acceptable

    • Séjour très long mais dans l’année (≥ 30 jours) :

      • RAISE WARNING 'Séjour long (% jours)...', v_nb_jours, NEW.idc;
      • On accepte la ligne, mais on signale un cas “limite”.

  3. Messages de debug ou de pédagogie

    • Affichage d’informations intermédiaires pendant le TD.

En résumé, pour la Partie A :

  • Les triggers doivent surtout viser sejour (INSERT/UPDATE/DELETE) et village (DELETE, éventuellement UPDATE).
  • La chaîne d’actions part toujours des opérations DML (directes ou via procédures) et les triggers injectent automatiquement archivage, contrôle métier et journalisation.
  • On réserve RAISE EXCEPTION aux violations franches de règles métier (multi-année, suppression d’un village occupé, modification de champs interdits, séjour introuvable), et on garde NOTICE/WARNING pour le suivi et les anomalies tolérées.

Partie B

Correction Exercice 1 — Archivage automatique

1. Fonction de trigger archiver_sejour

Objectif : à chaque suppression d’un séjour, copier la ligne supprimée dans ArchiveSejour en ajoutant la date d’archivage.

CREATE OR REPLACE FUNCTION archiver_sejour()
RETURNS trigger AS $$
BEGIN
    INSERT INTO archivesejour(ids, idc, idv, debut, fin, date_archivage)
    VALUES (OLD.ids, OLD.idc, OLD.idv, OLD.debut, OLD.fin, now());

    RAISE NOTICE 'Séjour % archivé pour le client % (village %)', 
                 OLD.ids, OLD.idc, OLD.idv;

    -- Pour un trigger AFTER DELETE, on renvoie OLD
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

Points à vérifier / expliquer :

  • RETURNS trigger : obligatoire pour toute fonction utilisée comme fonction de trigger.
  • OLD contient la ligne supprimée (dans un trigger DELETE).
  • L’INSERT copie les valeurs de la ligne supprimée dans ArchiveSejour et fixe date_archivage à now() (timestamp actuel).
  • RAISE NOTICE : permet de tracer dans les logs ce qui est archivé (utile en phase de test).
  • RETURN OLD; : convention pour un trigger AFTER DELETE. (En BEFORE INSERT/UPDATE, on renverrait typiquement NEW.)

2. Création du trigger trg_archive_sejour

Objectif : déclencher la fonction après chaque suppression dans sejour.

CREATE TRIGGER trg_archive_sejour
AFTER DELETE ON sejour
FOR EACH ROW
EXECUTE FUNCTION archiver_sejour();

Explications :

  • AFTER DELETE : l’archivage se fait après la suppression logique dans sejour. Si l’INSERT dans ArchiveSejour échoue, la transaction complète est annulée (suppression comprise).
  • FOR EACH ROW : le trigger se déclenche pour chaque ligne supprimée, pas une seule fois pour l’instruction.
  • EXECUTE FUNCTION archiver_sejour() : syntaxe PostgreSQL moderne (≥ 11).

3. Tests de validation

Scénario minimal reproductible.

  1. Vérifier la présence d’un séjour à supprimer :
SELECT * FROM sejour WHERE ids = 42;
  1. Supprimer ce séjour :
DELETE FROM sejour WHERE ids = 42;

On doit voir un message du type :

NOTICE:  Séjour 42 archivé pour le client 3 (village 7)
  1. Vérifier l’archivage :
SELECT *
FROM archivesejour
WHERE ids = 42
ORDER BY date_archivage DESC
LIMIT 1;

Commentaires :

  • Si l’INSERT dans ArchiveSejour plante (clés, types, etc.), le DELETE est lui aussi annulé : cohérence forte.
  • L’archivage est maintenant garanti même si on supprime les séjours via n’importe quel client (psql, appli, script), sans passer par une procédure spécifique.

Correction Exercice 2 — Contrôle de cohérence avant insertion

1. Fonction de trigger controle_sejour_annee

Règle : un séjour doit commencer et se terminer la même année. Sinon, refus.

CREATE OR REPLACE FUNCTION controle_sejour_annee()
RETURNS trigger AS $$
BEGIN
    -- Cas 1 : dates nulles → on laisse le soin aux contraintes NOT NULL de gérer
    IF NEW.debut IS NULL OR NEW.fin IS NULL THEN
        RETURN NEW;
    END IF;

    -- Cas 2 : incohérence de l'année calendaire
    IF EXTRACT(YEAR FROM NEW.debut) <> EXTRACT(YEAR FROM NEW.fin) THEN
        RAISE EXCEPTION
            USING MESSAGE = format(
                    'Séjour invalide : début %s et fin %s ne sont pas dans la même année',
                    NEW.debut, NEW.fin
                 ),
                  ERRCODE = 'P0001';
    END IF;

    -- Cas 3 : on laisse passer
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Commentaires techniques

  • RETURNS trigger : obligatoire pour une fonction de trigger.
  • Utilisation de NEW (ligne insérée ou mise à jour).
  • Vérification explicite des NULL pour éviter des surprises si les contraintes ne sont pas encore toutes posées.
  • EXTRACT(YEAR FROM …) donne l’année sous forme numérique.
  • RAISE EXCEPTION USING … permet de préciser un message lisible et un code d’erreur. P0001 est un code générique “exception utilisateur” côté PL/pgSQL.

2. Création du trigger trg_controle_sejour_annee

On veut contrôler à la fois à l’insertion et à la mise à jour.

CREATE TRIGGER trg_controle_sejour_annee
BEFORE INSERT OR UPDATE ON sejour
FOR EACH ROW
EXECUTE FUNCTION controle_sejour_annee();

Commentaires

  • BEFORE : le contrôle est fait avant l’écriture en table.
  • INSERT OR UPDATE : couvre création et modification de séjour.
  • FOR EACH ROW : le test est fait pour chaque ligne concernée par l’ordre DML.

3. Jeux de tests

Tester un cas valide

INSERT INTO sejour(idc, idv, debut, fin)
VALUES (1, 1, '2025-07-10', '2025-07-20');

Attendu : insertion réussie (même année 2025).

Tester un cas invalide

INSERT INTO sejour(idc, idv, debut, fin)
VALUES (1, 1, '2025-12-28', '2026-01-03');

Attendu : exception du type

ERROR:  Séjour invalide : début 2025-12-28 et fin 2026-01-03 ne sont pas dans la même année
SQLSTATE: P0001

Tester une mise à jour invalide

UPDATE sejour
SET fin = '2026-01-05'
WHERE ids = <id_d_un_sejour_existant_en_2025>;

Même comportement : levée d’exception, rollback de l’ordre UPDATE.

4. Remarque de conception

Cette règle pourrait aussi être exprimée via un CHECK :

ALTER TABLE sejour
ADD CONSTRAINT ck_sejour_meme_annee
CHECK (EXTRACT(YEAR FROM debut) = EXTRACT(YEAR FROM fin));

Ici, l’exercice vise explicitement l’usage des triggers et de RAISE EXCEPTION. Le CHECK reste une solution plus simple et plus standard pour cette règle, mais moins pédagogique pour illustrer la partie “Exceptions et triggers”.

Correction Exercice 3 — Refus de suppression d’un village réservé

1. Fonction de trigger interdire_suppression_village

Objectif métier : tant qu’il existe au moins un séjour qui référence un village, ce village ne doit pas être supprimé. On veut en plus un message explicite côté application.

CREATE OR REPLACE FUNCTION interdire_suppression_village()
RETURNS trigger AS $$
DECLARE
    v_nb_sejours int;
BEGIN
    -- Compter les séjours liés au village qu'on tente de supprimer
    SELECT count(*) INTO v_nb_sejours
    FROM sejour
    WHERE idv = OLD.idv;

    IF v_nb_sejours > 0 THEN
        RAISE EXCEPTION
            USING MESSAGE = format(
                    'Suppression interdite : % séjours encore liés au village %',
                    v_nb_sejours, OLD.idv
                 ),
                  ERRCODE = 'P0002';
    END IF;

    -- Si aucun séjour ne dépend de ce village, on autorise la suppression
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

Points importants :

  • OLD contient la ligne de village qu’on est en train de supprimer.

  • On interroge sejour avec idv = OLD.idv pour voir si le village est encore utilisé.

  • Si v_nb_sejours > 0, on lève une exception :

    • message explicite pour le développeur / l’utilisateur,
    • ERRCODE custom (P0002) pour distinguer ce cas des autres erreurs.

  • Si aucun séjour lié, on retourne simplement OLD et la suppression se poursuit.

Remarque par rapport au bloc 2 :

  • Dans le TD2, la FK sejour.idv → village.idv est en ON DELETE RESTRICT (ou NO ACTION) : la base interdit déjà la suppression si des séjours existent.
  • Ici, le trigger ajoute un message métier explicite plutôt que de laisser PostgreSQL renvoyer un message générique de contrainte violée.
  • Si la FK était en ON DELETE CASCADE, ce trigger viendrait contredire ce choix (il empêcherait la suppression même si la FK autorise le cascade).

2. Création du trigger trg_protect_village

CREATE TRIGGER trg_protect_village
BEFORE DELETE ON village
FOR EACH ROW
EXECUTE FUNCTION interdire_suppression_village();

Justification des choix :

  • BEFORE DELETE : on teste avant la suppression effective.
  • FOR EACH ROW : une vérification par village supprimé.
  • Si la fonction lève une exception, la commande DELETE est annulée.

3. Tests de validation

Cas 1 — village occupé (au moins un séjour)

  1. Vérifier :
SELECT idv, ville, capacite
FROM village
WHERE idv = 10;

SELECT *
FROM sejour
WHERE idv = 10;
  1. Tentative de suppression :
DELETE FROM village WHERE idv = 10;

Attendu :

ERROR:  Suppression interdite : 3 séjours encore liés au village 10
SQLSTATE: P0002

Aucun DELETE effectif, les lignes de village et sejour restent présentes.

Cas 2 — village libre (aucun séjour)

  1. S’assurer qu’il n’y a pas de séjour :
SELECT *
FROM sejour
WHERE idv = 11;

résultat vide.

  1. Suppression :
DELETE FROM village WHERE idv = 11;

Attendu : suppression réussie, aucune exception, pas d’effet sur d’autres tables.

Effet global :

  • Toute tentative de suppression “dangereuse” de village est bloquée, avec un diagnostic clair.
  • La cohérence avec le cahier des charges est assurée au niveau base, indépendamment du code applicatif.

Correction Exercice 4 — Mise à jour automatique des totaux de séjours par client

0. Préparation du modèle

Ajouter la colonne nb_sejours dans client :

ALTER TABLE client
  ADD COLUMN nb_sejours int NOT NULL DEFAULT 0;

Initialiser les valeurs à partir de l’existant :

UPDATE client c
SET nb_sejours = sub.nb
FROM (
  SELECT idc, count(*) AS nb
  FROM sejour
  GROUP BY idc
) AS sub
WHERE c.idc = sub.idc;

-- Pour les clients sans séjour, nb_sejours reste à 0

Objectif : à partir de maintenant, maintenir nb_sejours automatiquement via triggers.

1. Fonction de trigger maj_nb_sejours_client

Version robuste qui gère INSERT, DELETE et UPDATE (changement de client pour un séjour).

CREATE OR REPLACE FUNCTION maj_nb_sejours_client()
RETURNS trigger AS $$
BEGIN
    -- Insertion d'un séjour : +1 pour le client concerné
    IF TG_OP = 'INSERT' THEN
        UPDATE client
        SET nb_sejours = nb_sejours + 1
        WHERE idc = NEW.idc;

        RETURN NEW;
    END IF;

    -- Suppression d'un séjour : -1 pour le client concerné
    IF TG_OP = 'DELETE' THEN
        UPDATE client
        SET nb_sejours = nb_sejours - 1
        WHERE idc = OLD.idc;

        RETURN OLD;
    END IF;

    -- Mise à jour d'un séjour : cas particulier si le séjour change de client
    IF TG_OP = 'UPDATE' THEN
        IF NEW.idc <> OLD.idc THEN
            UPDATE client
            SET nb_sejours = nb_sejours - 1
            WHERE idc = OLD.idc;

            UPDATE client
            SET nb_sejours = nb_sejours + 1
            WHERE idc = NEW.idc;
        END IF;

        RETURN NEW;
    END IF;

    -- Par sécurité, branche non atteinte
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Points techniques :

  • TG_OP est une variable système dans un trigger : 'INSERT', 'UPDATE', 'DELETE'.
  • NEW et OLD représentent la ligne après / avant modification.
  • On ajuste nb_sejours uniquement pour les clients concernés.
  • On gère explicitement le cas UPDATE où idc change, sinon aucune modification.

2. Trigger sur sejour

Un seul trigger qui couvre les trois opérations :

CREATE TRIGGER trg_maj_nb_sejours_client
AFTER INSERT OR DELETE OR UPDATE OF idc ON sejour
FOR EACH ROW
EXECUTE FUNCTION maj_nb_sejours_client();

Justification :

  • AFTER : le séjour est déjà validé par les contraintes (FK, dates, etc.). On ne touche qu’au compteur.
  • FOR EACH ROW : mise à jour fine pour chaque séjour.
  • UPDATE OF idc : le trigger ne se déclenche sur UPDATE que si idc change.

3. Jeux de tests

Insertion

INSERT INTO sejour(idc, idv, debut, fin)
VALUES (1, 1, '2025-07-10', '2025-07-15');

SELECT idc, nb_sejours FROM client WHERE idc = 1;
-- nb_sejours doit avoir augmenté de 1

Suppression

DELETE FROM sejour
WHERE ids = <un_ids_du_client_1>;

SELECT idc, nb_sejours FROM client WHERE idc = 1;
-- nb_sejours doit avoir diminué de 1

Changement de client

-- On suppose un séjour ids = 50, actuellement pour idc = 1
UPDATE sejour
SET idc = 2
WHERE ids = 50;

-- Vérification
SELECT idc, nb_sejours FROM client WHERE idc IN (1,2);
-- client 1 : -1
-- client 2 : +1

4. Remarque d’architecture

  • nb_sejours est une dérivée d’une information déjà présente (count(*) sur sejour).
  • On choisit de la stocker pour accélérer certains affichages (bilan client) au prix d’une logique de maintenance en trigger.
  • L’exercice illustre l’usage combiné de TG_OP, NEW, OLD et des triggers AFTER pour maintenir des totaux cohérents.

Correction Exercice 5 — Gestion des erreurs et messages

Objectif : exploiter les différents niveaux de messages (NOTICE, WARNING, EXCEPTION) et voir l’effet sur les transactions.

On part des fonctions déjà écrites aux exercices 1–3 et on les fait évoluer.

1. Exemple 1 : journalisation “soft” avec RAISE NOTICE

Sur la fonction d’archivage (Ex. 1), on peut affiner le message.

Version enrichie :

CREATE OR REPLACE FUNCTION archiver_sejour()
RETURNS trigger AS $$
BEGIN
    INSERT INTO archivesejour(ids, idc, idv, debut, fin, date_archivage)
    VALUES (OLD.ids, OLD.idc, OLD.idv, OLD.debut, OLD.fin, now());

    RAISE NOTICE
        'Archivage : séjour % client % village % de % à %',
        OLD.ids, OLD.idc, OLD.idv, OLD.debut, OLD.fin;

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

Effet :

  • NOTICE n’interrompt jamais la transaction.
  • Utile pour tracer les opérations en développement ou en audit.

Test minimal :

DELETE FROM sejour WHERE ids = 100;
-- Vérifier : message NOTICE + ligne dans archivesejour

2. Exemple 2 : avertissement non bloquant avec RAISE WARNING

Supposons que l’on veuille signaler un séjour “suspicious” mais ne pas le bloquer, par exemple un séjour très long (≥ 30 jours), tout en gardant la règle de même année.

On modifie controle_sejour_annee ainsi :

CREATE OR REPLACE FUNCTION controle_sejour_annee()
RETURNS trigger AS $$
DECLARE
    v_nb_jours int;
BEGIN
    IF NEW.debut IS NULL OR NEW.fin IS NULL THEN
        RETURN NEW;
    END IF;

    -- Règle forte : même année ou erreur
    IF EXTRACT(YEAR FROM NEW.debut) <> EXTRACT(YEAR FROM NEW.fin) THEN
        RAISE EXCEPTION
            USING MESSAGE = format(
                    'Séjour invalide : début %s et fin %s ne sont pas dans la même année',
                    NEW.debut, NEW.fin
                 ),
                  ERRCODE = 'P0001';
    END IF;

    -- Avertissement : séjours très longs
    v_nb_jours := NEW.fin - NEW.debut;
    IF v_nb_jours >= 30 THEN
        RAISE WARNING
            'Séjour long (% jours) pour le client % (ids potentiellement %)',
            v_nb_jours, NEW.idc, NEW.ids;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Explications :

  • RAISE WARNING affiche un message plus “sérieux” que NOTICE mais n’annule pas la commande.
  • Ici on accepte la ligne, mais on attire l’attention sur une situation anormale.

Tests :

-- Séjour long mais sur une seule année : accepté + WARNING
INSERT INTO sejour(idc, idv, debut, fin)
VALUES (1, 1, '2025-07-01', '2025-08-10');

-- Séjour multi-année : EXCEPTION, transaction annulée
INSERT INTO sejour(idc, idv, debut, fin)
VALUES (1, 1, '2025-12-28', '2026-01-05');

3. Exemple 3 : exception métier explicite avec RAISE EXCEPTION USING

Sur la fonction interdire_suppression_village (Ex. 3), on avait déjà une exception. On la rend plus structurée :

CREATE OR REPLACE FUNCTION interdire_suppression_village()
RETURNS trigger AS $$
DECLARE
    v_nb_sejours int;
BEGIN
    SELECT count(*) INTO v_nb_sejours
    FROM sejour
    WHERE idv = OLD.idv;

    IF v_nb_sejours > 0 THEN
        RAISE EXCEPTION
            USING MESSAGE = format(
                    'Suppression interdite : % séjours encore liés au village % (%).',
                    v_nb_sejours, OLD.idv, OLD.ville
                 ),
                  ERRCODE = 'P0002',
                  HINT    = 'Annulez ou archivez les séjours avant de supprimer le village.';
    END IF;

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

Explications :

  • MESSAGE : texte principal.
  • ERRCODE : permet de filtrer côté application.
  • HINT : affiché par PostgreSQL après l’erreur, utile pour guider l’utilisateur.

Test :

DELETE FROM village WHERE idv = 10;
-- → ERROR avec SQLSTATE P0002, message + hint

4. Effet sur les transactions : démonstration simple

But : montrer la différence entre WARNING/NOTICE et EXCEPTION sur la persistance.

Cas 1 — WARNING, la transaction continue
BEGIN;

-- Suppose qu'on déclenche le WARNING de sejour long
INSERT INTO sejour(idc, idv, debut, fin)
VALUES (1, 1, '2025-07-01', '2025-08-10');

-- WARNING s'affiche, mais on peut continuer :
INSERT INTO sejour(idc, idv, debut, fin)
VALUES (1, 1, '2025-09-01', '2025-09-05');

COMMIT;

Résultat :

  • Les 2 séjours sont insérés.
  • Le WARNING ne provoque pas de rollback.
Cas 2 — EXCEPTION, rollback
BEGIN;

-- Séjour invalide (multi-année) → EXCEPTION dans controle_sejour_annee
INSERT INTO sejour(idc, idv, debut, fin)
VALUES (1, 1, '2025-12-28', '2026-01-05');

-- Jamais atteint si l'exception est levée
INSERT INTO sejour(idc, idv, debut, fin)
VALUES (1, 1, '2025-07-01', '2025-07-10');

COMMIT;

Résultat :

  • Dès le premier INSERT, RAISE EXCEPTION annule la commande.
  • La transaction entière est marquée en erreur → COMMIT échoue.
  • Aucun des deux séjours n’est inséré.

Conclusion :

  • NOTICE : journalisation innocente.
  • WARNING : anomalie signalée, données acceptées.
  • EXCEPTION : blocage net, rollback.

Les trois niveaux sont maintenant utilisés dans les fonctions de triggers précédentes, avec des messages métier et des codes d’erreur explicites.

Partie C

Correction Exercice 6 — Scénario complet d’intégration des triggers

But : vérifier, en un seul scénario, que tous les triggers et règles du Bloc 5 se comportent comme prévu :

  • archivage automatique,
  • contrôle de cohérence (même année),
  • protection des villages occupés,
  • gestion des messages (NOTICE / EXCEPTION).

On suppose qu’on as déjà :

  • purge_sejours(p_date_limite date) (Bloc 3),
  • trg_archive_sejour + archiver_sejour() (Ex. 1),
  • trg_controle_sejour_annee + controle_sejour_annee() (Ex. 2),
  • trg_protect_village + interdire_suppression_village() (Ex. 3).

1. Préparation d’un jeu de données minimal

Création d’un client, d’un village et d’un séjour valide.

BEGIN;

-- Client de test
INSERT INTO client(nom, age)
VALUES ('Client TD5', 35)
RETURNING idc;
-- Supposons que la valeur retournée soit idc = 1001

-- Village de test
INSERT INTO village(ville, activite, prix, capacite)
VALUES ('Rio', 'surf', 120, 50)
RETURNING idv;
-- Supposons idv = 2001

-- Séjour valide (même année)
INSERT INTO sejour(idc, idv, debut, fin)
VALUES (1001, 2001, '2025-07-10', '2025-07-20')
RETURNING ids;
-- Supposons ids = 3001

COMMIT;

Vérification rapide :

SELECT * FROM sejour WHERE ids = 3001;
SELECT * FROM archivesejour WHERE ids = 3001;
-- ArchiveSejour doit être encore vide pour ids=3001

2. Purge + archivage

On réutilise purge_sejours(p_date_limite date) qui fait un DELETE sur sejour. Le trigger trg_archive_sejour doit archiver les lignes supprimées.

-- On choisit une date limite > fin du séjour (20 juillet 2025)
CALL purge_sejours('2025-12-31');

Effet attendu :

  • La procédure purge_sejours supprime les séjours dont fin < '2025-12-31'.

  • Pour chaque ligne supprimée, trg_archive_sejour se déclenche et :

    • insère la ligne correspondante dans ArchiveSejour,
    • affiche un NOTICE du type Archivage : séjour 3001 client 1001 village 2001 de 2025-07-10 à 2025-07-20.

Contrôle :

-- Doit être vide (séjour purgé)
SELECT * FROM sejour WHERE ids = 3001;

-- Doit contenir l'archive
SELECT ids, idc, idv, debut, fin, date_archivage
FROM archivesejour
WHERE ids = 3001
ORDER BY date_archivage DESC
LIMIT 1;

3. Protection de la suppression d’un village occupé

On recrée un séjour pour réutiliser le village et déclencher le trigger de protection.

BEGIN;

-- Recréer un séjour pour le village 2001
INSERT INTO sejour(idc, idv, debut, fin)
VALUES (1001, 2001, '2025-08-01', '2025-08-05')
RETURNING ids;
-- Supposons ids = 3002

-- Vérifier la présence du séjour
SELECT * FROM sejour WHERE ids = 3002;

Tentative de suppression du village :

DELETE FROM village WHERE idv = 2001;

Effet attendu :

  • trg_protect_village (BEFORE DELETE) s’exécute.
  • Il compte les séjours sejour.idv = 2001.
  • Comme au moins un séjour existe (ids = 3002), il lève :
ERROR:  Suppression interdite : 1 séjours encore liés au village 2001 (Rio).
SQLSTATE: P0002
HINT:    Annulez ou archivez les séjours avant de supprimer le village.

Vérification post-erreur :

-- Le village n'a PAS été supprimé
SELECT * FROM village WHERE idv = 2001;

-- Le séjour est toujours là
SELECT * FROM sejour WHERE ids = 3002;

ROLLBACK;  -- on annule la transaction qui contenait le DELETE

4. Contrôle de cohérence : séjour multi-année interdit

On teste maintenant le trigger trg_controle_sejour_annee.

-- Séjour valide (même année) : doit passer
INSERT INTO sejour(idc, idv, debut, fin)
VALUES (1001, 2001, '2025-09-01', '2025-09-10');

-- Séjour invalide (multi-année) : doit échouer
INSERT INTO sejour(idc, idv, debut, fin)
VALUES (1001, 2001, '2025-12-28', '2026-01-05');

Effet attendu pour le second INSERT :

ERROR:  Séjour invalide : début 2025-12-28 et fin 2026-01-05 ne sont pas dans la même année
SQLSTATE: P0001

Vérification :

-- Le séjour multi-année ne doit pas exister
SELECT *
FROM sejour
WHERE idc = 1001
  AND idv = 2001
  AND debut = '2025-12-28'
  AND fin   = '2026-01-05';

Résultat attendu : 0 ligne.

5. Synthèse du scénario

Ce scénario valide :

  1. Archivage automatique Toute suppression de séjour via purge_sejours crée une entrée dans ArchiveSejour grâce au trigger AFTER DELETE ON sejour.

  2. Protection de la suppression d’un village occupé Le trigger BEFORE DELETE ON village compte les séjours associés et bloque la suppression avec un message métier explicite.

  3. Contrôle de cohérence sur l’année Le trigger BEFORE INSERT OR UPDATE ON sejour interdit les séjours qui traversent deux années et lève une EXCEPTION avec un SQLSTATE spécifique.

  4. Messages Les NOTICE et EXCEPTION apparaissent bien dans le client SQL et traduisent les règles métier de l’application de séjours.

Correction Exercice 7 — Journalisation via log_evenement

Exercice (rappel, reformulé) Créer un journal log_evenement alimenté par un trigger AFTER INSERT OR DELETE ON sejour. Colonnes minimales : evenement text, horodatage timestamptz default now(). Visualiser ensuite les logs des dernières opérations.

1. Création de la table de log

Version minimale, avec un identifiant technique :

CREATE TABLE log_evenement (
    id_log      bigserial PRIMARY KEY,
    evenement   text NOT NULL,
    horodatage  timestamptz NOT NULL DEFAULT now()
);

Remarques :

  • bigserial : identifiant unique, utile pour trier les événements.
  • evenement : texte libre, on y met un message métier.
  • horodatage : date/heure de l’événement, par défaut now().

2. Fonction de trigger

Objectif :

  • loguer les INSERT et DELETE sur sejour avec un message lisible ;
  • éventuellement loguer aussi les UPDATE si on veut compléter.
CREATE OR REPLACE FUNCTION log_evenement_sejour()
RETURNS trigger AS $$
DECLARE
    v_msg text;
BEGIN
    IF TG_OP = 'INSERT' THEN
        v_msg := format(
            'INSERT sejour: ids=%s, idc=%s, idv=%s, debut=%s, fin=%s',
            NEW.ids, NEW.idc, NEW.idv, NEW.debut, NEW.fin
        );

        INSERT INTO log_evenement(evenement)
        VALUES (v_msg);

        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        v_msg := format(
            'DELETE sejour: ids=%s, idc=%s, idv=%s, debut=%s, fin=%s',
            OLD.ids, OLD.idc, OLD.idv, OLD.debut, OLD.fin
        );

        INSERT INTO log_evenement(evenement)
        VALUES (v_msg);

        RETURN OLD;
    ELSE
        -- Si un jour on ajoute UPDATE à ce trigger, traiter ici
        RETURN NULL;
    END IF;
END;
$$ LANGUAGE plpgsql;

Points techniques :

  • TG_OP indique l’opération (INSERT, UPDATE, DELETE).
  • On utilise NEW pour INSERT, OLD pour DELETE.
  • format() permet de construire un message structuré.
  • On ne fixe pas horodatage : la valeur par défaut now() joue son rôle.

3. Création du trigger

CREATE TRIGGER trg_log_evenement_sejour
AFTER INSERT OR DELETE ON sejour
FOR EACH ROW
EXECUTE FUNCTION log_evenement_sejour();

Justification :

  • AFTER : on ne logue que si l’opération a effectivement réussi.
  • FOR EACH ROW : un log par séjour inséré ou supprimé.

4. Jeux de tests

Insertion

INSERT INTO sejour(idc, idv, debut, fin)
VALUES (1, 1, '2025-07-10', '2025-07-15')
RETURNING ids;
-- Supposons ids = 4001

Suppression

DELETE FROM sejour WHERE ids = 4001;

Inspecter les logs :

SELECT id_log, horodatage, evenement
FROM log_evenement
ORDER BY horodatage DESC
LIMIT 10;

Résultat typique :

 id_log |      horodatage              |                       evenement
--------+------------------------------+----------------------------------------------------
   2    | 2025-07-10 15:02:33.123+02   | DELETE sejour: ids=4001, idc=1, idv=1, ...
   1    | 2025-07-10 15:02:32.987+02   | INSERT sejour: ids=4001, idc=1, idv=1, ...

5. Interaction avec les autres triggers

  • Si la suppression déclenche aussi archiver_sejour(), l’ordre d’exécution par défaut est celui de création des triggers.

  • En pratique, pour ce TD, l’ordre n’est pas critique :

    • trg_archive_sejour gère l’INSERT dans ArchiveSejour,
    • trg_log_evenement_sejour journalise le DELETE.

On obtient donc :

  • archive métier dans ArchiveSejour ;
  • trace générique dans log_evenement.

Partie D

Correction Exercice 8 — Schéma dédié + vue avec trigger INSTEAD OF

Je restructure cet exo pour simplifier :

  1. Regrouper les fonctions de triggers dans un schéma dédié.
  2. Créer une vue de synthèse vue_sejours_actifs.
  3. Ajouter un trigger INSTEAD OF dessus pour gérer les suppressions via la vue.
  4. Comparer “logique en trigger” vs “logique en procédure”.

1. Schéma dédié pour la logique technique

Objectif : éviter de polluer public avec toutes les fonctions techniques (triggers, utilitaires). On crée un schéma systeme et on y met les fonctions de triggers.

1.1. Création du schéma
CREATE SCHEMA IF NOT EXISTS systeme;
1.2. Déplacement logique des fonctions

Idée : recréer les fonctions dans le schéma systeme, puis mettre à jour les triggers.

Exemple pour archiver_sejour :

CREATE OR REPLACE FUNCTION systeme.archiver_sejour()
RETURNS trigger AS $$
BEGIN
    INSERT INTO archivesejour(ids, idc, idv, debut, fin, date_archivage)
    VALUES (OLD.ids, OLD.idc, OLD.idv, OLD.debut, OLD.fin, now());

    RAISE NOTICE
        'Archivage : séjour % client % village % de % à %',
        OLD.ids, OLD.idc, OLD.idv, OLD.debut, OLD.fin;

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

Même chose pour les autres fonctions :

CREATE OR REPLACE FUNCTION systeme.controle_sejour_annee()
RETURNS trigger AS $$
DECLARE
    v_nb_jours int;
BEGIN
    IF NEW.debut IS NULL OR NEW.fin IS NULL THEN
        RETURN NEW;
    END IF;

    IF EXTRACT(YEAR FROM NEW.debut) <> EXTRACT(YEAR FROM NEW.fin) THEN
        RAISE EXCEPTION
            USING MESSAGE = format(
                    'Séjour invalide : début %s et fin %s ne sont pas dans la même année',
                    NEW.debut, NEW.fin
                 ),
                  ERRCODE = 'P0001';
    END IF;

    v_nb_jours := NEW.fin - NEW.debut;
    IF v_nb_jours >= 30 THEN
        RAISE WARNING
            'Séjour long (% jours) pour le client % (ids potentiellement %)',
            v_nb_jours, NEW.idc, NEW.ids;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION systeme.interdire_suppression_village()
RETURNS trigger AS $$
DECLARE
    v_nb_sejours int;
BEGIN
    SELECT count(*) INTO v_nb_sejours
    FROM sejour
    WHERE idv = OLD.idv;

    IF v_nb_sejours > 0 THEN
        RAISE EXCEPTION
            USING MESSAGE = format(
                    'Suppression interdite : % séjours encore liés au village % (%).',
                    v_nb_sejours, OLD.idv, OLD.ville
                 ),
                  ERRCODE = 'P0002',
                  HINT    = 'Annulez ou archivez les séjours avant de supprimer le village.';
    END IF;

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION systeme.maj_nb_sejours_client()
RETURNS trigger AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE client
        SET nb_sejours = nb_sejours + 1
        WHERE idc = NEW.idc;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE client
        SET nb_sejours = nb_sejours - 1
        WHERE idc = OLD.idc;
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        IF NEW.idc <> OLD.idc THEN
            UPDATE client
            SET nb_sejours = nb_sejours - 1
            WHERE idc = OLD.idc;

            UPDATE client
            SET nb_sejours = nb_sejours + 1
            WHERE idc = NEW.idc;
        END IF;
        RETURN NEW;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Idem pour log_evenement_sejour si on veut la déplacer :

CREATE OR REPLACE FUNCTION systeme.log_evenement_sejour()
RETURNS trigger AS $$
DECLARE
    v_msg text;
BEGIN
    IF TG_OP = 'INSERT' THEN
        v_msg := format(
            'INSERT sejour: ids=%s, idc=%s, idv=%s, debut=%s, fin=%s',
            NEW.ids, NEW.idc, NEW.idv, NEW.debut, NEW.fin
        );
        INSERT INTO log_evenement(evenement) VALUES (v_msg);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        v_msg := format(
            'DELETE sejour: ids=%s, idc=%s, idv=%s, debut=%s, fin=%s',
            OLD.ids, OLD.idc, OLD.idv, OLD.debut, OLD.fin
        );
        INSERT INTO log_evenement(evenement) VALUES (v_msg);
        RETURN OLD;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
1.3. Mise à jour des triggers

Il faut redéfinir les triggers pour appeler les versions qualifiées :

DROP TRIGGER IF EXISTS trg_archive_sejour ON sejour;
CREATE TRIGGER trg_archive_sejour
AFTER DELETE ON sejour
FOR EACH ROW
EXECUTE FUNCTION systeme.archiver_sejour();


DROP TRIGGER IF EXISTS trg_controle_sejour_annee ON sejour;
CREATE TRIGGER trg_controle_sejour_annee
BEFORE INSERT OR UPDATE ON sejour
FOR EACH ROW
EXECUTE FUNCTION systeme.controle_sejour_annee();


DROP TRIGGER IF EXISTS trg_protect_village ON village;
CREATE TRIGGER trg_protect_village
BEFORE DELETE ON village
FOR EACH ROW
EXECUTE FUNCTION systeme.interdire_suppression_village();


DROP TRIGGER IF EXISTS trg_maj_nb_sejours_client ON sejour;
CREATE TRIGGER trg_maj_nb_sejours_client
AFTER INSERT OR DELETE OR UPDATE OF idc ON sejour
FOR EACH ROW
EXECUTE FUNCTION systeme.maj_nb_sejours_client();


DROP TRIGGER IF EXISTS trg_log_evenement_sejour ON sejour;
CREATE TRIGGER trg_log_evenement_sejour
AFTER INSERT OR DELETE ON sejour
FOR EACH ROW
EXECUTE FUNCTION systeme.log_evenement_sejour();

Intérêt :

  • séparation claire modèle métier (public.client, public.sejour, …) / plomberie technique (systeme.*),
  • lecture plus propre du schéma public.

2. Vue de synthèse des séjours actifs

Objectif : fournir une vue “orientée métier” pour lecture, mais aussi pour permettre une suppression logique via la vue.

2.1. Création de la vue

Séjours actifs à la date du jour (ou on fixe une condition plus simple).

CREATE OR REPLACE VIEW vue_sejours_actifs AS
SELECT
    s.ids,
    s.idc,
    c.nom          AS nom_client,
    s.idv,
    v.ville,
    v.activite,
    s.debut,
    s.fin
FROM sejour s
JOIN client  c USING(idc)
JOIN village v USING(idv)
WHERE CURRENT_DATE BETWEEN s.debut AND s.fin;

Vue simple, lecture seule par défaut. Objectif du trigger INSTEAD OF : autoriser :

DELETE FROM vue_sejours_actifs WHERE ids = ...;

en le réinterprétant sur la table sejour (avec archivage automatique déjà en place).

3. Trigger INSTEAD OF sur la vue

3.1. Fonction de trigger
CREATE OR REPLACE FUNCTION systeme.delete_sejour_via_vue()
RETURNS trigger AS $$
BEGIN
    -- On redirige la suppression vers la table de base
    DELETE FROM sejour
    WHERE ids = OLD.ids;

    -- Le trigger INSTEAD OF doit renvoyer OLD pour DELETE
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

Remarque :

  • On n’essaie pas de supprimer les lignes dans client ou village : ce n’est pas l’objectif métier.
  • Le trigger AFTER DELETE ON sejour (trg_archive_sejour) s’exécutera et archivera automatiquement le séjour.
3.2. Définition du trigger INSTEAD OF
CREATE TRIGGER trg_delete_sejour_via_vue
INSTEAD OF DELETE ON vue_sejours_actifs
FOR EACH ROW
EXECUTE FUNCTION systeme.delete_sejour_via_vue();

Points clés :

  • INSTEAD OF n’est possible que sur des vues.
  • PostgreSQL exécute cette fonction à la place de la tentative de DELETE sur la vue.

4. Tests de bout en bout

4.1. Insertion d’un séjour actif
INSERT INTO client(nom, age) VALUES ('Client Vue', 40) RETURNING idc;
-- supposons idc=5001

INSERT INTO village(ville, activite, prix, capacite)
VALUES ('Nice', 'plage', 100, 20) RETURNING idv;
-- supposons idv=6001

INSERT INTO sejour(idc, idv, debut, fin)
VALUES (5001, 6001, CURRENT_DATE, CURRENT_DATE + 5)
RETURNING ids;
-- supposons ids=7001

Vérifier la vue :

SELECT * FROM vue_sejours_actifs WHERE ids = 7001;

Doit retourner 1 ligne.

4.2. Suppression via la vue
DELETE FROM vue_sejours_actifs WHERE ids = 7001;

Effet prévu :

  • Le INSTEAD OF appelle systeme.delete_sejour_via_vue() :

    • DELETE FROM sejour WHERE ids = 7001;

  • Ce DELETE déclenche :

    • trg_archive_sejour → insertion dans archivesejour,
    • trg_log_evenement_sejour → log dans log_evenement,
    • trg_maj_nb_sejours_client → mise à jour de nb_sejours, etc.

Contrôles :

-- Le séjour a disparu de la table
SELECT * FROM sejour WHERE ids = 7001;

-- Il n'est plus visible dans la vue
SELECT * FROM vue_sejours_actifs WHERE ids = 7001;

-- Il a été archivé
SELECT * FROM archivesejour WHERE ids = 7001 ORDER BY date_archivage DESC LIMIT 1;

-- Il est logué
SELECT * FROM log_evenement
WHERE evenement LIKE 'DELETE sejour: ids=7001%'
ORDER BY horodatage DESC
LIMIT 1;

5. Comparaison : triggers vs procédures

Ce qui émerge de cet exercice :

  • Procédures (Bloc 3–4)

    • utilisées par le code applicatif de manière explicite (CALL purge_sejours(...), CALL annuler_sejour(...)),
    • logique claire, mais rien n’empêche d’agir directement sur les tables en contournant la procédure.

  • Triggers (Bloc 5)

    • s’appliquent à toutes les opérations sur les tables / vues concernées,
    • imposent les règles métier même si un script SQL “brut” est exécuté,
    • centralisent des tâches techniques : archivage, comptages, journaux.

  • Vue + trigger INSTEAD OF

    • offrent une API SQL plus riche : on manipule vue_sejours_actifs comme un objet métier,
    • la logique d’écriture est routée vers les bonnes tables et déclencheurs.

Conclusion technique :

  • Procédures = entrée “officielle” pour l’application.
  • Triggers = garde-fous et automatisation sous-jacente.
  • Vues + triggers INSTEAD OF = façade métier SQL au-dessus de la structure physique.
Pierre-Henri Paris
Pierre-Henri Paris
Associate Professor in Artificial Intelligence

My research interests include Knowlegde Graphs, Information Extraction, and NLP.