SQL SERVER 2016 教程 PDF

数据库第六次试验：数据库的完整性

• 前言
• 一、实验目的
• 二、实验内容
• 三、实验原理、方法和手段
• 四、实验组织运行要求
• 五、实验条件
• 六、实验步骤
• 七、思考题
• 八、实验报告

前言

为了帮助同学们完成痛苦的实验课程设计，本作者将其作出的实验结果及代码贴中，供同学们学习参考。如有不足或描述不完善之处，敬请各位指出，欢迎各位的斧正！

一、实验目的

1、掌握 SQL Server 中的关系图创建向导的使用方法，加深对SQL Server关系图作用的理解。
2、掌握数据库触发器的设计和使用方法。

二、实验内容

1、创建、编辑和删除数据库关系图。
2、定义触发器，理解不同类型触发器的作用和执行原理。验证触发器的有效性。

三、实验原理、方法和手段

1、创建关系图

假如要在图书_读者数据库中建立一个读者_借阅_图书关系，要求该关系图包括图书、借阅和读者三个表，并包括它们之间的“图书.书号=借阅.书号 AND 借阅.读者借书证号=读者.借书证号”的外码与被参照表之间的关联。在企业管理器中通过向导建立数据库关系图。

如图 6.1 所示，选中“数据库关系图”，单击鼠标右键，选择“新建数据库关系图”。

图 6.1

在“添加表” 窗口中选中所有表，点击“添加”按钮，进入数据库关系图设计窗口。

图 6.2

数据库关系图设计窗口如图 6.3 所示。

图 6.3

如图 6.4 所示，可以使用鼠标右键删除表之间的关系。

图 6.4

用鼠标指针指向 table_book 表主码列左边的选择栏，然后按下鼠标左键，并向table_borrow 表中的外码列处拖动，创建表间关系。如图 6.5 所示。

图 6.5

出现“表和列”对话框，列出了当前所选择的主码和外码，点击“确定”按钮。

图 6.5

在出现的“外键关系”对话框中点击“确定”按钮。

图 6.6

即建立了 table_borrow 表的外码 book_no，对应被参照关系 table_book 的主码book_no。如图 6.7 所示。

图 6.7

点击图标，出现保存该数据库关系图的窗口，输入关系图名，点击“确定”按钮，将新建的关系图存盘。

图 6.8

2、编辑数据库关系图

在企业管理器中，展开数据库关系图所属的服务器、数据库文件夹、数据库以及关系表文件夹。用鼠标右键单击要编辑的关系表，在弹出的菜单中选择“修改”项，则弹出编辑关系表的对话框，数据库关系表中的所有操作都是在该对话框中完成的。

图 6.9

3、数据库关系图的删除

在企业管理器中，用鼠标右键单击欲删除的数据库关系图。在弹出的菜单上选择“删除”项。在随后出现的删除对象对话框中单击“确定”按钮，即可删除该数据库中被选中的关系图。如图 6.10，6.11 所示。

图 6.10

图 6.11

4、创建触发器

当对某一个表进行 update、insert、delete 这些操作的时候，系统会自动调用执行该表上对应的触发器。SQL Server 中的 DML 触发器分为： after 触发器（之后触发，也叫“FOR”触发器））和 instead of 触发器 （之前触发）。其中 after 触发器要求只有执行某一操作 insert、update、delete 之后触发器才被触发，且只能定义在表上。而 instead of 触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。既可以在表上定义 instead of 触发器，也可以在视图上

定义。

触发器有两个特殊的表：插入表（instered 表）和删除表（deleted 表）。这两张是逻辑表也是虚表。由系统在内存中创建者两张表，不会存储在数据库中。而且两张表都是只读的，只能读取数据而不能修改数据。这两张表的结果总是与触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。Inserted 表的数据是插入或是修改后的数据，而 deleted 表的数据是更新前的或是删除的数据。

注意的是：触发器本身就是一个事务，所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚，撤销操作。

（1）创建 after 触发器的语法为：

create trigger tgr_name
on table_name
with encrypion –加密触发器
 for update...
as
 Transact-SQL

（2）创建 instead of 触发器的语法为：

create trigger tgr_name
on table_name
with encryption
 instead of update...
as
 T-SQL

四、实验组织运行要求

本实验属于验证型实验，通过实验，加强对课堂讲授知识的理解。开始实验前，必须进行预习，实验过程中，先集中由老师进行具体要求和注意事项的讲解，然后各自独立在机器上完成实验。实验过程中出现问题，在实验指导老师帮助下解决。

五、实验条件

（1）硬件条件：个人计算机。
（2）软件条件：Windows；MS SQL Server。

六、实验步骤

