From 93ba63992c127b2f6d7215673df64994526afbed Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Sat, 11 Oct 2025 21:56:53 +0000 Subject: [PATCH] Standardize error messages in triggers --- .../lib/triggers/create/biography_data.m4 | 108 +++++++++--------- db/schemas/lib/triggers/create/comm_membs.m4 | 77 +++++++------ 2 files changed, 98 insertions(+), 87 deletions(-) diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index d51c335..43bd22a 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -50,12 +50,13 @@ define({check_sex}, {dnl AND biography_data.sex <> '$3'; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'The $2''s sex must be $3' - , DETAIL = 'The $2 of (AnimID) = (' + MESSAGE = 'Error on ' || TG_OP || ' of BIOGRAPHY_DATA' + , DETAIL = 'The $2''s sex must be $3' + || ': The $2 of Key (AnimID) = (' || NEW.animid - || ') has an (AnimID) = (' + || ') has a Key (AnimID) = (' || NEW.$1 - || ') and (Sex) = (' + || ') and Value (Sex) = (' || parent_sex || ')'; END IF; @@ -117,36 +118,37 @@ define({check_firstborn}, {dnl LIMIT 1; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'All but the youngest sibling (or siblings' - || ' in the case of twins) must have a' - || ' (BIOGRAPHY_DATA.FirstBorn) =' - || ' (sdb_not_firstborn)' - , DETAIL = '$2 (AnimID) = (' + MESSAGE = 'Error on ' || TG_OP || ' of BIOGRAPHY_DATA' + , DETAIL = 'All but the youngest sibling (or siblings' + || ' in the case of twins) must have a' + || ' (BIOGRAPHY_DATA.FirstBorn) =' + || ' (sdb_not_firstborn)' + || ': $2 Key (AnimID) = (' || $1.animid - || '), (AnimName) = (' + || '), Value (AnimName) = (' || $1.animname - || '), (MomID) = (' + || '), Value (MomID) = (' || $1.momid - || '), (BirthDate) = (' + || '), Value (BirthDate) = (' || $1.birthdate - || ') results in a sibling [(AnimID) = (' + || ') results in a sibling [Key (AnimID) = (' || bad_animid - || '), (AnimName) = (' + || '), Value (AnimName) = (' || bad_animname - || '), (MomID) = (' + || '), Value (MomID) = (' || $1.momid - || '), (BirthDate) = (' + || '), Value (BirthDate) = (' || bad_birthdate - || '), (FirstBorn) = (' + || '), Value (FirstBorn) = (' || bad_firstborn || ')] with an invalid FirstBorn value, being born' - || ' after [(AnimID) = (' + || ' after [Key (AnimID) = (' || fb_animid - || '), (AnimName) = (' + || '), Value (AnimName) = (' || fb_animname - || '), (MomID) = (' + || '), Value (MomID) = (' || $1.momid - || '), (BirthDate) = (' + || '), Value (BirthDate) = (' || fb_birthdate || ')]' , HINT = 'If the youngest sibling is no longer yougest,' @@ -227,20 +229,21 @@ CREATE OR REPLACE FUNCTION biography_data_func () LIMIT 1; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'An individual cannot have offspring when too young' - , DETAIL = '(AnimID) = (' + MESSAGE = 'Error on ' || TG_OP || ' of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot have offspring when too young' + || ': Key (AnimID) = (' || NEW.animid - || '), (AnimName) = (' + || '), Value (AnimName) = (' || NEW.animname - || '), (Sex) = (' + || '), Value (Sex) = (' || NEW.sex - || '), (BDMin) = (' + || '), Value (BDMin) = (' || NEW.bdmin - || ') has the offspring (AnimId) = (' + || ') has the offspring Key (AnimId) = (' || other_animid - || '), (AnimName) = (' + || '), Value (AnimName) = (' || other_animname - || '), (BDMax) = (' + || '), Value (BDMax) = (' || other_bdmax || ') too soon' , HINT = 'The minimum age for females to be a parent is' @@ -270,20 +273,21 @@ CREATE OR REPLACE FUNCTION biography_data_func () LIMIT 1; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'An individual cannot have offspring when too young' - , DETAIL = '(AnimID) = (' + MESSAGE = 'Error on ' || TG_OP || ' of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot have offspring when too young' + || ': Key (AnimID) = (' || NEW.animid - || '), (AnimName) = (' + || '), Value (AnimName) = (' || NEW.animname - || '), (BDMax) = (' + || '), Value (BDMax) = (' || NEW.bdmax - || ') has a parent (AnimId) = (' + || ') has a parent Key (AnimId) = (' || other_animid - || '), (AnimName) = (' + || '), Value (AnimName) = (' || other_animname - || '), (Sex) = (' + || '), Value (Sex) = (' || other_sex - || '), (BDMin) = (' + || '), Value (BDMin) = (' || other_bdmin || ') who is too young' , HINT = 'The minimum age for females to be a parent is' @@ -332,17 +336,18 @@ CREATE OR REPLACE FUNCTION biography_data_func () LIMIT 1; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'An individual may not be placed in a community before' - || ' the individual is under study' - , DETAIL = '(AnimID) = (' + 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 - || '), (AnimName) = (' + || '), Value (AnimName) = (' || NEW.animname - || '), (StartDate) = (' + || '), Value (StartDate) = (' || NEW.startdate - || ' has a related COMM_MEMBS row with (CommMID) = (' + || ' has a related COMM_MEMBS row with Key (CommMID) = (' || commmid - || '), and (StartDate) = (' + || '), and Value (StartDate) = (' || startdate || ')'; END IF; @@ -363,17 +368,18 @@ CREATE OR REPLACE FUNCTION biography_data_func () LIMIT 1; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'An individual may not be placed in a community after' - || ' the individual has left the study' - , DETAIL = '(AnimID) = (' + 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 - || '), (AnimName) = (' + || '), Value (AnimName) = (' || NEW.animname - || '), (DepartDate) = (' + || '), Value (DepartDate) = (' || NEW.departdate - || ' has a related COMM_MEMBS row with (CommMID) = (' + || ' has a related COMM_MEMBS row with Key (CommMID) = (' || commmid - || ') and (EndDate) = (' + || ') and Value (EndDate) = (' || enddate || ')'; END IF; diff --git a/db/schemas/lib/triggers/create/comm_membs.m4 b/db/schemas/lib/triggers/create/comm_membs.m4 index 669b9ee..0a6df56 100644 --- a/db/schemas/lib/triggers/create/comm_membs.m4 +++ b/db/schemas/lib/triggers/create/comm_membs.m4 @@ -57,24 +57,26 @@ CREATE OR REPLACE FUNCTION comm_membs_func () LIMIT 1; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'An individual may not be in more than one community' - || ' (or even twice in the same community) on' - || ' any given day' - , DETAIL = 'With COMM_MEMBS row (CommMID) = (' + MESSAGE = 'Error on ' || TG_OP || ' of COMM_MEMBS' + , DETAIL = 'An individual may not be in more than one community' + || ' (or even twice in the same community) on' + || ' any given day' + || ': Key (CommMID) = (' || NEW.commmid - || '), (AnimID) = (' + || '), Value (AnimID) = (' || NEW.animid - || ') is being given a (CommID) = (' + || ') is being given a Key (CommID) = (' || NEW.commid - || ') with a (StartDate) = (' + || ') with a Value (StartDate) = (' || NEW.StartDate - || ') and an (EndDate) = (' + || ') and an Value (EndDate) = (' || NEW.EndDate - || '), but this AnimID already has the row (CommMID) = (' + || '), but this AnimID already has the row' + || ' Key (CommMID) = (' || other_row.commmid - || '), (StartDate) = (' + || '), Value (StartDate) = (' || other_row.startdate - || '), (EndDate) = (' + || '), Value (EndDate) = (' || other_row.enddate || ')'; END IF; @@ -99,26 +101,27 @@ CREATE OR REPLACE FUNCTION comm_membs_func () LIMIT 1; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Two COMM_MEMBS rows may not be used to place a' - || ' single individual in the same community on' - || ' successive days' - , DETAIL = 'The new values in the row (CommMID) = (' + MESSAGE = 'Error on ' || TG_OP || ' of COMM_MEMBS' + , DETAIL = 'Two COMM_MEMBS rows may not be used to place a' + || ' single individual in the same community on' + || ' successive days' + || ': The new values in the row Key (CommMID) = (' || NEW.commmid - || '), (AnimID) = (' + || '), Value (AnimID) = (' || NEW.animid - || '), (CommID) = (' + || '), Value (CommID) = (' || NEW.commid - || '), (StartDate) = (' + || '), Value (StartDate) = (' || NEW.startdate - || '), (EndDate) = (' + || '), Value (EndDate) = (' || NEW.enddate - || ') are contiguous with the row where (CommMID) = (' + || ') are contiguous with the row where Key (CommMID) = (' || other_commmid - || '), (AnimID) = (' + || '), Value (AnimID) = (' || NEW.animid - || '), (StartDate) = (' + || '), Value (StartDate) = (' || other_startdate - || '), (EndDate) = (' + || '), Value (EndDate) = (' || other_enddate || ')' , HINT = 'Expand the time interval covered by an existing row'; @@ -137,15 +140,16 @@ CREATE OR REPLACE FUNCTION comm_membs_func () AND NEW.startdate < biography_data.entrydate; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'An individual may not be placed in a community' - || ' before the individual is under study' - , DETAIL = 'The new values in the row (CommMID) = (' + MESSAGE = 'Error on ' || TG_OP || ' of COMM_MEMBS' + , DETAIL = 'An individual may not be placed in a community' + || ' before the individual is under study' + || ': The new values in the row Key (CommMID) = (' || NEW.commmid - || '), (AnimID) = (' + || '), Value (AnimID) = (' || NEW.animid - || '), (CommID) = (' + || '), Value (CommID) = (' || NEW.commid - || '), (StartDate) = (' + || '), Value (StartDate) = (' || NEW.startdate || ') place the individual in the community before' || ' the individual came under study;' @@ -167,19 +171,20 @@ CREATE OR REPLACE FUNCTION comm_membs_func () AND biography_data.departdate < NEW.enddate; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'An individual may not be placed in a community' - || ' after the individual has left the study' - , DETAIL = 'The new values in the row (CommMID) = (' + MESSAGE = 'Error on ' || TG_OP || ' of COMM_MEMBS' + , DETAIL = 'An individual may not be placed in a community' + || ' after the individual has left the study' + || ': The new values in the row Key (CommMID) = (' || NEW.commmid - || '), (AnimID) = (' + || '), Value (AnimID) = (' || NEW.animid - || '), (CommID) = (' + || '), Value (CommID) = (' || NEW.commid - || '), (EndDate) = (' + || '), Value (EndDate) = (' || NEW.enddate || ') place the individual in the community after' || ' the individual left the study;' - || ' (BIOGRAPHY_DATA.Departdate) = (' + || ' Value (BIOGRAPHY_DATA.Departdate) = (' || departdate || ')'; END IF; -- 2.34.1