--事务

--如果销售表的某种商品卖出时,则要求该种商品的库存量相对减少,这两个操作要么都成功执行,要么都不执行.
--如果任何一个环节发生了错误,操作都将被取消.
select * from shop5_xs
select * from shop3_jb


--声明变量
declare @id int,@quan int,@sale float,@ywid int
--赋值变量
set @id=9
set @quan=3
set @sale=5
set @ywid=4
--事务开始
begin tran
insert into shop5_xs values(@id,@sale,@quan,GETDATE(),@ywid)
if(@@error!=0)
begin
--事务回滚
rollback tran
print '插入失败'
return
end
else
begin
update shop3_jb set stock=stock-@quan where id=@id
if(@@rowcount=0)
begin
rollback tran
print '修改失败'
return
end
else
begin
--提交事务
commit tran
print '操作成功'
end
end

declare @id int,@quan int,@sale float,@ywid int
set @id=10
set @quan=30
set @sale=3
set @ywid=3
begin tran
insert into shop_xs values(@id,@sale,@quan,GETDATE(),@ywid)
--保存点
save tran aa
insert into shop_xs values(@id,@sale,@quan,GETDATE(),@ywid)
if(@@error!=0)
begin
rollback tran aa
print '插入失败'
return
end
else
begin
update shop_jb set stock=stock-@quan where id=@id
if(@@rowcount!=0)
begin
rollback tran aa
print '修改失败'
return
end
else
begin
commit tran
print '操作成功'
end
end