1、基本操作实验
(1) 在 SQL Server 企业管理器中调出 Create Diagram Wizard（创建图表向导），完成在图书_读者数据库中建立一个图书_借阅关系图的操作。要求该关系图包括图书和借阅两个表,并包括图书与借阅之间的“图书.书号=借阅.书号”的外码与被参照表之间的关联。
(2) 对建立的关系图进行修改、删除等操作。
(3）设计触发器及实现
1)在学生课程数据库的 SC 表上定义一个 INSERT,UPDATE 触发器（after 触发器），当插入或修改该表时，如果成绩<60，则自动修改成绩为 60。

CREATE TRIGGER triggrad ON SC FOR INSERT,UPDATE AS
 DECLARE @GRADE smallint
 SELECT @GRADE=Grade FROM inserted
 IF @GRADE<60
 BEGIN
 update Sc set Grade=60 
 from SC,Inserted i
 where sc.Sno=i.Sno and SC.Cno=i.Cno
 END

2. 在学生课程数据库的 Student 表上定义一个 DELETE 触发器（instead of），当删除某个学生的信息时，先删除 SC 表中该学生的选课信息，再删除 Student 表中的信息。

create trigger tgr_Student_inteadOf
on Student
 instead of delete /*, update, insert*/
as
 declare @Sno char(10), @Sname char(20);
 --查询被删除的信息，并赋值
 select @Sno = Sno, @Sname = Sname from deleted;
 print 'Sno: ' + @Sno + ', Sname: ' + @Sname;
 --先删除SC的信息
 delete SC where Sno = @Sno;
 --再删除Student的信息
 delete Student where Sno = @Sno;
 print '删除[ Sno: ' + @Sno + ', name: ' + @Sname + ' ] 的信息成功！';
go

3. 验证触发器。
   2、提高操作实验
   在自设计的数据库应用项目中创建关系图并设计触发器。

七、思考题

1、写出实现所有操作要求的 SQL 语句。

八、实验报告

1、基本操作实验

(1) 在 SQL Server 企业管理器中调出 Create Diagram Wizard（创建图表向导），完成在图书_读者数据库中建立一个图书_借阅关系图的操作。要求该关系图包括图书和借阅两个表,并包括图书与借阅之间的“图书.书号=借阅.书号”的外码与被参照表之间的关联。

(2)对建立的关系图进行修改、删除等操作。

删除：

增加列：

(3）设计触发器及实现

1)在学生课程数据库的 SC 表上定义一个 INSERT,UPDATE 触发器（after 触发器），当插入或修改该表时，如果成绩<60，则自动修改成绩为 60。

CREATE TRIGGER triggrad ON SC FOR INSERT,UPDATE AS
 DECLARE @GRADE smallint
 SELECT @GRADE=Grade FROM inserted
 IF @GRADE<60
 BEGIN
 update Sc set Grade=60 
 from SC,Inserted i
 where sc.Sno=i.Sno and SC.Cno=i.Cno
 END

2. 在学生课程数据库的 Student 表上定义一个 DELETE 触发器（instead of），当删除某个学生的信息时，先删除 SC 表中该学生的选课信息，再删除 Student 表中的信息。

create trigger tgr_Student_inteadOf on Student
 instead of delete /*, update, insert*/
as
 declare @Sno char(10), @Sname char(20);
 --查询被删除的信息，并赋值
 select @Sno = Sno, @Sname = Sname from deleted;
 print 'Sno: ' + @Sno + ', Sname: ' + @Sname;
 --先删除SC的信息
 delete SC where Sno = @Sno;
 --再删除Student的信息
 delete Student where Sno = @Sno;
 print '删除[ Sno: ' + @Sno + ', name: ' + @Sname + ' ] 的信息成功！';
Go

3)验证触发器。

（1）执行插入学号为001同学，课程号为121的成绩

insert into SC values ('001','121',50)

执行效果如下：

此时插入数据变更如下：

（2）执行删除学号为001同学的sql语句：

delete from Student where sno='001'

执行效果如下：

此时表格中已无sno为001同学的信息：

2、提高操作实验

在自设计的数据库应用项目中创建关系图并设计触发器。

自创建的数据库的数据库关系图如下所示：

设计触发器代码如下所示：

CREATE TRIGGER triggrad ON BORROW FOR INSERT,UPDATE AS
 DECLARE @date date
 SELECT @date=BRdate FROM inserted
 IF @date<'2021-1-1'
 BEGIN
 update BORROW set BRdate='2020-1-1'  from BORROW,Inserted i where BORROW.Rno=i.Rno and BORROW.Bno=i.Bno
 END

执行插入BORROW表编号为Rno的读者与2019-1-1借Bno为2书的信息：

insert into BORROW values(1,2,'2019-1-1')

语句执行效果如下：

插入后表格信息如下：