Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
banniere

Le portail francophone de la géomatique


Toujours pas inscrit ? Mot de passe oublié ?
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

Annonce

 La 5ème édition des GeoDataDays aura lieu les 14 et 15 septembre 2022 au Futuroscope de Poitiers. 

Le détail du programme et des intervenants est disponible sur le site de l'évènement.

#1 Tue 10 May 2022 12:01

Kiecane
Membre
Date d'inscription: 31 Oct 2021
Messages: 47

déclencheur booléen

Bonjour,

Je suis assez novice en langage Postgre/SQL et j'aurais besoin de votre aide. J'essaye d'écrire un script pour imposer des contraintes sur les valeurs des champs d'une table qui seront saisies par l'utilisateur dans l'application qui découlera de la base de données que je suis en train de constituer. J'ai une table capture_piege qui étudie les captures d'espèces d'intérêt dans des pièges. J'ai créé les champs suivants dans mon type-entité : pk_suivi (associé à un jour de suivi d'un piège localisé à un emplacement précis), date_suivi, animal_capture (TRUE lorsqu'un ou plusieurs animaux sont capturés dans le piège), nb_ind (nombre d'individu lorsqu'une capture a eu lieu), id_espece_interet (identifiant de l'espèce capturée SI elle correspond à une des espèces recherchées (numéro TAXREF A, B, C ou D) ; NULL sinon ***), photo, animal_vivant (TRUE si l'animal capturé est vivant au moment de relever le piège)

*** la table capture_piege est reliée à une autre table num_espece qui liste les numéros TAXREF de toutes les espèces et leur libellé (nom de l'espèce) associé. Parmi toutes ces espèces, seules 4 d'entre elles (A, B, C et D) correspondent aux espèces d'intérêt que l'on souhaite capturer avec ce piège. La table capture_piege a donc comme clé étrangère fk_num_espece associée à la table num_espece (cardinalités 1,1 et 0,n).

On a id_espece_interet qui ne correspond pas au numéro TAXREF, c'est un identifiant au format : espèce_AAAA_n° avec :
- les trois première lettres du libelle de l'espèce de la table num_espece
- suivi de l'année de capture dans le piège
- suivi d'un numéro qui correspond à la nème capture de cette espèce dans l'année (par exemple, il est égal à 3 si c'est le 3e individu de cette espèce que l'on capture cette année-là)
Il faudrait donc aussi que je trouve comment créer automatiquement ce numéro mais j'avoue que je ne sais pas trop comment faire pour le moment. Je ne sais pas non plus si je dois le faire dans mon déclencheur ou à un autre endroit. Qu'en pensez-vous ?


Voici le déclencheur que j'ai écrit pour le moment :
CREATE FUNCTION capture_tri() RETURNS TRIGGER AS $BODY$
DECLARE
    capture BOOL;
BEGIN
    SELECT animal_capture INTO capture FROM capture_piege WHERE pk_suivi=NEW.pk_suivi;
    IF capture IS FALSE
        THEN (nb_ind='0' AND id_espece_interet='0' AND photo='0' AND animal_vivant='0')
    IF capture IS TRUE AND (fk_num_espece!='A' OR fk_num_espece!='B' OR fk_num_espece!='C' OR fk_num_espece!='D')
        THEN nb_ind>0 AND (id_espece_interet IS NULL)
    END IF;
END ;
$BODY$ LANGUAGE 'plpsql'
CREATE TRIGGER capture_tri AFTER INSERT OR UPDATE ON animal_capture
    FOR EACH ROW EXECUTE PROCEDURE capture_tri();

Pour écrire ça je me suis inspirée de ce que j'ai trouvé sur internet et dans des livres mais je ne suis pas sûre que cela fonctionne, et comme je n'ai pas encore les données pour constituer ma base de données, je ne peux pas le vérifier pour l'instant. Pourriez-vous m'indiquer si cela vous paraît correct s'il-vous-plaît ?


Merci d'avance à tous ceux qui voudront bien m'aider:) !

Hors ligne

 

#2 Tue 10 May 2022 14:09

