--- a/schema/gemma.sql	Fri Jun 14 17:56:42 2019 +0200
+++ b/schema/gemma.sql	Sat Jun 15 09:24:28 2019 +0200
@@ -72,6 +72,43 @@
 $$
 LANGUAGE plpgsql;
 
+-- Trigger functions to be used as statement-level AFTER triggers,
+-- associating time-based referencing objects to matching version
+CREATE OR REPLACE FUNCTION move_gauge_referencing() RETURNS trigger AS
+$$
+BEGIN
+    -- Avoid unnecessary execution ON UPDATE if validity did not change
+    IF OLD IS NULL OR NEW.validity <> OLD.validity THEN
+        UPDATE waterway.gauge_measurements
+        SET validity = NEW.validity
+        WHERE location = NEW.location
+            AND measure_date <@ NEW.validity;
+
+        UPDATE waterway.bottlenecks
+        SET gauge_validity = NEW.validity
+        WHERE gauge_location = NEW.location
+            AND lower(validity) <@ NEW.validity;
+    END IF;
+    RETURN NULL; -- ignored
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION move_bottleneck_referencing() RETURNS trigger AS
+$$
+BEGIN
+    -- Avoid unnecessary execution ON UPDATE if validity did not change
+    IF OLD IS NULL OR NEW.validity <> OLD.validity THEN
+        UPDATE waterway.sounding_results
+        SET bottleneck_validity = NEW.validity
+        WHERE bottleneck_id = NEW.bottleneck_id
+          AND CAST(date_info AS timestamptz) <@ NEW.validity;
+    END IF;
+    RETURN NULL; -- ignored
+END;
+$$
+LANGUAGE plpgsql;
+
 --
 -- GEMMA data
 --
@@ -296,6 +333,9 @@
     CREATE UNIQUE INDEX gauges_erased_unique_constraint
         ON gauges (location)
         WHERE NOT erased
+    -- Associate referencing objects to matching gauge version
+    CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity
+        ON gauges FOR EACH ROW EXECUTE FUNCTION move_gauge_referencing()
 
     CREATE TABLE gauges_reference_water_levels (
         location isrs NOT NULL,
@@ -525,6 +565,10 @@
     CREATE UNIQUE INDEX bottlenecks_erased_unique_constraint
         ON bottlenecks (bottleneck_id)
         WHERE NOT erased
+    -- Associate referencing objects to matching bottleneck version
+    CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity
+        ON bottlenecks FOR EACH ROW
+        EXECUTE FUNCTION move_bottleneck_referencing()
 
     CREATE TABLE bottlenecks_riverbed_materials (
         bottleneck_id int NOT NULL REFERENCES bottlenecks(id)