PostgreSQL触发器中如何比较不同表的两列并更新状态

需求：创建PostgreSQL触发器，当work_course_items表的current_attempt_count字段值大于或等于course_items表对应的attempt_count_limit字段值时，更新work_course_items的state字段。

对应的Java实体类定义：

@Table(name = "course_items")
public class CourseItem {
    @Id
    @Column(name = "course_item_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(name = "attempt_count_limit")
    private Integer attemptCountLimit;

@Table(name = "work_course_items")
public abstract class WorkCourseItem {
    @Id
    @Column(name = "work_course_item_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected Integer id;
    @Enumerated(EnumType.STRING)
    protected CourseItemState state;
    @ManyToOne
    @JoinColumn(name = "course_item_id", referencedColumnName = "course_item_id")
    protected CourseItem courseItem;
    @Column(name = "current_attempt_count")
    protected Integer currentAttemptCount;
}

原尝试的触发器代码：

IF ((SELECT attempt_count_limit
         FROM "ts".course_items
         WHERE course_items.course_item_id = new.course_item_id) <=
        (SELECT current_attempt_count
         FROM "ts".work_course_items
         WHERE work_course_items.work_course_item_id = new.work_course_id))
    THEN
      UPDATE "ts".work_course_items
      SET state = 'YEAP_IT_IS'
      WHERE work_course_item_id = new.work_course_item_id;

    END IF;
    RETURN new;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE TRIGGER quiz_attempt_count
   BEFORE UPDATE OF state
   ON "ts".work_course_items
   FOR EACH ROW
EXECUTE PROCEDURE change_work_course_state();

正确实现方案 #

原代码的问题点 #

1. 触发器时机错误：触发器监听的是state字段更新，但实际需要监听current_attempt_count字段的变化——只有尝试次数改变时才需要判断是否超限。
2. 子查询冗余且存在笔误：无需再次查询work_course_items获取current_attempt_count，触发器中的new变量已包含当前行更新后的该字段值；另外new.work_course_id是笔误，应为new.work_course_item_id。
3. 更新操作冗余：BEFORE触发器中直接修改new.state即可，无需额外执行UPDATE语句，效率更高。

正确的触发器函数和创建语句 #

CREATE OR REPLACE FUNCTION change_work_course_state()
RETURNS TRIGGER AS $$
DECLARE
    v_limit INTEGER;
BEGIN
    -- 从course_items获取对应记录的尝试次数上限
    SELECT attempt_count_limit INTO v_limit
    FROM "ts".course_items
    WHERE course_item_id = new.course_item_id;

    -- 判断当前尝试次数是否达到或超过上限
    IF new.current_attempt_count >= v_limit THEN
        new.state := 'YEAP_IT_IS'; -- 直接修改new的state字段，最终会应用到更新操作中
    END IF;

    RETURN new;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE TRIGGER quiz_attempt_count
BEFORE UPDATE OF current_attempt_count -- 仅监听尝试次数字段的更新
ON "ts".work_course_items
FOR EACH ROW
EXECUTE PROCEDURE change_work_course_state();

关键说明 #

• 用变量存储查询到的上限值，让逻辑更清晰易维护。
• 触发器时机改为BEFORE UPDATE OF current_attempt_count，避免不必要的触发。
• 直接修改new.state是BEFORE触发器的最优写法，PostgreSQL会自动将该修改同步到最终的数据库更新中。
• 如果需要处理course_items中无对应记录的场景，可以添加EXCEPTION WHEN NOT FOUND分支，设置默认上限或跳过状态更新。

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