--
-- BEGIN - Correctif des triggers de cohérence des om_collectivités
--

CREATE OR REPLACE FUNCTION check_zone_om_collectivite_coherence() RETURNS trigger AS
$check_zone_om_collectivite_coherence$
BEGIN
IF NEW.om_collectivite != (SELECT om_collectivite FROM opencimetiere.cimetiere c WHERE c.cimetiere = NEW.cimetiere) then
    RAISE EXCEPTION 'La zone doit posséder la même om_collectivite que son cimetière.';
END IF;
RETURN NEW;
END;
$check_zone_om_collectivite_coherence$
LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION check_voie_om_collectivite_coherence() RETURNS trigger AS
$check_voie_om_collectivite_coherence$
BEGIN
IF NEW.om_collectivite != (SELECT z.om_collectivite FROM opencimetiere.zone z WHERE z.zone = NEW.zone) 
    AND NEW.om_collectivite != (SELECT c.om_collectivite FROM opencimetiere.cimetiere c INNER JOIN opencimetiere.zone z ON z.cimetiere = c.cimetiere WHERE z.zone = NEW.zone) then
    RAISE EXCEPTION 'La voie doit posséder la même om_collectivite que sa zone.';
END IF;
RETURN NEW;
END;
$check_voie_om_collectivite_coherence$
LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION check_emplacement_om_collectivite_coherence() RETURNS trigger AS
$check_emplacement_om_collectivite_coherence$
BEGIN
IF NEW.om_collectivite != (SELECT v.om_collectivite FROM opencimetiere.voie v WHERE v.voie = NEW.voie)
    AND NEW.om_collectivite != (SELECT z.om_collectivite FROM opencimetiere.zone z INNER JOIN opencimetiere.voie v ON v.zone = z.zone WHERE v.voie = NEW.voie)
    AND NEW.om_collectivite != (SELECT c.om_collectivite FROM opencimetiere.cimetiere c INNER JOIN opencimetiere.zone z ON z.cimetiere = c.cimetiere INNER JOIN opencimetiere.voie v ON v.zone = z.zone WHERE v.voie = NEW.voie) then
    RAISE EXCEPTION 'L''emplacement doit posséder la même om_collectivite que sa zone, sa voie et son cimetière.';
END IF;
RETURN NEW;
END;
$check_emplacement_om_collectivite_coherence$
LANGUAGE 'plpgsql' VOLATILE;

DROP TRIGGER IF EXISTS before_insert_or_update_zone ON zone;
DROP TRIGGER IF EXISTS before_insert_or_update_voie ON voie;
DROP TRIGGER IF EXISTS before_insert_or_update_emplacement ON emplacement;

CREATE TRIGGER before_insert_or_update_zone
    BEFORE INSERT OR UPDATE ON zone
    FOR EACH ROW EXECUTE PROCEDURE check_zone_om_collectivite_coherence();

CREATE TRIGGER before_insert_or_update_voie
    BEFORE INSERT OR UPDATE ON voie
    FOR EACH ROW EXECUTE PROCEDURE check_voie_om_collectivite_coherence();

CREATE TRIGGER before_insert_or_update_emplacement
    BEFORE INSERT OR UPDATE ON emplacement
    FOR EACH ROW EXECUTE PROCEDURE check_emplacement_om_collectivite_coherence();

--
-- END - Correctif des triggers de cohérence des om_collectivités
--
