1

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?

2
  • 1
    you do understand that the loop will run for every updated row over and over again
    – nbk
    Apr 23 at 17:27
  • But the ab_is_active is not changed, there are no changes in the table
    – Rans
    Apr 23 at 17:38
0

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

1
  • I found my mistake. thank you!
    – Rans
    Apr 23 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.