实验六触发器设计实验.docx

1、实验六触发器设计实验实验六：触发器设计实验一、实验目的：掌握触发器编写方法，能运用设计的触发器实现对数据表中数据完整性约束的控制。二、实验内容：要求为Course表编写一个名为CourseTrigger插入触发器，当插入记录到Course表时，该触发器检查新插入记录的userid字段，如果插入这个字段的值不存在于Teacher表中，则从Course表中删除这个新插入记录。Teacher(userid char(20), name char(20),department char(20), age)Course(courseid int, userid char(20), name char(2

2、0), grade int)说明：1对表Teacher和Course之间不允许建立外键约束；2本实验属于设计型实验，要求学生设计一个符合实验内容要求的触发器。拓展训练：1.在Student、Course和SC表中创建以下触发器(1)创建一个after触发器，在Student表中删除某学生的记录时，删除其相应的选课记录 (不允许使用外键级联删除约束)。(2)创建一个触发器，不允许删除Course表中的任何数据。2.利用实验五中的Work表，分别建立以下触发器：(1)为worker表建立触发器T1，当插入或更新表中的数据时，保证所操作的记录的sage值大于0。(2)为worker表建立触发器T2，

3、禁止删除编号为“0001”的职工。(3)worker表中的人员的编号是唯一且不可改变的，为worker表建立触发器T3实现更新中编号的不可改变性。(4)为worker表建立触发器T4，要求插入记录的sage值必须比表中已记录的最大sage值大。三、实验学时2学时四、实验设备与环境Windows 2003平台 + SQL Server 2008系统-建立test数据库create database teston(name=student, filename=d:dbstudent.MDF, size=10MB, maxsize=50MB, filegrowth=1MB );use test;-要

4、求为Course表编写一个名为CourseTrigger插入触发器，当插入记录到Course表时，该触发器检查-新插入记录的userid字段，如果插入这个字段的值不存在于Teacher表中，则从Course表中删除这个新插入记录。-先建立Teacher、Course表create table Teacher(userid char(20), name char(20),department char(20), age int);create table Course(courseid int, userid char(20), name char(20), grade int);-建立Cour

5、seTrigger插入触发器create trigger CourseTrigger on Coursefor insert asdeclare course_id char(20)set course_id = (select userid from inserted) if not exists(select * from Teacher where userid = course_id) begin delete from Course where userid=course_idend;-验证（1）插入这个字段的值不存在于Teacher表中：insert into Course(cou

6、rseid,userid,name,grade)values (4,李勇,张三,92);select *from Teacher;select *from Course;-验证（2）插入这个字段的值存在于Teacher表中：insert into Teacher(userid,name,department,age)values (王五,张立,财务部,29);insert into Course(courseid,userid,name,grade)values (5,王五,刘晨,85);select *from Course;-拓展训练：-1.在Student、Course1和SC表中创建以

7、下触发器-建立student表create table student (Sno char(10) primary key, Sname char(10) unique, Ssex char(2), Sage int , Sdept varchar(10) );-建立Course1表create table Course1 (Cno char(4) primary key, Cname char(20), Cpno char(4), Ccredit int, foreign key (Cpno) references Course1(Cno) );-建立SC表create table SC (

8、Sno char(10), Cno char(4), Grade int , -primary key(Sno,Cno), -foreign key(Sno)references student(Sno) -foreign key(Cno)references Course1(Cno) );-添加student表中数据insert into student(Sno,Sname,Ssex,Sage,Sdept)Select 200215121,李勇,男,20,CSUnion Select 200215122,刘晨,女,19,CSUnion Select 200215123,王敏,女,18,MAU

9、nion Select 200215125,张立,男,19,IS;-添加Course1表中数据insertinto course1(Cno,Cname,Cpno,Ccredit)values (2,数学,NULL,2);insertinto course1(Cno,Cname,Cpno,Ccredit)values (6,数据处理,NULL,2);insertinto course1(Cno,Cname,Cpno,Ccredit)Select 4,操作系统,6,3Union Select 7,Pascal语言,6,4Union Select 5,数据结构,7,4Union Select 1,数

