0

I created a trigger that updates the number of species counted from the "effectif" table to the "citation" table. It works well except when I delete the last row, the calculation does not perform and remains at the last state. For example, if I delete the last row on the "effectif" table that represents 6 species, I would still have 6 on my "citation" table where I'm suppose to find 0 or null. Here is my trigger :

CREATE OR REPLACE FUNCTION data.del_eff_tot()
  RETURNS trigger AS
$BODY$
BEGIN
IF OLD.effectif IS NOT NULL THEN
UPDATE data.citation
SET effectif =
(SELECT sum(a.effectif) FROM data.effectif a, data.citation b WHERE OLD.id_cit = a.id_cit AND OLD.id_cit = b.id)+COALESCE((SELECT a.effectif FROM data.effectif a, data.citation b WHERE OLD.id_cit = a.id_cit AND OLD.id_cit = b.id AND sexe = 'sexe_4_1'),0)
WHERE id IN (SELECT id_cit FROM data.effectif a, data.citation b WHERE OLD.id_cit = b.id AND b.id = a.id_cit);
END IF;
RETURN OLD;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION data.del_eff_tot()
  OWNER TO postgres;

------------------------------

CREATE TRIGGER del_eff_tot
  AFTER DELETE
  ON data.effectif
  FOR EACH ROW
  EXECUTE PROCEDURE data.del_eff_tot();
5
  • Please add some sample data and table structure in question Commented Dec 8, 2020 at 9:27
  • Change the trigger to BEFORE DELETE. An AFTER TRIGGER happens after the DELETE has completed and there is nothing to count, therefore the 0. Commented Dec 8, 2020 at 14:26
  • Thank you for your answers. By using BEFORE, I get the same problem. I have the impression that it performs the calculation with the value before deleting it. By using AFTER, the WHERE condition I have the impression that the calculation is good but doesn't match any more with any lines and therefore doesn't UPDATE anything. Commented Dec 9, 2020 at 11:07
  • My two tables : CREATE TABLE data.effectif ( integrated workforce, id_cit integer, id integer NOT NULL DEFAULT nextval('data.detail_effective_id_seq'::regclass), sex character varying, stage character varying, prec_stade character varying, CONSTRAINT detail_pkey PRIMARY KEY (id), CONSTRAINED positive eff_positive CHECK (effective >= 0) ) Commented Dec 9, 2020 at 11:07
  • CREATE TABLE data.citation ( id integer NOT NULL DEFAULT nextval('data.citation_id_seq'::regclass), sp_typing character varying(250), integrated workforce, id_inv integer, id_taxo integer, confidential boolean, prec_eff character varying(80), sensibility character varying(80), mode_contact character varying(80), CONSTRAINED quote_pkey PRIMARY KEY (id), CONSTRAINED positive eff_positive CHECK (effective >= 0) ) Commented Dec 9, 2020 at 11:07

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.