实验六存储过程和触发器
一、目的与要求
1.掌握编写数据库存储过程的方法。

2.掌握建立数据库触发器的方法，通过实验观察触发器的作用和触发条件设置
等相关操作。

二、实验准备
1.了解编写存储过程和调用的T-SQL语法；
2.了解触发器的作用；
3.了解编写触发器的T-SQL语法。

三、实验内容
(一)存储过程
在studentdb数据库中建立存储过程getPractice，查询指定院系(名称)(作为存储过程的输入参数)中参与“实践”课程学习的所有学生学号、姓名、所学课程编号和课程名称，若院系不存在，返回提示信息。

提示：D_Info表中存储了院系代码D_ID，而St_Info表中学号字段St_ID的前两位与之对应，则D_Info表与St_Info表之间的联系通过这两个字段的运算构成连接条件。

1.分别执行存储过程getPractice，查询“法学院”和“材料科学与工程学院”
的学生中参与“实践”课程的所有学生学号、姓名、所学课程编号和课程
名称。

create procedure getPractice
@d_name varchar(30)
as
select st_info.st_id,st_info.st_name,s_c_info.c_no,c_info.c_name from st_info,d_info,s_c_info,c_info
where d_info.d_name=@d_name and st_info.st_id=s_c_info.st_id
and d_info.d_id=left(st_info.st_id,2)
and s_c_info.c_no=c_info.c_no and c_info.c_type='实践'
Go
exec getPractice '法学院'
exec getPractice '材料科学与工程学院'
2.利用系统存储过程sp_rename将getPractice更名为getPctStu
sp_rename getpractice,getPctStu
3.修改存储过程getPctStu，返回指定院系中参与实践课程的学生人次数，并
利用该存储过程以“法学院”为输入参数验证执行的结果alter procedure getPctStu
@d_name varchar(30)
as
select count(s_c_info.st_id)
from s_c_info,c_info,d_info,st_info
where d_info.d_name=@d_name and st_info.st_id=s_c_info.st_id and
d_info.d_id=left(st_info.st_id,2) and s_c_info.c_no=c_info.c_no
and c_info.c_type='实践'
go
exec getPctStu '法学院'
4.再修改存储过程getPctStu，返回指定院系中参与实践课程的学生人数。

注：“人数”和“人次数”是不同的，对某一学生而言，如果参与了多门实践课程，则“人次数”是指其参与的课程门数，而“人数”仍为1。

alter procedure getPctStu
@d_name varchar(30)
as
select count(distinct s_c_info.st_id)
from s_c_info,c_info,d_info,st_info
where d_info.d_name=@d_name and st_info.st_id=s_c_info.st_id and d_info.d_id=left(st_info.st_id,2)
and s_c_info.c_no=c_info.c_no and c_info.c_type='实践'
go
exec getPctStu '法学院'
(二)触发器
1.在studentdb数据库中建立一个具有审计功能的触发器：
触发器名为tr_sc，功能要求：审计在s_c_info表中对score字段的更新和插入操作，将这些操作记录到sc_log表中，sc_log表中有如下字段：操作类型type，学号st_id，课程号c_no，旧成绩oldscore，新成绩newscore，操作员uname，操作时间udate，其中操作员设定默认值为user，操作时间默认值为系统时间。

create table sc_log
(type char(6),
st_id char(8),
c_no char(8),
oldscore numeric(5,1),
newscore numeric(5,1),
uname varchar(20)default user,
udate datetime default getdate()
)
create trigger tr_sc
on s_c_info
for insert,update
as
if update(score)
begin
if(select count(*)from deleted)<>0
insert into sc_log(type,st_id,c_no,oldscore,newscore)
select'update',i.st_id,i.c_no,d.score,i.score
from inserted i ,deleted d
where i.st_id=d.st_id and i.c_no=d.c_no
else
insert into sc_log(type,st_id,c_no,newscore)
select'insert',st_id,c_no,score from inserted
end
2. 在s_c_info表上建立一个触发器tr_updasc，用于监控对成绩的更新，要求更新后的成绩不能比更新前低，如果新成绩低则取消操作，给出提示信息，否则允许更新。

create trigger tr_updasc
on s_c_info
for update
as
declare @newscore numeric(5,1),@oldscore numeric(5,1) select @newscore=score from inserted
select @oldscore=score from deleted
if(@newscore<@oldscore)
begin
print('新成绩不能比旧成绩低!')
rollback transaction
end
(三)查看存储过程和触发器的信息
1.用sp_help查看以上建立的存储过程和触发器的基本信息
sp_help getPctStu
sp_help tr_updasc
2.用sp_helptext查看存储过程和触发器的代码
sp_helptext getPctStu
sp_helptext tr_updasc
四、思考与练习
1．存储过程如何加密？
在创建存储过程的语句‘as’前添加语句with encryption。

2．触发器有什么好处和坏处？
好处：触发器可以帮助系统监测数据的正确处理，同时，触发器可以作为完整性约束，辅助用户建立正确的数据表，保证数据的正确性。

坏处：触发器要用的恰到好处，一个大型应用里，触发器越少越好，触发器会使编程时源码的结构被迫打乱，为将来的程序修改、源码阅读带来很大不便。