SQL 事务处理

use Demo
go

create table Account
(
AccountNumber varchar(20) not null primary key,
AccountName varchar(20) not null,
Balance decimal(18,2) not null check(Balance>=0)
)
go
--创建数据
insert into Account values(123456,'张晓慧',399.00)
insert into Account values(654321,'王晓丽',432.00)
go
select * from Account
go

--修改数据
alter table Account with check
add check(Balance>0 and Balance<1000)

go
--利用事务来处理所出现的问题
select * from Account
--声明变量
declare @Err int
--变量赋值

set @Err=0
begin transaction
--增加金额
update Account set Balance=Balance+200 where AccountNumber='123456'
set @Err=@@ERROR+@Err
--减少金额
update Account set Balance=Balance -200 where AccountNumber='654321'
set @Err=@@ERROR+@Err
if @Err=0
begin
 commit transaction  --提交事务
 print '事务提交成功'
end
else
begin
rollback transaction --事务回滚
print '事务回滚'
end
--查询数据库表内容
select * from Account