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;