如何在MySQL中编写触发器替代指定ASSERTION断言实现同等校验功能？

实现思路 #

首先修正原第二段断言的笔误：原断言中子查询SELECT *为语法错误，实际应为SELECT COUNT(*)，对应业务规则为：

1. 每篇论文的评审记录数必须恰好为3条
2. 每个PC委员的评审任务数最多为5条

由于MySQL不支持标准SQL的ASSERTION断言，我们可以通过在review表上创建触发器实现完全等价的校验逻辑，所有涉及评审记录增删改的操作都会触发约束校验，不满足条件时直接阻止操作执行。

具体触发器实现 #

1. 插入操作触发器（新增评审时校验）

DELIMITER //
CREATE TRIGGER trg_review_before_insert
BEFORE INSERT ON review
FOR EACH ROW
BEGIN
    -- 校验当前PC委员已分配评审数是否超过5
    DECLARE pc_review_count INT;
    SELECT COUNT(*) INTO pc_review_count FROM review WHERE email = NEW.email;
    IF pc_review_count >= 5 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '约束违反：单个PC委员最多只能承担5篇论文的评审任务';
    END IF;

    -- 校验当前论文已分配评审数是否超过3
    DECLARE paper_review_count INT;
    SELECT COUNT(*) INTO paper_review_count FROM review WHERE paperid = NEW.paperid;
    IF paper_review_count >= 3 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '约束违反：单篇论文只能分配恰好3位评审';
    END IF;
END //
DELIMITER ;

2. 更新操作触发器（修改评审关联的论文/PC委员时校验）

DELIMITER //
CREATE TRIGGER trg_review_before_update
BEFORE UPDATE ON review
FOR EACH ROW
BEGIN
    -- 如果修改了关联的PC委员
    IF NEW.email <> OLD.email THEN
        DECLARE new_pc_count INT;
        SELECT COUNT(*) INTO new_pc_count FROM review WHERE email = NEW.email;
        IF new_pc_count >= 5 THEN
            SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = '约束违反：单个PC委员最多只能承担5篇论文的评审任务';
        END IF;
    END IF;

    -- 如果修改了关联的论文
    IF NEW.paperid <> OLD.paperid THEN
        DECLARE old_paper_count INT;
        DECLARE new_paper_count INT;
        -- 原论文移除当前评审后不能少于3条
        SELECT COUNT(*) INTO old_paper_count FROM review WHERE paperid = OLD.paperid;
        IF old_paper_count - 1 < 3 THEN
            SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = '约束违反：单篇论文只能分配恰好3位评审，移除后数量不足';
        END IF;
        -- 新论文添加当前评审后不能超过3条
        SELECT COUNT(*) INTO new_paper_count FROM review WHERE paperid = NEW.paperid;
        IF new_paper_count + 1 > 3 THEN
            SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = '约束违反：单篇论文只能分配恰好3位评审，新增后数量超限';
        END IF;
    END IF;
END //
DELIMITER ;

3. 删除操作触发器（删除评审时校验）

DELIMITER //
CREATE TRIGGER trg_review_before_delete
BEFORE DELETE ON review
FOR EACH ROW
BEGIN
    -- 校验删除后对应论文的评审数不会少于3
    DECLARE paper_review_count INT;
    SELECT COUNT(*) INTO paper_review_count FROM review WHERE paperid = OLD.paperid;
    IF paper_review_count - 1 < 3 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '约束违反：单篇论文只能分配恰好3位评审，删除后数量不足';
    END IF;
END //
DELIMITER ;

业务适配说明 #

如果你的业务场景允许新创建的论文暂时未分配评审（评审数为0），可以将对应校验逻辑中的=3调整为IN (0,3)，仅限制评审数不能为1、2、大于3的情况即可，无需修改其他逻辑。

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