Re: Rows are repeating by the trigger function - Mailing list pgsql-general

From Kiran
Subject Re: Rows are repeating by the trigger function
Date
Msg-id CAJfd1U4is+R-FFeM6Wb8ioaCjC2z4fFa0ARRNzy9B1p2AQ43pg@mail.gmail.com
Whole thread Raw
In response to Re: Rows are repeating by the trigger function  (Alban Hertroys <haramrae@gmail.com>)
List 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@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.


pgsql-general by date:

Previous
From: Mark Morgan Lloyd
Date:
Subject: PostgreSQL and ArcGIS used in UK military exercise
Next
From: Adrian Klaver
Date:
Subject: Re: Rows are repeating by the trigger function