#1 Mon 04 June 2018 11:09
- tevrard
- Participant assidu
- Date d'inscription: 23 May 2016
- Messages: 320
Boucle dans une fonction trigger
Bonjour,
Je cherche une syntaxe efficace pour écrire une boucle dans un trigger de type "before_insert".
Concrètement, les données que je reçois comportent un attribut code_insee. Je crains cependant que le code insee ne sois pas à jour (avec les fusions de communes etc...)
J'ai donc créé une table de correspondance de type (code_commune, libelle_commune, code_commune_old, lib_commune_old, chef_lieu, annee) avec en clé primaire le triplet (code_commune, code_commune_old, annee)
J'ai mis en place la condition suivante :
Code:
IF NEW.code_insee = bdd_territoires.ref_communes_nouvelles.code_commune_old AND bdd_territoires.ref_communes_nouvelles.chef_lieu = 'N' AND annee = (SELECT max(annee) FROM bdd_territoires.ref_communes_nouvelles WHERE code_commune_old ='NEW.code_insee') THEN NEW.code_insee = bdd_territoires.ref_communes_nouvelles.code_commune; NEW.commune = bdd_territoires.ref_communes_nouvelles.lib_commune; NEW.code = bdd_territoires.ref_communes_nouvelles.code_commune; END IF;
Cependant, il est possible qu'une commune subisse plusieurs modifications :
Par exemple en 2015, les communes 77170 Episy, 77299 Montarlot et 77316 Orvanne ont fusionné pour devenir la commune 77316 Moret Loing et Orvanne.
En 2016 la commune 77316 Moret Loing et Orvanne est devenue 77316 Moret-Loing-et-Orvanne
J'ai donc besoin d'une boucle pour retracer l'historique des modifications.
Je ne sais pas trop quels mots clés utiliser, ni sur quels éléments m'appuyer ma première idée tant que code_commune = code_commune_old part en boucle infinie sur l'exemple précédent.
NB : RTFM, certes. J'ai parcouru la doc rapidement, mais sans avoir vraiment trouvé (compris) ce que je cherchais. Je n'ai malheureusement pas le temps de faire des recherches et des tests approfondis. Je m'en remets donc a vous
Dernière modification par tevrard (Mon 04 June 2018 11:23)
Hors ligne
#2 Mon 04 June 2018 12:33
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1159
Re: Boucle dans une fonction trigger
Salut,
j'opterais pour une mise à jour après insertion,
en un seul update.
Cela présuppose d'avoir une table de correspondance
qui associe pour chaque code insee sa dernière modification.
On peut réaliser çà avec une requête récursive :
Code:
WITH RECURSIVE t(insee_1, insee_2, d) AS( SELECT code_commune_old, code_commune, 1 UNION SELECT t.insee_1, r.code_commune , d+1 FROM ref_commune_nouvelle r JOIN t ON t.insee_2 = r.code_commune_old ), tc AS( SELECT DISTINCT ON (insee_1) insee_1 code_commune_a_remplacer, insee_2 dernier_code_commune FROM t ORDER BY insee_1, d DESC) UPDATE ma_table SET code_commune = (SELECT dernier_code_commune WHERE code_commune_a_remplacer = code_commune FROM tc) WHERE code_commune IN (SELECT code_commune_a_remplacer FROM tc)
Tout cela à intégrer dans un trigger AFTER UPDATE/INSERT.
Dernière modification par tumasgiu (Mon 04 June 2018 15:02)
Hors ligne
#3 Mon 15 April 2019 18:23
- tevrard
- Participant assidu
- Date d'inscription: 23 May 2016
- Messages: 320
Re: Boucle dans une fonction trigger
Bonjour,
Désolé pour le temps de réponse ^^ (J'ai dû laisser de côté cette problématique l'année dernière, et c'est de retour au programme aujourd'hui !)
Tout d'abord, Merci à tumasgiu pour sa réponse (et le temps qu'il passe à nous expliquer plein de chose sur le forum !)
J'ai quelques questions à propos de la requête
Code:
SELECT code_commune_old, code_commune, 1 UNION
Il n'y a pas de FROM associé au select?
Hors ligne
#4 Tue 16 April 2019 08:51
- tevrard
- Participant assidu
- Date d'inscription: 23 May 2016
- Messages: 320
Re: Boucle dans une fonction trigger
Bonjour,
La doc dit la chose suivante :
La forme générale d'une requête WITH est toujours un terme non récursif, puis UNION (ou UNION ALL), puis un terme récursif. Seul le terme récursif peut contenir une référence à la sortie propre de la requête.
Donc ma question ne tient plus
Hors ligne
#5 Tue 16 April 2019 15:05
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1159
Re: Boucle dans une fonction trigger
Salut,
non votre remarque était juste,
il manque bien une clause FROM dans la partie non récursive de la CTE t.
Hors ligne
#6 Tue 16 April 2019 16:43
- tevrard
- Participant assidu
- Date d'inscription: 23 May 2016
- Messages: 320
Re: Boucle dans une fonction trigger
Pour suivi :
Code:
WITH RECURSIVE t(insee_1, insee_2, nb_boucles) AS( SELECT code_commune, code_commune, 1 FROM test.population_historique UNION SELECT t.insee_1, r.com_ap , nb_boucles+1 FROM test.mvt_communes_simplifie r JOIN t ON t.insee_2 = r.com_av ), tc AS (SELECT insee_1, max(nb_boucles) as nb_boucles FROM t GROUP BY insee_1 ORDER BY insee_1) SELECT t.insee_2, sum(poph.population_municipale) as population_municipale FROM t JOIN tc ON (t.insee_1, t.nb_boucles) = (tc.insee_1, tc.nb_boucles) JOIN test.population_historique as poph ON t.insee_1 = poph.code_commune GROUP BY t.insee_2 ORDER BY t.insee_2
On a donc notre table t qui est notre table récursive, celle qui nous sert à retracer l'historique des évolutions de la commune.
Rq : je n'ai qu'un seul code_commune dans test.population_historique, du coup je l'insère dans insee_1 et insee_2 pour "initialiser" la table.
La table tc permet de conserver uniquement les lignes de t qui nous intéressent : c'est à dire les lignes où sont associés le code originel et le dernier code trouvé (nb_boucles est donc maximum)
Le SELECT de sortie permet d'une part de lister les codes communes "restant" (tjrs en vigueur). Ici il permet aussi de sommer les populations des communes ayant fusionnées.
Hors ligne
#7 Wed 17 April 2019 10:15
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1159
Re: Boucle dans une fonction trigger
Merci du suivi !
Hors ligne