数据库实验三：视图与触发器实验

1、实验目的

(1)熟悉SQL语言有关视图的操作，能够熟练使用SQL语句来创建需要的视图，定义数据库外模式，并能使用所创建的视图实现数据管理
(2)掌握数据库触发器的设计和使用方法
(3)利用触发器实现较为复杂的用户自定义完整性
(4)能够理解不同类型触发器的作用和执行原理，验证触发器的有效性

2、实验平台

使用SQL Server数据库管理系统提供的SSMS和查询编辑器。

3 实验内容及要求

(1)结合ST数据库中的各个表，针对给定的数据库模式，以及相应的应用需求，创建视图和带WITH CHECK OPTION的视图，并验证视图WITH CHECK OPTION选项的有效性。理解和掌握视图消解执行原理，掌握可更新视图和不可更新视图的区别。掌握数据库触发器的设计和使用方法
(2)设置一个触发器，实现学生选课总学分的完整性控制，了解触发器的工作机制
(3)定义BEFORE触发器和AFTER触发器
(4)设计一些示例数据，验证完整性检查机制
要求包括如下方面的内容：
3.1 建立及操作视图
使用create view语句完成如下的操作，包括：
1． 建立视图和带WITH CHECK OPTION选项的视图
2． 验证WITH CHECK OPTION视图的有效性
3. 消解视图并了解和掌握其原理
4. 设置可更新视图和不可更新视图并了解其区别
3.2 使用触发器
创建一个触发器，实现如下的完整性约束：

当向SC表中插入一行数据时，自动将学分累加到总学分中。
记录修改学分的操作。
定义BEFORE触发器和AFTER触发器。
４ 实验报告
　　　要求写出实验的基本过程。解释操作过程中出现的现象。

参考示例：
建立一个学生选课数据库，熟悉视图及触发器的使用方法。
一、视图建立以及操作
创建学生选课数据库TEST，包括三个基本表，其中Student表保存学生基本信息，Course表保存课程信息，SC表保存学生选课信息，其结构如下表：
表1. Student表结构
列名称 用途 类型 长度 约束 备注
Sno 学号 字符 8
Sname 姓名 字符 8 非空
Ssex 性别 字符 2 {男，女}
Sage 年龄 整型
Sdept 所在系 字符 20
Sclass 班级 字符 4 非空
Stotal 总学分 整型 默认值0

表2.SC表结构
列名称 用途 类型 长度 约束 备注
Sno 学号 字符 8 主码属性，外键 引用Student的Sno
Sname 姓名 字符 8 姓名
Cno 课程号 字符 4 主码属性，外键 引用Course的Cno
Grade 成绩 整型 0<=成绩<=100

1．创建基本表及视图

（1）创建学生表Student,由以下属性组成：学号Sno(char型，长度为9，主码)，姓名Sname(char型，长度为20，唯一)，性别Ssex(char型，长度为2)，年龄(smallint),所在系(char型，长度为20) ，并在Student表中插入下列数据：
200215121，李勇，男，20，CS
200215122，刘晨，女，19，CS
200215123，王敏。女，18，MA
200215125，张立，男，19，IS
CREATE TABLE Student
( Sno CHAR(9) ,
Sname CHAR(20) unique,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
insert into student(Sno,Sname,Ssex,Sage,Sdept)values(‘200215121’,‘李勇’,‘男’,20,‘CS’);
insert into student(Sno,Sname,Ssex,Sage,Sdept)values(‘200215122’,‘刘晨’,‘女’,19,‘CS’);
insert into student(Sno,Sname,Ssex,Sage,Sdept)values(‘200215123’,‘王敏’,‘女’,18,‘MA’);
insert into student(Sno,Sname,Ssex,Sage,Sdept)values(‘200215125’,‘张立’,‘男’,19,‘IS’);

（2）建立视图

1)建立信息系学生的视图。
create view IS_Student_No_wco AS SELECT Sno,Sname,Sage from student where Sdept=‘IS’;
2)建立带有WITH CHECK OPTION的信息系学生视图。
create view IS_Student as select Sno,Sname,Sage from student where Sdept=‘IS’ with check option;
3)对带有WITH CHECK OPTION和不带有WITH CHECK OPTION视图进行增改删查操作，并验证WITH CHECK OPTION的有效性。
update IS_Student set sname= ‘陈六’ where sno = ‘200215121’;
update IS_Student set sno = ‘200215121’ where sno = ‘200215125’;
update IS_Student No_wco set sno = ‘200215121’ where sno = ‘200215125’;

