PostGIS Trigger between polygon and point Layer / soil and label / https://gis.stackexchange.com/questions/160320/how-to-use-interacting-postgresql-postgis-triggers-in-qgis/162585

postgis_trigger.sql

BEGIN;

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

-- CREATE test tables

CREATE TABLE label_point (
gid serial NOT NULL,
geom geometry(point, 3857),
label_sample varchar(255),
CONSTRAINT label_point_pkey PRIMARY KEY (gid)
);

CREATE TABLE soil (
gid serial NOT NULL,
geom geometry(polygon, 3857),
label varchar(255),
CONSTRAINT soil_pkey PRIMARY KEY (gid)
);


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

-- Trigger function for label_point layer

CREATE OR REPLACE FUNCTION sample_label_point() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
IF TG_OP = 'INSERT'
THEN
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
		soil AS s
        WHERE st_Within(NEW.geom, s.geom)) AS foo) > 0
  THEN
    SELECT soil.label
INTO NEW.label_sample
FROM soil
WHERE ST_Intersects(NEW.geom, soil.geom);
    RETURN NEW; 
  ELSE
    RETURN NEW;
  END IF;
ELSIF TG_OP = 'UPDATE'
THEN
IF
(ST_Equals(NEW.geom , OLD.geom)=FALSE)
THEN
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
		soil AS s
        WHERE st_Within(NEW.geom, s.geom)
		) AS foo) > 0 
  THEN
SELECT soil.label
INTO NEW.label_sample
FROM soil
WHERE ST_Intersects(NEW.geom, soil.geom);
    RETURN NEW; 
  ELSE 
  SELECT NULL
	INTO NEW.label_sample;
  RETURN NEW; 
  END IF; 
ELSE
Return NEW;
END IF;
END IF; 
END; 
$BODY$ 
LANGUAGE plpgsql;

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

-- CREATE TRIGGER for label_point layer

 CREATE TRIGGER label_point_trigger_insert
  BEFORE INSERT 
  ON public.label_point
  FOR EACH ROW
  EXECUTE PROCEDURE public.sample_label_point();

    CREATE TRIGGER label_point_trigger_update
  BEFORE UPDATE OF geom
  ON public.label_point
  FOR EACH ROW
    WHEN (OLD.geom IS DISTINCT FROM NEW.geom) 
  EXECUTE PROCEDURE public.sample_label_point();


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

-- Trigger function for soil_label layer

CREATE OR REPLACE FUNCTION soil_label() RETURNS TRIGGER AS
$BODY$
DECLARE
new_label text := quote_ident(NEW.label);  -- assign at declaration
BEGIN
IF TG_OP = 'INSERT'
THEN
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
		soil AS s
        WHERE st_Within(t.geom, NEW.geom)) AS foo) > 0
  THEN
    EXECUTE 'UPDATE label_point SET label_sample = $2 WHERE ST_Within(label_point.geom, $1)'
        USING NEW.geom, NEW.label;
    RETURN NEW; 
  ELSE
    RETURN NEW;
  END IF;
ELSIF TG_OP = 'UPDATE'
THEN
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
		soil AS s
        WHERE st_Within(t.geom, NEW.geom)
		) 
		AS foo) > 0 
  THEN
EXECUTE 'UPDATE label_point SET label_sample = ' ||  quote_literal(NEW.label)  || ' WHERE ST_Within(label_point.geom, $1)'
        USING NEW.geom;
    RETURN NEW; 
  ELSE 
  EXECUTE 'UPDATE label_point SET label_sample = NULL WHERE ST_Within(label_point.geom, $2)'
        USING NEW.geom, OLD.geom;
  RETURN NEW; 
  END IF; 
END IF; 
END; 
$BODY$ 
LANGUAGE plpgsql;


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

-- CREATE TRIGGER for soil layer

 
CREATE TRIGGER label_soil_trigger_insert
  BEFORE INSERT
  ON public.soil
  FOR EACH ROW
  EXECUTE PROCEDURE public.soil_label();

  
CREATE TRIGGER label_soil_trigger_update
  BEFORE UPDATE OF geom
  ON public.soil
  FOR EACH ROW
  WHEN (OLD.geom IS DISTINCT FROM NEW.geom) 
  EXECUTE PROCEDURE public.soil_label();
  


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

-- Delete Trigger

CREATE OR REPLACE FUNCTION public.before_delete_soil()
  RETURNS trigger AS
$BODY$
BEGIN
 UPDATE label_point SET label_sample = NULL WHERE ST_Within(label_point.geom, OLD.geom);
 RETURN OLD;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER trigger_before_delete_soil
  BEFORE DELETE
  ON public.soil
  FOR EACH ROW
  EXECUTE PROCEDURE public.before_delete_soil();

  COMMIT;