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@heidieker.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-----