SQL练习3

Author: 张浩森 Date: May 14, 2018 Updated On: May 14, 2018
Categories:
Tags:

SQL实验:触发器

一、实验目的

加深和巩固对触发器概念的理解。

掌握触发器的简单应用。

二、实验内容

学生数据库由三张表组成:xsqk、xskc、xscj,各个表的结构如下:

表1 xsqk结构
列名 数据类型 长度 允许空值 说明
系别 Char 10
班级 Char 12
专业 Varchar 30
学号 Char 8 × 主键
姓名 Char 8 ×
性别 Char 2 默认值:男
出生年月 Smalldatetime 4
总学分 Tinyint 1
备注 Text 16
表2 xskc结构
列名 数据类型 长度 允许空值 说明
课程号 Char 3 × 主键
课程名 Char 16 ×
开课学期 Tinyint 1 × 规则:1~8
学时 Tinyint 1 ×
学分 Tinyint 1
表3 xscj结构
列名 数据类型 长度 允许空值 说明
学号 Char 8 × 主键
课程号 Char 3 × 主键
成绩 Tinyint 1
学分 Tinyint 1
表4 学生情况表(xsqk)数据样本
系别 班级 专业 学号 姓名 性别 出生年月 总学分 备注
计算机 计算机0203 计算机应用与维护 02020101 王玲玲 1981-8-26 9
计算机 计算机0203 计算机应用与维护 02020102 张燕红 1981-10-20 9
计算机 计算机0203 计算机应用与维护 02020103 杨勇 1982-3-15
计算机 计算机0203 计算机应用与维护 02020104 王红庆 1983-5-17
计算机 计算机0203 计算机应用与维护 02020105 陈园 1982-4-12
计算机 信息管理0201 信息管理 02020201 黄薇娜 1983-8-19 8
计算机 信息管理0201 信息管理 02020202 沈昊 1982-3-18 8
计算机 信息管理0201 信息管理 02020203 傅亮达 1983-1-22
计算机 信息管理0201 信息管理 02020204 任建刚 1981-12-21
计算机 信息管理0201 信息管理 02020205 叶小红 1983-7-16
表5 学生课程表(xskc)数据样本
课程号 课程名 开课学期 学时 学分
101 计算机文化基础 1 86 4
102 Qbasic 1 68 4
205 离散数学 3 64 4
206 VC 2 68 4
208 数据结构 2 68 4
210 操作系统 3 64 4
212 计算机组成 4 86 5
216 数据库原理 2 68 4
301 计算机网络 5 56 3
表6 学生成绩表(xscj)数据样本
学号 课程号 成绩 学分
02020101 101 85 4
02020101 102 70 5
02020102 101 90 4
02020102 102 80 5
02020201 101 86 4
02020201 208 80 4
02020202 208 50 4
02020202 216 60 4

一、触发器:

1、在学生成绩库中创建触发器trigger1,实现如下功能:当在学生成绩表(xscj)中插入一条学生选课信息后,自动实现更新该学生在学生情况表(xsqk)中的总学分信息。

1
2
3
4
5
CREATE TRIGGER trigger1 AFTER INSERT ON xscj
FOR EACH ROW
BEGIN
UPDATE xsqk SET xsqk.`总学分` = xsqk.`总学分` + new.`学分` WHERE xsqk.`学号`=new.`学号`;
END

分析:根据题意,要求在学生成绩表中插入一条记录时,自动更新学生情况表中的相应记录信息。可以通过在学生成绩表中定义INSERT类型的触发器,触发器中语句要完成的功能是更新学生情况表中的相应学生的总学分信息。其实,只要在该生原总学分基础上加上新选课程的学分就可以了。

2、创建触发器trigger2,实现当修改学生课程表(xskc)中的数据时,显示提示信息“学生课程表被修改了”。

1
2
3
4
CREATE TRIGGER trigger2 AFTER UPDATE ON xskc FOR EACH ROW
BEGIN
SIGNAL SQLSTATE 'TX000' SET message_text='学生课程表被修改了';
END;

3、创建触发器trigger3,实现当删除学生课程表中某门课程的记录时,对应学生成绩表中所有有关此课程的记录均删除。

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER trigger3 AFTER DELETE ON xskc
FOR EACH ROW
BEGIN
DELETE FROM xscj WHERE xscj.`课程号` = old.`课程号`;
END

