Re: Stored Procedure and Trigger they puzzle me - Mailing list pgsql-general
From | Lars Heidieker |
---|---|
Subject | Re: Stored Procedure and Trigger they puzzle me |
Date | |
Msg-id | 5CA9760F-B8B6-4D3E-823D-A7A9B18B57A8@heidieker.de Whole thread Raw |
In response to | Re: Stored Procedure and Trigger they puzzle me ("Albe Laurenz" <all@adv.magwien.gv.at>) |
List | 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@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-----
pgsql-general by date: