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();
Mathias
  • 13
  • 2
  • Please add some sample data and table structure in question – Akhilesh Mishra Dec 08 '20 at 09: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. – Adrian Klaver Dec 08 '20 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. – Mathias Dec 09 '20 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) ) – Mathias Dec 09 '20 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) ) – Mathias Dec 09 '20 at 11:07

0 Answers0