数据库实验实验七存储过程与触发器

悠悠 2023-09-29 15:24 39阅读 0赞

注：此内容仅仅只是记录成长与分享学习，不能保证绝对正确

**实验七 存储过程与触发器**

实验日期： 2022 年 5 月 20 日 星期 五  实验地点： 宿舍

一 实验目的

1. 掌握SQL Server中存储过程的创建和执行方法。

2. 理解触发器的功能，学会创建触发器，维护数据库完整性。

二 实验要求

1. 请大家务必动手完成实验，实验过程中，会随机抽查提问，作为实验成绩重要参考。

2. 请将数据库studb的两个基本文件自行保留，下次实验在此基础上进行。

3. 请在本实验报告指定方框认真填写对应的实验代码或实验结果。

三 实验内容

1. 创建用户存储过程，使用存储过程。

2. 创建触发器。

四 实验步骤

附加前述数据库，按下述要求完成实验：

1. **执行常见的系统存储过程**

在命令窗口依次执行如下SQL命令：**EXEC sp\_databases**;**EXEC sp\_tables**;**EXEC sp\_columns S**（查询当前数据库studb中数据表S的列的信息）;**EXEC sp\_help S**（查看数据表S的信息）。系统存储过程可通过图7.1方式查询，可自行练习执行其他的系统存储过程。

![28a796b022034148a6ae1b131d73f868.png][]

图7.1 系统存储过程

2. **通过****SQL****命令创建存储过程**

1）创建不带参数的简单存储过程（get\_S），该存储过程的功能是查询所有学生信息。完善并执行下述SQL命令。

**CREATE PROCEDURE get\_S**

**AS**

**SELECT** \* **FROM** S**;**

创建好存储过程get\_S之后，执行该存储过程的SQL命令为：

2）创建带参数的存储过程（get\_sc\_name），该存储过程根据提供的学号、课程号，返回相应的学生姓名、课程名。完善并执行下述SQL命令：

**CREATE PROCEDURE get\_sc\_name**

**@sno char(6),**

**@cno char(2),**

**@sname char(8)**output**,**

**@cname varchar(24)**output

**AS**

**SELECT @sname=SNAME,@cname=CNAME**

**FROM S,C,SC**

**WHERE S.SNO=SC.SNO AND** C.CNO=SC.CNO **AND** [SC.SNO=@sno][SC.SNO_sno] **AND** SC.CNO

=@cno **;**

创建好存储过程get\_sc\_name之后，以学号'S1',课程号'C3'为参数，执行该存储过程的SQL命令为：

**DECLARE @sname char(8),**

**@cname varchar(24)**

**EXEC get\_sc\_name 'S1','C3',@sname** output**,@cname** output**;**

执行存储过程get\_sc\_name之后，通过SELECT显示执行结果：

**SELECT @sname,@cname;**

3. **通过****SQL****命令创建触发器**

1）为数据表S创建一个名为Trig\_S的触发器，将删除的学生数据备份到学生存档表SBACK中。

第一步，在studb数据表中创建一个与学生表S结构完全相同的学生备份表SBACK。

第二步，在S表中任意添加一个学生信息。

第三步，创建数据表S的触发器Trig\_S：

**CREATE TRIGGER Trig\_S ON** S

**FOR** DELETE

**AS**

**INSERT INTO SBACK**

**SELECT** \* **FROM** deleted**;**

第四步，删除第二步添加的学生信息，删除后查看数据表SBACK中是否已经备份了刚刚删除的学生信息。

2）为数据表SC创建一个名为Trig\_SC\_UPDATE\_SCORE的触发器，在修改成绩表（SC）的成绩SCORE时，要求修改后的成绩一定不低于修改前的成绩（即临时表inserted中SCORE字段值大于等于临时表deleted中字段SCORE的值），否则回滚（ROLLBACK TRANSACTION）更新操作。完善并执行下页SQL命令。然后通过更新SC中的SCORE数值进行验证，可以看出，当更新后的SCORE不低于更新之前的SCORE时，能顺利通过，否则更新失败。

**CREATE TRIGGER Trig\_SC\_UPDATE\_SCORE ON** SC

**FOR UPDATE**

**AS**

**IF**

**(**

**SELECT COUNT(\*) FROM** inserted **,** deleted

**WHERE** inserted.SCORE<deleted.SCORE

**)>0**

**ROLLBACK TRANSACTION**

五 自选动作

请在下框填写除了上述实验内容之外自选的实验内容：

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:370.65pt;"> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;创建AFTER触发器Trig_SC_INSERT_SCORE，实现在SC表中添加的成绩必须大于等于60，否则添加失败，回滚插入操作。 CREATE TRIGGER Trig_SC_INSERT_SCORE ON&nbsp; SC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FOR INSERT AS IF( SELECT SCORE FROM&nbsp; inserted )&lt;60 ROLLBACK TRANSACTION 测试： insert SC values('S0','C0',59) 结果为： 事务在触发器中结束。批处理已中止。 </td> 
 </tr> 
 </tbody> 
</table>

六 实验总结

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:369.8pt;"> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;在做触发器实验的时候，我还不知道表inserted和deleted的存在，然后就一直在想，触发器是for类型的，也就是先进行删除，然后才进行备份，我想着不对劲啊，都先删除了还怎么备份啊，尝试了很久没有结果，也在百度上找了一下，都没有发现有deleted这张表的存在，最后无奈放弃了，跳过此题，开始读下一题，结果一读到括号里的内容瞬间就明白了，原来我是我露了一个知识点。 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;后来在自选实验我还想尝试，假设不用表deleted，FOR类型也不改，能不能做出来，网上查了触发器创建所有方式后发现其实也是可以的，只不过需要在触发器名字和 ON之间插入部分语句 BEFORE DELETE 然后其他的就可以实现了。 </td> 
 </tr> 
 </tbody> 
</table>

成绩

[28a796b022034148a6ae1b131d73f868.png]: https://img-blog.csdnimg.cn/28a796b022034148a6ae1b131d73f868.png
[SC.SNO_sno]: mailto:SC.SNO=@sno