数据库实验七

--1.	将stu数据库中student表的sno定义为主键；
alter table student
add constraint pk_sno primary key(sno)

--2.	将数据库stu的表course的cno字段定义为主键，约束名称为cno_pk;
alter table course
add constraint cno_pk primary key(cno)

--3.	为表course中的字段cname添加唯一值约束；
alter table course
add constraint uq_cname unique(cname)

--4.	将数据库stu的表sc的sno及cno字段组合定义为主键，约束名称为sc_pk;
alter table sc
add constraint sc_pk primary key(Sno,Cno)


--5.	对于数据表sc的sno、cno字段定义为外码，使之与表student的主码sno及表course的主码cno对应，实现如下参照完整性：
--1)	删除student表中记录的同时删除sc表中与该记录sno字段值相同的记录；
--2)	修改student表某记录的sno时，若sc表中与该字段值对应的有若干条记录，则拒绝修改；
--3)	修改course表cno字段值时，该字段在sc表中的对应值也应修改；
--4)	删除course表一条记录时，若该字段在在sc表中存在，则删除该字段对应的记录；
--5)	向sc表添加记录时，如果该记录的sno字段的值在student中不存在，则拒绝插入；
alter table sc
add constraint fk_sno_student foreign key (Sno)references student(Sno) on delete cascade on update no action

alter table sc
add constraint fk_cno_course foreign key (Cno)references course(Cno)on delete cascade on update cascade

delete from student
where Sno='200515017'

--6.	定义check约束，要求学生学号sno必须为9位数字字符，且不能以0开头，第二三位皆为0；
alter table student
with nocheck
add constraint ck_sno
check ([Sno] like '[1-9]00%s' and len(Sno)=9)

--7.	定义stu数据库中student表中学生年龄值在16-25范围内；
alter table student
with nocheck
add constraint ck_age
check (16<=Sage and Sage<=25)

--8.	定义stu数据库中student表中学生姓名长度在2-8之间；
alter table student
with nocheck
add constraint ck_name
check (len(Sname)<=8 and len(Sname)>=2)


--9.	定义stu数据库中student表中学生性别列中只能输入“男”或“女”；
alter table student
with nocheck
add constraint ck_sex
check (Ssex in ('男','女'))


--10.	定义stu数据库student表中学生年龄值默认值为20；
alter table student
add constraint df_age default '20'
for Sage


--11.	修改student表学生的年龄值约束可以为15-30范围内；
alter table student 
drop ck_age 

alter table student 
with nocheck
add constraint ck_age
check (15<=Sage and Sage<=30)

--12.	删除上述唯一值约束、外键约束及check约束；
alter table student
drop constraint ck_age,ck_sex,ck_sno,ck_name

alter table course
drop constraint uq_cname

alter table sc
drop constraint fk_sno_student,fk_cno_course


--13.向sc表中插入或修改一条记录时，通过触发器检查记录学号字段的值在student表中是否存在，同时还要检查课程号的值是否存在，若不存在，
--则取消插入或修改，否则插入成功；执行对sc的插入、修改操作，验证触发器的执行。
create trigger ck_scno
on sc
for insert,update
as
if not exists(select *
	from student
	where Sno=(
		select Sno
		from inserted
	)
  ) or not exists(select *
	from course
	where Cno=(
		select Cno
		from  inserted
	)
  )
rollback transaction



--14. 在student表中增加一列total,表示学生的总学分，初始值为0。定义一个触发器，实现有关学分的完整性约束：
--当向SC表插入一行选课记录时，若成绩大于等于60分，自动将该课程的学分累加到学生的总学分中。
alter table student
add total float

create trigger tr_xf
on sc
for insert
as
if((select Grade from inserted)>60)
begin
update student
set total=total+(select Ccredit from inserted,course where inserted.Cno=course.Cno)
where Sno=(select Sno from inserted)
end

insert sc
values('200515001','10','99')

--15.设计一触发器，约束数据库系统课程的课容量为120。
create trigger tr_db
on sc
for insert
as
if((select count(*) from sc,course where sc.Cno=course.Cno and Cname='数据库系统')>120)
rollback transaction

--16．设有两个表：商品库存表（商品编号，商品名称，库存数量，库存单价，库存金额）；
--商品销售表（商品编号，商品名称，购货商号，销售数量，销售单价，销售金额）；
--设计一触发器实现如下业务规则：
--（1）保证在商品库存表中插入的数据，库存金额 = 库存数量 * 库存单价。
--（2）如果销售的商品不存在库存或者库存为零，则返回提示信息。否则自动减少商品库存表中对应商品的库存数量和库存金额。
--建表并验证触发器的执行。
create table store(
	商品编号 char(10),
	商品名称 char(10),
	库存数量 int,
	库存单价 float,
	库存金额 float
)

create table sale(
	商品编号 char(10),
	商品名称 char(10),
	购货商号 char(10),
	销售数量 int,
	销售单价 float,
	销售金额 float
)

create trigger tr_st
on store
for insert
as
if (((select 库存数量 from inserted)*(select 库存单价 from inserted))!=(select 库存金额 from inserted))
rollback transaction

insert store
values('1','a','1','2','3')
insert store
values('1','a','1','2','2')

create trigger tr_sa
on sale
for insert
as
if ((select 库存数量 from store where store.商品编号=(select 商品编号 from inserted))=0)
begin
	print '库存不足'
	rollback transaction
end
else
begin
update store
set 库存数量=库存数量-(select 销售数量 from inserted)
where store.商品编号=(select 商品编号 from inserted)
update store
set 库存金额=库存数量*库存单价
where store.商品编号=(select 商品编号 from inserted)
end

insert sale
values('1','a','1','1','1','1')


--17．选做题：设计实例，验证after触发器与instead of触发器的异同。
--向student表插入数据时，检查学号是否存在于student表中，如不存在则进行插入操作，否则就不插入。
create trigger tr_test1
on student
instead of insert
as
if not exists(select * from student where Sno=(select Sno from inserted))
  begin
	insert into student select * from inserted
    print '成功插入！'
  end
else
  begin
     rollback transaction
     print '插入失败！'
  end

create trigger tr_test2
on student
for insert
as
if exists(select * from student where Sno=(select Sno from inserted))
	begin
     rollback transaction
     print '插入失败！'
  end