sqlsql 事务

use stuDB
go
create table bank(
customerName char(10),
currentMoney Money
)
alter table bank
add constraint ck_currentMoney check(currentMoney>=1)

select * from bank
insert into bank (customerName,currentMoney)
select '张三',100
union all select '李四',200

update bank set currentMoney=currentMoney+10 where customerName='张三'
update bank set currentMoney=currentMoney-10 where customerName='李四'
go
--银行转账事务
select * from bank

begin transaction
set nocount on
declare @errorsum int
set @errorsum=0
update bank set currentMoney=currentMoney-100 where customerName='张三'
set @errorsum=@errorsum+@@error
update bank set currentMoney=currentMoney+100 where customerName='李四'
set @errorsum=@errorsum+@@error
select * from  bank
if @errorsum<>0
begin 
print '交易失败，回滚事务'
rollback transaction
end
else
begin 
print '交易成功，提交事务'
commit transaction
end
go
select * from bank
print @@version

 实例 银行 存支 业务

--存储过程事务实现存支
create procedure proc_takeMoney @card varchar(18),@m money,@type char(5),@inputPass char(6)=''
as
if exists(select cardID,pass from cardInfo where cardID=@card and pass=@inputPass)
begin 
declare @cID int
declare @yuE money
declare @errorsum int
set @errorsum=0
begin transaction 
set nocount on
select @cID=customerID, @yuE=balance from cardInfo where cardID=@card
if(@yuE<@m)
begin
set @errorsum=@errorsum+1
print '账户余额'+cast(@yuE as char)+'元'+'不能超额支取'
end
insert into transInfo(customerID,transDate,cardID,transType,transMoney)
values(@cID,getDate(),@card,@type,@m)
set @errorsum=@errorsum+@@error
if(@type='支取')
update cardInfo set balance=balance-@m where cardID=@card 
else
update cardInfo set balance=balance+@m where cardID=@card
set @errorsum=@errorsum+@@error
if(@errorsum<>0)
rollback transaction
else
commit transaction
end
go
--执行存储过程
exec proc_takeMoney '1010357612121131',10,'支取','000001'
exec proc_takeMoney '1010357612121131',20,'存入','000001'

exec proc_takeMoney '1010357612121131',220,'支取','000001'

delete transInfo where transMoney=20
update cardInfo set balance=210 where balance=330