如何在新增lesson_vocabulary行时自动更新lessons表的vocab_count列

当然可行！在PostgreSQL环境下，我们可以利用触发器（Trigger）搭配触发器函数来实现这个自动更新的需求，完美契合你多对多关联的表结构。下面是具体的实现步骤和代码：

1. 创建触发器函数 #

首先我们需要编写一个PL/pgSQL函数，它的作用是在lesson_vocabulary表有新行插入时，自动计算对应课程的词汇关联数量，并更新lessons表的vocab_count字段：

CREATE OR REPLACE FUNCTION update_lesson_vocab_count()
RETURNS TRIGGER AS $$
BEGIN
    -- 统计当前课程关联的词汇总数，并更新到lessons表
    UPDATE lessons
    SET vocab_count = (
        SELECT COUNT(*)
        FROM lesson_vocabulary
        WHERE lesson_id = NEW.lesson_id
    )
    WHERE id = NEW.lesson_id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建触发器 #

接下来把这个函数绑定到lesson_vocabulary表的INSERT事件上，确保每次插入新的关联记录时都会触发更新操作：

CREATE TRIGGER trigger_update_vocab_count
AFTER INSERT ON lesson_vocabulary
FOR EACH ROW
EXECUTE FUNCTION update_lesson_vocab_count();

3. 可选：添加唯一约束（避免重复关联） #

为了防止同一个课程和词汇被重复关联（导致vocab_count被错误累加），建议给lesson_vocabulary表添加联合唯一约束：

ALTER TABLE lesson_vocabulary
ADD CONSTRAINT unique_lesson_vocab_pair UNIQUE (lesson_id, vocabulary_id);

工作流程说明 #

当你执行类似下面的插入语句时：

INSERT INTO lesson_vocabulary (lesson_id, vocabulary_id) VALUES (1, 4);

触发器会自动触发update_lesson_vocab_count函数，函数会统计lesson_id=1的所有关联词汇数，然后将lessons表中id=1的vocab_count更新为最新的数值。

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