Re: Stored Procedure and Trigger they puzzle me

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 19 Dec 2006, at 08:56, Alban Hertroys wrote:

>>
>> Not sure as I deleted them before, but currently I cant reproduce it.
>> I just get the following now:
>> 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"
>
> Looks like a locationpath being inserted with an invalid
> ltlocancestorid; probably the first or the last record inserted is
> wrong.
>
>> on: UPDATE ltlocation SET id = 45555 WHERE id = 18999;
>
> Not something that will actually happen in your application, I bet
> (what's the point of modifying an artificial key?); no reason it
> shouldn't work, though.
>
>> which I don't get if:
>> IF tg_op = 'UPDATE' THEN
>> DELETE FROM ltlocationpath WHERE ltlocation_id = old.id;
>> END IF;
>> is executed.
>
> I didn't suggest to remove that block, though it can probably be
> handled
> more elegantly (fe. only if a column referenced by ltlocationpath
> changed). I left it out because it wasn't part of what I tried to
> explain.
>
>> Probably I am running in some bad interaction between triggers and
>> foreign key constraints (cascading)
>> I'll just continue to play around to get a better understanding.
>

Thanks, yes the ltlocancester_id is invalid as it was changed by the
foreign key constraint before,
in the end it turned out to be some bad interaction between the
trigger and the foreign key constraint,
as Albe Laurenz found out.
That's where I got confused.

(Yes, updating the primary key doesn't happen, but I thought the
trigger should be able to handle that.)

- --

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)

iD8DBQFFh79UcxuYqjT7GRYRAu5WAKCXKY0GzAbSV5fxuBH6ANCddGVIWwCeJLcE
0vXHy8xr/Y54A9AYz95Aaqk=
=zRtO
-----END PGP SIGNATURE-----