Le portail francophone de la géomatique

muze · Wed 16 July 2014 11:05

Bonjour,

J'ai un grand besoin d'aide pour la mise en place d'un TRIGGER sous postgis. Voici ce que j'aimerais faire :
J'ai une couche de données sur des projets d'aménagement et je voudrais que, lorsqu'une modification est faite sur la typologie de logements d'un projet (T1, T2.... libre ou social), des champs de totaux se mettent à jour automatiquement.

Après avoir bataillé plusieurs heures sur la syntaxe à employer pour un tel trigger, voici ce à quoi j'ai abouti :

Code:

--Fonction--
CREATE OR REPLACE FUNCTION upgradeprojets_attribut()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
NEW."nbr_t1_total__calcul_" := NEW."nbr_t1_libres" + NEW."nbr_t1_sociaux";
NEW."nbr_t2_total__calcul_" := NEW."nbr_t2_libres" + NEW."nbr_t2_sociaux";
NEW."nbr_t3_total__calcul_" := NEW."nbr_t3_libres" + NEW."nbr_t3_sociaux";
NEW."nbr_t4_total__calcul_" := NEW."nbr_t4_libres" + NEW."nbr_t4_sociaux";
NEW."nbr_t5_total__calcul_" := NEW."nbr_t5_libres" + NEW."nbr_t5_sociaux";
NEW."nbr_studios_chambres_total__calcul_" = NEW."nbr_studios_chambres_libres" + NEW."nbr_studios_chambres_sociaux";
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

--Trigger--
CREATE TRIGGER upgradeprojets_attribut
    BEFORE UPDATE ON urba.projets
    FOR EACH ROW
    EXECUTE PROCEDURE upgradeprojets_attribut();

Ce qui fonctionne :
Lorsque les 2 champs nécessaires au calcul d'un total sont tous les 2 mis à jour, le total l'est également. Donc pour ça c'est bien !

Ce à quoi je voudrais aboutir :
J’aimerais que ce total soit aussi mis à jour quand la modification s'applique uniquement sur un seul des 2 champs nécessaire au calcul.

Je sèche complétement sur la méthode à employer (peut-etre intégrer des exceptions...), donc si vous avez une idée je suis plus qu'à l'écoute.

Merci d'avance.

Nicolas Ribot · Wed 16 July 2014 11:52

Bonjour,

