Re: Stored Procedure and Trigger they puzzle me

-----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-----