SQL 进阶学习之七

--  =========================================================================================
--  2007/06/06 SQL学习-- 事务
--  =========================================================================================
use  test
go

--
--  ******************************************************************************************
--  ===================================================================================================
--  事务与锁
--  事务（Transaction）的作用是保证一系列的数据操作可以全部正确完成，不会造成数据操作到一半未完成，
--  而导致数据的完整性出错。
--  锁（Lock）的作用是保证数据在操作的过程中不会受到任何的其他影响。
--  ===================================================================================================
--  事务是作为单个逻辑工作单元执行的一系列工作。必须具备的四个特性：
--  1）原子性
--  2）一致性
--  3）隔离性：由并发事务所作的修改必须与其他并发事务所作的修改隔离。
--  4）持久性：在事务完成后，其操作结果对于系统的影响应该是永久的。

--  
--  ===================================================================================================
select   *   from  authers
select   *   from  books

delete  authers 
where  author_id  =   4

delete  books 
where  book_id  =   8
--
--  开始事务
begin   tran
declare   @bid   int
--  添加一本书
insert  books
values ( 8 , ' ADO.NET ' , 4 , 300 )
if   @@error   >   0
goto  tranrollback
set   @bid   =   @@identity
--  添加一个作者
insert  authers
(author_id,lastname,firstname,company)
values ( 4 , ' bin ' , ' song ' , ' 21CNIT ' )
-- print '添加成功 !'
if   @@error   >   0
goto  tranrollback
--
tranrollback:
if   @@error   >   0   or   @@rowcount   <>   1
     rollback   tran   --  如果发生错误则回滚事务
else
     commit   tran   --  如果没有发生错误则提交事务
go



--
--  事务的执行方式分为三种：
--  1）显式事务
--  2）自动提交事务（默认的方式）
--   每一个T-SQL语句都是一个自动提交的事务
--  3）隐式事务
--   当执行 set implicit_transactions on 语句后，进入隐式模式
--   隐式事务产生的是一个连续的事务链

--  =================================================================================================
--  编写事务的原则：
--  1）事务要尽可能简短
--  2）在事务中访问的数据量要尽量最少
--  3）浏览数据时尽量不要打开事务 
--  4）在事务处理期间尽量不要请求用户输入


--  编写事务
--
select   *   from  books
--  事务开始
begin   tran
--  
insert  books
(book_id,title)
values ( 10 , ' wo ' )
if   @@error   >   0   or   @@rowcount   <>   1
goto  tranrollback
--
insert  books
(book_id,title)
values ( 11 , ' me ' )
if   @@error   >   0   or   @@rowcount   <>   1
goto  tranrollback
--
select   *   from  books
--
delete  books
where  book_id  =   15
if   @@error   >   0   or   @@rowcount   <>   1
begin
    tranrollback:
     rollback   tran       --  回滚事务
end
else
     commit   tran
--  事务结束
--  查看事务结束后的数据集
select   *   from  books




--  嵌套事务
--  嵌套事务里并不是将嵌套的语句完全执行完毕，嵌套事务会将内层事务的commit语句忽略，只提交最外层事务的commit语句
--  在内层事务里，rollback语句仍然有效，但只是回滚到外层事务开始之前的状态
--
begin   tran
print   ' 当前事务嵌套层次为：  '   +   cast ( @@trancount   as   nvarchar ( 5 ))
insert  books
(book_id,title)
values  ( 20 , ' qiantao ' )
if   @@error   >   0   or   @@rowcount   <>   1
goto  tranrollback
     --  嵌套开始
     begin   tran
     print   ' 当前事务嵌套层次为：  '   +   cast ( @@trancount   as   nvarchar ( 5 ))
     insert  books
    (book_id,title)
     values  ( 25 , ' qiantao1 ' )
     if   @@error   >   0   or   @@rowcount   <>   1
         rollback   tran
     else
         commit   tran
     --  嵌套结束
print   ' 当前事务嵌套层次为：  '   +   cast ( @@trancount   as   nvarchar ( 5 ))
if   @@error   >   1
begin
    tranrollback:
     rollback   tran
end
else
     commit   tran
go
--
select   *   from  books
    


--  事务保存点
save   tran  保存点名称
--  设置好事务保存点之后，可以让事务回滚到该保存点时的状态
--  在一个事务中可以设置多个事务保存点，可以回滚到任一个事务保存点所保存的状态



--  事务的隔离级别由低到高分为5个级别：
--  1）Read uncommitted 不隔离数据
--  2) Read committed 不允许读取没有提交的数据,这是SQL Server默认的隔离级别
--  3) Repeatable Read 在事务中锁定所读取的数据不别人修改和删除,如此可以保证在事务中每次读取到的数据都是一致的
--  4) Snapshot 快速隔离,
--  5) Serializable 将事务所要用到的数据表全部锁定
     set   tran   isolation   level
    { read   uncommitted
     | read   committed
     | Repeatable   Read
     | Snapshot
     | Serializable
    }
     [ ; ]




