[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Gnumed-devel] changes to add_name() in SQL
From: |
Karsten Hilbert |
Subject: |
Re: [Gnumed-devel] changes to add_name() in SQL |
Date: |
Mon, 24 Nov 2003 15:31:49 +0100 |
User-agent: |
Mutt/1.3.22.1i |
> perhaps the trigger gets invoked twice;
It does. I just don't understand why that doesn't give the
result I expect. Here goes:
-------------------------
create FUNCTION F_uniq_active_name() RETURNS OPAQUE AS '
DECLARE
BEGIN
if NEW.active = true then
update names set active = false where id_identity =
NEW.id_identity and active = true;
return NEW;
end if;
return NEW;
END;' LANGUAGE 'plpgsql';
create TRIGGER TR_uniq_active_name
BEFORE insert or update ON names
FOR EACH ROW EXECUTE PROCEDURE F_uniq_active_name();
---------------------
So, if I run this:
update names set active = TRUE where id_identity = 1;
I would expect this to happen:
1) TR_uniq_active_name fires F_uniq_active_name()
2) F_uniq_active_name() sees NEW.active being TRUE
3) hence it runs
update names set active = FALSE where id_identity = NEW.id_identit and
active = true;
3a) TR_uniq_active_name fires F_uniq_active_name()
3b) F_uniq_active_name() sees NEW.active being FALSE
3c) hence it immediately returns NEW
4) now F_uniq_active_name() returns NEW
5) now the original update statement runs
Now, why does it display the xor behaviour that Syan pointed
out ? I certainly don't know at the moment.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346