在存储过程中运用事务

create database study
use study
?
create table peopleInfor? --用户信息表
(
ID int primary key identity(1001,1),--主键自动增长
name1 nvarchar(30),--用户名
YMoney?? money default(0)--卡上剩余金额
)
create table peopleInfor_mx? --用户取款明细
(
mx_id int primary key identity(100001,1), --主键自动增长
mx_Money money ,? --取款或付款金额
mx_type nvarchar(20)? check (mx_type='取钱' or mx_type ='存钱'),--代表此条数据是 存钱，还是 取钱
peopleId int? foreign key (peopleID)references peopleInfor(id) not null
)
?
?
?
--创建一个基本的存储过程
create procedure pro_insert(@name nvarchar(30),@money money)as
begin?
? insert into peopleInfor(name1,YMoney)values(@name,@money)
end

--执行 存储过程。
exec pro_insert '赵刚',20000
exec pro_insert '王五',30000

--错误的例子模仿银行转账
create procedure pro_zhuangzhang?
?@name_Jin nvarchar(30),--转进账户名称
@name_chu nvarchar(30), --转出帐户名称
?@money money --转入钱数)
?as
begin tran? --开发事务
?? declare @YMoney money --账上余额
?? declare @chuID int --出款账号的ID
?? declare @JinID int --进款账号的ID
??? select? @YMoney=YMoney from peopleInfor where?name1=@name_chu?--获取账上余额
? if(@money<=@YMoney)--判断 账号余额 是否 大于等于 转款余额
?? begin
??????????? update peopleInfor set?YMoney=YMoney-@money?where?name1=@name_chu? -- 从 出帐 帐户中减去 转出金额
?????????
??????????? select @chuID=ID from? peopleInfor where?name1=@name_chu???? -- 获得出帐ID
??????????? insert into peopleInfor_mx (mx_Money,mx_type,peopleId)values(@money,'取钱',@chuID) --把转出金额 插入明细 表中并标明是 取出的钱

??????????? update peopleInfor set?YMoney=YMoney+@money?where?name1=@name_Jin-- 在 进帐 帐户中加上 转出金额
?????????
??????????? select @chuID=ID from? peopleInfor where?name1=@name_Jin-- -- 获得出帐ID
??????????? insert into peopleInfor_mx (mx_Money,mx_type,peopleId)values(@money,'存钱',@JinID)
??????????? -- 此时 @JinID 为空时 ，不能插入此列。故软件报错。
??????? if @@error<>0
??????? begin
?????????? rollback tran?
???????????
???????? end?
?? end
commit tran --执行事务
GO

?

?

--对的例子 模仿银行转账
create procedure pro_zhuangzhang?
?@name_Jin nvarchar(30),--转进账户名称
@name_chu nvarchar(30), --转出帐户名称
?@money money --转入钱数)
?as
begin tran? --开发事务
?? declare @YMoney money?
?? declare @chuID int?
?? declare @JinID int?
??? select? @YMoney=YMoney from peopleInfor where?name1=@name_chu
? if(@money<@YMoney)
?? begin
??????????? update peopleInfor set?YMoney=YMoney-@money?where?name1=@name_chu
?????????
??????????? select @chuID=ID from? peopleInfor where?name1=@name_chu
??????????? insert into peopleInfor_mx (mx_Money,mx_type,peopleId)values(@money,'取钱',@chuID)

??????????? update peopleInfor set?YMoney=YMoney+@money?where?name1=@name_Jin
?????????
??????????? select @JinID=ID from? peopleInfor where?name1=@name_Jin
??????????? insert into peopleInfor_mx (mx_Money,mx_type,peopleId)values(@money,'存钱',@JinID)
??????? if @@error<>0
??????? begin
?????????? rollback tran?
???????????
???????? end?
?? end
commit tran --执行事务
GO

select * from peopleInfor
select * from peopleInfor_mx
delete from peopleInfor where id=1003
exec pro_zhuangzhang '赵刚','王五',20000

? '赵刚',20000
? '王五',30000