实验八 存储过程和触发器_参考答案

1 实验八 存储过程和触发器 一、目的与要求 1. 正确理解存储过程和触发器的概念、功能和类型； 2. 掌握使用SSMS和T-SQL语句创建和管理存储过程和触发器。 二、上机准备 利用教师提供的XSGL数据库，该库中有3个表：student，course，sc。 三、实验内容 1. 将教师提供的XSGL数据库附加到本地数据库中。

2. 分别使用SSMS和T-SQL语句创建和管理存储过程和触发器。 （1）创建一个存储过程proc_stud_sc_info，查询学号、姓名、性别、系、课程号和成绩等信息。 use xsgl go create procedure proc_stud_sc_info as select student.sno,sname,sex,dept,cno,grade from student left join sc on student.sno=sc.sno go

（2）创建一个存储过程proc_stud_info，根据输入的学号，查询学生的基本信息。 use xsgl go create procedure proc_stud_info @sno char(5)='95001' as select * from student where sno=@sno go

（3）创建一个存储过程proc_stud_birth_year，根据输入的学生姓名，计算该学生的出生年份。 use xsgl go create procedure proc_stud_birth_year @sname varchar(6)='张立' as select sname,year(getdate())-age as 出生年份 from student where sname=@sname go 2

（4）创建一个存储过程proc_GetAvgScByCno，根据输入的课程号返回该课程的平均成绩。 use xsgl go create procedure proc_GetAvgScByCno @cno char(3)='1' as select @cno as 课程号,avg(grade) as 平均成绩 from sc where cno=@cno go 或

use xsgl go create procedure proc_GetAvgScByCno @cno char(3)='1',@aver smallint output as select @aver=avg(grade) from sc where cno=@cno go

（5）创建一个Insert触发器tri_StudentInsert，当向student表插入一条记录时，向客户端显示一条“您正在插入学生的数据”的信息。 create trigger tri_StudentInsert on student for insert as print '您正在插入学生的数据' go

--验证插入 --查看插入前状态 select * from student --执行插入 insert into student values('95006','范冰冰','f',35,'IS') --查看插入后状态 select * from student

（6）创建一个Update触发器tri_StudentUpdate，当修改student表的记录时，向客户端显示一条“原姓名与新姓名”的消息，并执行修改语句，验证触发器的运行。 create trigger tri_StudentUpdate on student for update as begin declare @oldname varchar(6),@newname varchar(6) select @oldname=sname from deleted select @newname=sname from inserted print '原姓名为：'+@oldname+space(3)+'新姓名为：'+@newname end go

--验证更新 --查看更新前状态 select * from student --执行更新 3

update student set sname='张小立' where sname='张立' --查看更新前状态 select * from student

（7）创建一个Delete触发器tri_StudentDelete，当学生表的数据删除时，该数据被自动地增加到毕业生表（需要新建）中，并删除student表中的数据，并返回毕业生表中的数据，验证触发器的操作。 --创建毕业生表 create table grad_student (sno char(5),sname varchar(6),sex char(1),age tinyint,dept char(2)) Go

--创建触发器 create trigger tri_StudentDelete on student for delete as begin declare @sno char(5),@sname varchar(6),@sex char(1),@age tinyint,@dept char(2) if exists(select * from Deleted) begin select @sno=sno,@sname=sname,@sex=sex,@age=age,@dept=dept from Deleted insert into grad_student values(@sno,@sname,@sex,@age,@dept) end end

go

--返回毕业生表中的数据，验证触发器的操作 delete from student where sname='范冰冰' select * from grad_student go

（8）创建一个Instead of触发器tri_ScInsert,当向sc表插入数据时，先检索student表和course表中是否有该同学以及该课程号的课程。如果没有，给出提示“学生表中没有该学号的同学！课程表中没有该课程号的课程”；有，则插入该数据，并执行插入语句测试触发器的动作。 create trigger tri_ScInsert on sc instead of insert as begin declare @sno char(5),@cno char(3),@grade tinyint select @sno=sno,@cno=cno,@grade=grade from inserted if (not exists(select sno from student where sno=@sno)) and (not exists(select cno from course where cno=@cno)) print '学生表中没有该学号的同学！课程表中没有该课程号的课程!' else if (exists(select sno from student where sno=@sno)) and (exists(select cno from course where cno=@cno)) begin insert into sc values(@sno,@cno,@grade) print '已成功插入!' end end

--插入数据，测试触发器的动作。 insert into sc values('95002','3',null) --成功插入数据 4

insert into sc values('95008','8',null) --显示“学生表中没有该学号的同学！课程表中没有该课程号的课程!”

3. 使用SSMS和T-SQL语句完成如下操作： （1）创建一个库存管理数据库，并添加如下三个基本表：

入库表 出库表 库存表 --创建数据库和3个表，并为库存表插入数据 create database 库存管理 on primary (name='库存管理',filename='h:\hyr\库存管理.mdf') log on (name='库存管理_log',filename='h:\hyr\库存管理_log.ldf') Go

use 库存管理 go create table 库存表 (商品名 varchar(20) primary key, 库存数量 int null) go create table 入库表 (入库号 int identity primary key, 商品名 varchar(20) null, 入库数量 int null, foreign key(商品名) references 库存表(商品名)) go create table 出库表 (出库号 int identity primary key, 商品名 varchar(20) null, 出库数量 int null, foreign key(商品名) references 库存表(商品名))

--为库存表插入数据 insert into 库存表 values('电视机',0)