时间戳

首页 > 解决方案 > 通过触发器更新另一个表，其中新值是 SELECT 查询的结果

sql - 通过触发器更新另一个表，其中新值是 SELECT 查询的结果

问题描述

我有这些表：

Has_skills 是前两个之间的多对多表，通过这些 FK：user_id (users.id) 和 Skill_id (skills.id)。

我想要做的是在将新行插入has_skills时更新技能内的计数列。我想通过表has_skills上的更新触发器来做到这一点。我将通过选择查询获得 count 的新值：

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

上面的 ID 是硬编码的 (1)，但它可以工作。

我还单独测试了这段代码，它可以工作（虽然也是硬编码的）：

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;

好的，所以这可能是问题所在。下面是触发器函数以及触发器本身。

功能：

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;

扳机：

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

我成功创建了函数和触发器，但是当我将新数据插入has_skills时，它不会更改Skills中的计数列。可能是什么问题呢？

标签： sqlpostgresqltriggerspostgresql-triggers

解决方案

触发器功能中根本不需要选择。技能表的键直接在 new.skill_id 中可用，所以直接使用它：

-- 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();

推荐阅读