Hello all,
I have two tables (simplified):
create table clin_episode (
pk serial primary key,
started timestamp with time zone,
fk_clin_narrative integer
unique
default null
);
create table clin_narrative (
pk serial primary key,
fk_episode integer
not null
references clin_episode(pk),
narrative text
not null
);
The idea behind this is that clin_narrative stores ALL
narrative of a medical record. Clin_episode stores episodes
during which care was received. Such episodes do have a name
(such as "knee pain Nov/04") but such episode names are part
of the medical record narrative and should be stored in
clin_narrative, too.
Now, obviously I got a cyclic referential integrity problem
here, eg. narrative pointing to episodes which point back to
narrative. No problem, I added a deferred constraint trigger
like this (don't worry about the health issue stuff, it isn't
related to the problem):
*-----------------------------------------
-- an episode not linked to a health issue must have a
-- name (at least when the transaction ends ...)
\unset ON_ERROR_STOP
drop trigger tr_standalone_epi_needs_name on clin_episode;
drop function trf_standalone_epi_needs_name();
\set ON_ERROR_STOP 1
create function trf_standalone_epi_needs_name() returns opaque as '
declare
msg text;
narr_pk integer;
narr_fk_episode integer;
begin
-- debug ...
raise notice ''%'', TG_OP;
-- *if* we have a name it must belong to us ...
-- (eg. check for cyclic referential integrity violations)
if NEW.fk_clin_narrative is not null then
select into narr_pk, narr_fk_episode cn.pk, cn.fk_episode
from clin_narrative cn
where cn.pk = NEW.fk_clin_narrative
limit 1;
if narr_fk_episode <> NEW.pk then
msg := ''trf_standalone_epi_needs_name: clin_narrative
row [''
|| narr_pk || ''] does not belong to episode [''
|| NEW.pk || ''] and cannot thus name that
episode'';
raise exception ''%'', msg;
end if;
return NULL;
end if;
-- if linked to a health issue we do not have to have a name of our own
...
if NEW.fk_health_issue is not null then
return NULL;
end if;
msg := ''trf_standalone_epi_needs_name: episodes not linked to a health
issue must point to a clin_narrative row at the end of the transaction'';
raise exception ''%'', msg;
end;
' language 'plpgsql';
-- the trick is to defer the trigger ...
create constraint trigger tr_standalone_epi_needs_name
after insert or update
on clin_episode
initially deferred
for each row
execute procedure trf_standalone_epi_needs_name()
;
*-----------------------------------------
This all works. However, to actually insert data I do the
following:
- insert into clin_episode with fk_clin_narrative=NULL
- insert into clin_narrative with fk_encounter = currval('clin_episode_pk_seq')
- update clin_episode with set fk_clin_narrative =
currval('clin_narrative_pk_seq')
What I end up with is the trigger being queued up for checking
at the end of transaction TWICE. This is quite logical since I
did an INSERT and then an UPDATE and the trigger fires on
both.
However, the first trigger invocation (on INSERT) correctly
raises an exception since at that time the NEW row did not yet
have a suitable fk_clin_narrative.
What is the proper solution here ?
- Try to deal with INSERT and UPDATE somehow separately ? The
problem I see with this is that *actually* I want to check the
state of the row at the *latest* UDPATE in a transaction
*only* and there might possibly be several UPDATEs in a tx.
- Don't check whether *NEW* has a fk_clin_narrative at trigger
execution time (eg. at the end of the transaction) but
rather explicitely SELECT fk_clin_narrative from the table
where pk = NEW.pk ? IOW check the actual state of the row
(which may have undergone several UPDATEs) at the end of the
transaction instead of the NEW row at trigger queue up
time. Would I then not run into trouble when
trying to insert more than one episode within the same
transaction ?
I'd be happy if someone had some help on this.
(Will post full schema details if needed.)
Karsten
GnuMed i18n coordinator
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