Vous pouvez utilisez des IF THEN dans une fonction plpgsql (http://www.postgresql.org/docs/current/ … NDITIONALS).

Dans votre cas, si un des deux champs est null dans l'objet NEW (une seule valeur mise a jour), alors la somme vaut NEW.champ1 + OLD.champ2 (ou NEW.champ2 + OLD.champ1)

Nicolas

muze · Mon 21 July 2014 18:57

Bonjour nicolas,

Merci beaucoup pour tes indications !!!

J'ai reussi à faire ce que je voulais en utilisant les conditions IF THEN ELSIF ELSE.

Voici un petit extrait :

Code:

CREATE OR REPLACE FUNCTION upgradeprojets_attribut()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
        IF NEW."nbr_t1_libres" is NULL
    THEN NEW."nbr_t1_total__calcul_" := OLD."nbr_t1_libres" + NEW."nbr_t1_sociaux";
    ELSIF NEW."nbr_t1_sociaux" is NULL
    THEN NEW."nbr_t1_total__calcul_" := NEW."nbr_t1_libres" + OLD."nbr_t1_sociaux";
    ELSE NEW."nbr_t1_total__calcul_" := NEW."nbr_t1_libres" + NEW."nbr_t1_sociaux";
    END IF;
        IF NEW."nbr_t2_libres" is NULL
    THEN NEW."nbr_t2_total__calcul_" := OLD."nbr_t2_libres" + NEW."nbr_t2_sociaux";
    ELSIF NEW."nbr_t2_sociaux" is NULL
    THEN NEW."nbr_t2_total__calcul_" := NEW."nbr_t2_libres" + OLD."nbr_t2_sociaux";
    ELSE NEW."nbr_t2_total__calcul_" := NEW."nbr_t2_libres" + NEW."nbr_t2_sociaux";
    END IF;
        Return NEW;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Nicolas Ribot · Mon 21 July 2014 19:59

Bonsoir,

Merci du retour.

Pfff, je devrais prendre qq jours de vacances...

Il y a une fonction SQL parfaite pour gérer des valeurs nulles: COALESCE(colonne, valeur_par_defaut)

Cette fonction renvoie soit la colonne si elle n'est pas nulle, soit la valeur par défaut.

Ainsi, dans votre trigger, la logique serait:

Code:

NEW."nbr_t1_total__calcul_" := coalesce(NEW."nbr_t1_libres", OLD."nbr_t1_libres") + coalesce(NEW."nbr_t1_sociaux", OLD."nbr_t1_sociaux");

Si une valeur NEW est nulle, la valeur contenue dans la table (OLD) sera prise en compte.

Nicolas

muze · Wed 23 July 2014 14:00

Merci pour cette astuce, c'est vrai que ça va me permettre de grandement simplifier mon trigger

Comme j'ai aussi intégré des pourcentages dans mon trigger, je me suis retrouvé avec une erreur sur QGIS ("division by zero not allowed") du au fait que le total, nécessaire pour le calcul, n'était pas toujours renseigné. J'ai suis passé par la fonction NULLIF pour y remédier

Finalement c'est un peu le même fonctionnement que COALESCE....

muze · Fri 12 September 2014 16:19

Bonjour, me revoila avec une question de TRIGGER

J'aimerais pouvoir mettre à jour un champs d'avancement en fonctionnement du renseignement d'une date
En gros j'ai 3 dates d'avancement, et 3 niveaux d'avancement. Donc quand 1 date et renseigné "avancement = 1", quand 2 dates sont renseignées "avancement = 2", quand 3 dates sont renseignées "avancement = 3"

Code:

CREATE OR REPLACE FUNCTION update_calendrier()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
    NEW.avancement := '03 – terminé' where NEW.date_3 is not null;
    NEW.avancement := '02 – en cours' where NEW.date_2 is not null AND (coalesce(NEW.date_3, OLD.date_3) is NULL);
        NEW.avancement := '01 – commencé' where NEW.date_1 is not null AND (coalesce(NEW.date_2, OLD.date_2) is NULL) AND       (coalesce(NEW.date_3, OLD.date_3) is NULL);
    Return NEW;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Ce trigger fonctionne uniquement pour la première condition, mais pas pour les deux suivantes.

Je suis preneur de n'importe qu'elle idée.

Merci d'avance.

MathieuB · Fri 12 September 2014 16:52

Bonjour,

je ferais quelquechose comme ça :

Code:

NEW.avancement := CASE WHEN NEW.date_3 is not null THEN '03 – terminé'
WHEN NEW.date_2 is not null THEN '02 – en cours'
WHEN NEW.date_1 is not null THEN  '01 – commencé'
END;

Dernière modification par MathieuB (Fri 12 September 2014 16:53)

muze · Wed 17 September 2014 11:57

Merci pour cette réponse !

Je viens d'essayer ça ce matin, et avec votre syntaxe ça marche très bien !

Par contre je suis confronté à un autre problème : dans le cas où aucune date n'est renseignée, j'aimerais pouvoir mettre l'état d'avancement de mon choix ("simulation" ou "projet" par exemple).
J'ai essayé avec la clause ELSE en fin de requête, mais je suis obligé de renseigner une expression spécifique comme valeur defaut.

Est-ce possible de s'affranchir de cette valeur de défaut, ou bien avoir plusieurs valeur de défaut ?

Nicolas Ribot · Wed 17 September 2014 12:03

Bonjour,

Il faut un nouveau WHEN dans votre cas:

Code:

when NEW.date_3 is null and NEW.date_2 is null and NEW.date_1 is null then 'simulation'

Nicolas

muze · Wed 17 September 2014 12:18

Bonjour,

Oui j'y ai pensé, mais le "problème" c'est que dans ce cas je suis restreint au terme 'simulation'.

Je voudrais pouvoir dire :
"Si aucune des dates n'est remplie, le champs avancement peut être 'simulation' ou 'projet' ou 'abandon'"

En gros avoir plusieurs résultats possible pour une même condition

Nicolas Ribot · Wed 17 September 2014 12:32

Mais quel est le critère qui determine l'avancement ?

Vous ne voulez pas mettre un valeur au hasard parmi ces trois valeurs, si ?

La règle, c'est d'attribuer une valeur pour une condition: il faut que le système ait un critère pour choisir parmi les 3 termes.

Ou alors vous voulez:

Code:

when NEW.date_3 is null and NEW.date_2 is null and NEW.date_1 is null then 'simulation ou projet ou abandon'

?

Nicolas

muze · Wed 17 September 2014 13:55

Mais quel est le critère qui determine l'avancement ?

Désolé je ne l'ai pas précisé, et c'est bien de là que vient le problème : pour une partie c'est la date qui définie l'état d'avancement et quand aucune date n'est renseignée l'état d'avancement est emprique.
(je travaille toujours sur des projets de logements)

Comme on ne peut déroger à la règle 1condition = 1valeur, serait-ce possible de mettre une condition au déclenchement du TRIGGER ?
Du type :
"Si une date est renseignée, on déclenche le trigger. Si non, rien ne se passe"

muze · Wed 17 September 2014 14:02

Je viens de trouver la solution en ajoutant une clause IF !

Code:

 RETURNS trigger AS
$BODY$
DECLARE
BEGIN
    IF NEW.date_1 IS NOT NULL
    THEN NEW.avancement := CASE WHEN NEW.date_1 is not null AND NEW.date_2 is NULL AND NEW.date_3 is NULL then '01 – commencé'
                   END;
                   END IF;
    IF NEW.date_2 IS NOT NULL
    THEN NEW.avancement := CASE WHEN NEW.date_2 is not null AND NEW.date_3 is NULL then '02 – en cours'
                   END;
                   END IF;
    IF NEW.date_3 IS NOT NULL
    THEN NEW.avancement := CASE WHEN NEW.date_3 is not null then '03 – terminé'
                   END;
                   END IF;
    Return NEW;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Merci de m'avoir mis la puce à l'oreille

Nicolas Ribot · Wed 17 September 2014 14:05

Oui, on peut créer un trigger avec une condition CHECK qui précise les cas ou le trigger est déclenché:

Code:

create trigger toto 
before insert or update
on matable
for each row
[b]WHEN (NEW.date_1 is not null OR NEW.date_2 is not null OR NEW.date_3 is not null)[/b]
....

Je ne comprends toujours pas le remplissage de cet avancement: quelles sont les regles empiriques ? Qq'un va saisir cette valeur a la main, ou cette valeur va dépendre d'autres champs de la table ou d'autres tables (auquel cas, une requete dans le trigger va permettre d'exprimer cette règle empirique, en faisant une jointure sur ces autres tables)

