3

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;
2

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.

  • Seriously? Are you checking a child table for the existence of a record when the parent is being inserted? I thought I'd seen it all when I took the dog for a walk this morning and came face to face with a lama. Seems not. – Gerard H. Pille Jul 6 '18 at 12:34
  • @GerardH.Pille: I have no idea what you are talking about. Which "child" and which "parent"? (Also: this is basically just the original code but with the obvious errors fixed) – a_horse_with_no_name Jul 6 '18 at 12:35
  • Empmerge references employee. The "not exists" and "on conflict" are part of your answer. I seriously hope I'm mistaken. – Gerard H. Pille Jul 6 '18 at 12:41
  • @GerardH.Pille: you apparently do not understand the code. It is not checking if the parent exists, it's checking if that row already exists in the target table empmerge table and makes sure the whole thing doesn't fail if it does exist. – a_horse_with_no_name Jul 6 '18 at 12:43
  • But it cannot exist before the parent. – Gerard H. Pille Jul 6 '18 at 12:45
1

Your trigger deals with all employee records (???) and since you call it "before insert", the new record is not there yet.

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.