5. 触发器实战（教学实例库）

第一部分：入门级实操：INSERT类型触发器

例 1：插入学生时，自动补全「班级」为「默认班」

需求：插入学生时如果没填班级，触发器自动填「默认班」

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER tri_stu_insert_fill_class
BEFORE INSERT ON student FOR EACH ROW
BEGIN
    -- 没填班级就自动赋值为「默认班」
    IF NEW.class IS NULL THEN
        SET NEW.class = '默认班';
    END IF;
END //
DELIMITER ;

查看测试语句（可复制）

sql

-- 插入学生时不填班级
INSERT INTO student(Sno, Sname, Ssex) VALUES ('999', '新手测试', '男');
-- 查看结果：class字段已自动填充为「默认班」
SELECT Sno, Sname, class FROM student WHERE Sno='999';

例 2：插入成绩时，强制分数不能小于 0

需求：插入成绩时如果填了负数，自动改成 0

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER tri_sc_insert_check_score
BEFORE INSERT ON sc FOR EACH ROW
BEGIN
    -- 分数<0就强制设为0
    IF NEW.score < 0 THEN
        SET NEW.score = 0;
    END IF;
END //
DELIMITER ;

查看测试语句（可复制）

sql

-- 插入负数分数（会被自动改成0）
INSERT INTO sc(Sno, Cno, score) VALUES ('999', '01', -10);
-- 查看结果：score=0
SELECT Sno, Cno, score FROM sc WHERE Sno='999' AND Cno='01';

例 3：插入教师时，自动填充「入职时间」为当前日期

需求：插入教师时不填入职日期，触发器自动填今天（新手易理解的时间赋值）

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER tri_teacher_insert_fill_date
BEFORE INSERT ON teacher FOR EACH ROW
BEGIN
    -- 入职日期为空就填当前时间
    IF NEW.entrydate IS NULL THEN
        SET NEW.entrydate = CURDATE();
    END IF;
END //
DELIMITER ;

查看测试语句（可复制）

sql

-- 插入教师时不填入职日期
INSERT INTO teacher(Tno, Tname) VALUES ('99', '新手老师');
-- 查看结果：entrydate=今天的日期
SELECT Tno, Tname, entrydate FROM teacher WHERE Tno='99';

例 4：插入课程时，课程号自动加前缀「KC-」

需求：插入课程时，自动给课程号加固定前缀（极简字符串拼接）

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER tri_course_insert_add_prefix
BEFORE INSERT ON course FOR EACH ROW
BEGIN
    -- 课程号拼接前缀「KC-」
    SET NEW.Cno = CONCAT('KC-', NEW.Cno);
END //
DELIMITER ;

查看测试语句（可复制）

sql

-- 插入课程号为09的课程
INSERT INTO course(Cno, Cname) VALUES ('09', '新手课程');
-- 查看结果：Cno=KC-09
SELECT Cno, Cname FROM course WHERE Cname='新手课程';

例 5：学生性别合法性前置校验

需求：性别只能是「男」或「女」

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER trigger_student_before_insert_sex_check
BEFORE INSERT ON student FOR EACH ROW
BEGIN
    IF NEW.Ssex NOT IN ('男','女') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '性别只能填写「男」或「女」，插入失败';
    END IF;
END //
DELIMITER ;

查看测试语句（可复制）

sql

-- 合法插入
INSERT INTO student(Sno,Sname,Ssex) VALUES ('021','测试同学','男');
-- 非法插入（会报错）
INSERT INTO student(Sno,Sname,Ssex) VALUES ('022','测试同学2','保密');

例 6：插入学生时自动计算年龄

需求：仅填出生日期，自动计算并填充 sage

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER trigger_student_before_insert_auto_age
BEFORE INSERT ON student FOR EACH ROW
BEGIN
    SET NEW.sage = TIMESTAMPDIFF(YEAR, NEW.Sbirth, CURDATE());
END //
DELIMITER ;

查看测试语句（可复制）

sql

INSERT INTO student(Sno,Sname,Sbirth,Ssex) VALUES ('023','张三','2010-05-20','男');
SELECT Sno,Sname,Sbirth,sage FROM student WHERE Sno='023';

例 7：新增学生后自动分配必修课程成绩

需求：自动插入语文(01)、数学(02)、英语(03)，默认0分

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER trigger_student_after_insert_auto_course
AFTER INSERT ON student FOR EACH ROW
BEGIN
    INSERT INTO sc(Sno,Cno,score) VALUES
    (NEW.Sno,'01',0.0),
    (NEW.Sno,'02',0.0),
    (NEW.Sno,'03',0.0);
END //
DELIMITER ;

查看测试语句（可复制）

sql

