What I'm thinking for the whole trigger is below, any feedback would be appreciated.
Code:
create TRIGGER tr_u_ProductVariant
on ProductVariant
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(Inventory)
BEGIN
UPDATE ProductVariant
set Published = 0
where variantID in (select VariantID from INSERTED)
and cast(CAST(ExtensionData AS XML).query('data(/AllowBackorder)') as varchar(10))='N'
AND Inventory <= 0
Update product set published = 0
where productID in (
select p.productID from product p left join (
select pv.productID, count(*) as publishedVars
from productVariant pv
where pv.published = 1
group by pv.productID
) as pvp on p.productID = pvp.productID
where p.productID in ( SELECT productID from INSERTED ) and pvp.PublishedVars is NULL
)
END
END
My logic is, if the variant is 'Not Allow Backorder' and it gets to 0 inventory, unpublish the variant.
Check the product, if the product has 0 published variants, also unpublish the product.