From 3ffba55e139359afe3f1a94533ed47ba6f137434 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 3 Oct 2025 23:54:32 +0000 Subject: [PATCH] Enforce single-ROLE row restrction for arrival events --- db/schemas/lib/triggers/create/roles.m4 | 66 +++++++++++++++++++++++++ 1 file changed, 66 insertions(+) diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index b248c72..4abf1b4 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -46,6 +46,72 @@ CREATE OR REPLACE FUNCTION roles_func () cannot_change(`ROLES', `EID') END IF; + -- There can be at most one related row on ROLES for arrival events + IF TG_OP = 'INSERT' THEN + DECLARE + a_pid roles.pid%TYPE; + a_role roles.role%TYPE; + a_participant roles.participant%TYPE; + a_behavior events.behavior%TYPE; + a_start events.start%TYPE; + a_stop events.stop%TYPE; + a_fid follows.fid%TYPE; + a_focal follows.focal%TYPE; + a_date follows.date%TYPE; + a_community follows.community%TYPE; + + BEGIN + SELECT roles.pid, roles.role, roles.participant + , events.behavior, events.start, events.stop + , follows.fid, follows.focal, follows.date, follows.community + INTO a_pid , a_role , a_participant + , a_behavior , a_start , a_stop + , a_fid , a_focal , a_date , a_community + FROM roles + JOIN events ON (events.eid = roles.eid) + JOIN follows ON (follows.fid = events.fid) + WHERE roles.eid = NEW.eid + AND roles.pid <> NEW.pid + AND events.behavior = 'sdb_arrival'; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on INSERT of ROLES' + , DETAIL = 'There can be only one related row on ROLES for an' + || ' event with a EVENTS.Behavior of (sdb_arrival):' + || ' Inserting: Key (PID) = (' + || NEW.pid + || '), Value (EID) = (' + || NEW.eid + || '), Value (Role) = (' + || NEW.role + || '), Value (Participant) = (' + || NEW.participant + || '): But the following ROLES row already exists:' + || ': Key (PID) = (' + || a_pid + || '), Value (Role) = (' + || a_role + || '), Value (Participant) = (' + || a_participant + || '), Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '), Value (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || '), Value (FOLLOWS.Community) = (' + || a_community + || ')'; + END IF; + END; + END IF; + RETURN NULL; END; $$; -- 2.34.1