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

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 ;

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 ;

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 ;

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 ;

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 ;

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

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 ;