operate.sql

一个经典的东东

--创建视图student_class_view便于查看student的班级信息
if exists(select * from sysobjects where type='v' and name='student_class_view')
	drop view student_class_view
go
create view student_class_view as
select StudentId,StudentNO,StudentName,Sex=case
		when Gender=0 then '男'
		else '女'
	end
,Birthday,classname,student.remark from student,class where student.classid=class.classid
go
--创建视图student_course_view便于查看student的课程信息
if exists(select * from sysobjects where type='v' and name='student_course_view')
	drop view student_course_view
go
create view student_course_view as
select student.StudentId,student.StudentNO
,student.StudentName
,Sex=case
	when Gender=0 then '男'
	else '女'
	end
,student.Birthday,class.classname,student.remark,course_view.subjectname
 from class right join student on class.classid=student.classid left join course_view 
 on class.classname=course_view.classname
go
--创建视图course_view便于查看course的信息
if exists(select * from sysobjects where type='v' and name='course_view')
	drop view course_view
go
create view course_view as
select courseid,classname,subjectname,begindate,finishdate,
subject.remark from course,class,subject where class.classid=course.classid 
and subject.subjectid=course.subjectid 
with check Option
go
--创建视图s1subject_view查看s1学期的所有科目
if exists(select * from sysobjects where type='v' and name='s1subject_view')
	drop view s1subject_view
go
create view s1subject_view as
select * from subject where remark like '%'+'s1'+'%'
go
--创建视图s1subject_view查看s2学期的所有科目
if exists(select * from sysobjects where type='v' and name='s2subject_view')
	drop view s2subject_view
go
create view s2subject_view as
select * from subject where remark like '%'+'s2'+'%'
go
--创建视图s1subject_view查看y2学期的所有科目
if exists(select * from sysobjects where type='v' and name='y2subject_view')
	drop view y2subject_view
go
create view y2subject_view as
select * from subject where remark like '%'+'y2'+'%'
go
--创建视图Score_View查看Score的所有学生考试的所有科目情况
if exists(select * from sysobjects where type='v' and name='Score_View')
	drop view Score_View
go
create view Score_View as
select ScoreID,StudentName,subjectname,Score from Score left join 
student on student.studentid=score.studentid left join course on 
course.courseid=score.courseid left join subject on 
course.subjectid=subject.subjectid
go
--创建视图Score_View_Class查看Score中按class分类的情况
if exists(select * from sysobjects where type='v' and name='Score_View_Class')
	drop view Score_View_Class
go
create view Score_View_Class as
select Distinct(score_view.scoreid),score_view.studentname,score_view.subjectname,
score_view.score,student_course_view.classname 
from score_view left join student_course_view 
on score_view.studentname=student_course_view.studentname
go



--为course创建insert触发器,约束不能插入classid和subjectid都相同的值
if exists(select * from sysobjects where type='tr' and name='course_insert_trigger')
	drop trigger course_insert_trigger
go
create trigger course_insert_trigger
	on course
	for insert
	as
declare @classid int,@subjectid int,@courseid int
select @courseid=courseid,@classid=classid,@subjectid=subjectid from inserted
declare @count int	--记录数据库表内存在刚插入记录的条数
select @count=count(*) from course where classid=@classid and subjectid=@subjectid
if(@count>=2)
	begin
	raiserror('插入失败',16,1)
	rollback transaction
	end
go
--为course创建update触发器,约束不能将原数据更新为已有的classid和subjectid都相同的值
if exists(select * from sysobjects where type='tr' and name='course_update_trigger')
	drop trigger course_update_trigger
go
create trigger course_update_trigger
	on course
	for update
	as
declare @classid int,@subjectid int
select @classid=classid,@subjectid=subjectid from inserted
declare @count int	--记录数据库表内存在刚更新记录的条数
select @count=count(*) from course where classid=@classid and subjectid=@subjectid
if(@count>=2)
	begin
	raiserror ('更新失败',16,1)
	rollback transaction
	end
go



--score与student表之间的级联外键的建立
--为student创建delete触发器，级联删除score表中数据
if exists(select * from sysobjects where type='tr' and name='student_delete_trigger')
	drop trigger student_delete_trigger
go
create trigger student_delete_trigger
	on student
	for delete
	as
declare @studentid int
select @studentid=studentid from deleted
delete score where studentid=@studentid
go
--为Score的StudentId创建insert触发器,如果要插入学生成绩的学生ID，不存在student中，则禁止插入
if exists(select * from sysobjects where type='tr' and name='student_insert_studentid_trigger')
	drop trigger student_insert_studentid_trigger
go
create trigger student_insert_studentid_trigger
	on score
	for insert
	as
declare @studentid int
select @studentid=studentid from inserted
if not exists(select * from student where studentid=@studentid)
	begin
	raiserror('插入失败',16,1)
	rollback transaction
	end
else
	print '插入成功'
go



