SQL事务
--实例:张三转800元到李四账户上
if exists(select * from sysobjects where name='bank')
drop table bank
create table bank
(
customerName char(10), --顾客姓名
currentMoney money --当前余额
)
--添加约束 ,账户不能少于1元
alter table bank add
constraint CK_currentMoney check(currentMoney>=1)
--插入测试数据
insert into bank(customerName,currentMoney)
select 'zhangs',1000 union
select '李四',1
select * from bank
/*方法一: 通过判断Error来回滚事务*/
--开始 事务
begin transaction --开始 事务
declare @errorSum int =0 --定义变量,用于累计事务执行过程中的错误
update bank set currentMoney=currentMoney-200 where customerName='zhangs'
set @errorSum=@errorSum+@@ERROR --累计是否有错误
update dbo.bank set currentMoney=currentMoney+200 where customerName='李四'
set @errorSum=@errorSum+@@ERROR --累计是否有错误
print @errorSum
if @errorSum>0
begin rollback transaction --事务回滚
--print '1'
end
else
begin commit transaction --提交事务
end
/*方法二:设置 xact_abort ON*/
set xact_abort ON --如果不设置该项为ON,在SQL中默认为OFF,那么只回滚产生错误的Transaction-SQl语句;设为ON,回滚整个事务
begin transaction --开始 事务
update bank set currentMoney=currentMoney-200 where customerName='zhangs'
update dbo.bank set currentMoney=currentMoney+200 where customerName='李四'
commit transaction --提交事务
select * from bank
/*方法三:try */
begin try
begin transaction --开始 事务
update bank set currentMoney=currentMoney-200 where customerName='zhangs'
update dbo.bank set currentMoney=currentMoney+200 where customerName='李四'
commit transaction --提交事务
end try
begin catch
if @@trancount>0
rollback
declare @ErrMsg nvarchar(4000),@ErrSeverity int
select @ErrMsg=ERROR_MESSAGE(),
@ErrSeverity=ERROR_SEVERITY()
raiserror(@ErrMsg,@ErrSeverity,1)
end catch
select * from bank