10、据库,5,4Union Select 3,信息系统,1,4;-添加SC表中数据insertinto SC(Sno,Cno,Grade)Select 200215121,1,92Union Select 200215121,2,85Union Select 200215121,3,88Union Select 200215122,2,90Union Select 200215122,3,80;-(1)创建一个after触发器，在Student表中删除某学生的记录时，删除其相应的选课记录 (不允许使用外键级联删除约束)。-单个删除：-方法一：create trigger studelete on

11、studentfor deleteasdeclare sno_id char(10)select sno_id = sno from deletedif sno_id is not null begin delete from sc where sno=sno_id end-方法二：create trigger StudentTrigger on Studentfor deleteasdeclare sno_id char(10)set sno_id=(select Sno from deleted )if exists(select * from SC where Sno=sno_id) b

12、egin delete from SC where Sno=sno_id end-单个删除及全部删除：-方法一：create trigger StudentTrigger on Studentfor deleteasif exists(select * from SC where Sno in (select Sno from deleted) ) begin delete from SC where Sno in(select Sno from deleted) end-方法二：create trigger trdstudenton studentfor deleteasdelete SCf

13、rom SC s,deleted dwhere s.sno=d.sno-验证:deletefrom Studentwhere Sno=200215122;select *from SC;select *from Student;-禁用触发器后再验证: alter table Student disable trigger StudentTrigger deletefrom Studentwhere Sno=200215121;select *from SC;select *from Student;-(2)创建一个触发器，不允许删除Course1表中的任何数据。create trigger C

14、ourse1Trigger on Course1for deleteasif(select count(*) from deleted)0 begin print(你不能删除该表中的任何记录) rollback transactionend-验证:deletefrom Course1where Cno=2;select *from Course1;-禁用触发器后再验证:alter table Course1 disable trigger Course1Trigger;deletefrom Course1where Cno=3;select *from Course1;-2.利用实验五中的Wo

15、rker表，分别建立以下触发器：-创建表workercreate table worker(number char(5), name char(8) constraint U1 unique, sex char(2), sage int constraint U2 check(sage=28), department char(20) constraint PK_workr primary key (number); -(1)为worker表建立触发器T1，当插入或更新表中的数据时，保证所操作的记录的sage值大于0。create trigger T1 on worker for insert

16、,updateASIf (select sage from inserted )1beginprint 年龄sage必须大于0,插入或更新失败Rollback transaction End-验证:insertinto worker(number,name,sex,sage,department)values (0001,张道,男,-2,财务部)-禁用触发器后再验证:alter table worker disable trigger T1insertinto worker(number,name,sex,sage,department)values (0001,张道,男,-2,财务部)sel

17、ect *from worker;-(2)为worker表建立触发器T2，禁止删除编号为“0001”的职工。create trigger T2 on worker for deleteAS-declare number_id char(5)If (select number from deleted )=0001beginprint 不能删除编号0001的记录Rollback transaction End-验证:deletefrom workerwhere number=0001-禁用触发器后再验证:alter table worker disable trigger T2deletefro

18、m workerwhere number=0001select *from worker;-(3)worker表中的人员的编号是唯一且不可改变的，为worker表建立触发器T3实现更新中编号的不可改变性。create trigger T3 on worker for updateASif update(number)beginprint 不能更改编号Rollback transaction End-验证:insertinto worker(number,name,sex,sage,department)values (0001,张道,男,-2,财务部);update workerset num

19、ber=0002where number=0001;-禁用触发器后再验证:alter table worker disable trigger T3update workerset number=0002where number=0001;select *from worker;-(4)为worker表建立触发器T4，要求插入记录的sage值必须比表中已记录的最大sage值大。Create trigger T4 on workerFor insertASIF(SELECT sage from inserted)=(select max(sage) from worker)Beginprint插入记录的sage值必须比表中已记录的最大sage值大Rollback TransactionEnd-验证:insertinto worker(number,name,sex,sage,department)values (0007,王旺,男,-9,人事部);-禁用触发器后再验证:alter table worker disable trigger T4;insertinto worker(number,name,sex,sage,department)values (0007,王旺,男,-9,人事部);select *from worker;