0

On insert in tbl1, the trigger I am working on, copies rows in tbl2 with some updates.

Trigger:

create or replace function myprocedure() returns trigger as
begin 
IF (TG_OP = 'INSERT')THEN 
 insert into tbl2
 select new.* from tbl1 ;

 update tbl2
 set (field1, field2) =  ...
 where gid = new.gid;

 return new ;
 END IF ;
END;
$$ LANGUAGE plpgsql ;

DROP TRIGGER IF EXISTS mytrig ON public.tbl1 ;
CREATE TRIGGER mytrig AFTER INSERT or UPDATE on public.tbl1
FOR EACH ROW EXECUTE PROCEDURE myprocedure();

My problem is:

  • The first insert in tbl1 gives 1 insert in tbl2. It is fine.
  • The second insert in tbl1 gives 2 (identical) lines in tbl2 (values of second insert)
  • The third insert in tbl1 gives 3 (identical) lines in tbl2 (values of third insert)
  • etc.

I am feeling a little lost. Any help?

1

Why do you do an update on tbl2? Anyway you must add a where clause with the primary key of the new row to the update. You are now updating all rows in tbl2.

Also the insert is inserting new.* for every row already in tbl1.

Why don't you rewrite the insert into something:

insert into tbl2 (field1, field2, ...) values (new.field1, new.field2, ...);

Now you can set the correct values and do not need an update afterwards.

  • update is needed to normalize datas. (I tested it and doesn't seem to be the problem). I updated the code too. – Leehan Nov 25 '15 at 8:47
  • Think you forgot to remove a ; in the update. Changed my reply too. – Marco Nov 25 '15 at 8:51
  • Values instead of Insert works fine. There is just one line in tbl2 for one line inserted in tbl1. (One more thing I do not understand...). Thanks anyway/ – Leehan Nov 25 '15 at 9:32
  • 1
    @Leehan the main problem was the from tbl1 because it was inserting a row (of the whole table tbl1) for each and every row of the INSERT. You could even replace the select new.* from u with select new.* ; – ypercubeᵀᴹ Nov 25 '15 at 10:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.