From 17ab18a8fa99216b9720088d1946a77c5cf8bf08 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc kop@karlpinc.com" <kop@karlpinc.com>
Date: Mon, 6 Oct 2025 22:34:33 +0000
Subject: [PATCH] An ARRIVALS row must be related to an event with Behavior =
 sdb_arrival

---
 db/schemas/lib/triggers/create/arrivals.m4 | 65 ++++++++++++++++++++++
 1 file changed, 65 insertions(+)

diff --git a/db/schemas/lib/triggers/create/arrivals.m4 b/db/schemas/lib/triggers/create/arrivals.m4
index aeabc78..2dd859e 100644
--- a/db/schemas/lib/triggers/create/arrivals.m4
+++ b/db/schemas/lib/triggers/create/arrivals.m4
@@ -39,6 +39,71 @@ CREATE OR REPLACE FUNCTION arrivals_func ()
     cannot_change(`ARRIVALS', `ArID')
   END IF;
 
+  -- The EVENTS.Behavior must be sdb_arrival
+  IF TG_OP = 'INSERT'
+     OR NEW.EID <> OLD.EID THEN
+    DECLARE
+      -- EVENTS
+      a_behavior events.behavior%TYPE;
+      a_start events.start%TYPE;
+      a_stop events.stop%TYPE;
+      -- FOLLOWS
+      a_fid follows.fid%TYPE;
+      a_focal follows.focal%TYPE;
+      a_date follows.date%TYPE;
+      -- ROLES
+      a_pid roles.pid%TYPE;
+      a_role roles.role%TYPE;
+      a_participant roles.participant%TYPE;
+
+    BEGIN
+      SELECT events.behavior, events.start, events.stop
+           , follows.fid, follows.focal, follows.date
+           , roles.pid, roles.role, roles.participant
+        INTO a_behavior     , a_start     , a_stop
+           , a_fid      , a_focal      , a_date
+           , a_pid    , a_role    , a_participant
+        FROM events
+          JOIN follows ON (follows.fid = events.fid)
+          LEFT OUTER JOIN roles ON (roles.eid = events.eid)
+        WHERE events.eid = NEW.eid
+              AND events.behavior <> 'sdb_arrival';
+      IF FOUND THEN
+        RAISE EXCEPTION integrity_constraint_violation USING
+              MESSAGE = 'Error on ' || TG_OP || ' of ARRIVALS'
+            , DETAIL = 'Arrivals can only be related to an event with an'
+                       || ' EVENTS.Behavior value of (sdb_arrival)'
+                       || ': Key (ArID = ('
+                       || NEW.arid
+                       || '): Value (Cycle) = ('
+                       || NEW.cycle
+                       || '): Value (DataSource) = ('
+                       || NEW.datasource
+                       || '): Key (ROLES.PID) = ('
+                       || textualize(`a_pid')
+                       || '), Value (ROLES.Role) = ('
+                       || textualize(`a_role')
+                       || '), Value (ROLES.Participant) = ('
+                       || textualize(`a_participant')
+                       || '): Key (EVENTS.EID) = ('
+                       || NEW.eid
+                       || '): Value (EVENTS.Behavior) = ('
+                       || a_behavior
+                       || '), Value (EVENTS.Start) = ('
+                       || a_start
+                       || '), Value (EVENTS.Stop) = ('
+                       || a_stop
+                       || '): Key (FOLLOWS.FID) = ('
+                       || a_fid
+                       || '), Value (FOLLOWS.Focal) = ('
+                       || a_focal
+                       || '), Value (FOLLOWS.Date) = ('
+                       || a_date
+                       || ')';
+      END IF;
+    END;
+  END IF;
+
   -- BIOGRAPHY_DATA
   IF TG_OP = 'INSERT'
        OR NEW.cycle <> OLD.cycle THEN
-- 
2.34.1