--- a/schema/gemma.sql	Mon Jun 24 15:46:13 2019 +0200
+++ b/schema/gemma.sql	Mon Jun 24 16:13:05 2019 +0200
@@ -178,6 +178,33 @@
 $$
 LANGUAGE plpgsql;
 
+-- Constraint trigger: sounding Results must intersect with the area
+-- of the bottleneck they belong to.
+CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    IF NOT st_intersects((SELECT area
+                          FROM waterway.bottlenecks
+                          WHERE (bottleneck_id, validity)
+                              =(NEW.bottleneck_id, NEW.bottleneck_validity)),
+                         NEW.area)
+    THEN
+        RAISE EXCEPTION
+            'new row for relation "%" violates constraint trigger "%"',
+                TG_TABLE_NAME, TG_NAME
+            USING
+                DETAIL = 'Failing row area has no intersection with bottleneck.',
+                ERRCODE = 23514,
+                SCHEMA = TG_TABLE_SCHEMA,
+                TABLE = TG_TABLE_NAME,
+                CONSTRAINT = TG_NAME;
+    END IF;
+    RETURN NEW;
+END;
+$$;
+
+
 --
 -- GEMMA data
 --
@@ -663,6 +690,9 @@
         octree_index bytea,
         staging_done boolean NOT NULL DEFAULT false
     )
+    CREATE CONSTRAINT TRIGGER sounding_results_in_bn_area
+        AFTER INSERT OR UPDATE ON sounding_results
+        FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area();
 
     CREATE TABLE sounding_results_contour_lines (
         sounding_result_id int NOT NULL REFERENCES sounding_results