我要执行3部分逻辑,用了3个游标,高手看是否可以优化!CREATE trigger [EBAYTransaction_Insert] on [dbo].[EBAYTransaction] for insert
as
-- declare updated values
declare @merchant_id as bigint;
declare @listing_id as bigint;
declare @product_id as bigint;
declare @product_detail_id as bigint;
declare @inventory_id as bigint;
declare @selling_quantity as int;
-- declare values
declare @rc as int;
set @product_detail_id=(
select l.ProductDetailID
from inserted i
left join dbo.LSTListing l
on (i.MerchantID=l.MerchantID and i.ListingID=l.ID)
)
set @product_id=(
select l.ProductID
from inserted i
left join dbo.LSTListing l
on (i.MerchantID=l.MerchantID and i.ListingID=l.ID)
)
-- get all updated records
declare csr cursor fast_forward for
select InventoryID,SellingQuantity
from dbo.INVFulfilledBy fb
where fb.MerchantID=i.MerchantID
and BindingType='PRDVariationDetail'
and BindingID in(
select ID from PRDVariationDetail vd
where vd.MerchantID=i.MerchantID
and vd.NameValueListJSON=i.NameValueListJSON
and vd.ProductDetailID = @product_detail_id
)
open csr;
fetch next from csr into @inventory_id,@selling_quantity;
while @@fetch_status = 0
begin
update dbo.INVInventory
set AvailableQuantity=AvailableQuantity-@selling_quantity*i.QuantityPurchased
where ID=@inventory_id
if @@ROWCOUNT = 1
set @rc=1;
fetch next from csr into @inventory_id,@selling_quantity;
end
close csr;
deallocate csr;if @rc=0
begin
declare csr cursor fast_forward for
select InventoryID,SellingQuantity
from dbo.INVFulfilledBy fb
where fb.MerchantID=i.MerchantID
and BindingType='ProductDetail'
and BindingID=@product_detail_id
open csr;
fetch next from csr into @inventory_id,@selling_quantity;
while @@fetch_status = 0
begin
update dbo.INVInventory
set AvailableQuantity=AvailableQuantity-@selling_quantity*i.QuantityPurchased
where ID=@inventory_id
if @@ROWCOUNT = 1
set @rc=1;
fetch next from csr into @inventory_id,@selling_quantity;
end
close csr;
deallocate csr;
end
if @rc=0
begin
declare csr cursor fast_forward for
select InventoryID,SellingQuantity
from dbo.INVFulfilledBy fb
where fb.MerchantID=i.MerchantID
and BindingType='Product'
and BindingID=@product_id
open csr;
fetch next from csr into @inventory_id,@selling_quantity;
while @@fetch_status = 0
begin
update dbo.INVInventory
set AvailableQuantity=AvailableQuantity-@selling_quantity*i.QuantityPurchased
where ID=@inventory_id
if @@ROWCOUNT = 1
set @rc=1;
fetch next from csr into @inventory_id,@selling_quantity;
end
close csr;
deallocate csr;
end
as
-- declare updated values
declare @merchant_id as bigint;
declare @listing_id as bigint;
declare @product_id as bigint;
declare @product_detail_id as bigint;
declare @inventory_id as bigint;
declare @selling_quantity as int;
-- declare values
declare @rc as int;
set @product_detail_id=(
select l.ProductDetailID
from inserted i
left join dbo.LSTListing l
on (i.MerchantID=l.MerchantID and i.ListingID=l.ID)
)
set @product_id=(
select l.ProductID
from inserted i
left join dbo.LSTListing l
on (i.MerchantID=l.MerchantID and i.ListingID=l.ID)
)
-- get all updated records
declare csr cursor fast_forward for
select InventoryID,SellingQuantity
from dbo.INVFulfilledBy fb
where fb.MerchantID=i.MerchantID
and BindingType='PRDVariationDetail'
and BindingID in(
select ID from PRDVariationDetail vd
where vd.MerchantID=i.MerchantID
and vd.NameValueListJSON=i.NameValueListJSON
and vd.ProductDetailID = @product_detail_id
)
open csr;
fetch next from csr into @inventory_id,@selling_quantity;
while @@fetch_status = 0
begin
update dbo.INVInventory
set AvailableQuantity=AvailableQuantity-@selling_quantity*i.QuantityPurchased
where ID=@inventory_id
if @@ROWCOUNT = 1
set @rc=1;
fetch next from csr into @inventory_id,@selling_quantity;
end
close csr;
deallocate csr;if @rc=0
begin
declare csr cursor fast_forward for
select InventoryID,SellingQuantity
from dbo.INVFulfilledBy fb
where fb.MerchantID=i.MerchantID
and BindingType='ProductDetail'
and BindingID=@product_detail_id
open csr;
fetch next from csr into @inventory_id,@selling_quantity;
while @@fetch_status = 0
begin
update dbo.INVInventory
set AvailableQuantity=AvailableQuantity-@selling_quantity*i.QuantityPurchased
where ID=@inventory_id
if @@ROWCOUNT = 1
set @rc=1;
fetch next from csr into @inventory_id,@selling_quantity;
end
close csr;
deallocate csr;
end
if @rc=0
begin
declare csr cursor fast_forward for
select InventoryID,SellingQuantity
from dbo.INVFulfilledBy fb
where fb.MerchantID=i.MerchantID
and BindingType='Product'
and BindingID=@product_id
open csr;
fetch next from csr into @inventory_id,@selling_quantity;
while @@fetch_status = 0
begin
update dbo.INVInventory
set AvailableQuantity=AvailableQuantity-@selling_quantity*i.QuantityPurchased
where ID=@inventory_id
if @@ROWCOUNT = 1
set @rc=1;
fetch next from csr into @inventory_id,@selling_quantity;
end
close csr;
deallocate csr;
end
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货