From: | Kiran <bangalore(dot)kiran(at)gmail(dot)com> |
---|---|
To: | Alban Hertroys <haramrae(at)gmail(dot)com> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Rows are repeating by the trigger function |
Date: | 2016-10-30 13:50:35 |
Message-ID: | CAJfd1U4is+R-FFeM6Wb8ioaCjC2z4fFa0ARRNzy9B1p2AQ43pg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 <haramrae(at)gmail(dot)com> wrote:
>
> > On 30 Oct 2016, at 10:31, Kiran <bangalore(dot)kiran(at)gmail(dot)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.
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-10-30 14:01:30 | Re: Rows are repeating by the trigger function |
Previous Message | Mark Morgan Lloyd | 2016-10-30 13:21:04 | PostgreSQL and ArcGIS used in UK military exercise |