Vous n'êtes pas identifié(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.
Hors ligne
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.
Hors ligne
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 !
Hors ligne
Ç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.
Hors ligne
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 
Hors ligne