INSERT INTO student(Sno,Sname,Sbirth,Ssex) VALUES ('024','李四','2009-08-15','女');
SELECT * FROM sc WHERE Sno='024';

例 8：教师入职日期合法性校验

需求：入职日期不能晚于当前日期

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER trigger_teacher_before_insert_entrydate_check
BEFORE INSERT ON teacher FOR EACH ROW
BEGIN
    IF NEW.entrydate > CURDATE() THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '入职日期不能晚于当前日期，插入失败';
    END IF;
END //
DELIMITER ;

查看测试语句（可复制）

sql

-- 合法
INSERT INTO teacher(Tno,Tname,entrydate) VALUES ('05','孙老师','2025-01-01');
-- 非法（报错）
INSERT INTO teacher(Tno,Tname,entrydate) VALUES ('06','周老师','2026-01-01');

第二部分：进阶级实操：UPDATE类型触发器

例 9：不允许修改学生姓名，自动提示「"学号：XXX姓名从XXX改成XXX"

需求：修改学生姓名后，触发器抛出提示 "学号：XXX姓名从XXX改成XXX"

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER tri_stu_update_tip_name
AFTER UPDATE ON student FOR EACH ROW
BEGIN
    -- 1.定义一个变量msg
    # DECLARE msg VARCHAR(100);
    -- 2. 先拼接字符串到变量中
    # SET msg = CONCAT('学号', OLD.Sno, '姓名从', OLD.Sname, '改成', NEW.Sname);
    -- 只有姓名改了才提示
    IF OLD.Sname != NEW.Sname THEN
         SELECT CONCAT('学号', OLD.Sno, '姓名从', OLD.Sname, '改成', NEW.Sname) AS提示信息;
    END IF;
END //
DELIMITER ;

查看测试语句（可复制）

sql

-- 修改999号学生的姓名（执行会报错提示，验证触发器生效）
UPDATE student SET Sname='测试改名' WHERE Sno='999';

例 10：修改成绩时，分数超过 100 自动改成 100

需求：修改成绩时如果填了 100 以上的数，自动修正为 100

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER tri_sc_update_fix_score
BEFORE UPDATE ON sc FOR EACH ROW
BEGIN
    -- 分数>100就强制设为100
    IF NEW.score > 100 THEN
        SET NEW.score = 100;
    END IF;
END //
DELIMITER ;

查看测试语句（可复制）

sql

-- 把999号学生的成绩改成150（会被自动改成100）
UPDATE sc SET score=150 WHERE Sno='999' AND Cno='01';
-- 查看结果：score=100
SELECT Sno, Cno, score FROM sc WHERE Sno='999' AND Cno='01';

第三部分：进阶级实操：DELETE类型触发器

例 11：禁止删除任何课程（固定拦截，无复杂判断）

需求：只要删除课程表数据，就直接拦截（新手易理解的「禁止删除」）

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER tri_course_delete_forbid
BEFORE DELETE ON course FOR EACH ROW
BEGIN
    -- 直接抛出错误，禁止删除
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '禁止删除课程表数据！';
END //
DELIMITER ;
-- 【测试语句】
DELETE FROM course WHERE Cno='01';

例 12：禁止删除有成绩记录的学生

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER trigger_student_before_delete_check
BEFORE DELETE ON student FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM sc WHERE Sno=OLD.Sno) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该学生存在成绩记录，禁止删除';
    END IF;
END //
DELIMITER ;

查看测试语句（可复制）

sql

-- 删除有成绩的（报错）
DELETE FROM student WHERE Sno='001';
-- 删除无成绩的
DELETE FROM student WHERE Sno='008';

例 13：删除学生后自动级联删除成绩记录

【注意】：请先删除上面的实例1触发器，避免冲突 DROP TRIGGER IF EXISTS trigger_student_before_delete_check;

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER trigger_student_after_delete_cascade_sc
AFTER DELETE ON student FOR EACH ROW
BEGIN
    DELETE FROM sc WHERE Sno=OLD.Sno;
END //
DELIMITER ;

查看测试语句（可复制）

sql

DELETE FROM student WHERE Sno='004';
SELECT * FROM sc WHERE Sno='004';

例 14：禁止删除有授课记录的教师

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER trigger_teacher_before_delete_check
BEFORE DELETE ON teacher FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM course WHERE Tno=OLD.Tno) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该教师有授课记录，禁止删除';
    END IF;
END //
DELIMITER ;

查看测试语句（可复制）

sql

-- 删除有授课的（报错）
DELETE FROM teacher WHERE Tno='01';
-- 删除无授课的
DELETE FROM teacher WHERE Tno='04';

例 15：删除课程自动记录归档日志

第一步：创建日志表

查看参考答案（可复制）

sql

