From 3cbba164b0e9f6f36de0774afed8b3bd2df6b7e3 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc kop@karlpinc.com" <kop@karlpinc.com>
Date: Mon, 1 Jun 2026 20:14:14 +0000
Subject: [PATCH] Fix check for unique roles per event

---
 db/schemas/lib/triggers/create/roles.m4 | 21 +++++++++++----------
 1 file changed, 11 insertions(+), 10 deletions(-)

diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4
index 3e67ccb..5aad51a 100644
--- a/db/schemas/lib/triggers/create/roles.m4
+++ b/db/schemas/lib/triggers/create/roles.m4
@@ -404,32 +404,33 @@ CREATE OR REPLACE FUNCTION roles_func ()
     DECLARE
       a_pid roles.pid%TYPE;
       a_role roles.role%TYPE;
+      a_participant roles.participant%TYPE;
+
       a_focal follows.focal%TYPE;
       a_date follows.date%TYPE;
       a_commid follows.commid%TYPE;
 
     BEGIN
-      SELECT roles.pid, roles.role
+      SELECT roles.pid, roles.role, roles.participant
            , follows.focal, follows.date, follows.commid
-        INTO a_pid    , a_role
+        INTO a_pid    , a_role    , a_participant
            , a_focal      , a_date      , a_commid
         FROM roles
            , follows
         WHERE roles.eid = NEW.eid
               AND roles.pid <> NEW.pid
               AND roles.role = NEW.role
-              AND roles.participant = NEW.participant
               AND follows.fid = a_fid;
       IF FOUND THEN
         RAISE EXCEPTION integrity_constraint_violation USING
               MESSAGE = 'Error on INSERT of ROLES'
             , DETAIL = 'There cannot be more than one row on ROLES'
-                       || ' with a ROLES.Participant value of ('
-                       || NEW.participant
-                       || ') related to a single '
+                       || ' with a ROLES.Role value of ('
+                       || NEW.Role
+                       || ') related to a single EVENTS row'
+                       || ' with a Behavior = ('
                        || a_behavior
-                       || ' EVENTS row:'
-                       || ' Inserting: Key (PID) = ('
+                       || '): Inserting: Key (PID) = ('
                        || NEW.pid
                        || '), Value (EID) = ('
                        || NEW.eid
@@ -443,7 +444,7 @@ CREATE OR REPLACE FUNCTION roles_func ()
                        || '), Value (Role) = ('
                        || a_role
                        || '), Value (Participant) = ('
-                       || NEW.participant
+                       || a_participant
                        || '), Value (EVENTS.Behavior) = ('
                        || a_behavior
                        || '), Value (EVENTS.Start) = ('
@@ -459,7 +460,7 @@ CREATE OR REPLACE FUNCTION roles_func ()
                        || '), Value (FOLLOWS.CommID) = ('
                        || a_commid
                        || ')'
-            , HINT = 'The participants in an '
+            , HINT = 'The roles in an '
                      || a_behavior
                      || ' event must be unique.';
       END IF;
-- 
2.34.1