同济大学数据库实验五触发器作业

数据库技术及应用实验六 视图、存储过程、触发器 学号: 1351789 姓名: 徐云帆

1． 定义视图并在视图上完成数据的查询功能（查询给出截屏）。

建立“查询有选课记录的学生学号、姓名、课程号、课程名称、成绩信息”操作的视图。

CREATE VIEW sc_score AS

SELECT sc.snum,

sections.cnum, course.cname, sc.score FROM sc

join sections on sc.secnum=sections.secnum Join course on sections.cnum=course.cnum

对上述视图完成查询：查询有不及格成绩的学生信息。

SELECT cname

AS 所有学生都及格的课程名称

FROM S_score GROUP BY cname Having min(score)<60

在上述视图的基础上完成查询：查询所有学生都及格的课程名称。

SELECT cname

AS 所有学生都及格的课程名称 FROM S_score GROUP BY cname

Having min(score)>=60

1

数据库技术及应用实验六 视图、存储过程、触发器 学号: 1351789 姓名: 徐云帆

2． 存储过程的建立与执行（给出调用语句以及执行结果截屏）。

建立存储过程，其功能为显示使用了某出版社出版的教材的课程信息。调用该存储过程：显示使用了高等教育出版社出版的教材的课程。

create proc 课程信息 as

select course.cname as 使用了高等教育出版社出版的教材的课程 from course

where course.textbook like '%高等教育出版社%' exec 使用了高等教育出版社出版的教材的课程

建立存储过程，其功能是查询给定出生年份信息（注意：出生年份是整型数）的学生信息

CREATE PROC 年份信息 @_year INT AS

SELECT * FROM student

WHERE YEAR(birthday)=@_year exec 年份信息 1994

exec 年份信息 1995

2

数据库技术及应用实验六 视图、存储过程、触发器 学号: 1351789 姓名: 徐云帆

exec 年份信息 1993

3． 建立一组触发器，并设计一组必要的数据操作，测试验证触发

器的功能（提示：在测试之前，将university中过去建立的表之间的关系删除）

自定义一个触发器，完成选课表sc与学生表student的参照完整性控制：① 对student表中记录的删除是受限的，即删除某学生信息时，如果该生有选课记录，则禁止删除，否则，允许删除；②对student表中记录的学号的修改采用级联更新sc表对应学号；sc表插入数据时，学号必须是student存在的学号。 （1）对student表中记录的删除时受限的。

create trigger student_delete on student for delete as begin

if((select snum from deleted) in(select distinct snum from sc)) print '该学生有选课，不能删除，撤销操作' rollback end

测试1：删除s001学生记录

delete from student where snum like 's001'

（2）对student表中记录的学号的修改采用级联更新sc表对应学号。

create trigger student_update on student for update as

declare @snum_old varchar(6),

3

数据库技术及应用实验六 视图、存储过程、触发器 学号: 1351789 姓名: 徐云帆

@snum_new varchar(6)

select @snum_new=snum from inserted select @snum_old=snum from deleted if exists(select * from sc where snum like @snum_old) begin

update sc set snum=@snum_new where snum like @snum_old print '级联更新了！' end

测试1：将学生s001的学号改为s009

update student set snum='s009' where snum like 's001'

4

数据库技术及应用实验六 视图、存储过程、触发器 学号: 1351789 姓名: 徐云帆

更新前：

5

数据库技术及应用实验六 视图、存储过程、触发器 学号: 1351789 姓名: 徐云帆

更新后：

（3）sc表插入数据时，学号必须是student存在的学号。

create trigger sc_insert on sc after insert as begin

if not exists(select * from student where snum in(select snum from inserted)) begin

print '没有该学生，禁止插入选课记录' rollback end end

测试：插入一个不存在的学生（s010）的选课记录

insert into sc

values('s010','11601',78)

6

数据库技术及应用实验六 视图、存储过程、触发器 学号: 1351789 姓名: 徐云帆

更新后：

（3）sc表插入数据时，学号必须是student存在的学号。

create trigger sc_insert on sc after insert as begin

if not exists(select * from student where snum in(select snum from inserted)) begin

print '没有该学生，禁止插入选课记录' rollback end end

测试：插入一个不存在的学生（s010）的选课记录

insert into sc

values('s010','11601',78)

6

本文来源：https://www.bwwdw.com/article/5m7x.html