In PostGIS I have two tables: closures (id, name, endpoint, geom) and chambers (id, name, geom). A closure must always be placed in a chamber for which it will take the chambers name it has been placed into and insert it in the closures's endpoint field. The following piece of code works as expected;
CREATE OR REPLACE FUNCTION the_schema.closures_endpoint_name_trigger_function() RETURNS TRIGGER AS $$ BEGIN IF (SELECT count(a.name) FROM the_schema.chambers a WHERE st_within(NEW.geom, st_buffer(a.geom,0.1))) > 0 THEN NEW.endpoint =(SELECT a.name as name FROM the_schema.chambers a WHERE st_within(NEW.geom, st_buffer(a.geom,0.1)) LIMIT 1); ELSEIF (SELECT count(a.name) FROM the_schema.chambers a WHERE st_within(NEW.geom, st_buffer(a.geom,0.1))) = 0 THEN RAISE EXCEPTION 'YOU NEED TO PLACE A CLOSURE IN A CHAMBER'; ELSEIF NEW.endpoint is null THEN RAISE EXCEPTION 'YOU MUST SAVE CHAMBERS LAYER FIRST OR MAKE SURE CLOSURES SNAP THEIR ENDPOINT'; END IF; RETURN NEW; END; $$ language plpgsql; CREATE TRIGGER closures_endpoint_name_trigger BEFORE INSERT OR UPDATE ON the_schema.closures FOR EACH ROW EXECUTE PROCEDURE the_schema.closures_endpoint_name_trigger_function();
But I have realised that if I delete a chamber or change it's placement, the closure will still have the endpoint of the chamber I just deleted or moved. Is there a way to create a trigger that deletes the closure or set to null the endpoint value (so that it raises an exception) after having removed or moved a chamber? I want to do it with a trigger as it is based on location so a 'on delete cascade' statement referencing the two related fields do not work for me. Any ideas as to how to achieve this?