实验六 触发器

一、实验目的

1）理解触发器的用途、类型和工作原理

2）掌握利用T-SQL语句创建和维护触发器的方法

3）掌握利用企业管理器创建、维护触发器的方法

二、实验内容

说明：在所有触发器取名时，请各位同学在所给定的名称后加上下划线及学号后四位数字构成自己的实验触发器名。如：deltr_20051101.各触发器中的所用到的参数变量名自取。

1、利用企业管理器创建与维护触发器

（1）创建简单触发器

创建一个触发器stu_modify在修改student表后，显示一个提示信息，告诉用户有多少行数据被修改了。

创建步骤：

create trigger stu_modify on student

after insert,delete,update

as

print'（所影响的行数为:'+cast(@@rowcount as varchar(10))+'行';

触发器的触发执行测试语句（T-SQL）：

update Student_20083386

set sex='男'

where sno='20050001'

执行结果：

（2）修改触发器

修改stu_modify触发器，使其为一个加密触发器。（提示：加with encpytion关键子句）修改步骤：

USE[XSGL_20083386]

GO

/****** Object: Trigger [dbo].[stu_modify] Script Date: 05/17/2010 19:59:55 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER trigger[dbo].[stu_modify]

on[dbo].[Student_20083386]

with Encryption

after insert,delete,update

as

print'（所影响的行数为:'+cast(@@rowcount as varchar(10))+'行)'

（3）删除触发器

删除stu_modify触发器。

删除步骤：

2、利用T-SQL语句创建与维护触发器

（1）创建after触发器

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

T-SQL语句：

create trigger sc_insert on SC_20083386

for insert

as

begin

if exists(select Sno from inserted where Sno not in(select Sno from

Student_20083386))

print'not Sno'

delete from SC_20083386

where Sno not in(select Sno from Student_20083386);

if exists(select Cno from inserted where Cno not in(select Cno from Course_20083386))

print'not cno'

delete from SC_20083386

where Cno not in(select Cno from Course_20083386);

end;

触发器的触发执行测试语句（T-SQL）：

insert

into SC_20083386

values('20050002','001','20');

执行结果：

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

T-SQL语句：

create trigger course_del on Course_20083386

for delete

as

begin

delete from SC_20083386

where SC_o in(select Cno from deleted)

end;

触发器的触发执行测试语句（T-SQL）：

delete

from Course_20083386

where Cno='002';

执行结果：

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

T-SQL语句：

create trigger avg_grade on Course_20083386

for update,insert,delete

as

begin transaction

begin

update Course_20083386

set avg_grade=(select avg(Grade)

from SC_20083386

where SC_o=Course_o)

End

触发器的触发执行测试语句（T-SQL）：

update SC_20083386

set grade=77

where Sno='20050001'

执行结果：

3、删除触发器

将触发器sc_insert删除。

T-SQL语句：

drop trigger sc_insert

三．实验总结

本次实验中出现错误：DELETE语句与REFERENCE约束“FK_SC_20083386_Course_20083386"发生冲突，网上查后知道是Cno外键的问题，然后删除外键成功运行。

最终我理解触发器的用途、类型和工作原理掌握了利用T-SQL语句创建和维护触发器的方法，掌握了利用企业管理器创建、维护触发器的方法。