Junction table Trigger after update in another table

All we need is an easy explanation of the problem, so here it is.

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 && 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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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
("a_id" INT, "a_is_active" Boolean)

CREATE Table b
("b_id" INT, "b_is_active" Boolean)

CREATE Table ab
("ab_id" INT , "a_id" int, "b_id" int, "ab_is_active" 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<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , ,

Leave a Reply