实验五 存储过程和触发器

实验五触发器、存储过程操作实验

本实验需要2学时。

请大家先根据“触发器.doc”文档完成相关操作，再进行本次实验。

介绍完“存储过程”后，需上交本次实验报告。

一、实验目的

（1）掌握SQL Server中的触发器的使用方法；

（2）掌握存储过程的操作方法。

二、实验内容

1. 创建、查看、修改和删除触发器。

2. 创建、查看、修改和删除存储过程。

三、实验方法

1. 触发器的操作

（1）建立触发器

方法一：使用企业管理器

首先，打开企业管理器，定位数据库并找到要创建触发器的表；

然后，右击该表名，在弹出的快捷菜单中选择“设计表”，在打开的“设计表”窗口中单击按钮，打开如图1所示窗口。

图1 触发器属性窗口

最后，在触发器属性窗口中输入触发器的内容，并单击“确定”按钮。

方法二：使用CREATE TRIGGER语句

语法：

CREATE TRIGGER <触发器名>

ON { <表名> | <视图名>}

{ FOR | INSTEAD OF | AFTER}

{ [UPDATE] [,] [INSERT] [,] [DELETE]}

AS

（2）查看触发器

方法一：使用企业管理器

方法二：使用T-SQL语句

- sp_help <触发器名>

- sp_helptext <触发器名>

- sp_depends <触发器名>

（3）修改触发器

方法一：使用企业管理器

方法二：使用ALTER TRIGGER语句

语法：

ALTER TRIGGER <触发器名>

ON { <表名> | <视图名>}

{ FOR | INSTEAD OF | AFTER}

{ [UPDATE] [,] [INSERT] [,] [DELETE]} AS

（4）删除触发器

方法一：使用企业管理器

方法二：使用DROP TRIGGER语句

语法为：

DROP TRIGGER <触发器名>

2. 存储过程的操作

（1）建立存储过程

方法一：使用建立存储过程向导

方法二：使用企业管理器

方法三：使用SQL语句（CREATE PROCEDURE）语法：

CREATE PROC[EDURE] <存储过程名>

[{@<参数名> <数据类型>}]

AS {<ＳＱＬ语句> | <语句块> }

（2）查看存储过程

方法一：使用企业管理器

方法二：使用SQL语句（系统存储过程）- sp_help <存储过程名>

- sp_helptext <存储过程名>

- sp_depends <存储过程名>

（3）修改存储过程

方法一：使用企业管理器

方法二：使用SQL语句（ALTER PROCEDURE）

语法为：

ALTER PROC[EDURE] <存储过程名>

[{@<参数名> <数据类型>}]

AS {<ＳＱＬ语句>| <语句块> }

（4）删除存储过程

方法一：使用企业管理器

方法二：使用DROP PROCEDURE语句

语法为：

DROP PROCEDURE <存储过程名>

四、实验内容

1、在学生表student上建立一个DELETE类型的触发器tr_delete，触发动作是显示信息“已删除学生表中的数据”。

2、创建名为为tr_delete1的触发器，要求实现如下功能：当删除课程表course中某一门课程的记录时，级联删除成绩表sc中有关此课程的记录。

3、创建名为tr_delete2的触发器，要求实现如下功能：当修改课程表course中某一门课程的课程号时，级联修改成绩表sc中有关此课程的课程号。

4、创建名为tr_delete3的触发器，要求如下：当同时修改student表中的姓名和性别字段时，提示用户“不能同时修改姓名和性别字段”，并撤销此次修改操作。

5、在学生表student上建立一个名为tr_sno的触发器。该触发器将被操作update所激活，将不允许用户修改表的sno字段。

6、创建名为pr_student1的存储过程，根据学生学号，查询该生的选课情况，其中包括学生学号、姓名、性别、课程号、课程名和成绩。执行该存储过程，查询学号为“95001”的选课学生情况。

7、创建名为pr_student2的存储过程。参数是学号，输出信息是指定学号所应的姓名、课程名称、成绩。运行时若没有指定学号，则提示请输入学号；若学号不存在则提示学号不存在。

8、创建名为pr_student3的存储过程，查询某同学所有课程成绩。如果存在不及格课程，则将该门课程的成绩修改为60分；否则显示所有课程成绩。

9、创建名为pr_student4的存储过程。参数是学号，输出信息是指定学号的姓名、选课门数、平均分、总分、最高分、最低分。运行时若没有指定学号，则输出学号为“95001”对应的相关信息。

10、创建名为pr_student5的存储过程，参数是学号和课程号，输出信息是指定学号和课程号的课程成绩。

五、实验报告要求

1. 用Transact-SQL写出创建触发器、存储过程的语句。

2. 实验步骤以及实验中出现的问题和解决办法。

3. 实验结果。

4. 体会或总结。

六、注意事项

1. 参照表和被参照表之间的关系。主码和外码间的关系。

2. 视图中字段名的重命名问题。

七、思考题

1. 什么是触发器？它和普通的约束有什么不同？

2. 为什么要建立存储过程？存储过程和基本查询有什么不同？