--  =======================================================================================================
--   锁
--  锁的作用是将数据临时锁定只提供给一个进程或程序使用,并防止其他的进程或程序修改或读取
--  锁是由并发性控制来决定的
--  并发性就是多个进程或程序同时对数据进行处理的性质
--  乐观并发性控制,悲观并发性控制

--  锁定模式: 
--  1)共享锁
--  2)更新锁
--  3)排他锁
--  4)意向锁
--  5)架构锁
--  6)大容量更新锁
--  7)键范围锁

--  死锁(Deadlock)
--  在两个或多个任务中,如果每个任务都锁定了自己的资源,却又在等待其他事务释放资源,此时就会造成死锁。 

--  =======================================================================================================

 

 

--  事务的示例：
--  用事务在类别表里插入两条记录，然后将插入的最后一条记录删除，看看成功提交事务的结果
select   *   from  Categories 
order   by  Categoryid  desc
--
begin   tran
--
insert  Categories(CategoryName)
values (N ' 午饭 ' )
if   @@error   >   0   or   @@rowcount   <>   1
     goto  rb
--
insert  Categories(CategoryName)
values (N ' 睡觉 ' )
if   @@error   >   0   or   @@rowcount   <>   1
     goto  rb
--
select   *   from  Categories 
order   by  Categoryid  desc
--
delete  Categories 
where  CategoryID  =  ( select   max (CategoryID)  from  Categories)
if   @@error   >   0   or   @@rowcount   <>   1
begin
    rb:
     rollback   tran
end
else
     commit   tran

select   *   from  Categories 
order   by  Categoryid  desc




--  error tran
--
select   *   from  Categories 
order   by  Categoryid  desc
--

begin   tran
--
insert  Categories(CategoryName)
values (N ' 午饭1 ' )
if   @@error   >   0   or   @@rowcount   <>   1
     goto  rb
--
insert  Categories(CategoryName)
values (N ' 睡觉1 ' )
if   @@error   >   0   or   @@rowcount   <>   1
     goto  rb
--
select   *   from  Categories 
order   by  Categoryid  desc
--
delete  Categories 
where  CategoryID  =   15
if   @@error   >   0   or   @@rowcount   <>   1
begin
    rb:
     rollback   tran
end
else
     commit   tran

select   *   from  Categories 
order   by  Categoryid  desc
--



--  嵌套的事务
--
begin   tran
print  N ' 当前事务的层次为： '   +   cast ( @@trancount   as   nvarchar ( 5 ))

insert  Products (ProductName,CategoryID)
values ( ' 西瓜汁 ' , 1 )
if   @@error   >   0   or   @@rowcount   <>   1
     goto  rb

insert  Products (ProductName,CategoryID)
values ( ' 草莓汁 ' , 1 )
if   @@error   >   0   or   @@rowcount   <>   1
     goto  rb

     --  开始嵌套    
         begin   tran
         print  N ' 当前事务的层次为： '   +   cast ( @@trancount   as   nvarchar ( 5 ))
         insert  Products (ProductName,CategoryID)
         values ( ' 菠萝汁 ' , 1 )
         if   @@error   >   0   or   @@rowcount   <>   1
             goto  rb
         else  
             commit   tran
     --  嵌套结束
print  N ' 当前事务的层次为： '   +   cast ( @@trancount   as   nvarchar ( 5 ))

if   @@error   >   0
     begin
        rb:
         rollback   tran
     end
else
     commit   tran

--
select   *   from  products

--  

--  事务保存点
--  save tran
--
select   *   from  products
--
begin   tran
insert  Products (ProductName,CategoryID)
values (N ' 西瓜汁1 ' , 1 )
if   @@error   >   0   or   @@rowcount   <>   1
     goto  rb

select   *   from  products
save   tran  savebyP

insert  Products (ProductName,CategoryID)
values (N ' 草莓汁2 ' , 1 )
if   @@error   >   0   or   @@rowcount   <>   1
     goto  rb

select   *   from  products
save   tran  savebyP1

     --  开始嵌套    
         begin   tran
         update  products
         set  ProductName  =   ' OK! '
         where  ProductID  =   115151
         if   @@error   >   0   or   @@rowcount   <>   1
             rollback   tran  savebyP
         else  
             commit   tran  savebyP
     --  嵌套结束

--
select   *   from  products
--
insert  Products (ProductName,CategoryID)
values (N ' 冰镇可乐1 ' , 1 )

if   @@error   >   0   or   @@rowcount   <>   1
     rollback   tran  
else
     commit   tran

--
select   *   from  products

if   @@error   >   0
     begin
        rb:
         rollback   tran
     end
else
     commit   tran

--
select   *   from  products