update IS_Student set sname=‘张立’ where sno = ‘200215125’;
delete IS_Student where sno = ‘200215125’;

insert into IS_Student values(‘200215127’,‘马勇’,‘男’,20,‘IS’)
insert into IS_Student No_wco values(‘200215127’,‘马勇’,‘男’,20,‘IS’)
了解WITH CHECK OPTION的作用，并验证视图WITH CHECK OPTION选项的有效性。

（3）视图消解

1)对视图进行查询和消解
select * from IS_Student where Ssex = ‘男’;

select * from Student where Ssex = ‘男’ And Sdept = ‘IS’;
理解和掌握视图消解执行原理。

（4）更新视图和不可更新视图

1)包含以下关键字的sql语句, distinct, group by, having, union, union all
2)常量视图 例create or replace view emp_v as select “张三” as name
3)select中包含子查询 例create or replace view emp_v as select (select last_name from employees)
4)join一个不能更新的视图
5)from一个不能更新的视图
6)where字句的子查询用了from字句中的表
创建几种不可更新视图并分析和更新视图的区别

二、触发器的定义及使用

1．定义触发器

（1）定义一个触发器，实现有关学分的完整性约束：当向SC表插入一行选课记录时，自动将该课程的学分累加到该学生的总学分中。

CREATE TRIGGER tr_INSERT ON SC
FOR INSERT
AS
–声明变量
DECLARE @sno char(8)
DECLARE @cridit int
DECLARE @cno char(4)
–提取插入的数据
SELECT @sno=Sno,@cno=Cno FROM inserted
–提取学生的总学分
SELECT @cridit = Ccredit
FROM SC join Course ON (SC.Cno = Course.Cno)
WHERE SC.Cno = @cno
–更新总学分
UPDATE Student SET Stotal = Stotal + @cridit
WHERE Sno = @sno
GO

（2）定义一个触发器，实现对SC表的操作登记：当用户向SC表插入或修改时，记录该操作到数据库中。

创建日志登记表:
CREATE TABLE LOG_TABLE
(
username char(10), --操作人员
date datetime, --修改时间
Sno char(8) , --学生学号
Cno char(4) --课程号
)
创建日志登记触发器:
CREATE TRIGGER tr_UPDATE ON SC
FOR INSERT,UPDATE
AS
DECLARE @sno char(8)
DECLARE @cno char(4)
DECLARE @new smallint
SELECT @sno=Sno,@cno=Cno FROM inserted
INSERT INTO LOG_TABLE VALUES(CURRENT_USER,getdate(),@Sno,@Cno);
GO

（3）执行插入操作，触发触发器：

INSERT INTO SC VALUES(‘20100001’,‘6’,95)

（4）验证触发器是否触发

select * from student
select * from LOG_TABLE

2.定义BEFORE和AFTER触发器

(1)定义一个BEFORE行级触发器，为SC表定义完整性规则“学生的3号课程成绩不得低于60分，如果低于60分，自动改为60分“。

CREATE TRIGGER tr_UPDATE ON SC
BEFORE INSERT OR UPDATE ON SC
FOR EACH ROW
AS BEGIN
IF(new.Cno = ‘3’)AND(new.Grade < 60) THEN
new.Grade := 60;
END IF;
END;

(2)定义一个AFTER行级触发器，为SC表定义完整性规则“插入姓名如果不符合规则显示用户名非法“。

CREATE TRIGGER tr_INSERT ON SC
AFTER INSERT,UPDATE
AS BEGIN
DECLARE @name nvarchar(50);
SELECT @name=name from inserted
If not @name like ‘%[a-z]%’
ROLLBACK TRANSACTION
PRINT ‘用户名非法’
END