From bd9aafe7cd95df3061d12a8ba648667fd65ee8d5 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Sun, 12 Oct 2025 21:27:49 +0000 Subject: [PATCH] Add additional conditions to avoid unnecessary queries when updating --- .../lib/triggers/create/biography_data.m4 | 697 +++++++++--------- 1 file changed, 358 insertions(+), 339 deletions(-) diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index 4c0ad19..6eda6e8 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -324,67 +324,75 @@ CREATE OR REPLACE FUNCTION biography_data_func () -- An individual may not be placed in a community before the -- individual is under study. - DECLARE - commmid comm_membs.commmid%TYPE; - startdate comm_membs.startdate%TYPE; - BEGIN - SELECT comm_membs.startdate, comm_membs.commmid - INTO startdate, commmid - FROM comm_membs - WHERE comm_membs.animid = NEW.animid - AND comm_membs.startdate < NEW.entrydate - ORDER BY comm_membs.startdate - LIMIT 1; - IF FOUND THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on ' || TG_OP || ' of BIOGRAPHY_DATA' - , DETAIL = 'An individual may not be placed in a community before' - || ' the individual is under study' - || ': Key (AnimID) = (' - || NEW.animid - || '), Value (AnimName) = (' - || NEW.animname - || '), Value (StartDate) = (' - || NEW.startdate - || ' has a related COMM_MEMBS row with Key (CommMID) = (' - || commmid - || '), and Value (StartDate) = (' - || startdate - || ')'; - END IF; - END; + IF TG_OP = 'INSERT' + OR (NEW.entrydate <> OLD.entrydate) THEN + DECLARE + commmid comm_membs.commmid%TYPE; + startdate comm_membs.startdate%TYPE; + BEGIN + SELECT comm_membs.startdate, comm_membs.commmid + INTO startdate, commmid + FROM comm_membs + WHERE comm_membs.animid = NEW.animid + AND comm_membs.startdate < NEW.entrydate + ORDER BY comm_membs.startdate + LIMIT 1; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of BIOGRAPHY_DATA' + , DETAIL = 'An individual may not be placed in a community before' + || ' the individual is under study' + || ': Key (AnimID) = (' + || NEW.animid + || '), Value (AnimName) = (' + || NEW.animname + || '), Value (StartDate) = (' + || NEW.startdate + || ' has a related COMM_MEMBS row with ' + || ' Key (CommMID) = (' + || commmid + || '), and Value (StartDate) = (' + || startdate + || ')'; + END IF; + END; + END IF; -- An individual may not be placed in a community after the -- individual left the study. - DECLARE - commmid comm_membs.commmid%TYPE; - enddate comm_membs.enddate%TYPE; - BEGIN - SELECT comm_membs.enddate, comm_membs.commmid - INTO enddate, commmid - FROM comm_membs - WHERE comm_membs.animid = NEW.animid - AND NEW.departdate < comm_membs.enddate - ORDER BY comm_membs.enddate DESC - LIMIT 1; - IF FOUND THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on ' || TG_OP || ' of BIOGRAPHY_DATA' - , DETAIL = 'An individual may not be placed in a community after' - || ' the individual has left the study' - || ': Key (AnimID) = (' - || NEW.animid - || '), Value (AnimName) = (' - || NEW.animname - || '), Value (DepartDate) = (' - || NEW.departdate - || ' has a related COMM_MEMBS row with Key (CommMID) = (' - || commmid - || ') and Value (EndDate) = (' - || enddate - || ')'; - END IF; - END; + IF TG_OP = 'INSERT' + OR (NEW.entrydate <> OLD.entrydate) THEN + DECLARE + commmid comm_membs.commmid%TYPE; + enddate comm_membs.enddate%TYPE; + BEGIN + SELECT comm_membs.enddate, comm_membs.commmid + INTO enddate, commmid + FROM comm_membs + WHERE comm_membs.animid = NEW.animid + AND NEW.departdate < comm_membs.enddate + ORDER BY comm_membs.enddate DESC + LIMIT 1; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of BIOGRAPHY_DATA' + , DETAIL = 'An individual may not be placed in a community after' + || ' the individual has left the study' + || ': Key (AnimID) = (' + || NEW.animid + || '), Value (AnimName) = (' + || NEW.animname + || '), Value (DepartDate) = (' + || NEW.departdate + || ' has a related COMM_MEMBS row with' + || ' Key (CommMID) = (' + || commmid + || ') and Value (EndDate) = (' + || enddate + || ')'; + END IF; + END; + END IF; -- -- Other table checking @@ -397,53 +405,57 @@ CREATE OR REPLACE FUNCTION biography_data_func () a_date follows.date%TYPE; BEGIN -- Cannot have a follow before the individual was studied. - SELECT follows.fid, follows.community, follows.date - INTO a_fid , a_community , a_date - FROM follows - WHERE follows.focal = NEW.animid - AND follows.date < NEW.entrydate - ORDER BY follows.date; -- consistency - IF FOUND THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' - , DETAIL = 'An individual cannot be followed before they are' - || ' under study' - || ': Key (Animid) = (' - || NEW.animid - || '), Value (EntryDate) = (' - || NEW.entrydate - || ': Key (FOLLOWS.FID) = (' - || a_fid - || '), Value (Date) = (' - || a_date - || '), Value (Community) = (' - || a_community - || ')'; + IF NEW.entrydate <> OLD.entrydate THEN + SELECT follows.fid, follows.community, follows.date + INTO a_fid , a_community , a_date + FROM follows + WHERE follows.focal = NEW.animid + AND follows.date < NEW.entrydate + ORDER BY follows.date; -- consistency + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot be followed before they are' + || ' under study' + || ': Key (Animid) = (' + || NEW.animid + || '), Value (EntryDate) = (' + || NEW.entrydate + || ': Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (Date) = (' + || a_date + || '), Value (Community) = (' + || a_community + || ')'; + END IF; END IF; -- Cannot have a follow after study of the individual has ceased. - SELECT follows.fid, follows.community, follows.date - INTO a_fid , a_community , a_date - FROM follows - WHERE follows.focal = NEW.animid - AND follows.date > NEW.departdate - ORDER BY follows.date DESC; -- consistency - IF FOUND THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' - , DETAIL = 'An individual cannot be followed after they are' - || ' no longer under study' - || ': Key (Animid) = (' - || NEW.animid - || '), Value (DepartDate) = (' - || NEW.departdate - || ': Key (FOLLOWS.FID) = (' - || a_fid - || '), Value (Date) = (' - || a_date - || '), Value (Community) = (' - || a_community - || ')'; + IF NEW.departdate <> OLD.departdate THEN + SELECT follows.fid, follows.community, follows.date + INTO a_fid , a_community , a_date + FROM follows + WHERE follows.focal = NEW.animid + AND follows.date > NEW.departdate + ORDER BY follows.date DESC; -- consistency + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot be followed after they are' + || ' no longer under study' + || ': Key (Animid) = (' + || NEW.animid + || '), Value (DepartDate) = (' + || NEW.departdate + || ': Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (Date) = (' + || a_date + || '), Value (Community) = (' + || a_community + || ')'; + END IF; END IF; END; @@ -588,250 +600,257 @@ CREATE OR REPLACE FUNCTION biography_data_func () -- Check ARRIVALS.Cycle IF NEW.sex = 'sdb_female' THEN - -- The sdb_male_swelling code (n/a) cannot be assigned to - -- females. - SELECT roles.role - , events.eid, events.behavior, events.start, events.stop - , arrivals.arid, arrivals.cycle - , follows.fid, follows.focal, follows.date - INTO a_role - , a_eid , a_behavior , a_start , a_stop - , a_arid , a_cycle - , a_fid , a_focal , a_date - FROM roles - JOIN events ON (events.eid = roles.eid) - JOIN arrivals ON (arrivals.eid = roles.eid) - JOIN follows ON (follows.fid = events.fid) - WHERE roles.participant = NEW.animid - AND arrivals.cycle = 'sdb_male_swelling' - -- Produce a consistent error message - ORDER BY follows.date, follows.fid - , events.start, events.stop, events.eid - , roles.role, roles.pid; - IF FOUND THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' - , DETAIL = 'A female cannot be assigned the' - || ' (sdb_male_swelling) code as a sexual cycle state' - || ': Key (Animid) = (' - || NEW.animid - || '): Value (Sex) = (' - || NEW.sex - || '): Value (BirthDate) = (' - || NEW.birthdate - || '): Key (ROLES.PID) = (' - || a_pid - || '), Value (ROLES.Role) = (' - || a_role - || '): Key (EVENTS.EID) = (' - || a_eid - || '): Value (EVENTS.Behavior) = (' - || a_behavior - || '), Value (EVENTS.Start) = (' - || a_start - || '), Value (EVENTS.Stop) = (' - || a_stop - || '): Key (ARRIVALS.ArID) = (' - || a_arid - || '), Value (ARRIVALS.Cycle) = (' - || a_cycle - || '): Key (FOLLOWS.FID) = (' - || a_fid - || '), Value (FOLLOWS.Focal) = (' - || a_focal - || '), Value (FOLLOWS.Date) = (' - || a_date - || ')' - , HINT = 'sdb_follow_age_hint'; + IF NEW.sex <> OLD.sex THEN + -- The sdb_male_swelling code (n/a) cannot be assigned to + -- females. + SELECT roles.role + , events.eid, events.behavior, events.start, events.stop + , arrivals.arid, arrivals.cycle + , follows.fid, follows.focal, follows.date + INTO a_role + , a_eid , a_behavior , a_start , a_stop + , a_arid , a_cycle + , a_fid , a_focal , a_date + FROM roles + JOIN events ON (events.eid = roles.eid) + JOIN arrivals ON (arrivals.eid = roles.eid) + JOIN follows ON (follows.fid = events.fid) + WHERE roles.participant = NEW.animid + AND arrivals.cycle = 'sdb_male_swelling' + -- Produce a consistent error message + ORDER BY follows.date, follows.fid + , events.start, events.stop, events.eid + , roles.role, roles.pid; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'A female cannot be assigned the' + || ' (sdb_male_swelling) code as a sexual cycle state' + || ': Key (Animid) = (' + || NEW.animid + || '): Value (Sex) = (' + || NEW.sex + || '): Value (BirthDate) = (' + || NEW.birthdate + || '): Key (ROLES.PID) = (' + || a_pid + || '), Value (ROLES.Role) = (' + || a_role + || '): Key (EVENTS.EID) = (' + || a_eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '): Key (ARRIVALS.ArID) = (' + || a_arid + || '), Value (ARRIVALS.Cycle) = (' + || a_cycle + || '): Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || ')' + , HINT = 'sdb_follow_age_hint'; + END IF; END IF; - -- The sdb_adolescent_swelling code (U) can only be assigned to - -- adolescent females. -- Can't be too young. - SELECT roles.role - , events.eid, events.behavior, events.start, events.stop - , arrivals.arid, arrivals.cycle - , follows.fid, follows.focal, follows.date - INTO a_role - , a_eid , a_behavior , a_start , a_stop - , a_arid , a_cycle - , a_fid , a_focal , a_date - FROM roles - JOIN events ON (events.eid = roles.eid) - JOIN arrivals ON (arrivals.eid = roles.eid) - JOIN follows ON (follows.fid = events.fid) - WHERE roles.participant = NEW.animid - AND arrivals.cycle = 'sdb_adolescent_swelling' - AND NEW.birthdate - > (follows.date -- 'sdb_min_adolescent_age sdb_min_adolescent_age_units'::INTERVAL - ) - -- Produce a consistent error message - ORDER BY follows.date, follows.fid - , events.start, events.stop, events.eid - , roles.role, roles.pid; - IF FOUND THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' - , DETAIL = 'A non-adolescent female, a female younger than' - || ' sdb_min_adolescent_age' - || ' sdb_min_adolescent_age_units old, cannot be' - || ' assigned the' - || ' (sdb_adolescent_swelling) code as a sexual' - || ' cycle state' - || ': Key (Animid) = (' - || NEW.animid - || '): Value (Sex) = (' - || NEW.sex - || '): Value (BirthDate) = (' - || NEW.birthdate - || '): Key (ROLES.PID) = (' - || a_pid - || '), Value (ROLES.Role) = (' - || a_role - || '): Key (EVENTS.EID) = (' - || a_eid - || '): Value (EVENTS.Behavior) = (' - || a_behavior - || '), Value (EVENTS.Start) = (' - || a_start - || '), Value (EVENTS.Stop) = (' - || a_stop - || '): Key (ARRIVALS.ArID) = (' - || a_arid - || '), Value (ARRIVALS.Cycle) = (' - || a_cycle - || '): Key (FOLLOWS.FID) = (' - || a_fid - || '), Value (FOLLOWS.Focal) = (' - || a_focal - || '), Value (FOLLOWS.Date) = (' - || a_date - || ')' - , HINT = 'sdb_follow_age_hint'; - END IF; - - -- The sdb_adolescent_swelling code (U) can only be assigned to - -- adolescent females. -- Can't be too old. - SELECT roles.role - , events.eid, events.behavior, events.start, events.stop - , arrivals.arid, arrivals.cycle - , follows.fid, follows.focal, follows.date - INTO a_role - , a_eid , a_behavior , a_start , a_stop - , a_arid , a_cycle - , a_fid , a_focal , a_date - FROM roles - JOIN events ON (events.eid = roles.eid) - JOIN arrivals ON (arrivals.eid = roles.eid) - JOIN follows ON (follows.fid = events.fid) - WHERE roles.participant = NEW.animid - AND arrivals.cycle = 'sdb_adolescent_swelling' - AND NEW.birthdate - <= (follows.date - - '1 sdb_max_adolescent_age_units'::INTERVAL -- 'sdb_max_adolescent_age sdb_max_adolescent_age_units'::INTERVAL - ) - -- Produce a consistent error message - ORDER BY follows.date, follows.fid - , events.start, events.stop, events.eid - , roles.role, roles.pid; - IF FOUND THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' - , DETAIL = 'A non-adolescent female, a female older than' - || ' sdb_max_adolescent_age' - || ' sdb_max_adolescent_age_units old, cannot be' - || ' assigned the' - || ' (sdb_adolescent_swelling) code as a sexual' - || ' cycle state' - || ': Key (Animid) = (' - || NEW.animid - || '): Value (Sex) = (' - || NEW.sex - || '): Value (BirthDate) = (' - || NEW.birthdate - || '): Key (ROLES.PID) = (' - || a_pid - || '), Value (ROLES.Role) = (' - || a_role - || '): Key (EVENTS.EID) = (' - || a_eid - || '): Value (EVENTS.Behavior) = (' - || a_behavior - || '), Value (EVENTS.Start) = (' - || a_start - || '), Value (EVENTS.Stop) = (' - || a_stop - || '): Key (ARRIVALS.ArID) = (' - || a_arid - || '), Value (ARRIVALS.Cycle) = (' - || a_cycle - || '): Key (FOLLOWS.FID) = (' - || a_fid - || '), Value (FOLLOWS.Focal) = (' - || a_focal - || '), Value (FOLLOWS.Date) = (' - || a_date - || ')' - , HINT = 'sdb_follow_age_hint'; + IF NEW.sex <> OLD.sex + OR NEW.birthdate <> OLD.birthdate THEN + -- The sdb_adolescent_swelling code (U) can only be assigned to + -- adolescent females. -- Can't be too young. + SELECT roles.role + , events.eid, events.behavior, events.start, events.stop + , arrivals.arid, arrivals.cycle + , follows.fid, follows.focal, follows.date + INTO a_role + , a_eid , a_behavior , a_start , a_stop + , a_arid , a_cycle + , a_fid , a_focal , a_date + FROM roles + JOIN events ON (events.eid = roles.eid) + JOIN arrivals ON (arrivals.eid = roles.eid) + JOIN follows ON (follows.fid = events.fid) + WHERE roles.participant = NEW.animid + AND arrivals.cycle = 'sdb_adolescent_swelling' + AND NEW.birthdate + > (follows.date + - 'sdb_min_adolescent_age sdb_min_adolescent_age_units'::INTERVAL + ) + -- Produce a consistent error message + ORDER BY follows.date, follows.fid + , events.start, events.stop, events.eid + , roles.role, roles.pid; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'A non-adolescent female, a female younger than' + || ' sdb_min_adolescent_age' + || ' sdb_min_adolescent_age_units old, cannot be' + || ' assigned the' + || ' (sdb_adolescent_swelling) code as a sexual' + || ' cycle state' + || ': Key (Animid) = (' + || NEW.animid + || '): Value (Sex) = (' + || NEW.sex + || '): Value (BirthDate) = (' + || NEW.birthdate + || '): Key (ROLES.PID) = (' + || a_pid + || '), Value (ROLES.Role) = (' + || a_role + || '): Key (EVENTS.EID) = (' + || a_eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '): Key (ARRIVALS.ArID) = (' + || a_arid + || '), Value (ARRIVALS.Cycle) = (' + || a_cycle + || '): Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || ')' + , HINT = 'sdb_follow_age_hint'; + END IF; + + -- The sdb_adolescent_swelling code (U) can only be assigned to + -- adolescent females. -- Can't be too old. + SELECT roles.role + , events.eid, events.behavior, events.start, events.stop + , arrivals.arid, arrivals.cycle + , follows.fid, follows.focal, follows.date + INTO a_role + , a_eid , a_behavior , a_start , a_stop + , a_arid , a_cycle + , a_fid , a_focal , a_date + FROM roles + JOIN events ON (events.eid = roles.eid) + JOIN arrivals ON (arrivals.eid = roles.eid) + JOIN follows ON (follows.fid = events.fid) + WHERE roles.participant = NEW.animid + AND arrivals.cycle = 'sdb_adolescent_swelling' + AND NEW.birthdate + <= (follows.date + - '1 sdb_max_adolescent_age_units'::INTERVAL + - 'sdb_max_adolescent_age sdb_max_adolescent_age_units'::INTERVAL + ) + -- Produce a consistent error message + ORDER BY follows.date, follows.fid + , events.start, events.stop, events.eid + , roles.role, roles.pid; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'A non-adolescent female, a female older than' + || ' sdb_max_adolescent_age' + || ' sdb_max_adolescent_age_units old, cannot be' + || ' assigned the' + || ' (sdb_adolescent_swelling) code as a sexual' + || ' cycle state' + || ': Key (Animid) = (' + || NEW.animid + || '): Value (Sex) = (' + || NEW.sex + || '): Value (BirthDate) = (' + || NEW.birthdate + || '): Key (ROLES.PID) = (' + || a_pid + || '), Value (ROLES.Role) = (' + || a_role + || '): Key (EVENTS.EID) = (' + || a_eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '): Key (ARRIVALS.ArID) = (' + || a_arid + || '), Value (ARRIVALS.Cycle) = (' + || a_cycle + || '): Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || ')' + , HINT = 'sdb_follow_age_hint'; + END IF; END IF; ELSE -- Not female. -- Only the sdb_male_swelling code (n/a) can be assigned -- to non-females. - SELECT roles.role - , events.eid, events.behavior, events.start, events.stop - , arrivals.arid, arrivals.cycle - , follows.fid, follows.focal, follows.date - INTO a_role - , a_eid , a_behavior , a_start , a_stop - , a_arid , a_cycle - , a_fid , a_focal , a_date - FROM roles - JOIN events ON (events.eid = roles.eid) - JOIN arrivals ON (arrivals.eid = roles.eid) - JOIN follows ON (follows.fid = events.fid) - WHERE roles.participant = NEW.animid - AND arrivals.cycle <> 'sdb_male_swelling' - -- Produce a consistent error message - ORDER BY follows.date, follows.fid - , events.start, events.stop, events.eid - , roles.role, roles.pid; - IF FOUND THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' - , DETAIL = 'A non-female can only be assigned the' - || ' (sdb_male_swelling) code as a sexual' - || ' cycle state' - || ': Key (Animid) = (' - || NEW.animid - || '): Value (Sex) = (' - || NEW.sex - || '): Value (BirthDate) = (' - || NEW.birthdate - || '): Key (ROLES.PID) = (' - || a_pid - || '), Value (ROLES.Role) = (' - || a_role - || '): Key (EVENTS.EID) = (' - || a_eid - || '): Value (EVENTS.Behavior) = (' - || a_behavior - || '), Value (EVENTS.Start) = (' - || a_start - || '), Value (EVENTS.Stop) = (' - || a_stop - || '): Key (ARRIVALS.ArID) = (' - || a_arid - || '), Value (ARRIVALS.Cycle) = (' - || a_cycle - || '): Key (FOLLOWS.FID) = (' - || a_fid - || '), Value (FOLLOWS.Focal) = (' - || a_focal - || '), Value (FOLLOWS.Date) = (' - || a_date - || ')'; + IF NEW.sex <> OLD.sex THEN + SELECT roles.role + , events.eid, events.behavior, events.start, events.stop + , arrivals.arid, arrivals.cycle + , follows.fid, follows.focal, follows.date + INTO a_role + , a_eid , a_behavior , a_start , a_stop + , a_arid , a_cycle + , a_fid , a_focal , a_date + FROM roles + JOIN events ON (events.eid = roles.eid) + JOIN arrivals ON (arrivals.eid = roles.eid) + JOIN follows ON (follows.fid = events.fid) + WHERE roles.participant = NEW.animid + AND arrivals.cycle <> 'sdb_male_swelling' + -- Produce a consistent error message + ORDER BY follows.date, follows.fid + , events.start, events.stop, events.eid + , roles.role, roles.pid; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'A non-female can only be assigned the' + || ' (sdb_male_swelling) code as a sexual' + || ' cycle state' + || ': Key (Animid) = (' + || NEW.animid + || '): Value (Sex) = (' + || NEW.sex + || '): Value (BirthDate) = (' + || NEW.birthdate + || '): Key (ROLES.PID) = (' + || a_pid + || '), Value (ROLES.Role) = (' + || a_role + || '): Key (EVENTS.EID) = (' + || a_eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '): Key (ARRIVALS.ArID) = (' + || a_arid + || '), Value (ARRIVALS.Cycle) = (' + || a_cycle + || '): Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || ')'; + END IF; END IF; END IF; END; -- 2.34.1