/**/ /*
当 SET XACT_ABORT 为 ON 时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。 

当 SET XACT_ABORT 为 OFF 时，有时只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。如果错误很严重，
那么即使 SET XACT_ABORT 为 OFF，也可能回滚整个事务。

编译错误（如语法错误）不受 SET XACT_ABORT 的影响

*/
--
select   *   from  products
set  xact_abort  on
--
begin   tran  a
insert  Products (ProductName,CategoryID)
values ( ' ok1 ' , 1 )

insert  Products (ProductName,CategoryID)
values ( ' SQL1 ' , 1 )

select   *   from  products

insert  Products (ProductName,CategoryID)
values ( ' SQL3 ' , 111 )
select   *   from  products

commit   tran  a

select   *   from  products

--  ===================================================================================================
--  Trigger
--  ===================================================================================================
--  insert trigger
create   trigger  CategoriesbyInsert
on  Categories
after  insert
as
     begin
         print  N ' 类别表里新增了一条记录！ '
     end
go

--
create   trigger  CategoriesbyInsert_1
on  Categories
after  insert
as
     begin
         print  N ' 测试顺序！ '
     end
go
--
insert   into  Categories
(CategoryName)
values (N ' 测试触发器4 ' )

--
--  sp_settriggerorder
exec  sp_settriggerorder  ' CategoriesbyInsert_1 '  , ' first ' , ' insert '
exec  sp_settriggerorder  ' CategoriesbyInsert ' , ' last ' , ' insert '

--
create   trigger  Categoriesbyupdate
on  Categories
after  update
as
     begin
         print  N ' 类别表里更新了一条记录！ '
     end
go
--
select   *   from  Categories
update  Categories
set  CategoryName  =  N ' 测试触发器2 '
where  CategoryID  =   17
--
create   trigger  Categoriesbydelete
on  Categories
after  delete
as
     begin
         print  N ' 类别表里新删除了一条记录！ '
     end
go



--  
select   *   from   [ Order Details ]
select   *   from  orders
--
create   trigger  odbyinsert
on   [ Order Details ]
after  insert
as
begin
     if  ( select  discount  from  inserted) >   0.7
     begin
         print  N ' 折扣不能大于70% '
         rollback   tran
     end
end
go

insert   [ Order Details ]
values ( 10252 , 1 ,$ 20 , 50 , 0.8 )




--  触发器的嵌套
--
create   table  testlog
(
    id  int   identity ( 1 , 1 )  not   null ,
    tablename  nvarchar ( 50 )  not   null ,
    opsentence  nvarchar ( 2000 )  not   null ,
    opcontent  nvarchar ( 2000 )  not   null ,
    optime  datetime   not   null
     constraint  df_testlog_optime  default  ( getdate ()),
     constraint  pk_testlog  primary   key   clustered
    (
        id  asc
    )
     with  (ignore_dup_key  =   off )  on   [ primary ]
)
on   [ primary ]
go
--
create   trigger  testloginsert
on  testlog
after  insert
as
begin
     print   ' 测试时，表记录有所变动 '
end
go
--
create   trigger  bookinsert3
on  books
after  insert
as
begin
     declare   @name   nvarchar ( 20 ), @page   nvarchar ( max )
     set   @name   =  ( select  title  from  inserted)
     set   @page   =  ( select  pages  from  inserted)
    
     insert   into  testlog(tablename,opsentence,opcontent)
     values ( ' books表 ' , ' 插入记录 ' , ' 书名 ： '   +   @name   +   '  页数:  ' +   @page )
end
go
--  测试
insert  books
(book_id,title,pages)
values ( 8 , ' HTML ' , 200 )

select   *   from  testlog



--  instead of 触发器
create   trigger  od_insert
on   [ Order Details ]
instead  of   insert
as  
begin
     declare
     @oid   int ,
     @pid   int ,
     @price   money ,
     @q   smallint ,
     @d   real

     set   @oid   =  ( select  orderid  from  inserted)
     set   @pid   =  ( select  productid  from  inserted)
     set   @price   =  ( select  UnitPrice  from  inserted)
     set   @q   =  ( select  Quantity  from  inserted)
     set   @d   =  ( select  Discount  from  inserted)
    
     if  ( @d )  >   0.6
         print  N ' 折扣不能大于0.6 '
     else
         insert   into   [ Order Details ]
             values ( @oid , @pid , @price , @q , @d )
    
end
go

--
drop   trigger  od_insert

insert   into   [ Order Details ]
             values ( 10252 , 1 ,$ 20 , 50 , 0.8 )

select   *   from   [ Order Details ]
where  Discount  >   0.5

exec  sp_helptext od_insert

--  禁用一个触发器
--  
alter   table   [ Order Details ]
disable  trigger  od_insert

--  启用
alter   table   [ Order Details ]
enable  trigger  od_insert


--
create   trigger  test_table
on   database
for  drop_table ,alter_table
as  
begin
     print  N ' 对不起,你不能对数据表进行操作 '
     rollback ;
end

drop   table   [ Order Details ]