postgresql trigger unexpected behaviour by many copies of inserted row

Question

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?

Answer 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.