-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 19 Dec 2006, at 11:44, Albe Laurenz wrote:
Actually I just figured out, that is is not the trigger but the two
cascade on update triggers collide.
It happens also without the trigger:
lt=# UPDATE ltlocation SET id = 45555 WHERE id = 18999;
ERROR: insert or update on table "ltlocationpath" violates foreign
key constraint "ltlocancester_fkey"
DETAIL: Key (ltlocancester_id)=(18999) is not present in table
"ltlocation".
CONTEXT: SQL statement "UPDATE ONLY "public"."ltlocationpath" SET
"ltlocation_id" = $1 WHERE "ltlocation_id" = $2"
I think the two foreign key constraints together make it impossible
to change the primary key (which isn't needed),
as they would have to run "as one" which they can't...
The two foreign key constraints worked fine when I tried them,
the only problem was the trigger.
I don't get what you describe.
Have you changed anything in the definitions?
If yes, post table, key, and trigger definitions as you have them now.
Yes you are right, I must have messed something up when I tried that,
the foreign keys work properly.
Nevertheless I changed the Trigger Function to the following:
CREATE OR REPLACE FUNCTION populatelocationpath() RETURNS trigger AS $$
DECLARE
workid integer := 0;
BEGIN
IF tg_op = 'UPDATE' THEN
IF old.parent <> new.parent THEN
DELETE FROM ltlocationpath WHERE ltlocation_id = new.id;
workid := new.id;
WHILE workid > 0 LOOP
INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id)
VALUES (new.id, workid);
SELECT INTO workid parent FROM ltlocation WHERE id = workid;
END LOOP;
END IF;
END IF;
IF tg_op = 'INSERT' then
workid := new.id;
WHILE workid > 0 LOOP
INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id)
VALUES (new.id, workid);
SELECT INTO workid parent FROM ltlocation WHERE id = workid;
END LOOP;
END IF;
RETURN new;
END;
$$ LANGUAGE plpgsql;
So it handles only the cases the foreign keys can't and now it works!
- --
Viele Grüße,
Lars Heidieker
lars@xxxxxxxxxxxx
http://paradoxon.info
- ------------------------------------
Mystische Erklärungen.
Die mystischen Erklärungen gelten für tief;
die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind.
-- Friedrich Nietzsche
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (Darwin)
iD8DBQFFiStycxuYqjT7GRYRAn3QAKDCkDL1DZy0xi7t04XeZTl/4Ng3+wCgyOSe
dhd3fFsifDjtY3BGpCP/5rY=
=5IBW
-----END PGP SIGNATURE-----
