Forums PostgreSQL.fr

bl4n

Membre

[Trigger] Récupérer l'ID d'une entrée soit insérée, soit sélectionnée

Bonjour,

Étant débutant en TRIGGER, je me permet de solliciter un coup de pouce afin de résoudre ce problème qui me tracasse depuis quelques temps.

Voici un schéma résumé de ce sur quoi je travaille : http://dbpatterns.com/documents/57fceed … 3da5724af/

Ce que j'aimerais obtenir :

• Remplir le champ intervention_id de la table equipment_history

• Faire ce remplissage au moment de l'insertion d'une ligne dans equipment_history (MAIS je ne sais pas si je dois faire un Trigger BEFORE ou AFTER l'insertion)

Ce qu'il faut savoir sur le champ intervention_id :

• Si une entrée existe déjà pour le couple date/place_id donnée, on utilise cet ID

• Sinon on insère une nouvelle intervention et on utilise cet ID pour remplir le champ intervention_id

Jusqu'à maintenant j'ai tenté d'utiliser un truc comme ça :

---- TRIGGER générique qui copie les données d'une table vers sa table "_history"

CREATE OR REPLACE FUNCTION gissmo_audit()
            RETURNS trigger AS $body$
            DECLARE
                name TEXT;
            BEGIN
                IF (TG_OP = 'UPDATE') THEN
                    name := TG_TABLE_NAME::TEXT || '_history';
                    EXECUTE format('INSERT INTO %I VALUES(($1).*)', name) USING OLD;
                    RETURN NEW;
                END IF;
            EXCEPTION
                WHEN data_exception THEN
                    RAISE WARNING '[gissmo_audit] - ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
                WHEN unique_violation THEN
                    RAISE WARNING '[gissmo_audit] - ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
                WHEN OTHERS THEN
                    RAISE WARNING '[gissmo_audit] - ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
                    RETURN NULL;
            END;
            $body$ LANGUAGE plpgsql;

---- Ajout du TRIGGER générique sur la table equipment_equipment

CREATE TRIGGER equipment_equipment_audit
            AFTER UPDATE ON equipment_equipment
            FOR EACH ROW EXECUTE PROCEDURE gissmo_audit();

---- TRIGGER censé remplir le champ intervention_id lors de l'insertion d'une ligne d'historique (NE FONCTIONNE PAS :'()

CREATE OR REPLACE FUNCTION have_place_id_field_clustering()
            RETURNS trigger AS $body$
            DECLARE
                intervention_id INTEGER;
                given_date DATE;
            BEGIN
                IF (TG_OP = 'INSERT') AND (NEW.intervention_id IS NULL) THEN
                    given_date := date_trunc('day', NEW.insert_datetime);
                    INSERT INTO intervention_intervention AS intervention (id, date, place_id, confirmed) VALUES (DEFAULT, given_date, NEW.place_id, False)
                        ON CONFLICT (date, place_id) DO UPDATE SET
                            confirmed = 'f'
                        RETURNING id
                    INTO intervention_id;
                    NEW.intervention_id := (SELECT id FROM intervention_intervention WHERE date = given_date AND place_id = NEW.place_id LIMIT 1);
                    RETURN NEW;
                END IF;
            EXCEPTION
                WHEN data_exception THEN
                    RAISE WARNING '[have_place_id_field_clustering] - ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
                WHEN unique_violation THEN
                    RAISE WARNING '[have_place_id_field_clustering] - ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
                WHEN OTHERS THEN
                    RAISE WARNING '[have_place_id_field_clustering] - ERROR - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
                    RETURN NULL;
            END;
            $body$ LANGUAGE plpgsql;

Je pense n'être pas loin du bon résultat, mais je crains d'être un peu trop simplet pour aligner les choses correctement et atteindre un résultat probant.

Je vous remercie d'avance pour toute l'aide que vous pourriez m'apporter à ce sujet et vous souhaite une agréable journée,

bl4n alias Personne.

Marc Cousin

Membre

Re : [Trigger] Récupérer l'ID d'une entrée soit insérée, soit sélectionnée

Le premier truc qui m'embête dans le trigger:

                IF (TG_OP = 'INSERT') AND (NEW.intervention_id IS NULL) THEN
                    given_date := date_trunc('day', NEW.insert_datetime);
                    INSERT INTO intervention_intervention AS intervention (id, date, place_id, confirmed) VALUES (DEFAULT, given_date, NEW.place_id, False)
                        ON CONFLICT (date, place_id) DO UPDATE SET
                            confirmed = 'f'
                        RETURNING id
                    INTO intervention_id;
                    NEW.intervention_id := (SELECT id FROM intervention_intervention WHERE date = given_date AND place_id = NEW.place_id LIMIT 1);
                    RETURN NEW;
                END IF;

Si on ne passe pas dans ce IF, la fonction est censée faire quoi ? il faut qu'elle fasse un RETURN de quelque chose dans ce cas là.

le mieux c'est de faire des RAISE NOTICE un peu partout pour tracer où le trigger passe…

---

Marc.

bl4n

Membre

Re : [Trigger] Récupérer l'ID d'une entrée soit insérée, soit sélectionnée

En effet, je n'avais pas vu cela comme ça étant donné que je ne prévoyais pas d'appeler le trigger autrement que pour un INSERT.

Je pense, mais peut-être que je me trompe, qu'un RETURN NULL devrait convenir, un peu comme ça :

                IF (TG_OP = 'INSERT') AND (NEW.intervention_id IS NULL) THEN
                    given_date := date_trunc('day', NEW.insert_datetime);
                    INSERT INTO intervention_intervention AS intervention (id, date, place_id, confirmed) VALUES (DEFAULT, given_date, NEW.place_id, False)
                        ON CONFLICT (date, place_id) DO UPDATE SET
                            confirmed = 'f'
                        RETURNING id
                    INTO intervention_id;
                    NEW.intervention_id := (SELECT id FROM intervention_intervention WHERE date = given_date AND place_id = NEW.place_id LIMIT 1);
                    RETURN NEW;
                ELSE
                    RETURN NULL;
                END IF;

Qu'en pensez-vous ?

Je n'avais pas pensé à utiliser des RAISE NOTICE dans mon trigger. Ça va me permettre de faire du debug et voir ce qui cloche, merci !

Marc Cousin

Membre

Re : [Trigger] Récupérer l'ID d'une entrée soit insérée, soit sélectionnée

Ça semble un bon début.

Sinon, il y a aussi raise debug qui est intéressant… il faut faire log_min_messages à debug ou client_min_messages à debug pour faire apparaître les messages, c'est intéressant quand on veut pouvoir fournir des traces de debug dans du code PL qui ne soit pas actif en permanence.

---

Marc.

bl4n

Membre

Re : [Trigger] Récupérer l'ID d'une entrée soit insérée, soit sélectionnée

Bon, finalement je crois avoir trouvé l'ensemble des soucis que j'avais :

1/ Au départ je n'utilisais pas "id" dans la ligne d'INSERT, donc le RETURNING ne renvoyait pas l'id
2/ Plus la peine d'utiliser une sous requête si on récupère bien l'id dans "intervention_id"
3/ Ajout du ELSE par précaution
4/ Lorsque j'affecte le TRIGGER à une table, je faisais un "AFTER INSERT" et ça n'enregistrait pas la valeur dans la colonne. C'est désormais le cas. Voici l'ajout du trigger :

CREATE TRIGGER equipment_equipment_cluster
            BEFORE INSERT ON equipment_equipment_history
            FOR EACH ROW EXECUTE PROCEDURE have_place_id_field_clustering();

Maintenant que j'ai réglé ces petits soucis, il va me falloir faire de même pour plusieurs autre cas un tout petit peu plus corsés