Update another table through a trigger where new value is the result of a SELECT query

Question

I have these tables:

Users
Skills (name - string, count - integer)
Has_skills (skill_id - skills.id, user_id users.id)

Has_skills is a many to many table between the first two through these FK: user_id (users.id) and skill_id (skills.id).

What I want to do is update the count column inside skills when a new row is inserted into has_skills. I want to do this through an update trigger on table has_skills. The new value for count I will get through a select query:

SELECT COUNT(*) AS cnt FROM skills 
JOIN has_skills hs ON skills.id = hs.skill_id 
WHERE hs.skill_id = 1;

The ID above is hardcoded (1), but it works.

I also tested this code in isolation, and it works (although hardcoded, as well):

UPDATE skills
SET count = subquery.cnt
FROM (
    SELECT COUNT(*) AS cnt FROM skills 
    JOIN has_skills hs ON skills.id = hs.skill_id 
    WHERE hs.skill_id = 1
) AS subquery
WHERE skills.id = 1;
RETURN NEW;

Alright, so here's probably where the problem is. Below is the trigger function and also the trigger itself.

Function:

CREATE OR REPLACE FUNCTION update_skill_count() RETURNS trigger AS
$func$
BEGIN
UPDATE skills
SET count = subquery.cnt
FROM (
    SELECT COUNT(*) AS cnt FROM skills 
    JOIN has_skills hs ON skills.id = hs.skill_id 
    WHERE hs.skill_id = NEW.skill_id
) AS subquery
WHERE skills.id = NEW.skill_id;
RETURN NEW;
END;
$func$ LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER on_has_skills_insert
AFTER INSERT ON has_skills
FOR EACH ROW
EXECUTE PROCEDURE update_skill_count();

I successfully create the function and trigger, but when I insert new data into has_skills, it doesn't change the count column inside skills. What could be the problem?

I'm facing the same exact issue. Did you find an answer? – nezu Jan 02 '23 at 16:51 — nezu, Jan 02 '23 at 16:51

score 1 · Answer 1 · answered Jun 17 '19 at 02:26

1

There's no need for a select in the trigger function at all. The key for the skill table is directly available in new.skill_id so just use it directly:

-- trigger function and trigger
create or replace function update_skill_count() 
  returns trigger  
as $func$
begin
    update skills sk
       set count = count+1
     where sk.skill_id = new.skill_id;
    return new;
end;
$func$ language plpgsql;

create trigger on_has_skills_insert
       after insert on has_skills
       for each row
       execute procedure update_skill_count();

answered Jun 17 '19 at 02:26

Belayer

13,578
2
11
22

I use SELECT because I want to count the number of all the skills inside has_skills table and use the result of that query as a new value for the count. I initially wanted to update it like you do, by incrementing, but I got told it's tricky to work with increments in this case. Thanks for the answer! – krcky Jun 17 '19 at 05:07
The only tricky part would be the need to also handle Updates and Deletes, to keep the count correct. But you need to do that anyway. So SELECTing the count becomes just as tricky. What's tricky is doing it in a trigger. – Belayer Jun 17 '19 at 17:14

score 0 · Answer 2 · answered Jun 16 '19 at 02:02

I'm not much familiar with postgresql, but having understanding of Oracle and SQL Server, this looks to be a mutating trigger problem, which is: Trying to read from or write into the same table within a row level trigger on which the trigger is placed.

One of the ways to get rid of mutating trigger/table problem can be changing the row level trigger to a statement level trigger and changing the function accordingly. Here is a psudo code you can try out (not providing the exact tested code as I do not have Postgresql installed):

Function:

CREATE OR REPLACE FUNCTION update_skill_count() RETURNS trigger AS
$func$
BEGIN
UPDATE skills
SET count = subquery.cnt
FROM (
        SELECT hs.skill_id, COUNT(*) AS cnt
        FROM   new_table hs
        GROUP  BY hs.skill_id
     ) AS subquery
WHERE skills.id = subquery.skill_id;
RETURN NULL;
END;
$func$ LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER on_has_skills_insert
AFTER INSERT ON has_skills
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE PROCEDURE update_skill_count();

Hm, doesn't work. I don't get an error, but nothing happens when I insert new skills into **has_skills** table. Not sure what else could it be. — krcky, Jun 16 '19 at 06:47
there is no such thing as a "mutating trigger problem" in Postgres — , Jun 16 '19 at 08:50
As commented, pardon my knowledge in Postgresql. But here are a few links that could be relevant to this 'mutating tables' issue of Postgresql: 1> https://www.postgresql.org/message-id/000a01c2d9df$b4282760$fb01a8c0@ttcristian 2> https://www.postgresql.org/message-id/001301c2d9db%246a9f3880%24fb01a8c0%40ttcristian — san, Jun 16 '19 at 09:01

Update another table through a trigger where new value is the result of a SELECT query

2 Answers2