例如在添加购物车(的存储过程)时,内部嵌套事物,当用户已经有购物车时,获取该用户的购物车ID;若用户还没有购物车时,为该用户添加一个购物车ID:
-- =============================================
-- 添加购物车
-- =============================================
ALTER PROCEDURE [dbo].[AddCart]
@userID varchar(50),
@bookISBN varchar(50)
AS
BEGIN
declare @errorInfo int
set @errorInfo=0
Begin Transaction T
--获取用户购物车ID
declare @userCartID int
if(exists(select Cart_ID from Carts where U_ID=@userID))
begin
--获取已存在的购物车ID
select @userCartID=Cart_ID from Carts where U_ID=@userID
end
else
begin
--如果该用户还没有购物车,那么为该用户新增一个购物车
insert into Carts values(@userID)
set @userCartID=@@IDENTITY
set @errorInfo=@errorInfo+@@error
end
--往购物车详细表里加入书籍
if(Exists(select Book_ISBN from CartDetails where Book_ISBN=@bookISBN and Cart_ID=@userCartID))
begin
update CartDetails set Book_Count=Book_Count+1 where Book_ISBN=@bookISBN and Cart_ID=@userCartID
end
else
begin
insert into CartDetails values(@userCartID,@bookISBN,1)
set @errorInfo=@errorInfo+@@error
end
if(@errorInfo<>0)
begin
Rollback Transaction T
end
else
begin
Commit Transaction T
end
END