触发器、存储过程和事务处理使用

 

-- 创建数据库 scroll dynamic
create   database  Database1
GO
-- 置此数据库为当前数据库
use  Database1
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