I'm just trying to create a trigger that performs an update in a junction table.
In principle, I have three tables with Many-to-Many relationship.
Table a: a_id, a_is_active Table b: b_id, b_is_active Table ab: ab_id, a_id, b_id, ab_is_active
What I want is that the
ab_is_active needs to be actualized when
b_is_active is updated, so
ab_is_active should be like this (pseudo code):
ab_is_active = a_is_acvite && b_is_acvite where a_id = a.a_id and b_id = b.b_id
and that must be carried out after each
is_active update in Table a or in Table b:
I have tried the following, but it does not work (nothing is updated in ab):
CREATE OR REPLACE FUNCTION is_active_changes() returns trigger language plpgsql AS $BODY$ DECLARE b_is_active boolean; r b%ROWTYPE; BEGIN for r in select * into b_is_active from ab join b _b on ab.b_id= _b.b_id join a _a on ab.a_id= _a.a_id LOOP update ab set ab_is_active = r.a_is_active and r.b_is_active where b_id = r.b_id and a_id = r.a_id; END LOOP; END; $BODY$; create trigger is_active_trigger after update on a for each row execute procedure is_active_changes();
Can someone tell me what I do wrong or suggest a better solution?