or

CREATE TRIGGER trigger3 AFTER DELETE ON xskc
FOR EACH ROW
BEGIN
DELETE FROM xscj WHERE xscj.`课程号` = xskc.`课程号`;
END

其实我感觉这样做触发器不如直接做外键,做级联删除。

4、创建触发器trigger4,实现当修改学生课程表(xskc)中的某门课的课程号时,对应学生成绩表(xscj)中的课程号也作相应修改。

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER trigger4 AFTER UPDATE ON xskc
FOR EACH ROW
BEGIN
UPDATE xscj SET xscj.`课程号` = new.`课程号` where xscj.`课程号` = xskc.`课程号`;
END

or

CREATE TRIGGER trigger4 AFTER UPDATE ON xskc
FOR EACH ROW
BEGIN
UPDATE xscj SET xscj.`课程号` = new.`课程号` where xscj.`课程号` = old.`课程号`;
END

这里也是添加外键做级联修改会容易理解一些,

5、创建触发器trigger5,实现当向学生成绩表(xscj)中插入一条选课记录时,查看该学生的信息是否存在在学生信息表中,如果不存在,则把该学生的基本信息加入到学生信息表中。

1
2
3
4
5
6
7
CREATE TRIGGER trigger5 BEFORE INSERT ON xscj
FOR EACH ROW
BEGIN
IF((SELECT `学号` FROM xsqk WHERE new.`学号` = xsqk.`学号`) IS NULL)
THEN INSERT INTO xsqk VALUES ('计算机','','',new.`学号`,'张浩森','','1981-08-26',new.`成绩`,'');
END IF;
END

6、在学生成绩库中创建触发器trigger6,实现如下功能:当在学生成绩表(xscj)中插入一条学生选课信息后,查看该学生的信息是否存在在学生信息表中,如果不存在,则给出“该记录不能被插入!”的错误提示,并撤销插入操作;同样,如果课程信息在课程信息表中不存在,给出“该记录不能被插入!”的错误提示,并撤销插入操作。

1
2
3
4
5
6
7
8
9
10
11
CREATE TRIGGER trigger6 AFTER INSERT ON xscj
FOR EACH ROW
BEGIN
IF ((SELECT `学号` FROM xsqk WHERE new.`学号` = xsqk.`学号`) IS NULL)
THEN
SIGNAL SQLSTATE 'TX000' SET message_text = '该记录不能被插入';
ELSEIF((SELECT cno FROM xskc WHERE new.`学号` = `学号`) is NULL)
THEN
SIGNAL SQLSTATE 'TX000' SET message_text = '该记录不能被插入';
END IF;
END;

7、创建触发器trigger7,强制实现业务规则:当向学生成绩表中插入一条记录时,自动修改学生情况表中该学生的总学分,要求总学分为该学生所有已修课程的学分总和。

1
2
3
4
5
6
7
CREATE TRIGGER trigger7 AFTER INSERT ON xscj
FOR EACH ROW
BEGIN
DECLARE allxf int;
SELECT sum(`学分`) INTO allxf FROM xscj WHERE `学号` = new.`学号`;
UPDATE xsqk SET `总学分` = allxf WHERE `学号` = new.`学号`;
END;

其实跟第一题是一样的,这里用了另一种方法实现,用DECLARE声明一个int变量,并且在INSERT后计算总学分和,再UPDATE xsqk中的数据。这样做比较好理解。

8、创建触发器trigger8,要求实现:当向xscj表插入一条记录时,判断该学生的总学分,如果总学分大于等于25,则给出“该学生已修满,不需要再选修!”的提示信息;否则,自动更新该学生的总学分。

1
2
3
4
5
6
7
8
9
10
11
CREATE TRIGGER trigger8 BEFORE INSERT ON xscj
FOR EACH ROW
BEGIN
DECLARE allxf int;
SELECT sum(`学分`) INTO allxf FROM xscj WHERE `学号` = new.`学号`;
SET allxf = allxf + new.`学分`;
IF(allxf >= 25)
THEN SIGNAL SQLSTATE 'TX001' SET message_text='该学生已修满,不需要再选修!';
ELSE UPDATE xsqk SET `总学分` = allxf WHERE `学号` = new.`学号`;
END IF;
END

