如何在PostgreSQL中创建触发器自动更新投票选项总票数votes_value

需求说明 #

对vote表执行INSERT/UPDATE/DELETE操作时，自动同步更新voting_option表的votes_value统计字段，规则如下：

• INSERT操作：对应投票选项的votes_value += 新插入投票的value
• DELETE操作：对应投票选项的votes_value -= 被删除投票的value
• UPDATE操作：关联投票选项不变时，对应选项votes_value = 原值 - 旧投票value + 新投票value；关联投票选项变更时，原选项减去旧value，新选项加上新value

步骤1：创建触发器处理函数 #

CREATE OR REPLACE FUNCTION update_voting_option_votes()
RETURNS TRIGGER AS $$
BEGIN
    -- 处理新增投票
    IF TG_OP = 'INSERT' THEN
        UPDATE voting_option
        SET votes_value = votes_value + NEW.value
        WHERE id = NEW.voting_option_id;
        RETURN NEW;
    -- 处理删除投票
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE voting_option
        SET votes_value = votes_value - OLD.value
        WHERE id = OLD.voting_option_id;
        RETURN OLD;
    -- 处理更新投票
    ELSIF TG_OP = 'UPDATE' THEN
        -- 投票关联选项变更场景，同时更新新旧两个选项的统计值
        IF OLD.voting_option_id != NEW.voting_option_id THEN
            UPDATE voting_option
            SET votes_value = votes_value - OLD.value
            WHERE id = OLD.voting_option_id;
            UPDATE voting_option
            SET votes_value = votes_value + NEW.value
            WHERE id = NEW.voting_option_id;
        ELSE
            -- 关联选项不变，仅更新当前选项统计值
            UPDATE voting_option
            SET votes_value = votes_value - OLD.value + NEW.value
            WHERE id = NEW.voting_option_id;
        END IF;
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

步骤2：绑定触发器到vote表 #

CREATE TRIGGER trigger_vote_change
AFTER INSERT OR UPDATE OR DELETE ON vote
FOR EACH ROW
EXECUTE FUNCTION update_voting_option_votes();

内容的提问来源于stack exchange，提问作者DmitryScaletta