1、 Teacher(userid char(20), name char(20),department char(20), age) Course(courseid int, userid char(20), name char(20), grade int) 说明: 1对表Teacher和Course之间不允许建立外键约束; 2本实验属于设计型实验,要求学生设计一个符合实验内容要求的触发器。 拓展训练: 1.在Student、Course和SC表中创建以下触发器 (1)创建一个after触发器,在Student表中删除某学生的记录时,删除其相应的选课记录 (不允许使用外键级联删除约束)。 (2
2、)创建一个触发器,不允许删除Course表中的任何数据。 2.利用实验五中的Work表,分别建立以下触发器: (1)为worker表建立触发器T1,当插入或更新表中的数据时,保证所操作的记录的sage值大于0。 (2)为worker表建立触发器T2,禁止删除编号为“0001”的职工。 (3)worker表中的人员的编号是唯一且不可改变的,为worker表建立触发器T3实现更新中编号的不可改变性。 (4)为worker表建立触发器T4,要求插入记录的sage值必须比表中已记录的最大sage值大。 三、实验学时 2学时 四、实验设备与环境 Windows 2003平台 + SQL Server 2
3、008系统 -建立test数据库 create database test on (name=student, filename=d:db, size=10MB, maxsize=50MB, filegrowth=1MB ); use test; -要求为Course表编写一个名为CourseTrigger插入触发器,当插入记录到Course表时,该触发器检查 -新插入记录的userid字段,如果插入这个字段的值不存在于Teacher表中,则从Course表中删除这个新插入记录。 -先建立Teacher、Course表 create table Teacher(userid char(20),
4、 name char(20),department char(20), age int); create table Course(courseid int, userid char(20), name char(20), grade int); -建立CourseTrigger插入触发器 create trigger CourseTrigger on Course for insert as declare course_id char(20) set course_id = (select userid from inserted) if not exists(select * from
5、Teacher where userid = course_id) begin deletefrom Course where userid=course_id end; -验证插入这个字段的值不存在于Teacher表中: insert into Course(courseid,userid,name,grade) values (4,李勇,张三,92); select * from Teacher; select * from Course; -验证插入这个字段的值存在于Teacher表中: insert into Teacher(userid,name,department,age) va
6、lues (王五,张立,财务部,29); insert into Course(courseid,userid,name,grade) values (5,王五,刘晨,85); -拓展训练: -1.在Student、Course1和SC表中创建以下触发器 -建立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(
7、4) primary key,Cname char(20),Cpno char(4),Ccredit int, foreign key (Cpno) references Course1(Cno); -建立SC表 create table SC(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(
8、Sno,Sname,Ssex,Sage,Sdept) Select 200215121,李勇,男,20,CS Union Select 200215122,刘晨,女,19,CS Union Select 200215123,王敏,女,18,MA Union Select 200215125,张立,男,19,IS; -添加Course1表中数据 insert into course1(Cno,Cname,Cpno,Ccredit) values (2,数学,NULL,2); insert into course1(Cno,Cname,Cpno,Ccredit) values (6,数据处理,NU
9、LL,2); insert into course1(Cno,Cname,Cpno,Ccredit) Select 4,操作系统,6,3 Union Select 7,Pascal语言,6,4 Union Select 5,数据结构,7,4 Union Select 1,数据库,5,4 Union Select 3,信息系统,1,4; -添加SC表中数据 insert into SC(Sno,Cno,Grade) Select 200215121,1,92 Union Select 200215121,2,85 Union Select 200215121,3,88 Union Select
10、200215122,2,90 Union Select 200215122,3,80; -(1)创建一个after触发器,在Student表中删除某学生的记录时,删除其相应的选课记录 (不允许使用外键级联删除约束)。 -单个删除: -方法一: create trigger studelete on student for delete as declare sno_id char(10) select sno_id = sno from deleted if sno_id is not null begin delete from scwhere sno=sno_id end -方法二: cr
11、eate trigger StudentTrigger on Student for delete as declare sno_id char(10) set sno_id=(select Sno from deleted ) if exists(select * from SC where Sno=sno_id) begin delete from SC where Sno=sno_id end -单个删除及全部删除: create trigger StudentTrigger on Student for delete as if exists(select * from SC wher
12、e Sno in (select Sno from deleted) ) begin delete from SC where Sno in(select Sno from deleted) end -方法二: create trigger trdstudent on student for delete as delete SC from SC s,deleted d where = -验证: delete from Student where Sno=200215122; select * from SC; select * from Student; -禁用触发器后再验证:alter t
13、able Student disable trigger StudentTrigger delete from Student where Sno=200215121; -(2)创建一个触发器,不允许删除Course1表中的任何数据。 create trigger Course1Trigger on Course1 for delete as if(select count(*) from deleted)0 begin print(你不能删除该表中的任何记录) rollback transaction end -验证: delete from Course1 where Cno=2; sel
14、ect * from Course1; alter table Course1 disable trigger Course1Trigger; delete from Course1 where Cno=3; -2.利用实验五中的Worker表,分别建立以下触发器: -创建表worker create table worker (number char(5), name char(8) constraint U1 unique, sex char(2), sage int constraint U2 check(sageconstraint PK_workr primary key (numb
15、er) ); -(1)为worker表建立触发器T1,当插入或更新表中的数据时,保证所操作的记录的sage值大于0。 create trigger T1 on worker for insert,update AS If (select sage from inserted )print 年龄sage必须大于0,插入或更新失败 Rollback transaction End -验证: insert into worker(number,name,sex,sage,department) values (0001,张道,男,-2,财务部) -禁用触发器后再验证: alter table wor
16、ker disable trigger T1 insert into worker(number,name,sex,sage,department) values (0001,张道,男,-2,财务部) select * from worker; -(2)为worker表建立触发器T2,禁止删除编号为“0001”的职工。 create trigger T2 on worker for delete AS -declare number_id char(5) If (select number from deleted )=0001 begin print 不能删除编号0001的记录 Rollba
17、ck transaction End -验证: delete from worker where number=0001 -禁用触发器后再验证: alter table worker disable trigger T2 delete from worker where number=0001 select * from worker; -(3)worker表中的人员的编号是唯一且不可改变的,为worker表建立触发器T3实现更新中编号的不可改变性。 create trigger T3 on worker for update AS if update(number) begin print
18、不能更改编号 Rollback transaction End -验证: insert into worker(number,name,sex,sage,department) values (0001,张道,男,-2,财务部); update worker set number=0002 where number=0001; alter table worker disable trigger T3 update worker set number=0002 where number=0001; select * from worker; -(4)为worker表建立触发器T4,要求插入记录的sage值必须比表中已记录的最大sage值大。 Create trigger T4 on worker For insert AS IF(SELECT sage from inserted)print插入记录的sage值必须比表中已记录的最大sage值大 Rollback Transaction End -验证: insert into worker(number,name,sex,sage,department) values (0007,王旺,男,-9,人事部); alter table worker disable trigger T4;
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1