创建事物.sql

来自「QL SERVER 应用开发 银行ATM(取款机）系统 数据库设计 我在学校」· SQL 代码 · 共 44 行

use bankDB
GO
--创建事务
if exists(select name from sysobjects where name='proc_transfer')
drop proc proc_transfer
GO
create proc proc_transfer
@card1 char(19),
@card2 char(19),
@outMoney money
as
print'开始转帐，请稍后......'
begin tran
set nocount on
declare @errcount int

set @errcount=0
insert into transInfo(transType,cardID,transMoney)values('支取',@card1,@outMoney)
set @errcount=@errcount+@@error
insert into transInfo(transType,cardID,transMoney)values('存入',@card2,@outMoney)
set @errcount=@errcount+@@error
if(@errcount<>0)
  begin
  print'转帐失败'
  rollback tran
end

else 
  print'转帐成功!'
  commit tran
GO

declare @card1 char(19)
select @card1=cardID from cardInfo inner join userInfo on cardInfo.customerID=userInfo.customerID where userInfo.customerName='张三'

declare @card2 char(19)
select @card2=cardID from cardInfo inner join userInfo on cardInfo.customerID=userInfo.customerID where userInfo.customerName='李四'

exec proc_transfer @card1,@card2,2000

GO
select * from view_cardInfo
select * from view_transInfo
GO