--class与student表之间的级联外键的建立
--为班级表（class）建立触发器级联删除student表中的存在要删除的classid的值
if exists(select * from sysobjects where type='tr' and name='class_student_delete_trigger')
	drop trigger class_student_delete_trigger
go
create trigger class_student_delete_trigger
	on class
	for delete
	as
declare @classid int
select @classid=classid from deleted
delete student where classid=@classid
go
--为student表建立触发器，当向student插入数据的时候，如果该数据的classid不存在class中，则插入失败
if exists(select * from sysobjects where type='tr' and name='student_course_insert_trigger')
	drop trigger student_course_insert_trigger
go
create trigger student_course_insert_trigger
	on student
	for insert
	as
declare @classid int
select @classid=classid from inserted
if not exists(select * from class where classid=@classid)
	begin
	raiserror('插入失败',16,1)
	rollback transaction
	end
else
	print '插入成功'
go



--class与student表之间的级联外键的建立
--为班级表（class）建立触发器级联删除course表中存在要删除的classid的值
if exists(select * from sysobjects where type='tr' and name='class_course_delete_trigger')
	drop trigger class_course_delete_trigger
go
create trigger class_course_delete_trigger
	on class
	for delete
	as
declare @classid int
select @classid=classid from deleted
delete course where classid=@classid
go
--为course表建立触发器，当向course插入数据的时候，如果该数据的classid不存在class中，则插入失败
if exists(select * from sysobjects where type='u' and name='course_class_insert_trigger')
	drop trigger course_class_insert_trigger
go
create trigger course_class_insert_trigger
	on course
	for insert
	as
declare @classid int
select @classid=classid from inserted
if not exists(select * from class where classid=@classid)
	begin
	raiserror('插入失败',16,1)
	rollback transaction
	end
else
	print '插入成功'
go


--score与course表之间的级联外键的建立
--为course建立触发器级联删除score表中存在要删除的courseid的值
if exists(select * from sysobjects where type='tr' and name='course_score_delete_trigger')
	drop trigger course_score_delete_trigger
go
create trigger course_score_delete_trigger
	on course
	for delete
	as
declare @courseid int
select @courseid=courseid from deleted
delete score where courseid=@courseid
go
--为score表建立触发器，当向score插入数据的时候，如果该数据的courseid不存在course中，则插入失败
if exists(select * from sysobjects where type='u' and name='score_course_insert_trigger')
	drop trigger score_course_insert_trigger
go
create trigger score_course_insert_trigger
	on score
	for insert
	as
declare @courseid int
select @courseid=courseid from inserted
if not exists(select * from course where courseid=@courseid)
	begin
	raiserror('插入失败',16,1)
	rollback transaction
	end
else
	print '插入成功'
go



--subject与course表之间的级联外键的建立
--为subject建立触发器级联删除course表中存在要删除的subjectid的值
if exists(select * from sysobjects where type='u' and name='subject_course_delete_trigger')
	drop trigger subject_course_delete_trigger
go
create trigger subject_course_delete_trigger
	on subject
	for delete
	as
declare @subjectid int
select @subjectid=subjectid from deleted
delete course where subjectid=@subjectid
go
--为course表建立触发器，当向course插入数据的时候，如果该数据的subjectid不存在subject中，则插入失败
if exists(select * from sysobjects where type='u' and name='course_subject_insert_trigger')
	drop trigger course_subject_insert_trigger
go
create trigger course_subject_insert_trigger
	on course
	for insert
	as
declare @subjectid int
select @subjectid=subjectid from inserted
if not exists(select * from subject where subjectid=@subjectid)
	begin
	raiserror('插入失败',16,1)
	rollback transaction
	end
else
	print '插入成功'
go

--为score创建insert触发器,约束不能插入courseid和subjectid都相同的值
if exists(select * from sysobjects where type='tr' and name='score_insert_trigger')
	drop trigger score_insert_trigger
go
create trigger score_insert_trigger
	on score
	for insert
	as
declare @courseid int,@studentid int
select @courseid=courseid,@studentid=studentid from inserted
declare @count int	--记录数据库表内存在刚插入记录的条数
select @count=count(*) from score where courseid=@courseid and studentid=@studentid
if(@count>=2)
	begin
	raiserror('插入失败',16,1)
	rollback transaction
	end
go
--为course创建update触发器,约束不能将原数据更新为已有的classid和subjectid都相同的值
if exists(select * from sysobjects where type='tr' and name='score_update_trigger')
	drop trigger score_update_trigger
go
create trigger score_update_trigger
	on score
	for update
	as
declare @courseid int,@studentid int
select @courseid=courseid,@studentid=studentid from inserted
declare @count int	--记录数据库表内存在刚插入记录的条数
select @count=count(*) from score where courseid=@courseid and studentid=@studentid
if(@count>=2)
	begin
	raiserror('更新失败',16,1)
	rollback transaction
	end
go