Hi Alban, I agree with you about the UPDATE. Thanks for pointing out.
regards Kiran On Sun, Oct 30, 2016 at 12:49 PM, Alban Hertroys <haram...@gmail.com> wrote: > > > On 30 Oct 2016, at 10:31, Kiran <bangalore.ki...@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. > >
