I have a ‘test’ PostgreSQL 12 schema, defined with initial tables:
begin;
drop schema if exists test cascade;
create schema test;
set search_path = test,public;
create table jobs (
job_id serial primary key,
job_type_lu text);
create table phases (
phase_id serial primary key,
job_id_fk int references jobs(job_id),
phase_type_lu text);
create table phase_steps (
phase_step_id serial primary key,
phase_id_fk int references phases(phase_id),
step_type_lu text);
commit;
‘jobs’ are composed of one or more ‘phases’, which in turn have multiple ‘phase_steps’ What I want to accomplish is to fire two triggers when a new job is inserted into the ‘jobs’ table. I have these two triggers defined:
create or replace function test.setup_phases () returns trigger as $$
begin
insert into test.phase_steps (job_id_fk,phase_type_lu)
values
(new.jobs_id,'RESEARCH: JOB_INPUT'),
(new.jobs_id,'RESEARCH: WATER'),
(new.jobs_id,'RESEARCH: OTHER'),
(new.jobs_id,'RESEARCH: REVISION_ATEAM'),
(new.jobs_id,'RESEARCH: REVISION_CLIENT');
return new;
end;
$$ language plpgsql;
create trigger setup_test_phases after insert on test.jobs
for each row execute procedure test.setup_phases();
create or replace function test.setup_phase_steps() returns trigger as $$
begin
if (new.phase_type_lu = 'RESEARCH')
then
insert into test.phase_steps (phase_id_fk,step_type_lu)
values
(new.phase_id,'RESEARCH: JOB_INPUT'),
(new.phase_id,'RESEARCH: WATER'),
(new.phase_id,'RESEARCH: OTHER'),
(new.phase_id,'RESEARCH: REVISION_ATEAM'),
(new.phase_id,'RESEARCH: REVISION_CLIENT');
return new;
end if;
if (new.phase_type_lu = 'SURVEY')
then
insert into test.phase_steps (phase_id_fk,step_type_lu)
values
(new.phase_id,'SURVEY: SCHEDULING'),
(new.phase_id,'SURVEY: FIELDING'),
(new.phase_id,'SURVEY: REVISION_ATEAM'),
(new.phase_id,'SURVEY: REVISION_CLIENT');
return new;
end if;
end;
$$ language plpgsql;
create trigger setup_test_phase_steps after insert on test.phases
for each row execute procedure test.setup_phase_steps();
If I input data directly for just the phases (therefore invoking only the ‘setup_test_phase_steps’ trigger, e.g.,
insert into test.phases (phase_type_lu)
values
('RESEARCH'),
('SURVEY');
The correct results are obtained:
However, if I enter input directly into the ‘jobs’ table (which then will invoke the ‘setup_test_phases’ AND the ‘setup_test_phase_steps’ triggers):
insert into test.jobs (job_type_lu) values ('NT-TYP');
I get this error:
I’m obviously misunderstanding cascading triggers, but what?
phase_steps
has no columnjob_id_fk
, so you cannot insert into that column. Seems to be a typo.insert into test.phase_steps (phase_id_fk, ...) VALUES ...
, you are saying that you want to insert something into columnphase_id_fk
. Perhaps you have a basic misunderstanding of 'INSERT`.