From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | Kiran <bangalore(dot)kiran(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 11:49:19 |
Message-ID: | F8E942E8-E8DD-4610-975E-D5AF9228CED5@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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 | Alban Hertroys | 2016-10-30 12:04:10 | Re: Way to quickly detect if database tables/columns/etc. were modified? |
Previous Message | Evan Martin | 2016-10-30 09:45:51 | Way to quickly detect if database tables/columns/etc. were modified? |