Re: Rows are repeating by the trigger function

Adrian Klaver <adrian.klaver@xxxxxxxxxxx> · Mon, 31 Oct 2016 06:41:09 -0700

On 10/31/2016 02:06 AM, Kiran wrote:
Hi Adrian,

I want the trigger function to insert a row  in cf_user_question_link
table with fields as show in the function once the there is a insert
from in the cf_question table.

If so and I think this was mentioned before, why the UPDATE in the 
trigger statement?

I know 94 = 1 + (3 * 31).
I am just having a normal insert statement into cf_question table.

Are there any other triggers on the tables?

regards
Kiran

On Sun, Oct 30, 2016 at 3:01 PM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> wrote:

    On 10/30/2016 02:31 AM, Kiran 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();

    In addition to what Alban said:

    What do you want the trigger function to do ?

    In other words what are the condition(s) that are supposed to create
    a new row in cf_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

    Well actually 1 row + (3 * 31 rows).

    So what is the query you are sending to cf_question?

                         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.

        regards
        Kiran

    --
    Adrian Klaver
    adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general