7实验七 触发器_实验七触发器

7实验七 触发器由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“实验七触发器”。

实验七

触发器

一、实验目的（1）理解触发器的用途、类型和工作原理

（2）掌握利用T-SQL语句创建和维护触发器的方法（3）掌握利用企业管理器创建、维护触发器的方法

二、实验内容

1.创建after触发器

(1）创建一个在插入时触发的触发器sc_insert,当向sc表插入数据时，须确保插入的学号已在Student表中存在，并且还须确保插入的课程号在Course表中存在；若不存在，则给出相应的提示信息，并取消插入操作，提示信息要求指明插入信息是学号不满足条件还是课程号不满足条件（注：Student表与sc表的外键约束要先取消）。

create trigger sc_insert on sc after insert as if not exists(select * from student,inserted

where student.sno=inserted.sno)begin print '插入信息的学号不在学生表中！' if not exists(select * from course,inserted where

print '插入信息的课程号不在课程表中！' rollback course.cno=inserted.cno)end else

begin

if not exists(select * from course,inserted where begin print '插入信息的课程号不在课程表中！' rollback course.cno=inserted.cno)end end

执行：

①、insert into SC values('20110112','001','78')

删除外键约束：

alter table SC drop constraint FK__SC__Sno__182C9B23

②、insert into SC values('20110002','001','78')

③、insert into SC values('20110002','006','78')

(2）为Course表创建一个触发器Course_del，当删除了Course表中的一条课程信息时，同时将表sc表中相应的学生选课记录删除掉。

create trigger course_del on course after delete as

if exists(select * from sc,deleted

where sc.cno=deleted.cno)begin

delete from sc where sc.cno in(select cno from deleted)end

delete from Course where Cno='003'

select * from SC

(3）在Course表中添加一个平均成绩avg_Grade字段（记录每门课程的平均成绩），创建一个触发器Grade_modify，当SC表中的某学生的成绩发生变化时，则Course表中的平均成绩也能及时相应的发生改变。alter table Course add avg_Grade smallint

update Course set avg_Grade=(select AVG(Grade)from SC

where SC.Cno=Course.Cno)

select * from Course

create trigger Grade_modify on sc after update as if update(grade)begin update course

set avg_grade=(select avg(grade)

from sc where course.cno=sc.cno

group by cno)end

update SC set Grade='90' where Sno='20050001' and Cno='001'

（4）测试上述三个触发器。测试过程在（1）、（2）、（3）中均给出

2.创建instead of 触发器

(1）创建一视图Student_view,包含学号、姓名、课程号、课程名、成绩等属性，在Student_view上创建一个触发器Grade_moidfy，当对Student_view中的学生的成绩进行修改时，实际修改的是sc中的相应记录。

create view Student_view as select s.Sno,Sname,c.Cno,Cname,Grade from Student s,Course c,SC where s.Sno=SC.Sno and c.Cno=SC.Cno

select * from Student_view

create trigger Grade_moidfy on Student_view instead of update as if UPDATE(Grade)begin update SC

set Grade=(select Grade from inserted)where Sno=(select Sno from inserted)and Cno=(select Cno from inserted)end

update Student_view set Grade=40 where Sno='20110001' and Cno='002'

select * from Student_view

select * from SC

(2）在SC表中插入一个getcredit字段（记录某学生，所选课程所获学分的情况），创建一个触发器ins_credit，当更改（注：含插入时）SC表中的学生成绩时，如果新成绩大于等于60分，则该生可获得这门课的学分，且该学分须与Course表中的值一致；如果新成绩小于60分，则该生未能获得学分，修改值为0。

alter table SC add getcredit smallint

select * from SC

update SC set getcredit=(select credit from Course where SC.Cno=Course.Cno)where Grade>=60

update SC set getcredit=0 where Grade

create trigger ins_credit on SC instead of update,insert as begin

declare @s_no char(8),@c_no char(3),@new_grade smallint,@cred select @s_no=sno,@c_no=cno,@new_grade=grade from inserted select @cred=credit from course where cno=@c_no if(@new_grade>=60)

begin delete from SC where sno=@s_no and cno=@c_no insert into SC values(@s_no,@c_no,@new_grade,@cred)smallint end else begin delete from SC where sno=@s_no and cno=@c_no insert into SC values(@s_no,@c_no,@new_grade,0)end end

insert into SC(sno,cno,grade)values('20081800','002',85)

（3）测试上述两个触发器。测试结果在（1）、（2）中均已给出 3.使用T-SQL语句管理和维护

(1）用系统存储过程sp_helptrigger查看触发器Grade_modify的相关信息 sp_helptrigger Student_view

(2）使用系统存储过程sp_helptext查看触发器Grade_modify中的定义内容。sp_helptext Grade_moidfy

(3)使用select语句查看触发器Grade_modify的定义内容。

select o.id,c.text from sysobjects o inner join syscomments c on o.id=c.id where o.type='TR' and o.name='Grade_modify'

（4）用系统存储过程sp_depends查看触发器Grade_modify的相关性。sp_depends Grade_modify

（5）将sc_insert触发器改为instead of 触发器，实现的功能不变。drop trigger sc_insert

create trigger sc_insert on sc instead of insert as if not exists(select * from student,inserted

where student.sno=inserted.sno)begin print '插入信息的学号不在学生表中！' if not exists(select * from course,inserted where

print '插入信息的课程号不在课程表中！' rollback course.cno=inserted.cno)end else

begin

if not exists(select * from course,inserted where begin print '插入信息的课程号不在课程表中！' rollback course.cno=inserted.cno)end end

insert into SC values('20110005','001','78','6')

（6）将触发器sc_insert删除。drop trigger sc_insert

4.使用SQL Server Management Studio管理存储过程

（1）在SQL Server Management Studio中重新创建刚删除的触发器sc_insert 选中SC表，展开→右击“触发器”→新建触发器

出现如下界面：

编写余下的SQL语句：

CREATE TRIGGER sc_insert

ON SC

INSTEAD OF INSERT AS BEGIN

if not exists(select * from student,inserted

where student.sno=inserted.sno)begin print '插入信息的学号不在学生表中！' if not exists(select * from course,inserted where

print '插入信息的课程号不在课程表中！' rollback course.cno=inserted.cno)end else begin

if not exists(select * from course,inserted where begin print '插入信息的课程号不在课程表中！' rollback course.cno=inserted.cno)end end END

（2）查看触发器sc_insert 的内容。

展开表SC→触发器→选择sc_insert触发器→查看依赖关系

出现如下界面：

（3）删除触发器sc_insert 选中sc_insert触发器，右击→删除