I have created two tables "post" and "node" and I want to assign the sum of the entities of the "node" table in the attribute "nb_noeud" of the "post" table by trigger. But, the code below does not work and I think I missed something. My code is as follows:
CREATE TABLE noeud
(
id_noeud serial NOT NULL,
code_noeud varchar(10) NULL,
type_noeud t_noeud NULL,
phase t_phase NULL
x_32632 bigint NULL,
y_32632 bigint NULL,
geom_noeud geometry(point) NULL,
obs text NULL
)
;
CREATE TABLE poste
(
id_pt serial NOT NULL,
code_pt varchar(8) NULL,
nom_pt varchar(50) NULL,
nb_noeud smallint NULL,
geom_pt geometry(polygon) NULL,
surf_pt numeric(15,2) NULL,
obs text NULL
)
;
CREATE OR REPLACE FUNCTION recap_noeud() RETURNS TRIGGER
language plpgsql AS
$$
DECLARE
som_noeud smallint;
BEGIN
IF (TG_OP = 'INSERT') THEN
SELECT COUNT(*) INTO som_noeud FROM noeud;
UPDATE poste set NEW.nb_noeud = som_noeud;
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
SELECT COUNT(*) INTO som_noeud FROM noeud;
UPDATE poste set NEW.nb_noeud = som_noeud;
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
RETURN NULL;
ELSE
RAISE WARNING 'Other action occurred: %, at %', TG_OP, now();
RETURN NULL;
END IF;
END;
$$
;
DROP TRIGGER IF EXISTS trig_recap_noeud ON noeud;
CREATE TRIGGER trig_recap_noeud AFTER INSERT OR UPDATE OR DELETE ON noeud FOR EACH ROW EXECUTE PROCEDURE recap_noeud();
Replace DELETE and INSERT clauses with
IF TG_OP = 'INSERT' OR TG_OP = 'DELETE' THEN
SELECT COUNT(*) INTO som_noeud FROM noeud;
UPDATE poste set nb_noeud = som_noeud;
RETURN NULL;