PostgreSQL trigger doesn't insert all records I'm working with PostgreSQL 10. I'm updating another table when a record is inserted into the original table.  The trigger doesn't insert the last record if there are more than one. If it's just one record it won't get inserted at all. Any ideas why it's doing this?

create table employee
(
   empID SERIAL unique PRIMARY KEY NOT NULL,
   first_name text not null,
   mi text not null,
   last_name text not null,
   full_name character varying (150) UNIQUE NOT NULL,
   employee_email character varying(150) UNIQUE
);

CREATE TABLE empmerge
(
  empid integer NOT NULL REFERENCES employee(empID) MATCH FULL
      ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  full_name character varying NOT NULL REFERENCES employee(full_name) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  org_section text,
  date_of_xfer date

);


The employee trigger:

create or replace function concat_full_name() returns trigger as $$
begin
    if NEW.full_name is null 
      then NEW.full_name := concat(new.first_name, ' ',new.mi,'.', ' ', new.last_name);
    end if;
    return new;
end;
$$ language plpgsql;

create trigger trig_concat_full_name
before insert
on employee
for each row
execute procedure concat_full_name();


The merge trigger

create or replace function insert_empMerge() 
  returns trigger as $$
begin
  INSERT INTO empMerge(empID, full_name) 
  SELECT employee.empID, employee.full_name
  FROM employee
  WHERE NOT EXISTS (SELECT 1
                    FROM empMerge
                    WHERE employee.empID = empMerge.empID 
                     and  employee.full_name = empMerge.full_name);
    return new;
end;
$$ language plpgsql;

create trigger trig_insert_empMerge
before insert
on employee
for each row
execute procedure insert_empMerge();


The insert statement:

insert into employee(first_name, mi, last_name) 
values 
 ('dora','l', 'explorer'), 
 ('atticus','m', 'finch'), 
 ('nancy','s', 'drew');

select * from empMerge;
The select from employee table won't work as it is a before trigger and thus the new rows are not yet in the employees table. 

You can use the new record directly.

create or replace function insert_empMerge() 
  returns trigger as $$
begin
  INSERT INTO empMerge(empID, full_name) 
  SELECT new.empID, new.full_name
  WHERE NOT EXISTS (SELECT 1
                    FROM empMerge
                    WHERE new.empID = empMerge.empID 
                     and  new.full_name = empMerge.full_name);
  return new;
end;
$$ language plpgsql;


You could make that a bit shorter (and faster) if you declare a unique constraint on empmerge(empid, full_name) and then use insert on conflict:

alter table empmerge
   add constraint unique_employee
   unique (empid, full_name);


Then change the trigger function:

create or replace function insert_empMerge() 
  returns trigger as $$
begin
  INSERT INTO empMerge(empID, full_name) 
  values (new.empID, new.full_name)
  on conflict on constraint unique_employee do nothing;
  return new;
end;
$$ language plpgsql;




Another option would be to make the existing trigger an AFTER trigger. In that case the new row is already inserted into the employee table and it would be visible for the trigger.