Absence d'intégrité médias et attributs

[camillemonchicourt]

Il n'y a pas de contrainte d'intégrité sur taxonomie.cor_taxon_attribut ni taxonomie.t_medias.
Cela est du au fait qu'on rattache les enregistrements de ces 2 tables à un CD_ref, hors les CD_ref ne sont pas uniques dans taxonomie.bib_noms.

Du coup il est possible de supprimer un taxon dans taxonomie.bib_noms et que des attributs et médias deviennent alors orphelins.

Une solution imaginée est de gérer cette intégrité par un trigger mais ça ne semble pas une piste idéale.

A creuser.

[gildeluermoz]

J'ai mis ce trigger BEFORE UPDATE OR DELETE en test sur notre base taxhub

CREATE OR REPLACE FUNCTION taxonomie.update_or_delete_taxon()
  RETURNS trigger AS
$BODY$
DECLARE
    nboldcdref integer;
    nbnewcdref integer;
    nbattr integer;
    nbmedia integer;
BEGIN
    IF (TG_OP = 'DELETE') THEN
    --on regarde si le taxon supprimé est le seul synonyme, donc si après suppression son cd_ref existera ou n'existera plus dans la table
    SELECT INTO nboldcdref count(cd_ref) FROM taxonomie.bib_noms WHERE cd_ref = old.cd_ref;
    IF nboldcdref <=1 THEN
        --si oui on vérifie si des attributs ou des medias sont attachés à ce cd_ref
        SELECT INTO nbattr count(cd_ref) FROM taxonomie.cor_taxon_attribut WHERE cd_ref = old.cd_ref;
        SELECT INTO nbmedia count(cd_ref) FROM taxonomie.t_medias WHERE cd_ref = old.cd_ref;
        IF nbattr > 0 THEN
            RAISE EXCEPTION 'Le cd_ref % ne peut être supprimé car il est référencé dans la table taxonomie.cor_taxon_attribut', old.cd_ref 
            USING HINT = 'Vous devez supprimer les enregistrements correspondants au préalable.';
            RETURN NULL; -- = annulation de l'opération delete
        END IF;
        IF nbmedia > 0 THEN
            RAISE EXCEPTION 'Le cd_ref % ne peut être supprimé car il est référencé dans la table taxonomie.t_medias', old.cd_ref 
            USING HINT = 'Vous devez supprimer les enregistrements correspondants au préalable.';
            RETURN NULL; -- = annulation de l'opération delete
        END IF;
    END IF;
    RETURN OLD;
    END IF;
    IF (TG_OP = 'UPDATE') AND new.cd_ref <> old.cd_ref THEN
    --on regarde si le taxon supprimé est le seul synonyme, donc si après modification du cd_ref, l'ancien cd_ref existera ou n'existera plus dans la table
    SELECT INTO nboldcdref count(cd_ref) FROM taxonomie.bib_noms WHERE cd_ref = old.cd_ref;
    IF nboldcdref <= 1 THEN --si l'ancien cd_ref disparait de la table taxonomie.bib_noms
        --si oui on vérifie si des attributs ou des medias sont attachés à ce cd_ref qui n'existera plus
        SELECT INTO nbattr count(cd_ref) FROM taxonomie.cor_taxon_attribut WHERE cd_ref = old.cd_ref;
        SELECT INTO nbmedia count(cd_ref) FROM taxonomie.t_medias WHERE cd_ref = old.cd_ref;
        IF nbattr > 0 THEN --si oui on cascade la modification dans la table taxonomie.cor_taxon attribut
            UPDATE taxonomie.cor_taxon_attribut SET cd_ref = new.cd_ref WHERE cd_ref = old.cd_ref;
        END IF;
        IF nbmedia > 0 THEN --si oui on cascade la modification dans la table taxonomie.t_medias
            UPDATE taxonomie.t_medias SET cd_ref = new.cd_ref WHERE cd_ref = old.cd_ref;
        END IF;
    ELSE --si l'ancien cd_ref continue d'exister dans la table taxonomie.bib_noms (via un synonyme)
        SELECT INTO nbnewcdref count(cd_ref) FROM taxonomie.bib_noms WHERE cd_ref = new.cd_ref; --si le nouveau cd_ref existe déjà dans la table taxonomie.bib_noms
        IF nbnewcdref > 0 THEN
            RAISE WARNING 'L''ancien cd_ref (%) ainsi que le nouveau cd_ref (%) que vous venez d''affecter au taxon modifié existe aussi dans un autre enregistrement de la table taxonomie.bib_noms (synonymes ).', old.cd_ref, new.cd_ref
            USING HINT = E'Vous devez vérifier si ce nouveau cd_ref de rattachement dispose ou non d''attributs et/ou de médias et s''ils sont cohérents avec le taxon modifié.\nEn effet, les éventuels médias et/ou les attributs attachés à l''ancien cd_ref du taxon que vous venez de modifier reste attachés à cet ancien cd_ref. Ils ne seront donc pas rattachés au nouveau cd_ref.';
        ELSE
            RAISE INFO 'L''ancien cd_ref (%) existe dans un autre enregistrement synonyme de la table taxonomie.bib_noms mais le nouveau cd_ref (%) n''existait pas.', old.cd_ref, new.cd_ref
            USING HINT = E'Le taxon dont vous venez de modifier le cd_ref n''a donc ni attribut, ni média.\nEn effet, les éventuels médias et/ou les attributs attachés à l''ancien cd_ref du taxon que vous venez de modifier reste attachés à l''ancien cd_ref.';
        END IF;
    END IF;
    RETURN NEW;
    ELSE
    RETURN NEW;
    END IF;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION taxonomie.update_or_delete_taxon()
  OWNER TO cartopne;
GRANT EXECUTE ON FUNCTION taxonomie.update_or_delete_taxon() TO public;