准备工作
1.创建表
create Table T_User(
id int identity(1,1) primary key,
[Name] nvarchar(50),
[Money] int
)
2.添加数据
insert into T_User
select N'张三',1000 union
select N'李四',1000
添加约束
---添加约束
alter table T_User
add constraint CK_Money check([Money]>=0)
创建存储过程
go
--创建存储过程
create proc cp_aaa --(cp_aaa存储过程名称可以自取)
(
@idOut int,--定义转出的账号ID
@idIn int,--定义转入的账号ID
@remain int--定义转账金额
)
as
--开始事务
begin Transaction tran_Charge
--定义变量记录错误数
declare @err_nums int
set @err_nums=0
begin try
update T_User set Money=Money-@remain where ID=@idOut
set @err_nums=@err_nums+@@error
update T_User set Money=Money+@remain where ID=@idIn
set @err_nums=@err_nums+@@error
end try
begin catch
print '错误编号:'+convert(varchar,error_number())+'错误消息'+error_message()
set @err_nums=@err_nums+1
end catch
if(@err_nums>0)
rollback Transaction tran_Charge--回滚事务
else
commit Transaction tran_Charge--提交事务
调用
exec cp_aaa 2,1,500 --(2表示转出的账号ID,1表示转入的账号ID,500表示金额)
注释
1.
begin Transaction tran_Charge --开始事务
rollback Transaction tran_Charge --回滚事务
commit Transaction tran_Charge --提交事务