Nicolas

Nicolas Ribot · Wed 17 September 2014 14:16

D'accord: en fait, vous ne voulez pas de ELSE dans vos conditions. ELSE est optionnel dans une clause CASE

Le CASE suffit. Il faut s'en servir avant l'affectation de la valeur avancement:

CASE WHEN NEW.date_1 is not null AND NEW.date_2 is NULL AND NEW.date_3 is NULL then NEW.avancement := '01 – commencé'
WHEN NEW.date_2 is not null AND NEW.date_3 is NULL then NEW.avancement := '02 – en cours'
WHEN NEW.date_3 is not null then NEW.avancement := '03 – terminé' END;

Nicolas

muze · Wed 17 September 2014 15:04

Oui ce sera la valeur sera saisie à la main par la personne en charge du dossier.

Super, ça simplifie grandement ma solution avec tout les "IF" !

muze · Wed 26 November 2014 10:38

Bonjour,

De retour avec une question POSTGIS/POSTGRESQL

Dans ma BDD postgis j'a une couche sur laquelle j'ai appliqué 2 types de TRIGGER :
- 1 de type "AFTER INSERT" qui met à jour des champs de zonages qui sont en lien avec la géométrie d'autres couches ( ex : ST_contains).
- 1 de type "BEFORE UPDATE" qui met à jour des champs de calcul (voir début du topic).

J'ai récemment ajouté un champs "date de mise à jour" et pour qu'il fonctionne de façon automatique je l'ai intégré à mon TRIGGER de type "before update" :

Code:

NEW."date_de_mise_a_jour" := NOW();

Mon problème :
Lorsque j'ajoute un objet, le premier TRIGGER se déclenche et met à jour les champs de zonages pour l'ensemble de mes objets présent dans ma couche ! Du coup comme il y a update, le deuxième TRIGGER se déclenche et met à jour, pour tous les objets, le champs "Date de mise à jour"...

J'aimerais donc avoir une "date de mise à jour" actualisée uniquement lors de l'ajout d'un nouvel objet.
Avez-vous une idée ?

Merci d'avance

Nicolas Ribot · Wed 26 November 2014 11:32

Bonjour,

Pas sur de comprendre sur quelles tables et champs agissent vos triggers.
Sont-ils définis FOR EACH ROW ou FOR EACH STATEMENT

Vous pourriez nous en donner la définition ainsi que la def des fonctions des triggers ?

Merci
Nico

muze · Wed 26 November 2014 11:42

Oui bien sur,

Comme il y a beaucoup de champs mis à jour pour avec chaque trigger, je vous met un extrait à chaque fois

