实验六触发器设计实验.docx
《实验六触发器设计实验.docx》由会员分享，可在线阅读，更多相关《实验六触发器设计实验.docx（11页珍藏版）》请在冰豆网上搜索。
实验六触发器设计实验
实验六：
触发器设计实验
一、实验目的：
掌握触发器编写方法，能运用设计的触发器实现对数据表中数据完整性约束的控制。
二、实验内容：
要求为Course表编写一个名为CourseTrigger插入触发器，当插入记录到Course表时，该触发器检查新插入记录的userid字段，如果插入这个字段的值不存在于Teacher表中，则从Course表中删除这个新插入记录。
Teacher（useridchar（20）,namechar（20）,departmentchar（20）,age）
Course（courseidint,useridchar（20）,namechar（20）,gradeint）
说明：
1．对表Teacher和Course之间不允许建立外键约束；
2．本实验属于设计型实验，要求学生设计一个符合实验内容要求的触发器。
拓展训练：
1.在Student、Course和SC表中创建以下触发器
（1）创建一个after触发器，在Student表中删除某学生的记录时，删除其相应的选课记录（不允许使用外键级联删除约束）。
（2）创建一个触发器，不允许删除Course表中的任何数据。
2.利用实验五中的Work表，分别建立以下触发器：
（1）为worker表建立触发器T1，当插入或更新表中的数据时，保证所操作的记录的sage值大于0。
（2）为worker表建立触发器T2，禁止删除编号为“0001”的职工。
（3）worker表中的人员的编号是唯一且不可改变的，为worker表建立触发器T3实现更新中编号的不可改变性。
（4）为worker表建立触发器T4，要求插入记录的sage值必须比表中已记录的最大sage值大。
三、实验学时
2学时
四、实验设备与环境
Windows2003平台+SQLServer2008系统
--建立test数据库
createdatabasetest
on
（name=student,
filename='d:
\db\student.MDF',
size=10MB,
maxsize=50MB,
filegrowth=1MB
）;
usetest;
--要求为Course表编写一个名为CourseTrigger插入触发器，当插入记录到Course表时，该触发器检查
--新插入记录的userid字段，如果插入这个字段的值不存在于Teacher表中，则从Course表中删除这个新插入记录。
--先建立Teacher、Course表
createtableTeacher（useridchar（20）,namechar（20）,departmentchar（20）,ageint）;
createtableCourse（courseidint,useridchar（20）,namechar（20）,gradeint）;
--建立CourseTrigger插入触发器
createtriggerCourseTriggeronCourse
forinsert
as
declare@course_idchar（20）
set@course_id=（selectuseridfrominserted）
ifnotexists（select*fromTeacherwhereuserid=@course_id）
begin
delete
fromCourse
whereuserid=@course_id
end;
--验证
（1）插入这个字段的值不存在于Teacher表中：
insert
intoCourse（courseid,userid,name,grade）
values（4,'李勇','张三',92）;
select*
fromTeacher;
select*
fromCourse;
--验证
（2）插入这个字段的值存在于Teacher表中：
insert
intoTeacher（userid,name,department,age）
values（'王五','张立','财务部',29）;
insert
intoCourse（courseid,userid,name,grade）
values（5,'王五','刘晨',85）;
select*
fromCourse;
--拓展训练：
--1.在Student、Course1和SC表中创建以下触发器
--建立student表
createtablestudent
（Snochar（10）primarykey,
Snamechar（10）unique,
Ssexchar
（2）,
Sageint,
Sdeptvarchar（10）
）;
--建立Course1表
createtableCourse1
（Cnochar（4）primarykey,
Cnamechar（20）,
Cpnochar（4）,
Ccreditint,
foreignkey（Cpno）referencesCourse1（Cno）
）;
--建立SC表
createtableSC
（Snochar（10）,
Cnochar（4）,
Gradeint,
--primarykey（Sno,Cno）,
--foreignkey（Sno）referencesstudent（Sno）
--foreignkey（Cno）referencesCourse1（Cno）
）;
--添加student表中数据
insert
intostudent（Sno,Sname,Ssex,Sage,Sdept）
Select'200215121','李勇','男',20,'CS'
UnionSelect'200215122','刘晨','女',19,'CS'
UnionSelect'200215123','王敏','女',18,'MA'
UnionSelect'200215125','张立','男',19,'IS';
--添加Course1表中数据
insert
intocourse1（Cno,Cname,Cpno,Ccredit）
values（'2','数学',NULL,2）;
insert
intocourse1（Cno,Cname,Cpno,Ccredit）
values（'6','数据处理',NULL,2）;
insert
intocourse1（Cno,Cname,Cpno,Ccredit）
Select'4','操作系统','6',3
UnionSelect'7','Pascal语言','6',4
UnionSelect'5','数据结构','7',4
UnionSelect'1','数据库','5',4
UnionSelect'3','信息系统','1',4;
--添加SC表中数据
insert
intoSC（Sno,Cno,Grade）
Select'200215121','1',92
UnionSelect'200215121','2',85
UnionSelect'200215121','3',88
UnionSelect'200215122','2',90
UnionSelect'200215122','3',80;
--
（1）创建一个after触发器，在Student表中删除某学生的记录时，删除其相应的选课记录（不允许使用外键级联删除约束）。
--单个删除：
--方法一：
createtriggerstudeleteonstudent
fordelete
as
declare@sno_idchar（10）
select@sno_id=snofromdeleted
if@sno_idisnotnull
begin
delete
fromsc
wheresno=@sno_id
end
--方法二：
createtriggerStudentTriggeronStudent
fordelete
as
declare@sno_idchar（10）
set@sno_id=（selectSnofromdeleted）
ifexists（select*fromSCwhereSno=@sno_id）
begin
delete
fromSC
whereSno=@sno_id
end
--单个删除及全部删除：
--方法一：
createtriggerStudentTriggeronStudent
fordelete
as
ifexists（select*fromSCwhereSnoin（selectSnofromdeleted））
begin
delete
fromSC
whereSnoin（selectSnofromdeleted）
end
--方法二：
createtriggertrdstudent
onstudent
fordelete
as
deleteSC
fromSCs,deletedd
wheres.sno=d.sno
--验证:
delete
fromStudent
whereSno='200215122';
select*
fromSC;
select*
fromStudent;
--禁用触发器后再验证:
altertableStudentdisabletriggerStudentTrigger
delete
fromStudent
whereSno='200215121';
select*
fromSC;
select*
fromStudent;
--
（2）创建一个触发器，不允许删除Course1表中的任何数据。
createtriggerCourse1TriggeronCourse1
fordelete
as
if（selectcount（*）fromdeleted）>0
begin
print（'你不能删除该表中的任何记录'）
rollbacktransaction
end
--验证:
delete
fromCourse1
whereCno='2';
select*
fromCourse1;
--禁用触发器后再验证:
altertableCourse1disabletriggerCourse1Trigger;
delete
fromCourse1
whereCno='3';
select*
fromCourse1;
--2.利用实验五中的Worker表，分别建立以下触发器：
--创建表worker
createtableworker
（numberchar（5）,
namechar（8）constraintU1unique,
sexchar
（2）,
sageintconstraintU2check（sage<=28）,
departmentchar（20）
constraintPK_workrprimarykey（number）
）;
--
（1）为worker表建立触发器T1，当插入或更新表中的数据时，保证所操作的记录的sage值大于0。
createtriggerT1onworkerforinsert,update
AS
If（selectsagefrominserted）<1
begin
print'年龄sage必须大于0,插入或更新失败'
Rollbacktransaction
End
--验证:
insert
intoworker（number,name,sex,sage,department）
values（'0001','张道','男',-2,'财务部'）
--禁用触发器后再验证:
altertableworkerdisabletriggerT1
insert
intoworker（number,name,sex,sage,department）
values（'0001','张道','男',-2,'财务部'）
select*
fromworker;
--
（2）为worker表建立触发器T2，禁止删除编号为“0001”的职工。
createtriggerT2onworkerfordelete
AS
--declare@number_idchar（5）
If（selectnumberfromdeleted）='0001'
begin
print'不能删除编号0001的记录'
Rollbacktransaction
End
--验证:
delete
fromworker
wherenumber='0001'
--禁用触发器后再验证:
altertableworkerdisabletriggerT2
delete
fromworker
wherenumber='0001'
select*
fromworker;
--（3）worker表中的人员的编号是唯一且不可改变的，为worker表建立触发器T3实现更新中编号的不可改变性。
createtriggerT3onworkerforupdate
AS
ifupdate（number）
begin
print'不能更改编号'
Rollbacktransaction
End
--验证:
insert
intoworker（number,name,sex,sage,department）
values（'0001','张道','男',-2,'财务部'）;
updateworker
setnumber='0002'
wherenumber='0001';
--禁用触发器后再验证:
altertableworkerdisabletriggerT3
updateworker
setnumber='0002'
wherenumber='0001';
select*
fromworker;
--（4）为worker表建立触发器T4，要求插入记录的sage值必须比表中已记录的最大sage值大。
CreatetriggerT4onworker
Forinsert
AS
IF（SELECTsagefrominserted）<=（selectmax（sage）fromworker）
Begin
print'插入记录的sage值必须比表中已记录的最大sage值大'
RollbackTransaction
End
--验证:
insert
intoworker（number,name,sex,sage,department）
values（'0007','王旺','男',-9,'人事部'）;
--禁用触发器后再验证:
altertableworkerdisabletriggerT4;
insert
intoworker（number,name,sex,sage,department）
values（'0007','王旺','男',-9,'人事部'）;
select*
fromworker;
升级会员 