0

I use postgresql 11. I think my problem comes from a trigger that doesn't do an update so the next trigger doesn't launch.

I have a table projet with columns : projet_temps_doe, projet_temps_etudes, projet_temps_globale.

The goal is to update each columns depending on other columns values. The idea is : projet_temps_globale = projet_temps_doe + projet_temps_etudes.

I have a first trigger on projet_temps_doe which works perfectly:

create function temps_globale_doe() returns trigger
    language plpgsql
as
$$
begin
        new.projet_temps_globale_doe :=  new.projet_temps_doe_gc_bts + new.projet_temps_doe_gc_nra;
        return new;
    end;
$$;

CREATE TRIGGER temps_globale_doe
    BEFORE UPDATE OF projet_temps_doe_gc_bts, projet_temps_doe_gc_nra
    ON public.projet
    FOR EACH ROW
    EXECUTE PROCEDURE public.temps_globale_doe();

I have a similar trigger on projet_temps_etudes which works perfectly too.

Then the trigger I struggle with on projet_temps_globale :

create trigger maj_temps_globale_projet
    before update of projet_temps_doe, projet_temps_etudes on projet
    for each row
    execute procedure maj_temps_globale_projet();

create or replace function maj_temps_globale_projet()returns trigger
language plpgsql
as
    $$
    begin
        new.projet_temps_globale := new.projet_temps_doe + new.projet_temps_etudes;
        raise info 'TEST!!';
        return new;
    end;
    $$;

When projet_temps_doe and/or projet_temps_etudes are updated via triggers my last trigger doesn't launch. However when I manually change projet_temps_doe and/or projet_temps_etudes values the trigger maj_temps_globale_projet is fired.

I want to learn from this, so, if possible, explain to me what I'm doing wrong here, or if my approach is lacking insight.

1 Answer 1

1

The doc says

The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE command.

The column projet_temps_globale_doe is not part of the update command but is rather set in another trigger via new.projet_temps_globale_doe = ... so the trigger on this particular column is not called.

It would be easier to have only one trigger on the entire table that sets the 3 derived values.

Sign up to request clarification or add additional context in comments.

1 Comment

That's right, I tride with only one trigger for the 3 derived values and it works perfectly. Thank you

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.