PostgreSQL – Cascading Triggers Between Three Tables

cascadepostgresqltrigger

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?

Best Answer

To resolve your problem, I did the following (all of the code below can be found on the fiddle here):

I changed the formatting somewhat - it helps me think - I try to base my SQL coding standards on this, but that's, as Fr. Jack would say, an ecumenical matter (if you're not a Fr. Ted fan, don't worry...).

The crux of your problem is that you have a cascading chain of FOREIGN KEYs from job to phase to phase_step - which is good - helps keep track of what work goes with what contract &c.

The problem is with your code here:

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'),

You have setup_phases, BUT underneath that, you have insert into test.phase_steps (job_id_fk,phase_type_lu) - you "skip" the INSERT INTO phases step - so your chain of FOREIGN KEYs is broken.

Now, I imagine that your work flow is something like this:

You receive a job - the first phase of which is normally RESEARCH, so I created the phase table as follows (job remains unchanged):

CREATE TABLE phase 
(
  phase_id      SERIAL PRIMARY KEY,
  job_id_fk     INTEGER references job (job_id),
  phase_type_lu TEXT DEFAULT 'RESEARCH',
  
  CONSTRAINT phase_type_ck CHECK (phase_type_lu IN ('RESEARCH', 'SURVEY'))  
);

So, then I did this:

CREATE OR REPLACE FUNCTION setup_phase_bis()
RETURNS TRIGGER AS 
$$
BEGIN
  INSERT INTO phase (job_id_fk) VALUES
    (NEW.job_id);
    RETURN NEW;
END;
$$ LANGUAGE PLpgSQL;

and:

CREATE TRIGGER setup_phase AFTER INSERT ON job
FOR EACH ROW EXECUTE PROCEDURE setup_phase_bis();

So, now, when I create a job, it sets up a RESEARCH phase. You missed this in your code. What happens now when I INSERT a job is the following:

INSERT INTO job (job_type_lu) VALUES ('NT-TYP');

and:

SELECT * FROM job;
SELECT * FROM phase;
SELECT * FROM phase_step;

Result:

job_id  job_type_lu
1   NT-TYP
phase_id    job_id_fk   phase_type_lu
1   1   RESEARCH
phase_step_id   phase_id_fk step_type_lu

Note: no phase steps because I haven't done that yet.

So then I copied your code - with the notable exception (pardon the pun...) of the lines (see fiddle):

  IF NEW.job_id_fk IS NULL THEN
    RAISE EXCEPTION 'You must supply a job_id for this phase...';
  END IF;

Not having this provokes the error message you are receiving...

So, now, when a job is inserted, the job cascades to (RESEARCH) phase and the phase cascades to the appropriate steps - as follows:

INSERT INTO job (job_type_lu) VALUES ('XXXXX');

And:

SELECT * FROM job;
SELECT * FROM phase;
SELECT * FROM phase_step;

Result:

job_id  job_type_lu
1   NT-TYP
2   XXXXX
phase_id    job_id_fk   phase_type_lu
1   1   RESEARCH
2   2   RESEARCH
phase_step_id   phase_id_fk step_type_lu
1   2   RESEARCH: JOB_INPUT
2   2   RESEARCH: WATER
3   2   RESEARCH: OTHER
4   2   RESEARCH: REVISION_ATEAM
5   2   RESEARCH: REVISION_CLIENT

So, we have our cascade.

Now (again, making assumptions about your workflow) - if the client is happy with the research phase, they'll want to progress to the SURVEY phase and so you will wish to enter that into the system but not as a new job (hope I'm OK so far... :-) )

So, now we do this:

INSERT INTO phase (phase_type_lu) VALUES ('SURVEY');

BUT, there's no associated job_id, so, thanks to the EXCEPTION, this fails as follows:

ERROR:  You must supply a job_id for this phase...
CONTEXT:  PL/pgSQL function setup_phase_steps() line 5 at RAISE

But, if we do this:

INSERT INTO phase (job_id_fk, phase_type_lu) VALUES (2, 'SURVEY');

we get:

SELECT * FROM job;
SELECT * FROM phase;
SELECT * FROM phase_step;

Result:

job_id  job_type_lu
1   NT-TYP
2   XXXXX
phase_id    job_id_fk   phase_type_lu
1   1   RESEARCH
2   2   RESEARCH
4   2   SURVEY
phase_step_id   phase_id_fk step_type_lu
1   2   RESEARCH: JOB_INPUT
2   2   RESEARCH: WATER
3   2   RESEARCH: OTHER
4   2   RESEARCH: REVISION_ATEAM
5   2   RESEARCH: REVISION_CLIENT
6   4   SURVEY: SCHEDULING
7   4   SURVEY: FIELDING
8   4   SURVEY: REVISION_ATEAM
9   4   SURVEY: REVISION_CLIENT

Our SURVEY phase steps have been propagated with the correct FOREIGN KEYs. So, all is rosy in the garden? I ran a final test at the bottom of the fiddle (not shown for brevity), and all appears OK!

