GeoNature. Petit trick pour modifier les noms français et cd_ref (attention, le trigger modifie la table taxonomie.taxref)

my_taxref.sql

CREATE TABLE taxonomie.taxref_mytaxref (
    cd_nom integer UNIQUE,
    cd_ref integer,
    nom_vern varchar(1000)
)
COMMENT ON TABLE taxonomie.taxref_mytaxref IS 'Attention, les modifications de cd_ref ou nom_vern intégrées dans cette table écraserons les valeurs de la table taxref.';

CREATE OR REPLACE FUNCTION taxonomie.fct_trg_refresh_taxons_forautocomplete()
  RETURNS trigger
  LANGUAGE plpgsql
  AS $function$
BEGIN
 LOCK TABLE gn_synthese.synthese IN SHARE MODE;
 DELETE FROM gn_synthese.taxons_synthese_autocomplete WHERE cd_nom=NEW.cd_nom;
 --UPDATE TAXREF
  UPDATE taxonomie.taxref SET
    cd_ref = coalesce(NEW.cd_ref,taxonomie.taxref.cd_ref),
    nom_vern = coalesce(NEW.nom_vern,taxonomie.taxref.nom_vern)
 WHERE taxonomie.taxref.cd_nom = NEW.cd_nom;
 --Temporary disable meta et area trigger
 ALTER TABLE gn_synthese.synthese DISABLE TRIGGER tri_meta_dates_change_synthese;
 ALTER TABLE gn_synthese.synthese DISABLE TRIGGER tri_insert_cor_area_synthese;
 --UPDATE cd_nom (pour déclencher la mise à jour de taxons_synthese_autocomplete) 
  UPDATE gn_synthese.synthese 
  SET cd_nom = cd_nom 
  WHERE id_synthese IN (SELECT id_synthese FROM gn_synthese.synthese WHERE cd_nom = NEW.cd_nom LIMIT 1 );
 --enable triggers
 ALTER TABLE gn_synthese.synthese ENABLE TRIGGER tri_meta_dates_change_synthese;
 ALTER TABLE gn_synthese.synthese ENABLE TRIGGER tri_insert_cor_area_synthese;
RETURN NULL;
END;
$function$
;

CREATE TRIGGER trg_refresh_taxons_forautocomplete AFTER INSERT OR UPDATE
ON taxonomie.taxref_mytaxref FOR EACH ROW EXECUTE PROCEDURE taxonomie.fct_trg_refresh_taxons_forautocomplete();