On Sun, Oct 30, 2016 at 12:49 PM, Alban Hertroys <haramrae@gmail.com> wrote:
> On 30 Oct 2016, at 10:31, Kiran <bangalore.kiran@gmail.com> wrote: > > Dear Folks, > > I have a table cf_question with 31 rows. > I want to insert/update another table cf_user_question_link when cf_question table is inserted/updated with row(s). > I have written trigger function for this as follows. > > > CREATE FUNCTION user_question_link() RETURNS trigger AS > $user_question_link$ > begin > SET search_path TO monolith; > INSERT INTO > cf_user_question_link(cf_user_id,cf_question_id) > VALUES(NEW.user_id,NEW.cf_question_id); > RETURN NEW; > end; > $user_question_link$ > LANGUAGE plpgsql > COST 100; > > > /* Call the trigger function */ > > CREATE TRIGGER insert_user_question_link AFTER INSERT OR UPDATE > ON monolith.cf_question > FOR EACH ROW EXECUTE PROCEDURE user_question_link(); > > > Problem: The cf_user_question_link gets inserted with 94 rows instead of 31 rows. The 31 rows are repeated 3 times > I tried dropping the trigger function and recreating it but with the same 94 rows in the table. > > It would be great if any from the forum point to me where I am doing wrong.
I don't think you want that same trigger to fire on UPDATE of cf_question, like you do now.
On UPDATE you have two choices; - either you need to take changes to those _id columns into account and delete rows that belong to the OLD link and not to the NEW one (or do nothing if those stayed the same) - or you do nothing (no trigger needed) because in the majority of cases changing FK's is limited to a few power users at best and they're supposed to know what they're doing. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
