✏️ 触发器练习 2
第 6 章 · 索引、视图和触发器 · 共 6 题 · 学生成绩库
🛠 建表与测试数据（练习前先执行）
sql
-- 准备数据
drop database if exists studentgradeinfo;
create database studentgradeinfo
default character set utf8
default collate utf8_general_ci;
use studentgradeinfo;
create table Student(
StudentId varchar(20) primary key,
StudentName varchar(20) unique not null,
StudentSex varchar(2) default '男',
StudentClass varchar(20),
StudentDepartment varchar(20),
StudentNation varchar(20),
StudentPlitics varchar(20),
StudentBirthday datetime
);
create table Course(
CourseID varchar(20) not null primary key,
CourseName varchar(20) not null,
Credit float comment '课程学分',
CourseType varchar(20)
);
create table Grade(
CourseId varchar(20),
StudentId varchar(20),
Score float,
TeacherId varchar(20),
primary key (CourseID, StudentID),
constraint fk_grade_student foreign key (StudentId) references Student(StudentId)
);
create table Teacher(
TeacherId varchar(20) not null primary key,
TeacherName varchar(20) not null,
TeacherSex varchar(2),
TeacherDepartment varchar(20),
TeacherSchool varchar(20),
TeacherPhone char(11)
);
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1001001', '赵明亮', '男', '计算机2001', '信息工程学院', '汉族', '团员', '2001-02-15 00:00:00');
INSERT INTO `student` VALUES ('1001002', '钱多多', '男', '计算机2001', '信息工程学院', '汉族', '党员', '2001-08-25 00:00:00');
INSERT INTO `student` VALUES ('1001003', '孙晓梅', '女', '计算机2001', '信息工程学院', '壮族', '团员', '2001-12-25 00:00:00');
INSERT INTO `student` VALUES ('1002001', '李静', '女', '网络2002', '信息工程学院', '汉族', '团员', '2000-01-20 00:00:00');
INSERT INTO `student` VALUES ('1002002', '王明伟', '男', '网络2002', '信息工程学院', '壮族', '党员', '2001-03-18 00:00:00');
INSERT INTO `student` VALUES ('1002003', '李晓蓉', '女', '网络2002', '信息工程学院', '苗族', '党员', '2000-10-22 00:00:00');
INSERT INTO `student` VALUES ('1002004', '王浩云', '男', '网络2002', '信息工程学院', '苗族', '群众', '2001-09-21 00:00:00');
INSERT INTO `student` VALUES ('1002005', '魏金木', '男', '网络2002', '信息工程学院', '汉族', '群众', '2002-08-28 00:00:00');
INSERT INTO `student` VALUES ('2002001', '韦谨言', '男', '商务2002', '工商管理学院', '汉族', '群众', '2001-05-16 00:00:00');
INSERT INTO `student` VALUES ('2002002', '黄慧', '女', '商务2002', '工商管理学院', '汉族', '群众', '2001-07-07 00:00:00');
INSERT INTO `student` VALUES ('3001001', '李运国', '男', '动漫2001', '艺术学院', '汉族', '群众', '2001-11-25 00:00:00');
INSERT INTO `student` VALUES ('3001002', '张轩宇', '男', '动漫2001', '艺术学院', '汉族', '群众', '2001-06-06 00:00:00');
INSERT INTO `student` VALUES ('4001001', '李强', '男', '机械2001', '机械工程学院', '汉族', '团员', '2000-12-16 00:00:00');
INSERT INTO `student` VALUES ('4001002', '莫小荣', '男', '机械2001', '机械工程学院', '壮族', '团员', '2000-06-27 00:00:00');
INSERT INTO `student` VALUES ('5001001', '李佳欣', '女', '汽修2001', '机械工程学院', '汉族', '团员', '2000-01-07 00:00:00');
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1001', '程序设计基础', 3, '专业基础');
INSERT INTO `course` VALUES ('1002', 'C#程序设计', 3.5, '专业基础');
INSERT INTO `course` VALUES ('1003', '数据库应用', 3.5, '专业基础');
INSERT INTO `course` VALUES ('1004', '计算机网络', 3.5, '专业基础');
INSERT INTO `course` VALUES ('2001', 'JAVA程序设计', 3, '专业必修');
INSERT INTO `course` VALUES ('2002', '移动应用设计', 3, '专业必修');
INSERT INTO `course` VALUES ('3001', '高等数学', 6, '基础必修');
INSERT INTO `course` VALUES ('3002', '大学英语', 6, '基础必修');
INSERT INTO `course` VALUES ('3003', '体育', 8, '基础必修');
INSERT INTO `course` VALUES ('4001', '职业素养', 1, '选修课');
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '吴子明', '男', '信息工程', '广西机电职业技术学院', '13557830145');
INSERT INTO `teacher` VALUES ('2', '罗晓燕', '女', '信息工程', '广西工业职业技术学院', '13557830145');
INSERT INTO `teacher` VALUES ('3', '王建国', '男', '信息工程', '广西职业技能技术学院', '13557830145');
INSERT INTO `teacher` VALUES ('4', '李铭', '男', '信息工程', '广西机电职业技术学院', '13557830145');
INSERT INTO `teacher` VALUES ('5', '李咏浩', '男', '信息工程', '广西交通职业技术学院', '13557830145');
INSERT INTO `teacher` VALUES ('6', '冯名扬', '男', '信息工程', '南宁职业技术学院', '13557830145');
INSERT INTO `teacher` VALUES ('7', '梁君', '男', '信息工程', '广西机电职业技术学院', '13557830145');
INSERT INTO `teacher` VALUES ('8', '石玉梅', '女', '艺术学院', '广西机电职业技术学院', '13557830145');
INSERT INTO `teacher` VALUES ('9', '朱永刚', '男', '信息工程', '南宁职业技术学院', '13557830145');
-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES ('1001', '1001001', 85, '1');
INSERT INTO `grade` VALUES ('1001', '1001002', 80, '1');
INSERT INTO `grade` VALUES ('1001', '1001003', 55, '1');
INSERT INTO `grade` VALUES ('1002', '1001001', 90, '2');
INSERT INTO `grade` VALUES ('1002', '1001002', 78, '2');
INSERT INTO `grade` VALUES ('1002', '1001003', 65, '2');
INSERT INTO `grade` VALUES ('1003', '1001001', 86, '3');
INSERT INTO `grade` VALUES ('1003', '1001002', 75, '3');
INSERT INTO `grade` VALUES ('1003', '1001003', 70, '3');
INSERT INTO `grade` VALUES ('1004', '1001002', 95, '5');
INSERT INTO `grade` VALUES ('1004', '1001003', 75, '5');
INSERT INTO `grade` VALUES ('1004', '1002001', 90, '5');
INSERT INTO `grade` VALUES ('1004', '2002001', 80, '6');
INSERT INTO `grade` VALUES ('1004', '2002002', 85, '5');
INSERT INTO `grade` VALUES ('3001', '1001001', 85, '8');
INSERT INTO `grade` VALUES ('3001', '1001002', 86, '8');
INSERT INTO `grade` VALUES ('3001', '3001001', 75, '8');
INSERT INTO `grade` VALUES ('3001', '3001002', 85, '8');
INSERT INTO `grade` VALUES ('3001', '4001001', 90, '8');
INSERT INTO `grade` VALUES ('3001', '4001002', 93, '8');
INSERT INTO `grade` VALUES ('3001', '5001001', 59, '8');
SET FOREIGN_KEY_CHECKS = 1;
-- ---------------------------------------------------------------
use studentgradeinfo;1. 在学生表student上创建触发器insert_student_trigger，每次插入数据后，将变量str的值设置为"学生信息已添加"
查看参考答案（可复制）
sql
drop trigger if exists insert_student_trigger;
delimiter $$
create trigger insert_student_trigger
after insert
on student
for each row
begin
set @str='学生信息已添加';
end $$
delimiter ;
insert into student values ('6001002', '钱三一','男','软件2001','信息工程学院','汉族','党员','2001-08-25');
select @str;2. 在成绩表grade上创建触发器insert_grade_trigger，当向grade表中插入的学生成绩为null时，将成绩设置为0；如果不为null，就按照设定值插入
查看参考答案（可复制）
sql
drop trigger if exists insert_grade_trigger;
delimiter $$
create trigger insert_grade_trigger
before insert
on grade
for each row
begin
if new.Score is null then
set new.Score = 0;
end if;
end $$
delimiter ;
insert into grade values ('4001', '6001002', null,'7');
select * from grade where CourseId='4001' and StudentId='6001002'; ;3. 创建一个触发器update_t_trigger，当修改teacher表中的手机号时，把修改时间和修改前后老师的部分信息添加到update_t_log表中
查看参考答案（可复制）
sql
drop table if exists update_t_log;
create table update_t_log(
tId char(20) not null,
tName char(20) not null,
oldtPhone char(11) not null,
newtPhone char(11) not null,
udate datetime not null
);
drop trigger if exists update_t_trigger;
delimiter $$
create trigger update_t_trigger
after update
on teacher
for each row
begin
insert into update_t_log values (old.TeacherId, old.TeacherName,old.TeacherPhone, new.TeacherPhone,now());
end $$
delimiter ;
update teacher set TeacherPhone='18177062321' where TeacherId='2';
select * from update_t_log;4. 创建一个触发器delete_s_trigger，当删除student表中某个学生的记录时，同时删除grade表中相应的所有课程的成绩
查看参考答案（可复制）
sql
drop trigger if exists delete_s_trigger
delimiter $$
create trigger delete_s_trigger
before delete
on student
for each row
begin
delete from grade where grade.StudentId = old.StudentID;
end $$
delimiter ;
delete from student where StudentId='1001001';
select * from student where StudentId = '1001001';
select * from grade where StudentId = '1001001';5. 查看触发器
查看参考答案（可复制）
sql
select * from information_schema.triggers;6. 删除触发器
查看参考答案（可复制）
sql
drop trigger if exists delete_s_trigger;