数据库 实验五 数据库的完整性约束与触发器实验

–1、创建、删除约束
–在选修数据库中，以Student(sno,sname,ssex,sage,sdept)，Course(cno,cname,cpno,credit)，SC(sno,cno,grade)表为基础完成下列完整性约束。
–（1）为数据库表Course创建check约束：当插入或修改一个记录时，确保学分为1-8之间。

alter table course add constraint c1 check(credit between 1 and 8 );

–（2）为数据库表Student创建check约束：当插入或修改一个记录时，确保女学生年龄<30，确保男学生年龄<40。

alter table student add constraint s1 check((ssex='男' and sage<40) or (ssex='女' and sage<30) );

–（3）为数据库表SC创建check约束：当插入或修改一个记录时，确保成绩为0-100之间。

alter table sc add constraint sc1 check(grade between 0 and 100);

–（4）为数据库表SC中sno，cno创建外键约束。

alter table sc add constraint fk_sc_sno foreign key(sno) references student (sno) ;
alter table sc add constraint fk_sc_cno foreign key(cno) references course (cno) ;

–2、创建、删除触发器
–在选修数据库中，以Student(sno,sname,ssex,sage,sdept)，Course(cno,cname,cpno,credit)，SC(sno,cno,grade)表为基础完成下列触发器。
–（1）为数据库表SC创建一个触发器：当插入或修改一个记录时，确保此记录的成绩为0-100之间。

create trigger tr_sc1   on sc for insert,update as
	declare @sc_grade tinyint 
	select @sc_grade=grade FROM inserted
	IF @sc_grade not between 0 and 100
	BEGIN
		PRINT '成绩超出0~100！请重新输入。'
		Rollback Transaction
	END

–（2）为数据库表Student创建一个触发器：当插入或修改一个记录时，确保女学生年龄<30，确保男学生年龄<40。

create trigger tr_s   on student for insert,update as
	declare @s_age int ,@s_sex char 
	select @s_age=sage FROM inserted
	select @s_sex=ssex FROM inserted
	IF  (@s_sex='女' and @s_age<30) or (@s_sex='男' and @s_age<40)
	commit
	else
	BEGIN
		PRINT '数据不合法.'
		rollback transaction
	END

–（3）为数据库表Student、SC、Course创建参照完整性，级联删除和级联修改触发器。

//级联删除

create trigger DC_SC
on course for delete
as 
declare @cno_del char
select @cno_del=cno from deleted
delete from sc where cno=@cno_del 

create trigger DS_SC
on student for delete
as 
declare @sno_del char
select @sno_del=sno from deleted
delete from sc where sno=@sno_del 

//级联更新

create trigger UC_SC
on course for update 
as
declare @cno_old char,@cno_new char
select @cno_old=cno from deleted
select @cno_new=cno from inserted
update sc set cno=@cno_new where cno=@cno_old

create trigger US_SC
on student for update 
as
declare @sno_old char,@sno_new char
select @sno_old=sno from deleted
select @sno_new=sno from inserted
update sc set sno=@sno_new where sno=@sno_old

有大佬发现错误可以指点指点我