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]