Junction table Trigger after update in another table 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 a_is_active or b_is_active is updated, so ab_is_active should be like this (pseudo code):
ab_is_active = a_is_acvite &amp;&amp; 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?
Your problem is , that you made  some small errors
in the for loop you need to select those columns that fit the rowtype
I added as last a Version that is better as it only update one row and not all where b.id and a.id are present


CREATE Table a
(&quot;a_id&quot; INT, &quot;a_is_active&quot; Boolean)




CREATE Table b
(&quot;b_id&quot; INT, &quot;b_is_active&quot; Boolean)




CREATE Table ab
(&quot;ab_id&quot; INT , &quot;a_id&quot; int, &quot;b_id&quot; int, &quot;ab_is_active&quot; Boolean)




INSERT INTO a VALUES (1,TRUE)




INSERT INTO B VALUES (1,TRUE)




INSERT INTO ab VALUES (1,1,1,FALSE)




DO
$BODY$
DECLARE
    b_is_active boolean;
    r            ab%ROWTYPE;
BEGIN
    for r in select ab.ab_id, ab.b_id,ab.a_id,_a.a_is_active AND _b.b_is_active AS ab_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.ab_is_active 
                where b_id = r.b_id AND a_id = r.a_id;
        END LOOP;
END;
$BODY$;


✓




SELECT * FROM ab


ab_id | a_id | b_id | ab_is_active
----: | ---: | ---: | :-----------
    1 |    1 |    1 | t           




DO
$BODY$
DECLARE
    b_is_active boolean;
    r            ab%ROWTYPE;
BEGIN
    for r in select ab.ab_id, ab.b_id,ab.a_id,_a.a_is_active AND _b.b_is_active AS ab_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.ab_is_active 
                where ab_id = r.ab_id;
        END LOOP;
END;
$BODY$;


✓


db&lt;&gt;fiddle here