实验六 存储过程和触发器

实验六 存储过程和触发器（2学时）
1．实验目的
（1） 掌握通过SQL Server管理平台和Transact-SQL语句CREATE
PROCEDURE创建存储过程的方法和步骤。
（2） 掌握使用Transact-SQL语句EXECUTE执行存储过程的方法。
（3） 掌握通过SQL Server管理平台和Transact-SQL语句CREATE
TRIGGER创建触发器的方法和步骤。
（4） 掌握引发触发器的方法。
（5） 掌握事务、命名事务的创建方法，了解不同类型的事务的处理情况。

2．实验内容
（1） 输入以下T-SQL代码，创建一个利用流控制语句的存储过程
letters_print，该存储过程能够显示26个小写字母。

CREATE PROCEDURE letters_print
AS
DECLARE @count int
SET @count=0
WHILE @count<26
BEGIN
PRINT CHAR(ASCII('a')+ @count)
SET @count=@count +1
END

使用EXECUTE命令执行letters_print存储过程。
（2） 输入以下代码，创建存储过程stu_info，执行时通过输入姓名，可以

查询该姓名对应的学生的各科成绩。

CREATE PROCEDURE stu_info @name varchar(40)
AS
SELECT a.no,name,cno,grade
FROM Student a INNER JOIN grade b
ON a.no= b.sno
WHERE name= @name

使用EXECUTE命令执行存储过程stu_info，其参数值为“马东”。
如果存储过程stu_info执行时没有提供参数，要求能按默认值查询（设姓名
为“刘卫平”），如何修改该过程的定义？
（3） 使用student_db数据库中的Student表、course表、grade表。

① 创建一个存储过程stu_grade，查询学号为0001的学生的姓名、课程名称、
分数。
② 执行存储过程stu_grade，查询0001学生的姓名、课程名称、分数。

③ 使用系统存储过程sp_rename将存储过程stu_grade更名为stu_g。
（4） 使用Student表、course表、grade表。
① 创建一个带参数的存储过程stu_g_p，当任意输入一个学生的姓名时，将
从3个表中返回该学生的学号、选修的课程名称和课程成绩。

② 执行存储过程stu_g_p，查询“张卫民”的学号、选修课程和课程成绩。
③ 使用系统存储过程sp_helptext，查看存储过程stu_g_p的文本信息。

（5） 输入以下代码，复制Student表命名为stu2，为stu2表创建一个触发
器stu_tr，当stu2表插入一条记录时，为该记录生成一个学号，该学号为学号列
数据的最大值加1。

--复制Student表命名为stu2
SELECT * INTO stu2 FROM Student
GO
--为stu2表创建一个INSERT型触发器stu_tr
CREATE TRIGGER stu_tr
ON stu2 FOR INSERT
AS
DECLARE @max char(4)
SET @max=(SELECT MAX(no) FROM stu2)
SET @max=@max+1
UPDATE stu2 SET no=REPLICATE('0',4-len(@max))+@max
FROM stu2 INNER JOIN inserted on stu2.no=inserted.no

执行以上代码，查看studentsdb数据库中是否有stu2表，展开stu2，查看其
触发器项中是否有stu_str触发器。
在查询编辑窗口输入以下代码：

INSERT INTO stu2(no,name,sex) VALUES('0001','张主','女')
运行以上代码，查看stu2表的变化情况，为什么插入记录的学号值发生了改
变？
（6） 为grade表建立一个名为insert_g_tr 的INSERT触发器，当用户向

grade表中插入记录时，如果插入的是在course表中没有的课程编号，则提示用
户不能插入记录，否则提示记录插入成功。在进行插入测试时，分别输入以下数
据：
学号 课程编号 分数
0004 0003 76
0005 0007 69
观察插入数据时的运行情况，说明为什么？
create trigger insert_g_tr
on grade for insert
as
begin
declare @sno varchar(50),@cno varchar(50),@grade int
select @cno=cno,@sno=sno,@grade=grade from inserted
if not exists (select * from course where no=@cno)
print '没有该课程编号,不能插入记录'
else
insert into sc values(@sno,@cno,@grade)
end
（7） 为course表创建一个名为del_c_tr的DELETE触发器，该触发器的

作用是禁止删除course表中的记录。
create trigger del_c_tr
on course instead of delete
as
begin
declare @cno varchar(50)
select @cno=no from deleted
if exists (select * from course where no=@cno)
print 'grade表中有记录,不能删除记录'
else
delete from course where no=@cno
end

（8） 为Student表创建一个名为update_s_tr的UPDATE触发器，该触发

器的作用是禁止更新Student表中的“姓名”字段的内容。
create trigger update_s_tr
on student instead of update
as
begin
declare @oldsno varchar(50),@newsno varchar(50)
if update(no)
select @oldsno=no from deleted
select @newsno=no from inserted
if exists (select * from grade where sno=@oldsno)
print '禁止修改'
else
update student set no=@newsno where no=@oldsno
end
（9） 使用Transact-SQL语句DROP TRIGGER删除update_s_tr触发器。
DROP TRIGGER update_s_tr