当产品类别中的最后一个产品被删除时，如何编写删除产品类别的触发器

Question

我正在尝试编写一个触发器，该触发器应在删除产品类别中的最后一个产品时删除该产品类别。但它应该只在删除一个产品而不是多个产品时起作用。

我已经编写了触发器，但问题是，当产品类别为空时，它会删除产品类别，并且一次删除一个或多个产品也无关紧要。

这是我到目前为止的代码：

drop trigger if exists mytrigger1
go
create trigger mytrigger1
on Product
after delete
as
select count(*) 
from DELETED 
having count(*) = 1
begin
delete from Productcategory 
where Produktcategory.produktcategory_id in 
(select productcategory_id 
from Productcategory
left join Product on Product.product_productcategory_id = Produktcategory.produktcategory_id
where product_productcategory_id is null) 
end
go

Answer 1

您可以更简洁地写这篇文章，但我这样写是为了分离(a(在当前删除操作中有多行受影响的类别，(b(表中有多个产品的类别，然后(c(根据是(a(的一部分而不是(b(的的一部分来删除的行。

DROP TRIGGER IF EXISTS dbo.mytrigger1;
GO
CREATE TRIGGER dbo.mytrigger1
ON dbo.Product
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
;WITH SingleRowCategoriesAffected AS 
(
-- categories where only one row was deleted
SELECT product_productcategory_id 
FROM deleted
GROUP BY product_productcategory_id
HAVING COUNT(*) = 1
),
MultiRowCategoriesLeft AS
(
-- categories remaining that have more than one product
SELECT product_productcategory_id
FROM Product
GROUP BY product_productcategory_id
HAVING COUNT(*) > 1
)
DELETE c
FROM dbo.Productcategory AS c
INNER JOIN SingleRowCategoriesAffected AS s
ON c.productcategory_id = s.product_productcategory_id
LEFT OUTER JOIN MultiRowCategoriesLeft AS m
ON s.product_productcategory_id = m.product_productcategory_id
WHERE m.product_productcategory_id IS NULL;
END

Answer 2

您需要从Productcategory中删除所有行，该行没有相应的产品，并且已在此操作中删除(即存在于已删除的伪表中(：

delete from Productcategory 
where produktcategory_id in (select product_productcategory_id from deleted)
and not exists(select * from Product where product_productcategory_id = Productcategory.produktcategory_id)