if exists(select name from sysobjects where name='usp_takeMoney')
drop proc usp_takeMoney
go
create proc usp_takeMoney
@name varchar(20),--存款用户姓名
@type char(4),--交易类型
@money int,--金钱
@pass char(6)=null--密码
as
declare @cardID CHAR(19)--用户1卡号
declare @error int
declare @balance int
set @error=0
--获取卡号
select @cardID=cardID from cardInfo where customerID=
(select customerID from userInfo where customerName=@name)
--存款,如果类型是存入,就执行下列事务
if @type='存入'
begin
--开启事务
begin transaction
insert tradeInfo values(DEFAULT,@type,@cardID,@money,null)
set @error=@error+@@error
update cardInfo set balance=balance+@money where cardID=@cardID
set @error=@error+@@error
print '交易正进行,请稍后……'
if @error=0
begin
print '交易成功!交易金额:'+convert(varchar(20),@money)
select @balance=balance from cardInfo
print '卡号'+@cardID+' 余额:'+convert(varchar(20),@balance)
commit transaction
end
else
begin
print '交易失败!'
rollback transaction
end
end
--取款,否则执行支取
else if(@type = '支取')
begin
begin transaction
insert tradeInfo values(DEFAULT,@type,@cardID,@money,null)
set @error=@error+@@error
update cardInfo set balance=balance-@money where cardID=@cardID and pass=@pass and @money<balance
set @error=@error+@@error
print '交易正进行,请稍后……'
if @error=0
begin
print '交易成功!交易金额:'+convert(varchar(20),@money)
select @balance=balance from cardInfo
print '卡号'+@cardID+' 余额:'+convert(varchar(20),@balance)
commit transaction
end
else
begin
if(@pass<>(select pass from cardInfo))
raiserror('密码错误',16,1)
if(@balance>(select balance from cardInfo))
raiserror('交易失败!余额不足',16,1)
rollback transaction
end
end
exec usp_takeMoney @name ='张三',@type='支取',@money=3000,@pass='888888'
ps:我存款的事务执行是可以的,但是取款的时候明明我故意把密码写错或者取款金额大于余额,程序仍然执行成功,就是上面的图片,不知道哪步出错了,没进事务去,求各位大大解惑,本人新人