MSSQL 事務(wù)說(shuō)明

Create proc RegisterUser
(@usrName varchar(30), @usrPasswd varchar(30),@age int,@sex varchar(10), @PhoneNum varchar(20), @Address varchar(50) )
as begin
begin tran
insert into userinfo(userName,userPasswd) values(@usrName,@usrPasswd)
if @@error>0
begin

rollback tran

return -1
end
insert into userdoc(userName,age,sex,PhoneNumber,Address)values(@Usrname,@age,@sex,@PhoneNum,@Address)
if @@error>0
begin

rollback tran
return -1
end

commit tran
return 0
end

use bookdb
go
begin tran mytran
insert into book
values(9,"windows2000',1,22,'出版社')
save tran mysave
delete book where book_id=9
rollback tran mysave
commit tran
go
select * from book
go

use test
go
create table testback(cola int primary key ,colb char(3))
go
insert into testback values(1,'aaa')
insert into testback values(2,'bbb')
insert into testback value(3,'ccc')
go
select * from testback
go

use test
go
set nocount on
create table t1(a int)
go
insert into t1 values(1)
go

print '使用顯式事務(wù)'
begin tran
insert into t1 values(2)
print '事務(wù)外的事務(wù)數(shù)目:'+cast(@@trancount as char(5))
commint tran
print '事務(wù)外的事務(wù)數(shù)目:'+cast(@@trancount as char(5))
go

print
go
set implicit_transactions on
go

print '使用隱式事務(wù)'
go
insert into t1 values*4)
print'事務(wù)內(nèi)的事務(wù)數(shù)目:'+cast(@@trancount as char(5))
commint tran
print'事務(wù)外的事務(wù)數(shù)目:'+cast(@@trancount as char(5))
go

begin tran
                            declare @rownum1 int --未添加訂單的空房數(shù)
                            declare @rownum2 int --添加訂單的空房數(shù)目
                            declare @BookID1 int
                                set @BookID1=0
                            insert into T_BookRoomInfo(RoomID,CustomerName,CustomerCardID,Discount,
                                  EnterTime,DepositMoney,Memo,UserID,UpdTime)
                            values (@RoomID,@CustomerName,@CustomerCardID,@Discount,
                                   getdate(),@DepositMoney,@Memo,@UserID,getdate())
                            select @BookID1=@@IDENTITY
                            if(@BookID1>0)
                                begin
                                       select @rownum1=count(1)
                                         from T_Room
                                        where IsEmploy=0
                                update T_Room
                                set IsEmploy=1
                                where RoomID=@RoomID
                                       select @rownum1=count(1)
                                         from T_Room
                                        where IsEmploy=0

                                if(@rownum1=@rownum2)
                                    begin
                                        rollback tran
                                    end
                                else
                                    begin
                                        commit tran       
                                    end
                                end
                            else
                                    begin
                                         rollback tran       
                                    end