存储过程、触发器和事务处理
--
创建数据库 scroll dynamic
create database StudentDB
GO
-- 置此数据库为当前数据库
use StudentDB
GO
-- 创建学生表
create table student
(
SId varchar ( 20 ) primary key , -- 学生编号
SName varchar ( 20 ), -- 学生姓名
SClass varchar ( 20 ), -- 学生班级
SSex varchar ( 10 ), -- 学生性别
SScore float default ( 0 ) check (SScore >= 0 ) -- 学生平均分
)
GO
-- 创建课程表
create table class
(
EId varchar ( 20 ) primary key , -- 课程编号
EName varchar ( 20 ), -- 课程名称
ETime int check (ETime >= 0 ) -- 课程课时
)
GO
-- 创建分数表
create table score
(
SId varchar ( 20 ), -- 学生编号
EId varchar ( 20 ), -- 课程编号
EScore float , -- 课程分数
primary key (SID,EID), -- 定义主码
foreign key (SID) references student(SID) on delete cascade , -- 声明及联删除
foreign key (EID) references class(EID) on delete cascade -- 声明及联删除
)
GO
-- 创建计算平均值得触发器
create trigger trigger_avg_insert on score for insert
as
begin transaction
declare @count int
update student set SScore = ( select avg (EScore) from score where SId = ( select SId from inserted)) where SId = ( select SId from inserted)
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
-- 创建计算平均值得触发器
create trigger trigger_avg_delete on score for delete
as
begin transaction
update student set SScore = ( select avg (EScore) from score where SId = ( select SId from deleted)) where SId = ( select SId from deleted)
declare @count int
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
-- 创建计算平均值得触发器
create trigger trigger_avg_update on score for update
as
begin transaction
declare @count int
update student set SScore = ( select avg (EScore) from score where SId = ( select SId from inserted)) where SId = ( select SId from deleted)
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建查询学生信息的存储过程
create proc proc_student_select
as
begin transaction
declare @count int
select * from student
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建查找平均分存储过程
CREATE PROCEDURE proc_student_avg
(
@SID varchar ( 20 )
)
AS
begin transaction
select avg (EScore) as SAvg from score where SId = @SId
declare @count int
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建通过学号查询学生信息的存储过程
create proc proc_student_select_bySId
(
@SId varchar ( 20 )
)
as
begin transaction
declare @count int
select * from student where SId = @SId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建插入学生信息的存储过程
create proc proc_student_insert
(
@SId varchar ( 20 ),
@SName varchar ( 20 ),
@SClass varchar ( 20 ),
@SSex varchar ( 10 )
)
as
begin transaction
declare @count int
insert into student(SID,SName,SClass,SSex) values ( @SId , @SName , @SClass , @SSex )
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 删除学生信息的存储过程
create proc proc_student_delete
(
@SId varchar ( 20 )
)
as
begin transaction
declare @count int
delete from student where SId = @SId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 修改学生信息的存储过程
create proc proc_student_update
(
@SId varchar ( 20 ),
@SName varchar ( 20 ),
@SClass varchar ( 20 ),
@SSex varchar ( 10 )
)
as
begin transaction
declare @count int
update student set SName = @SName ,SClass = @SClass ,SSex = @SSex where SId = @SId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建查询课程信息的存储过程
create proc proc_class_select
as
begin transaction
declare @count int
select * from class
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建通过课程号查询课程信息的存储过程
create proc proc_class_select_byEId
(
@EId varchar ( 20 )
)
as
begin transaction
declare @count int
select * from class where EId = @EId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
-- 创建插入课程信息的存储过程
GO
create proc proc_class_insert
(
@EId varchar ( 20 ),
@EName varchar ( 20 ),
@ETime int
)
as
begin transaction
declare @count int
insert into class(EId,EName,ETime) values ( @EId , @EName , @ETime )
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
-- 创建删除课程信息的存错过程
GO
create proc proc_class_delete
(
@EId varchar ( 20 )
)
as
begin transaction
declare @count int
delete from class where EId = @EId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
-- 创建修改课程信息的存储过程
GO
create proc proc_class_update
(
@EId varchar ( 20 ),
@EName varchar ( 20 ),
@ETime int
)
as
begin transaction
declare @count int
update class set EName = @EName ,ETime = @ETime where EId = @EId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建查询成绩信息的存储过程
create proc proc_score_select
as
begin transaction
declare @count int
select * from score
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建通过学号查询成绩信息的存储过程
create proc proc_score_select_bySId
(
@SId varchar ( 20 )
)
as
begin transaction
declare @count int
select * from score where SId = @SId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建通过查询成绩信息的存储过程
create proc proc_score_select_byEId
(
@EId varchar ( 20 )
)
as
begin transaction
declare @count int
select * from score where EId = @EId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建插入成绩信息的存储过程
create proc proc_score_insert
(
@SId varchar ( 20 ),
@EId varchar ( 20 ),
@EScore float
)
as
begin transaction
declare @count int
insert into score(SId,EId,EScore) values ( @SId , @EId , @EScore )
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建删除成绩信息的存错过程
create proc proc_score_delete
(
@SId varchar ( 20 ),
@EId varchar ( 20 )
)
as
begin transaction
declare @count int
delete from score where SId = @SId and EId = @EId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建通过学号删除成绩信息的存错过程
create proc proc_score_delete_bySId
(
@SId varchar ( 20 )
)
as
begin transaction
declare @count int
delete from score where SId = @SId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建通过课程号删除成绩信息的存错过程
create proc proc_score_delete_byEId
(
@EId varchar ( 20 )
)
as
begin transaction
declare @count int
delete from score where EId = @EId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建修改成绩信息的存储过程
create proc proc_score_update
(
@SId varchar ( 20 ),
@EId varchar ( 20 ),
@EScore float
)
as
begin transaction
declare @count int
update score set EScore = @EScore where SId = @SId and EId = @EId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建查询学生所有信息的存储过程
create proc proc_student_one_information
(
@SId varchar ( 20 )
)
as
begin transaction
declare @count int
select student.SName,student.SClass,student.SSex,class.EName,class.ETime,score.EScore,student.SScore from student,class,score where student.SId = score.SId and class.EId = score.EId and student.SId = @SId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建查询所有学生所有信息的存储过程
create proc proc_student_all_information
as
begin transaction
declare @count int
select student.SName,student.SClass,student.SSex,class.EName,class.ETime,score.EScore,student.SScore from student,class,score where student.SId = score.SId and class.EId = score.EId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建某个学生已经有了分数的课程
create proc proc_class_in
(
@SId varchar ( 20 )
)
as
begin transaction
declare @count int
select class.EId,class.EName from class,score where class.EId = score.EId and score.SId = @SId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建某个学生没有分数的课程
create proc proc_class_notin
(
@SId varchar ( 20 )
)
as
begin transaction
declare @count int
select EId,EName from class where EId not in ( select EId from score where SId = @SId )
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
create database StudentDB
GO
-- 置此数据库为当前数据库
use StudentDB
GO
-- 创建学生表
create table student
(
SId varchar ( 20 ) primary key , -- 学生编号
SName varchar ( 20 ), -- 学生姓名
SClass varchar ( 20 ), -- 学生班级
SSex varchar ( 10 ), -- 学生性别
SScore float default ( 0 ) check (SScore >= 0 ) -- 学生平均分
)
GO
-- 创建课程表
create table class
(
EId varchar ( 20 ) primary key , -- 课程编号
EName varchar ( 20 ), -- 课程名称
ETime int check (ETime >= 0 ) -- 课程课时
)
GO
-- 创建分数表
create table score
(
SId varchar ( 20 ), -- 学生编号
EId varchar ( 20 ), -- 课程编号
EScore float , -- 课程分数
primary key (SID,EID), -- 定义主码
foreign key (SID) references student(SID) on delete cascade , -- 声明及联删除
foreign key (EID) references class(EID) on delete cascade -- 声明及联删除
)
GO
-- 创建计算平均值得触发器
create trigger trigger_avg_insert on score for insert
as
begin transaction
declare @count int
update student set SScore = ( select avg (EScore) from score where SId = ( select SId from inserted)) where SId = ( select SId from inserted)
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
-- 创建计算平均值得触发器
create trigger trigger_avg_delete on score for delete
as
begin transaction
update student set SScore = ( select avg (EScore) from score where SId = ( select SId from deleted)) where SId = ( select SId from deleted)
declare @count int
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
-- 创建计算平均值得触发器
create trigger trigger_avg_update on score for update
as
begin transaction
declare @count int
update student set SScore = ( select avg (EScore) from score where SId = ( select SId from inserted)) where SId = ( select SId from deleted)
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建查询学生信息的存储过程
create proc proc_student_select
as
begin transaction
declare @count int
select * from student
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建查找平均分存储过程
CREATE PROCEDURE proc_student_avg
(
@SID varchar ( 20 )
)
AS
begin transaction
select avg (EScore) as SAvg from score where SId = @SId
declare @count int
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建通过学号查询学生信息的存储过程
create proc proc_student_select_bySId
(
@SId varchar ( 20 )
)
as
begin transaction
declare @count int
select * from student where SId = @SId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建插入学生信息的存储过程
create proc proc_student_insert
(
@SId varchar ( 20 ),
@SName varchar ( 20 ),
@SClass varchar ( 20 ),
@SSex varchar ( 10 )
)
as
begin transaction
declare @count int
insert into student(SID,SName,SClass,SSex) values ( @SId , @SName , @SClass , @SSex )
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 删除学生信息的存储过程
create proc proc_student_delete
(
@SId varchar ( 20 )
)
as
begin transaction
declare @count int
delete from student where SId = @SId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 修改学生信息的存储过程
create proc proc_student_update
(
@SId varchar ( 20 ),
@SName varchar ( 20 ),
@SClass varchar ( 20 ),
@SSex varchar ( 10 )
)
as
begin transaction
declare @count int
update student set SName = @SName ,SClass = @SClass ,SSex = @SSex where SId = @SId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建查询课程信息的存储过程
create proc proc_class_select
as
begin transaction
declare @count int
select * from class
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建通过课程号查询课程信息的存储过程
create proc proc_class_select_byEId
(
@EId varchar ( 20 )
)
as
begin transaction
declare @count int
select * from class where EId = @EId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
-- 创建插入课程信息的存储过程
GO
create proc proc_class_insert
(
@EId varchar ( 20 ),
@EName varchar ( 20 ),
@ETime int
)
as
begin transaction
declare @count int
insert into class(EId,EName,ETime) values ( @EId , @EName , @ETime )
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
-- 创建删除课程信息的存错过程
GO
create proc proc_class_delete
(
@EId varchar ( 20 )
)
as
begin transaction
declare @count int
delete from class where EId = @EId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
-- 创建修改课程信息的存储过程
GO
create proc proc_class_update
(
@EId varchar ( 20 ),
@EName varchar ( 20 ),
@ETime int
)
as
begin transaction
declare @count int
update class set EName = @EName ,ETime = @ETime where EId = @EId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建查询成绩信息的存储过程
create proc proc_score_select
as
begin transaction
declare @count int
select * from score
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建通过学号查询成绩信息的存储过程
create proc proc_score_select_bySId
(
@SId varchar ( 20 )
)
as
begin transaction
declare @count int
select * from score where SId = @SId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建通过查询成绩信息的存储过程
create proc proc_score_select_byEId
(
@EId varchar ( 20 )
)
as
begin transaction
declare @count int
select * from score where EId = @EId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建插入成绩信息的存储过程
create proc proc_score_insert
(
@SId varchar ( 20 ),
@EId varchar ( 20 ),
@EScore float
)
as
begin transaction
declare @count int
insert into score(SId,EId,EScore) values ( @SId , @EId , @EScore )
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建删除成绩信息的存错过程
create proc proc_score_delete
(
@SId varchar ( 20 ),
@EId varchar ( 20 )
)
as
begin transaction
declare @count int
delete from score where SId = @SId and EId = @EId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建通过学号删除成绩信息的存错过程
create proc proc_score_delete_bySId
(
@SId varchar ( 20 )
)
as
begin transaction
declare @count int
delete from score where SId = @SId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建通过课程号删除成绩信息的存错过程
create proc proc_score_delete_byEId
(
@EId varchar ( 20 )
)
as
begin transaction
declare @count int
delete from score where EId = @EId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建修改成绩信息的存储过程
create proc proc_score_update
(
@SId varchar ( 20 ),
@EId varchar ( 20 ),
@EScore float
)
as
begin transaction
declare @count int
update score set EScore = @EScore where SId = @SId and EId = @EId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建查询学生所有信息的存储过程
create proc proc_student_one_information
(
@SId varchar ( 20 )
)
as
begin transaction
declare @count int
select student.SName,student.SClass,student.SSex,class.EName,class.ETime,score.EScore,student.SScore from student,class,score where student.SId = score.SId and class.EId = score.EId and student.SId = @SId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建查询所有学生所有信息的存储过程
create proc proc_student_all_information
as
begin transaction
declare @count int
select student.SName,student.SClass,student.SSex,class.EName,class.ETime,score.EScore,student.SScore from student,class,score where student.SId = score.SId and class.EId = score.EId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建某个学生已经有了分数的课程
create proc proc_class_in
(
@SId varchar ( 20 )
)
as
begin transaction
declare @count int
select class.EId,class.EName from class,score where class.EId = score.EId and score.SId = @SId
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction
GO
-- 创建某个学生没有分数的课程
create proc proc_class_notin
(
@SId varchar ( 20 )
)
as
begin transaction
declare @count int
select EId,EName from class where EId not in ( select EId from score where SId = @SId )
select @count = @@error
if ( @count = 0 )
commit transaction
else
rollback transaction