- TRIGGER 1 : en utilisant les geom :

Code:

 CREATE OR REPLACE FUNCTION updateprospective()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
    UPDATE urbanisme.prospective SET
        "secteur_scolaire_maternelle" = (SELECT nom FROM education.secteurs_maternelles WHERE (SELECT ST_Contains(secteurs_maternelles.geom, ST_Centroid(prospective.geom)))),
        "secteur_scolaire_elementaire" = (SELECT nom FROM education.secteurs_elementaires WHERE (SELECT ST_Contains(secteurs_elementaires.geom, ST_Centroid(prospective.geom)))),       
    RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER updateprospective
  AFTER INSERT
  ON urbanisme.prospective
  FOR EACH ROW
  EXECUTE PROCEDURE updateprospective();

- TRIGGER 2, mise à jour des attributs :

Code:

CREATE OR REPLACE FUNCTION updateprospective_attribut()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
    NEW."nbr_t1_total__calcul_" := coalesce(NEW."nbr_t1_libres", OLD."nbr_t1_libres", 0) + coalesce(NEW."nbr_t1_sociaux", OLD."nbr_t1_sociaux", 0);
    NEW."nbr_t2_total__calcul_" := coalesce(NEW."nbr_t2_libres", OLD."nbr_t2_libres", 0) + coalesce(NEW."nbr_t2_sociaux", OLD."nbr_t2_sociaux", 0);
        NEW."date_de_mise_a_jour" = now();
Return NEW;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER updateprospective_attribut
  BEFORE UPDATE
  ON urbanisme.prospective
  FOR EACH ROW
  EXECUTE PROCEDURE updateprospective_attribut();

Merci beaucoup !!

tumasgiu · Tue 02 December 2014 15:08

Salut,

Si j'ai bien compris :

Pour le trigger 1, à chaque fois que vous ajouter une prospective vous voulez renseigner le secteur scolaire qui contient cette prospective,

Je pense qu'il faudrait utiliser le record NEW dans votre requête géométrique afin de la limiter à la ligne que vous venez d'insérer.

Sinon pour la forme, plutôt que de stocker le nom du secteur associé à la prospective, j'aurai stocké son identifiant (clef primaire).

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Wed 16 July 2014 11:05

[POSTGIS] trigger pour mise à jour de champs

Code:

#2 Wed 16 July 2014 11:52

Re: [POSTGIS] trigger pour mise à jour de champs

#3 Mon 21 July 2014 18:57

Re: [POSTGIS] trigger pour mise à jour de champs

Code:

#4 Mon 21 July 2014 19:59

Re: [POSTGIS] trigger pour mise à jour de champs

Code:

#5 Wed 23 July 2014 14:00

Re: [POSTGIS] trigger pour mise à jour de champs

#6 Fri 12 September 2014 16:19

Re: [POSTGIS] trigger pour mise à jour de champs

Code:

#7 Fri 12 September 2014 16:52

Re: [POSTGIS] trigger pour mise à jour de champs

Code:

#8 Wed 17 September 2014 11:57

Re: [POSTGIS] trigger pour mise à jour de champs

#9 Wed 17 September 2014 12:03

Re: [POSTGIS] trigger pour mise à jour de champs

Code:

#10 Wed 17 September 2014 12:18

Re: [POSTGIS] trigger pour mise à jour de champs

#11 Wed 17 September 2014 12:32

Re: [POSTGIS] trigger pour mise à jour de champs

Code:

#12 Wed 17 September 2014 13:55

Re: [POSTGIS] trigger pour mise à jour de champs

#13 Wed 17 September 2014 14:02

Re: [POSTGIS] trigger pour mise à jour de champs

Code:

#14 Wed 17 September 2014 14:05

Re: [POSTGIS] trigger pour mise à jour de champs

Code:

#15 Wed 17 September 2014 14:16

Re: [POSTGIS] trigger pour mise à jour de champs

#16 Wed 17 September 2014 15:04

Re: [POSTGIS] trigger pour mise à jour de champs

#17 Wed 26 November 2014 10:38

Re: [POSTGIS] trigger pour mise à jour de champs

Code:

#18 Wed 26 November 2014 11:32

Re: [POSTGIS] trigger pour mise à jour de champs

#19 Wed 26 November 2014 11:42

Re: [POSTGIS] trigger pour mise à jour de champs

Code:

Code:

#20 Tue 02 December 2014 15:08

Re: [POSTGIS] trigger pour mise à jour de champs

Pied de page des forums