Trigger PostGIS QGIS

Question

I have 2 layers on PostGIS: a point layer named 'tdia' and a polygon layer named 'com': -the com layer contains the city name. (Polygon) -the tdia layer contains bus stops. (point) I want to create a trigger on my layer tdia that allows to recover the name of the city after creating a bus stops.

My code does not work:

CREATE TABLE tdia (gid SERIAL NOT NULL PRIMARY KEY, numero
VARCHAR(5), CODE_INSEE VARCHAR(5), NOM_COM VARCHAR(50), date DATE, geom geometry(point, 2154))


CREATE OR REPLACE FUNCTION maj_tdia()
RETURNS "trigger" AS
$BODY$
BEGIN
UPDATE tdia SET CODE_INSEE =
(SELECT com.CODE_INSEE
FROM com, tdia
WHERE st_intersects (tdia.geometry, com.geometry));
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER maj_tdia BEFORE INSERT OR UPDATE
ON tdia
FOR EACH ROW
EXECUTE PROCEDURE maj_tdia();

What am I doing wrong?

Answer 1

Don't UPDATE the tables, simply set the value of the NEW record; the NEW (and OLD in UPDATES) record is passed to the function for each row and will hold the full set of values that is adressed by the UPDATE/INSERT. BEFORE(/AFTER) triggers then accept a (manipulated) record from their functions to be inserted/updated (instead):

CREATE OR REPLACE FUNCTION maj_tdia()
RETURNS TRIGGER AS
$BODY$
  BEGIN
    NEW.code_insee := (
      SELECT code_insee
      FROM   com
      WHERE  ST_Intersects(com.geometry, NEW.geometry)
    );
    RETURN NEW;
  END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER maj_tdia
BEFORE INSERT OR UPDATE
ON tdia
FOR EACH ROW
EXECUTE PROCEDURE maj_tdia();

---

Let me add:

It might be useful to separate INSERT and UPDATE triggers in this context; e.g. if you update only the name of a bus station, it is unnecessary to calculate the intersection, especially if the target tables are large.

You can specify that the trigger will only fire when geometry is directly or implicitly referred to within the UPDATE statement (and actually differs from the stored geometry):

CREATE TRIGGER maj_tdia_ins
    BEFORE INSERT
    ON tdia
    FOR EACH ROW
    EXECUTE PROCEDURE maj_tdia();

CREATE TRIGGER maj_tdia_upd
    BEFORE UPDATE
    OF geometry
    ON tdia
    FOR EACH ROW
    WHEN (ST_Equals(NEW.geometry, OLD.geometry) = false)
    EXECUTE PROCEDURE maj_tdia();