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