104 lines
3.1 KiB
PL/PgSQL
104 lines
3.1 KiB
PL/PgSQL
-- ============================================================
|
|
-- 05_triggers.sql — Triggers métier et maintenance
|
|
-- ============================================================
|
|
|
|
-- ===========================================
|
|
-- 1. Mettre à jour automatiquement modifie_le
|
|
-- ===========================================
|
|
CREATE OR REPLACE FUNCTION set_modifie_le()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.modifie_le := now();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Appliquer sur les tables qui ont modifie_le
|
|
CREATE TRIGGER trg_update_modifie_le_utilisateurs
|
|
BEFORE UPDATE ON utilisateurs
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_modifie_le();
|
|
|
|
CREATE TRIGGER trg_update_modifie_le_dossiers
|
|
BEFORE UPDATE ON dossiers
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_modifie_le();
|
|
|
|
CREATE TRIGGER trg_update_modifie_le_contrats
|
|
BEFORE UPDATE ON contrats
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_modifie_le();
|
|
|
|
CREATE TRIGGER trg_update_modifie_le_avenants
|
|
BEFORE UPDATE ON avenants_contrats
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_modifie_le();
|
|
|
|
CREATE TRIGGER trg_update_modifie_le_evenements
|
|
BEFORE UPDATE ON evenements
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_modifie_le();
|
|
|
|
CREATE TRIGGER trg_update_modifie_le_validations
|
|
BEFORE UPDATE ON validations
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_modifie_le();
|
|
|
|
-- ===========================================
|
|
-- 2. Empêcher plusieurs contrats actifs par dossier
|
|
-- ===========================================
|
|
CREATE OR REPLACE FUNCTION prevent_multiple_active_contrats()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
nb_actifs INT;
|
|
BEGIN
|
|
IF NEW.statut IN ('en_attente_signature','valide') THEN
|
|
SELECT COUNT(*) INTO nb_actifs
|
|
FROM contrats
|
|
WHERE id_dossier = NEW.id_dossier
|
|
AND statut IN ('en_attente_signature','valide')
|
|
AND id <> COALESCE(NEW.id, gen_random_uuid());
|
|
|
|
IF nb_actifs > 0 THEN
|
|
RAISE EXCEPTION 'Un contrat actif existe déjà pour ce dossier (%).', NEW.id_dossier;
|
|
END IF;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_one_active_contrat_per_dossier
|
|
BEFORE INSERT OR UPDATE ON contrats
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION prevent_multiple_active_contrats();
|
|
|
|
-- ===========================================
|
|
-- 3. Empêcher événements incohérents
|
|
-- - Pas de chevauchement de congés pour le même AM
|
|
-- ===========================================
|
|
CREATE OR REPLACE FUNCTION prevent_overlapping_events()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
nb_overlap INT;
|
|
BEGIN
|
|
IF NEW.id_am IS NOT NULL AND NEW.date_debut IS NOT NULL AND NEW.date_fin IS NOT NULL THEN
|
|
SELECT COUNT(*) INTO nb_overlap
|
|
FROM evenements ev
|
|
WHERE ev.id_am = NEW.id_am
|
|
AND ev.id <> COALESCE(NEW.id, gen_random_uuid())
|
|
AND ev.date_fin >= NEW.date_debut
|
|
AND ev.date_debut <= NEW.date_fin;
|
|
|
|
IF nb_overlap > 0 THEN
|
|
RAISE EXCEPTION 'Conflit d''événements : chevauchement détecté pour AM %', NEW.id_am;
|
|
END IF;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_no_overlapping_am_events
|
|
BEFORE INSERT OR UPDATE ON evenements
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION prevent_overlapping_events();
|