数据库专题训练------触发器

【实验目的】

1. 掌握触发器的分类及工作原理。
2. 掌握触发器的创建方法。
3. 掌握触发器的使用方法。

【实验环境】

Sql server 2005

【实验重点及难点】

1. 创建insert触发器。
2. 创建delete触发器。
3. 创建update触发器
4. 执行触发器的方法。
5. 查看触发器。
6. 禁止和启动 触发器。

【实验内容】

设定已有一个名为XSCJ的数据库，在XSCJ数据库中创建如下关系表：学生信息表S，课程表C，成绩表SC。

学生基本信息表S：

Sno	Sname	Ssex	Sage	Sdept
S1	李强	男	19	计算机
S2	王松	男	20	通信工程
S3	李丽	女	18	电子
S4	张平	女	21	计算机
S5	何晴	女	19	通信工程
S6	王小可	男	20	计算机
S7	张欢	男	20	电子

课程信息表C：                                          学生成绩表SC：

Sno	Cno	Score
S1	C2	85
S2	C3	72
S3	C4	90
S4	C1	84
S5	C2	58
S6	C3	88
S3	C1	69
S1	C5	88
S1	C3	95

Cno	Cname	Cteacher
C1	C语言	刘军
C2	C++程序设计	李彤
C3	操作系统	吴明
C4	数据库	李白
C5	计算机网络	苏志朋
C6	数据结构	刘军

1、创建一个名为 tri_Delete_C的触发器，测试该触发器的执行情况，并给出出实验结果。该触发器的作用为：当在课程表C中删除某门课程时，则所有选修该课程的记录也全部删除。

create trigger tri_Delete_C on C after delete as delete from sc where cno in (select cno from deleted) Go

2、创建一个名为tri_Insert_S的触发器，测试该触发器的执行情况，并给出实验结果。当插入的新记录中Sage的值不是18至25之间的数值时，就激活该触发器，撤销该插入操作，并给出错误提示。

create trigger tri_Insert_S on S after insert,update as if exists(select * from inserted where sage not between 18 and 25) begin     print '插入的sage值不在18到25之间！'     rollback transaction end go

3、创建一个名为tri_Update_SC的触发器，要求：（1）首先判断数据库中是否已经存在名为tri_Update_SC的触发器，如果存在，首先删除，再创建。（2）当试图修改SC表中的学生成绩时，给出不能随便修改成绩的信息提示。

if exists(select name from sysobjects where name='tri_Update_SC' and type='TR') drop trigger tri_Update_SC go create trigger tri_Update_SC on SC after update as if UPDATE(score) begin print '不能修改考试成绩！' rollback transaction end go

4、使用系统存储过程sp_help，sp_helptext，sp_helptrigger查看触发器相关信息。给出显示结果。

exec sp_helptrigger 'SC' exec sp_helptext 'tri_Update_SC' exec sp_help 'tri_Update_SC' go

5、禁用tri_Update_SC触发器，并演示禁用该触发器后Update的执行情况。

alter table sc disable trigger tri_Update_SC go

6、在SC表上创建一个instead of类型的触发器，触发器名称为：tri_Insert_SC。实现：当向表SC插入记录时检查分数的合理性，分数必须在0到100之间，如果不合理就拒绝插入，并给出信息提示，

create trigger tri_Insert_SC on SC instead of insert as if not exists(select * from inserted where score not between 0 and 100)     insert into sc select * from inserted else     print '分数必须在到内！' go

7、创建一个名为tri_Update_S的触发器，实现：当学生基本表S中的学号Sno发生变化时，SC选课记录中对应的学号也发生改变。并通过测试数据验证该触发器的执行情况。

create trigger tri_Update_S on s after update as if UPDATE(sno) begin     declare @old_sno varchar(50),@new_sno varchar(50)     select @old_sno=sno from deleted     select @new_sno=sno from inserted     update sc set sno=@new_sno where sno=@old_sno end go

8、创建一个名为tri_Delete_C1 的触发器，实现：删除一门课程时候，首先判断该课程有否有人选，如果有人选，则不能删除并通过测试数据验证该触发器的执行情况。

create trigger tri_Delete_C1 on C instead of delete as if exists(select * from sc where cno in (select cno from deleted))     print '有学生选择此课程，不能删除！' else     delete from C where cno in (select cno from deleted) go