MySQL触发器实战：SC表成绩约束与学生状态维护详解

陆遥

发布时间：2026-10-16| 4人浏览过

原创

通过具体场景分析MySQL触发器的编写技巧，涵盖针对SC表的增删改查逻辑限制，实现自动化的数据校验与状态同步。

准备条件与环境

本文将基于经典的 school 数据库模型进行演示，涉及三张核心表：student（学生信息）、course（课程信息）和 sc（选课及成绩）。操作环境推荐使用 MySQL 客户端（如 Navicat 或 CMD命令行），确保具备创建和管理触发器的权限。

1. 成绩数值范围约束 (0-100)

第1步：处理插入操作 (`BEFORE INSERT`)

目的：在数据入库前拦截非法分数，防止脏数据产生。入口：SC表的INSERT事件。动作：检查新记录的分数字段 new.grade。

DELIMITER ;;
DROP TRIGGER IF EXISTS `tri_insert_grade`;
CREATE TRIGGER `tri_insert_grade` BEFORE INSERT ON `sc` FOR EACH ROW 
BEGIN
    IF new.grade > 100 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '成绩超出上限';
    END IF;
    IF new.grade < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '成绩低于下限';
    END IF;
END;;
DELIMITER ;

注意：原文中的 delete from sc 会导致全表删除，属严重错误。正确做法是使用 SIGNAL 抛出异常中断事务，或利用 SET new.grade=... 修正值。此处采用报错方式以符合“撤销插入”的需求。

第2步：处理修改操作 (`BEFORE UPDATE`)

目的：同步插入逻辑，防止手动修改时绕过校验。

DELIMITER ;;
DROP TRIGGER IF EXISTS `tri_update_grade`;
CREATE TRIGGER `tri_update_grade` BEFORE UPDATE ON `sc` FOR EACH ROW 
BEGIN
    IF new.grade > 100 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '修改后成绩超出上限';
    END IF;
    IF new.grade < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '修改后成绩低于下限';
    END IF;
END;;
DELIMITER ;

2. 选修总学分限制 (≤30)

第3步：插入时累计学分并校验

目的：在学生新增一门选课时，动态计算其当前已修学分，若超过阈值则拒绝。

DELIMITER ;;
DROP TRIGGER IF EXISTS `tri_credit_limit`;
CREATE TRIGGER `tri_credit_limit` BEFORE INSERT ON `sc` FOR EACH ROW 
BEGIN
    DECLARE current_credits DECIMAL(10, 2);
    -- 计算该学生目前已有的总学分（包含即将插入的这门新课）
    SELECT COALESCE(SUM(c.Ccredit), 0) INTO current_credits 
    FROM SC s 
    JOIN Course c ON s.Cno = c.Cno 
    WHERE s.Sno = NEW.Sno;
    
    -- 如果超过30分，拒绝操作
    IF current_credits > 30 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '总学分超过30，无法继续选修';
    END IF;
END;;
DELIMITER ;

说明：原代码逻辑混乱且直接删除数据是错误的。此处先聚合查询当前总和，再进行判断。需确保 Course 表存在 Ccredit 字段。

3. 保护不及格学生的选课记录

第4步：阻止修改不及格成绩

目的：锁定历史不及格记录，防止被误改或篡改。入口：SC表的UPDATE事件。

DELIMITER ;;
DROP TRIGGER IF EXISTS `tri_protect_fail_grade`;
CREATE TRIGGER `tri_protect_fail_grade` BEFORE UPDATE ON `sc` FOR EACH ROW 
BEGIN
    -- 获取旧记录的成绩
    SET @old_grade = OLD.grade;
    
    -- 如果原成绩不及格(<60)，禁止任何修改操作
    IF @old_grade < 60 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不及格记录不可修改';
    END IF;
END;;
DELIMITER ;

4. 新生入学年龄默认值处理

第5步：插入学生时填补空值

目的：当未提供年龄时，自动赋予当前全校学生的平均年龄，保持数据合理性。

DELIMITER ;;
DROP TRIGGER IF EXISTS `tri_default_age`;
CREATE TRIGGER `tri_default_age` BEFORE INSERT ON `student` FOR EACH ROW 
BEGIN
    DECLARE avg_age DECIMAL(10, 2);
    
    -- 仅当传入的年龄为空或未指定时执行
    IF NEW.Sage IS NULL THEN
        SELECT AVG(Sage) INTO avg_age FROM student;
        -- 若无任何学生记录，避免null赋值，可根据需求设置默认值如18
        IF avg_age IS NOT NULL THEN
            SET NEW.Sage = avg_age;
        ELSE
            SET NEW.Sage = 18; -- 假设默认年龄
        END IF;
    END IF;
END;;
DELIMITER ;

5. 自动维护平均成绩与等级评定

第6步：增加必要列

首先在 student 表中添加存放平均分和等级的列（若尚不存在）：

ALTER TABLE student ADD COLUMN Savg DECIMAL(5,2);
ALTER TABLE student ADD COLUMN Scomment VARCHAR(10);

第7步：创建后置触发器 (`AFTER INSERT`)

目的：每次学生成绩录入成功后，立即重新计算该生的平均分，并依据规则更新等级标签。规则：≥85为“优”，60-85为“良”，<60为“差”。

DELIMITER ;;
DROP TRIGGER IF EXISTS `tri_auto_calculate_avg`;
CREATE TRIGGER `tri_auto_calculate_avg` AFTER INSERT ON `sc` FOR EACH ROW 
BEGIN
    DECLARE avg_val DECIMAL(5,2);
    DECLARE level_str VARCHAR(10);
    
    -- 重新计算该学生的平均分
    SELECT AVG(Grade) INTO avg_val 
    FROM sc 
    WHERE Sno = NEW.Sno;
    
    -- 判定等级
    IF avg_val >= 85 THEN
        SET level_str = '优';
    ELSEIF avg_val >= 60 THEN
        SET level_str = '良';
    ELSE
        SET level_str = '差';
    END IF;
    
    -- 更新学生表中的综合信息
    UPDATE student 
    SET Savg = avg_val, Scomment = level_str 
    WHERE Sno = NEW.Sno;
END;;
DELIMITER ;

修正说明：原文提供的代码试图在 BEFORE UPDATE student 上触发并操作 SC 表，逻辑颠倒且语法有误。正确的做法是监听 SC 表的变动（INSERT），然后反向更新 STUDENT 表。