For future reference, it would be great if you could provide appropriate fiddles with your questions and also if you could give us the version of PostgreSQL that you are running - help us to help you!

p.s. welcome to the forum!

Related Solutions

Postgresql – Inherit audit columns and triggers

From http://postgresql.1045698.n5.nabble.com/DELETE-and-UPDATE-triggers-on-parent-table-of-partioned-table-not-firing-td5683717.html:

The DELETE and UPDATE triggers need to be on the child tables. An operation on a child doesn't fire the triggers of the parent.

As such, the following code must also be executed:

CREATE TRIGGER ${name}_delete BEFORE DELETE ON $name
    FOR EACH ROW EXECUTE PROCEDURE audit_delete();

The audit_delete() function won't work as written in the question: setting the deleted date is not possible using OLD.deleted = current_timestamp. Instead, assuming that the table has a primary key field of id:

CREATE OR REPLACE FUNCTION audit_delete()
  RETURNS trigger AS
$BODY$
BEGIN
  EXECUTE
    'UPDATE ' || TG_TABLE_NAME || ' SET deleted = current_timestamp WHERE id = ' || OLD.id;
  RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;

Mysql – Synchronise data from one table to another & vice-versa using triggers

I concur with @NathanJolly, this is a potential data nightmare and seems ill-advised... but there is a way to implement this that is, at least conceptually, fairly straightforward.

You need not worry about the possibility of recursion in triggers, because this won't happen... MySQL doesn't support it.

I built this fiddle that creates tables t1 and t2, each of which has an AFTER INSERT trigger that tries to insert the record into the other table... then tries to do an insert. Theoretically, there's a potential for recursion, but MySQL prevents this:

ERROR 1442 (HY000): Can't update table 't1' in stored function/trigger because it is already
                    used by statement which invoked this stored function/trigger.

So it won't run in a loop, but it still throws an exception. Technically, it's not trigger recursion causing the exception, because we haven't actually gotten that far... it's the fact that the trigger in t2 tries to modify table t1, which was involved in the currently-executing statement.

Then again, technically, it's not just the recursion of the triggers we need to avoid -- we have to stop one step before that -- if the client modifies t1, the t1 trigger modifies t2, then t2's trigger needs to not even try to do anything to t1 (including, but not limited to, firing the trigger again on t1).

Implementing this is actually fairly straightforward... in each trigger, we toggle the value of a session to tell the other trigger not to run.

CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW BEGIN
  IF @__disable_trigger_t1t2 = 1 THEN
    SET @__disable_trigger_t1t2 = NULL;
  ELSE
    SET @__disable_trigger_t1t2 = 1;
    -- trigger logic goes in here
    INSERT INTO t2 (id,things) VALUES (NEW.id,NEW.stuff);
  END IF;
END //

CREATE TRIGGER t2_ai AFTER INSERT ON t2 FOR EACH ROW BEGIN
  IF @__disable_trigger_t1t2 = 1 THEN
    SET @__disable_trigger_t1t2 = NULL;
  ELSE
    SET @__disable_trigger_t1t2 = 1;
    -- trigger logic goes in here
    INSERT INTO t1 (id,stuff) VALUES (NEW.id,NEW.things);
  END IF;
END //

A test case for this logic working can be seen in this fiddle.

Essentially, when a trigger fires, it checks the value of the @__disable_trigger_t1t2 variable (this variable name is, of course, arbitrary). If '1', it sets it back to NULL (the default for session variables) and does no further processing, because the '1' means this row is being manipulated by the trigger on the other table. If already NULL, then it sets the value to 1 and executes the trigger logic -- which will update the other table, whose trigger will see the '1', will not execute its own logic, and will reset the variable back to NULL, so that the next execution of either trigger -- whether for the next row in the same statement, or for a subsequent statement -- will see the NULL and execute normally.

In MySQL, triggers are only supported FOR EACH ROW, meaning that we can define a MySQL trigger as "a stored program that is executed before or after each row is inserted, updated, or deleted"... which means that no matter how many rows your query affects, the trigger will run once for each row. Actions against other tables will cause the triggers on those tables to fire, and the execution of the trigger on t1 for the first row will not complete until all of the triggers it caused to fire have also completed... and this is why we can use this variable like this -- only one row is actually being handled at a time, so we set and clear this variable for each row, always leaving it NULL after each statement terminates.

A significant caveat is that this approach fails if you need to trigger on cascading deletes or updates related to foreign keys, because:

"Currently, cascaded foreign key actions do not activate triggers."

^{-- http://dev.mysql.com/doc/refman/5.6/en/create-trigger.html}

So, while I have misgivings about the long-term viability of maintaining database synchronization with this approach because of the number of things that could go wrong with two applications manipulating each other's data or logical errors in the trigger definitions... it seems technically possible.

You also mentioned:

views cannot have triggers in MySQL

No, but updating an updatable view will fire the triggers on the underlying base table as if the table had been directly manipulated.

Best Answer

Related Solutions

Postgresql – Inherit audit columns and triggers

Mysql – Synchronise data from one table to another & vice-versa using triggers

Related Question