CREATE TABLE IF NOT EXISTS course_delete_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    Cno VARCHAR(10) NOT NULL COMMENT '课程编号',
    Cname VARCHAR(10) NOT NULL COMMENT '课程名称',
    Tno VARCHAR(10) COMMENT '授课教师编号',
    delete_time DATETIME NOT NULL DEFAULT NOW() COMMENT '删除时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

第二步：创建触发器

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER trigger_course_after_delete_log
AFTER DELETE ON course FOR EACH ROW
BEGIN
    INSERT INTO course_delete_log(Cno,Cname,Tno)
    VALUES (OLD.Cno, OLD.Cname, OLD.Tno);
END //
DELIMITER ;

查看测试语句（可复制）

sql

-- 先清理关联数据
DELETE FROM sc WHERE Cno='04';
-- 删除课程
DELETE FROM course WHERE Cno='04';
-- 查看日志
SELECT * FROM course_delete_log;

第四部分：高阶实操：触发器复杂业务场景

例 16：教师排课数量上限校验，

需求：每名老师最多排2门课，多排提示"老师最多只能排2门课"

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER trigger_sc_before_insert_course_limit
BEFORE INSERT ON course FOR EACH ROW
BEGIN
    DECLARE teacher_count INT DEFAULT 0;
    SELECT COUNT(*) INTO teacher_count FROM course WHERE tno=NEW.tno;
    IF teacher_count >=2 THEN
        SIGNAL SQLSTATE '10001' SET MESSAGE_TEXT = '老师最多只能排2门课';
    END IF;
END //
DELIMITER ;

查看测试语句（可复制）

sql

-- 教师01已选2门，插入报错
INSERT INTO course(Sno,Cname,tno) VALUES ('05','Python','01');
-- 教师02未选满，插入成功
INSERT INTO sc(Sno,Cno,score) VALUES ('05','Python','02');

例 17：成绩插入时自动计算等级

第一步：给sc表新增等级字段

查看参考答案（可复制）

sql

ALTER TABLE sc ADD COLUMN grade_level VARCHAR(10) COMMENT '成绩等级' AFTER score;

第二步：创建INSERT触发器

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER trigger_sc_before_insert_auto_grade
BEFORE INSERT ON sc FOR EACH ROW
BEGIN
    set new.grade_level=(SELECT grade_level
    FROM grades
    WHERE NEW.score BETWEEN lowest_sc AND highest_sc);
END //
DELIMITER ;

拓展：成绩更新时，自动计算等级

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER trigger_sc_before_update_auto_grade
BEFORE UPDATE ON sc FOR EACH ROW
BEGIN
    IF OLD.score != NEW.score THEN
        set new.grade_level=(SELECT grade_level
    FROM grades
    WHERE NEW.score BETWEEN lowest_sc AND highest_sc);
    END IF;
END //
DELIMITER ;

查看测试语句（可复制）

sql

INSERT INTO sc(Sno,Cno,score) VALUES ('009','02',85.0);
SELECT * FROM sc WHERE Sno='009';
UPDATE sc SET score=95.0 WHERE Sno='009' AND Cno='02';
SELECT * FROM sc WHERE Sno='009' AND Cno='02';

例 18：教师编号修改自动级联更新课程表

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER trigger_teacher_after_update_tno_cascade
AFTER UPDATE ON teacher FOR EACH ROW
BEGIN
    IF OLD.Tno != NEW.Tno THEN
        UPDATE course SET Tno=NEW.Tno WHERE Tno=OLD.Tno;
    END IF;
END //
DELIMITER ;

查看测试语句（可复制）

sql

-- 先恢复数据（如果之前删了）
INSERT INTO teacher(Tno,Tname,entrydate) VALUES ('01','张老师','2017-08-30');
-- 修改编号
UPDATE teacher SET Tno='10' WHERE Tno='01';
-- 查看课程表
SELECT * FROM course WHERE Cname='数学';

例 19：课程选课人数上限强校验

需求：每门课最多10人选

查看参考答案（可复制）

sql

DELIMITER //
CREATE TRIGGER trigger_sc_before_insert_course_stu_limit
BEFORE INSERT ON sc FOR EACH ROW
BEGIN
    DECLARE stu_count INT DEFAULT 0;
    SELECT COUNT(*) INTO stu_count FROM sc WHERE Cno=NEW.Cno;
    IF stu_count >=10 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该课程选课人数已达上限10人，选课失败';
    END IF;
END //
DELIMITER ;

查看测试语句（可复制）

sql

-- 尝试给语文(01)插入第11人（会报错）
INSERT INTO sc(Sno,Cno,score) VALUES ('012','01',85.0);

←

上一节 4. 触发器：自动执行的特殊程序

下一节 6. 本章小结

→