9、分别用触发器和存储过程实现对学生情况表(xsqk)和学生成绩表(xscj)的级联删除。

1
2
3
4
5
6
#触发器
CREATE TRIGGER trigger9 AFTER DELETE ON xsqk
FOR EACH ROW
BEGIN
DELETE FROM xscj WHERE `学号` = old.`学号`;
END
1
2
3
4
5
6
7
8
9
#存储过程
CREATE PROCEDURE proc (IN parameter CHAR(10))
BEGIN
IF(parameter IS NOT NULL)
THEN
DELETE FROM xsqk WHERE `学号` = parameter;
DELETE FROM xscj WHERE `学号` = parameter;
END IF;
END;

10、在数据库中用以下语句创建两张表:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE `卷烟销售表`
(
`卷烟品牌` VARCHAR(40) PRIMARY KEY NOT NULL,
`购货商` VARCHAR(40) NULL,
`销售数量` INT NULL,
`销售单价` INT NULL,
`销售金额` INT NULL
);
CREATE TABLE `卷烟库存表`
(
`卷烟品牌` VARCHAR(40) PRIMARY KEY NOT NULL,
`库存数量` INT NULL,
`库存单价` INT NULL,
`库存金额` INT NULL
);

–业务规则1:销售金额 = 销售数量 * 销售单价

–业务规则2:库存金额 = 库存数量 * 库存单价

创建触发器[T_INSERT_卷烟库存表],实现每当[卷烟库存表]发生 INSERT 动作,则引发该触发器。触发器功能:强制执行业务规则2,保证插入的数据中,库存金额 = 库存数量 * 库存单价。

1
2
3
4
5
CREATE TRIGGER trigger10 BEFORE INSERT ON `卷烟库存表`
FOR EACH ROW
BEGIN
SET new.`库存金额` = new.`库存数量` + new.`库存单价`;
END

测试用例:针对[卷烟库存表],插入4条测试数据。注意,第一条数据(红塔山新势力)中的数据符合业务规则,第二条数据(红塔山人为峰)中,[库存金额]空,不符合业务规则,第三条数据(云南映像)中,[库存金额]不等于[库存数量]乘以[库存单价],不符合业务规则。第四条数据库存数量为0。请注意在插入数据后,检查[卷烟库存表]中的数据是否满足:库存金额 = 库存数量 * 库存单价。

1
2
3
4
INSERT INTO `卷烟库存表`(`卷烟品牌`,`库存数量`,`库存单价`,`库存金额`) values( '红塔山新势力',100,12,1200);
INSERT INTO `卷烟库存表`(`卷烟品牌`,`库存数量`,`库存单价`,`库存金额`) values( '红塔山人为峰',100,22,null);
INSERT INTO `卷烟库存表`(`卷烟品牌`,`库存数量`,`库存单价`,`库存金额`) values( '云南映像',100,60,500);
INSERT INTO `卷烟库存表`(`卷烟品牌`,`库存数量`,`库存单价`,`库存金额`) values( '玉溪',0,30,0);

验证结果:

11、创建触发器[T_INSERT_卷烟销售表],实现每当卷烟销售表发生 INSERT 动作,则引发该触发器。触发器功能: 实现业务规则:如果销售的卷烟品牌不存在库存或者库存为零,则返回错误。否则,判断是否符合业务规则1,如果符合,则自动减少卷烟库存表中对应品牌卷烟的库存数量和库存金额。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TRIGGER trigger11 BEFORE INSERT ON `卷烟销售表`
FOR EACH ROW
BEGIN
DECLARE num1 int;
DECLARE num2 int;
SELECT count(*) into num1 from `卷烟库存表` where `卷烟品牌` = new.`卷烟品牌`;
SELECT `库存数量` - new.`销售数量` into num2 from `卷烟库存表` where `卷烟品牌` = new.`卷烟品牌`;
IF(num1 = 0 || num2<=0 || (new.`销售金额` != new.`销售数量` * new.`销售单价`))
THEN
SIGNAL SQLSTATE 'TX001' SET message_text='该记录不能被插入';
ELSE
UPDATE `卷烟库存表` SET `库存金额` = (`库存数量` - new.`销售数量`) * `库存单价`,`库存数量` = `库存数量` - new.`销售数量` WHERE `卷烟品牌` = new.`卷烟品牌`;
END IF;
END;