Franck2908
Membre
Date d'inscription: 5 Mar 2010
Messages: 74

Re: déclencheur booléen

Bonjour Kiecane,

le projet de stage avance ?

dans un premier temps, cette ligne ne sert à rien et ne fonctionnera pas telle est écrite :

SELECT animal_capture INTO capture FROM capture_piege WHERE pk_suivi=NEW.pk_suivi;


là vous voulez savoir si il y a des  animaux capturés lors de la nouvelle observation sur le terrain pour lancer le trigger c'est bien ça ?

Hors ligne

 

#3 Wed 11 May 2022 11:35

Kiecane
Membre
Date d'inscription: 31 Oct 2021
Messages: 47

Re: déclencheur booléen

Bonjour,

Oui ça avance merci:) ! Après je dois avouer que ça reste laborieux (et ça me fait un peu peur) car mon maître de stage n'est pas vraiment présent donc je m'aide surtout d'internet et de livres, sachant que je n'ai pas trop d'expérience dans ce domaine....mais bon ça fait partie du jeu alors j'essayer de m'adapter;)

là vous voulez savoir si il y a des  animaux capturés lors de la nouvelle observation sur le terrain pour lancer le trigger c'est bien ça ?


Oui c'est bien ça ! J'avais trouvé cette solution dans un livre, est-ce que vous pouvez m'expliquer pourquoi ça ne fonctionnera pas svp ? Le but c'était d'effectuer des modifications uniquement sur les nouvelles lignes ajoutées par l'utilisateur car comme l'ancienne base de données est sous format Excel (avec des fichiers annexes sous format shapefiles), je me suis dit qu'il pouvait y avoir de vieilles erreurs ou des lacunes qui trainent (et je pense qu'il y en a pas mal en réalité).

Hors ligne

 

#4 Wed 11 May 2022 11:51

Franck2908
Membre
Date d'inscription: 5 Mar 2010
Messages: 74

Re: déclencheur booléen

SELECT animal_capture INTO capture FROM capture_piege WHERE pk_suivi=NEW.pk_suivi;


ça ne peut pas fonctionner ici car vous recherchez une ligne qui n'existe pas encore dans la base car elle est nouvelle (NEW).

je n'ai plus votre MCD en tête, pourriez-vous le remettre svp.

espèce_AAAA_n°


- pour l'espèce il va falloir écrire une requête qui va récupérer cette information
- AAAA : c'est l'année en cours ?, il va y avoir une reprise des données des années précédentes ?
- n° : il va falloir se baser sur des séquences

Dans un premier temps il faut vous attarder sur la récupération des 3eres lettres du libelle de l'espèce de la table num_espece. IL faut écrire une requête qui stocke l'info dans une nouvelle variable de votre fonction

Hors ligne

 

#5 Thu 12 May 2022 17:39

Kiecane
Membre
Date d'inscription: 31 Oct 2021
Messages: 47

Re: déclencheur booléen

Bonjour,


Je vous ai mis en pièce jointe la partie de mon MCD qui est utile pour répondre à ma question. Pour faciliter sa compréhension, j'ai coloré en bleu les entités correspondant aux protocoles qui sont mis en place pour trouver les individus des espèces d'intérêt (ce sont les 4 espèces A, B, C et D apparentées). Les entités en jaune correspondent quant à elles aux entités en lien avec les entités bleues (num_espece est aussi en lien avec individu coloré en rose). J'ai mis le reste en orange. Enfin, l'entité rose correspond à l'entité "centrale" où sont répertoriées tous les individus rencontrés avec les différents protocoles et leurs caractéristiques.


ça ne peut pas fonctionner ici car vous recherchez une ligne qui n'existe pas encore dans la base car elle est nouvelle (NEW).


Ok, merci, du coup j'imagine qu'il faut utiliser le NEW à un autre endroit que dans le SELECT pour que cela fonctionne ? Dans quelle partie de ma fonction faudrait-il que je le place dans ce cas svp ?


- pour l'espèce il va falloir écrire une requête qui va récupérer cette information
- AAAA : c'est l'année en cours ?, il va y avoir une reprise des données des années précédentes ?
- n° : il va falloir se baser sur des séquences

Dans un premier temps il faut vous attarder sur la récupération des 3eres lettres du libelle de l'espèce de la table num_espece. IL faut écrire une requête qui stocke l'info dans une nouvelle variable de votre fonction


- espece : du coup, il faut que je récupère l'information de la clé étrangère fk_num_espece pour chaque protocole lorsqu'il y a capture d'une espèce d'intérêt. Autrement dit, il faut que je récupère fk_num_espece quand fk_num_espece = A ou B ou C ou D
- AAAA : c'est l'année de capture de l'animal (il y a des années très anciennes), autrement dit, il y aura bien une reprise des données des années précédentes, ces dernières étant stockées dans cette base de données
- n° : je n'ai jamais fait ça, pourriez-vous m'indiquer la démarche à suivre svp ? J'ai vu sur internet qu'il existait CREATE SEQUENCE, est-ce que c'est ça dont vous parlez ? Sinon, je me demande si je ne pourrais pas utiliser les dates : pour une année x, pour une espèce d'intérêt donnée, si le jour/mois_1 est inférieur au jour/mois_2 alors on attribue un numéro inférieur pour le jour/mois_1 ??? Et du coup on pourrait utiliser cette méthode pour les vieilles données à incorporer dans la base, et pour les nouvelles données, on incrémenterait automatiquement le bon numéro de manière semblable ?

CREATE FUNCTION creation_identifiant() RETURNS TRIGGER AS $BODY$
DECLARE
prem_espece VARCHAR(3) ;
annee VARCHAR(4) ;
numero INTEGER ;
BEGIN
SELECT substr(num_espece.libelle, 1, 3) INTO prem_espece, substr(individu.date_ind, 7, 4) INTO annee, ? INTO numero
FROM num_espece, individu ;
IF num_espece.libelle==A OR num_espece.libelle==B OR num_espece.libelle==C OR num_espece.libelle== D
THEN concat(espece, annee, numero) AS id_espece_interet ;
END IF ;
RETURN concat(espece, annee, numero) AS id_espece_interet ;
END ;
$BODY$
LANGUAGE 'plpgsql'

Et après j'ai un nouveau soucis : il faudrait créer un déclencheur associé à cette fonction tout le temps (autrement dit pas de déclencheur....) ou alors modifier les données que je mets dans la base de données puis créer cette fonction et l'associer à :
CREATE TRIGGER creation_identifiant AFTER INSERT OR UPDATE ON (capture_piege_type1 OR capture_piege_type2 OR donnees_observations OR individu)
FOR EACH ROW EXECUTE PROCEDURE creation_identifiant();

J'ai mis en italiques car à mon avis ça ne va pas.....


Et pour le ? plus haut dans le SELECT de la fonction, c'est le problème de séquence qui m'embête : je crois avoir trouvé comment faire pour avoir la date la plus récente :

SELECT *
FROM individu,
(SELECT pk_individu, max(date_ind) AS max_date
FROM individu
GROUP BY rien
) individu_max
WHERE (individu.fk_espece= individu_max.fk_espece AND individu.substr(individu.date_ind, 7, 4) = individu_max.substr(individu.date_ind, 7, 4 AND individu.date_ind=individu_max.max_date)

mais ensuite il faudrait utiliser CREATE SEQUENCE pour que la valeur la plus récente présente le numéro le plus élevé de séquence, et aussi créer une boucle pour comparer les valeurs une à une. Pour créer une boucle, j'ai vu qu'il existait :
LOOP <instruction>
CONTINUE WHEN <prédicat> ;
END LOOP

mais je ne vois pas comment compiler tout ça ensemble......


Excusez-moi pour toute cette longueur, mais je voulais vous montrer toutes les recherches que j'ai faites pour essayer de m'en sortir par moi-même (même si je suis clairement loin d'avoir réussi...).

Merci pour votre aide:)


Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

Hors ligne

 

Pied de page des forums

Powered by FluxBB

Partagez  |