From: | Lars Heidieker <lars(at)heidieker(dot)de> |
---|---|
To: | Albe Laurenz <all(at)adv(dot)magwien(dot)gv(dot)at> |
Cc: | "Alban Hertroys" <alban(at)magproductions(dot)nl>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Stored Procedure and Trigger they puzzle me |
Date: | 2006-12-20 12:24:17 |
Message-ID: | 5CA9760F-B8B6-4D3E-823D-A7A9B18B57A8@heidieker.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
-----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(at)heidieker(dot)de
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-----
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Silveira | 2006-12-20 12:54:48 | Re: xml2 install problem |
Previous Message | Parthan SR | 2006-12-20 10:33:43 | Re: Simple Trigger Error |