Re: [Gnumed-devel] changes to add_name() in SQL

[Karsten Hilbert]

> 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