存储过程，触发器，事务和锁

存储过程：
        预先用SQL语句写好的，并用存储起来，如果需要的数据库提供与定义好的存储过程的功能相同时，只要调用execute()方法，即可执行。

触发器：
        个人认为是一种特殊的存储过程,因为它是当运行到标签所在的位置时，才触发这个SQL语名的功能.

事务:
       事务就是一个单元的工作，包括一系列的操作，这些操作要么全部成功，要么全部失败。
锁:
       锁就是保护指定的资源，不被其他事务操作。


事务和锁的特点

事务的特点：

1. 事务是一个单元的工作，要么全做，要么全不做

2. 事务保证操作的一致性和可恢复性

3. 每一条Transact-SQL语句都可以是一个事务

4. 实际使用的事务是用户定义的事务，它包括一系列操作或者语句

5. 在多服务器环境中，使用用户定义的分布式事务，保证操作的一致性

锁的特点：

1. 锁是保证并发控制的手段

2. 可以锁定的资源包括行、页、簇、表和数据库

3. 锁的类型主要包括共享锁和排它锁

4. 特殊类型的锁包括意图锁、修改锁和模式锁

5. 共享锁允许其他事务继续使用锁定的资源

6. 排它锁只允许一个事务访问数据

7. 系统本身可以处理死锁

8. 用户可以根据实际情况定制锁的一些特征

======================================================

-- 创建数据库 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

转载于:https://www.cnblogs.com/hunter_gio/archive/2007/08/15/856548.html