实验六触发器设计实验.docx
《实验六触发器设计实验.docx》由会员分享，可在线阅读，更多相关《实验六触发器设计实验.docx（3页珍藏版）》请在冰豆网上搜索。
实验六触发器设计实验
实验六：
触发器设计实验
实验六：
触发器设计实验 一、实验目的：
掌握触发器编写方法，能运用设计的触发器实现对数据表中数据完整性约束的控制。
二、实验内容：
要求为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数据库createdatabaseteston （name=student, filename=‘d:
\\db\\’,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插入触发器 createtriggerCourseTriggeronCourseforinsertas declare@course_idchar（20） set@course_id=（selectuseridfrominserted） ifnotexists（select*fromTeacherwhereuserid=@course_id）begindelete fromCoursewhereuserid=@course_idend; --验证插入这个字段的值不存在于Teacher表中：
insert intoCourse（courseid,userid,name,grade）values（4,’李勇’,’张三’,92）; select* fromTeacher; select* fromCourse; --验证插入这个字段的值存在于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’,4UnionSelect‘5’,’数据结构’,’7’,4UnionSelect‘1’,’数据库’,’5’,4UnionSelect‘3’,’信息系统’,’1’,4; --添加SC表中数据 insert intoSC（Sno,Cno,Grade）Select‘200215121’,’1’,92 UnionSelect‘200215121’,’2’,85UnionSelect‘200215121’,’3’,88UnionSelect‘200215122’,’2’,90UnionSelect‘200215122’,’3’,80; --
（1）创建一个after触发器，在Student表中删除某学生的记录时，删除其相应的选课记录（不允许使用外键级联删除约束）。
--单个删除：
--方法一：
createtriggerstudeleteonstudentfordeleteas declare@sno_idchar（10） select@sno_id=snofromdeletedif@sno_idisnotnullbegindeletefromsc wheresno=@sno_idend --方法二：
createtriggerStudentTriggeronStudentfordeleteas declare@sno_idchar（10） set@sno_id=（selectSnofromdeleted） ifexists（select*fromSCwhereSno=@sno_id）begindeletefromSCwhereSno=@sno_idend --单个删除及全部删除：
--方法一：
createtriggerStudentTriggeronStudentfordeleteas ifexists（select*fromSCwhereSnoin（selectSnofromdeleted））begindeletefromSCwhereSnoin（selectSnofromdeleted）end --方法二：
createtriggertrdstudentonstudentfordeleteas deleteSC fromSCs,deleteddwhere= --验证:
delete fromStudent whereSno=‘200215122’; select*fromSC; select* fromStudent; --禁用触发器后再验证:
altertableStudentdisabletriggerStudentTrigger delete fromStudent whereSno=‘200215121’; select*fromSC; select* fromStudent; --
（2）创建一个触发器，不允许删除Course1表中的任何数据。
createtriggerCourse1TriggeronCourse1fordeleteas if（selectcount（*）fromdeleted）>0 begin print（‘你不能删除该表中的任何记录’） rollbacktransactionend --验证:
delete fromCourse1whereCno=‘2’; select* fromCourse1; --禁用触发器后再验证:
altertableCourse1disabletriggerCourse1Trigger; delete fromCourse1whereCno=‘3’; select* fromCourse1; --2.利用实验五中的Worker表，分别建立以下触发器：
--创建表worker createtableworker（numberchar（5）, namechar（8）constraintU1unique,sexchar
（2）, sageintconstraintU2check（sage constraintPK_workrprimarykey（number））; --
（1）为worker表建立触发器T1，当插入或更新表中的数据时，保证所操作的记录的sage值大于0。
createtriggerT1onworkerforinsert,updateAS If（selectsagefrominserted） print‘年龄sage必须大于0,插入或更新失败’ RollbacktransactionEnd --验证:
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”的职工。
createtriggerT2onworkerfordeleteAS --declare@number_idchar（5） If（selectnumberfromdeleted）=‘0001’begin print‘不能删除编号0001的记录’RollbacktransactionEnd --验证:
delete fromworker wherenumber=‘0001’ --禁用触发器后再验证:
altertableworkerdisabletriggerT2 delete fromworker wherenumber=‘0001’ select* fromworker; --（3）worker表中的人员的编号是唯一且不可改变的，为worker表建立触发器T3实现更新中编号的不可改变性。
createtriggerT3onworkerforupdateAS ifupdate（number）begin print‘不能更改编号’RollbacktransactionEnd --验证:
insert intoworker（number,name,sex,sage,department）values（‘0001’,’张道’,’男’,-2,’财务部’）; updateworkersetnumber=‘0002’wherenumber=‘0001’; --禁用触发器后再验证:
altertableworkerdisabletriggerT3 updateworkersetnumber=‘0002’wherenumber=‘0001’; select* fromworker; --（4）为worker表建立触发器T4，要求插入记录的sage值必须比表中已记录的最大sage值大。
CreatetriggerT4onworkerForinsertAS IF（SELECTsagefrominserted） print’插入记录的sage值必须比表中已记录的最大sage值大’RollbackTransactionEnd --验证:
insert intoworker（number,name,sex,sage,department）values（‘0007’,’王旺’,’男’,-9,’人事部’）; --禁用触发器后再验证:
altertableworkerdisabletriggerT4; insert intoworker（number,name,sex,sage,department）values（‘0007’,’王旺’,’男’,-9,’人事部’）; select* fromworker;
升级会员 