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

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

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

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

4、legrowth=1MB ); use test; -要求为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), n

5、ame 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 Teacher where userid = course_id) begin deletefrom Course where userid=course_id end; -验证插入这个字段的值不

6、存在于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) values (王五,张立,财务部,29); insert into Course(courseid,userid,name,grade) values (5,王五,刘晨,85); select *

7、from Course; -拓展训练： -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(4) primary key,Cname char(20),Cpno char(4),Ccredit int, foreign key (Cpno) r

8、eferences 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(Sno,Sname,Ssex,Sage,Sdept) Select 200215121,李勇,男,20,CS Union Select 20021512

9、2,刘晨,女,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,数据处理,NULL,2); insert into course1(Cno,Cname,Cpno,Ccredit) Select 4,操作系统,6,3 Union S

10、elect 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 200215122,2,90 Union Select 200215122,3,80; -(1)创建一个after触发器，在Student表中删除某学生

11、的记录时，删除其相应的选课记录 (不允许使用外键级联删除约束)。 -单个删除： -方法一： 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 -方法二： create trigger StudentTrigger on Student for delete as declare sno_id char(10)

12、 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 where Sno in (select Sno from deleted) ) begin delete from SC where Sno in

13、(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 table Student disable trigger StudentTrigger delete from Student where

14、Sno=200215121; select * from SC; select * from Student; -(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; select * from Course1; -禁用触发器后再验

15、证: alter table Course1 disable trigger Course1Trigger; delete from Course1 where Cno=3; select * from Course1; -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

16、 (number) ); -(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 tab

17、le worker 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的记录

18、Rollback 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

19、print 不能更改编号 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)为wor

20、ker表建立触发器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; insert into worker(number,name,sex,sage,department) values (0007,王旺,男,-9,人事部); select * from worker;