Rows are repeating by the trigger function

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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 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@aklaver.com

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

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.

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

regards
Kiran

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

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@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

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

On 31 October 2016 at 14:41, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

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

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?

I'm fairly confident that the duplicates are from updates on the
cf_question table. Since the trigger also fires on update and then
inserts another record, that would explain the duplication pretty
well.

Nevertheless, if there are other triggers those bear investigation.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

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

Dear Adrian and Alban,

Thanks for the suggestions.
I revisited the entire tables, triggers related to the tables. I did not
find anything strange.
But, I removed all the rows from the cf_user_question_link and inserted
relevant rows into the table from *cf_question*.

Also, I recreated the function without the update and deployed it.
Now the trigger function works as expected ( when a new row is inserted
into the *cf_question*, a row with necessary fields is inserted into
the cf_user_question_link
table)

Though now the trigger function works as expected but the issue that I
faced was it because there were rows already in the cf_question table
before the trigger function was deployed ?

regards
Kiran

On Mon, Oct 31, 2016 at 3:45 PM, Alban Hertroys <haramrae@gmail.com> wrote:

On 10/31/2016 10:02 AM, Kiran wrote:

Dear Adrian and Alban,

Thanks for the suggestions.
I revisited the entire tables, triggers related to the tables. I did not
find anything strange.
But, I removed all the rows from the* *cf_user_question_link and
inserted relevant rows into the table from *cf_question*.

Also, I recreated the function without the update and deployed it.
Now the trigger function works as expected ( when a new row is inserted
into the *cf_question*, a row with necessary fields is inserted into
the cf_user_question_link table)

Though now the trigger function works as expected but the issue that I
faced was it because there were rows already in the cf_question table
before the trigger function was deployed ?

Not really, the issue, as Alban pointed out, was you had UPDATE in the
trigger statement. So every time you did an UPDATE to cf_question you
got an INSERT into cf_user_question_link. So I suspect somewhere/somehow
you did 3 UPDATES to all the rows in cf_question while you had the old
trigger statement deployed.

regards
Kiran

On Mon, Oct 31, 2016 at 3:45 PM, Alban Hertroys <haramrae@gmail.com
<mailto:haramrae@gmail.com>> wrote:

On 31 October 2016 at 14:41, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

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

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?

I'm fairly confident that the duplicates are from updates on the
cf_question table. Since the trigger also fires on update and then
inserts another record, that would explain the duplication pretty
well.

Nevertheless, if there are other triggers those bear investigation.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

--
Adrian Klaver
adrian.klaver@aklaver.com

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