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

实验八存储过程和触发器

一、目的与要求

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

（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

--执行更新

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) --成功插入数据