sqlite - SQLite 中的 case 语句

Question

请解决我的疑问

create trigger DeleteProduct
Before delete on Product
BEGIN
    select CASE WHEN ((SELECT Inventory.InventoryID FROM Inventory WHERE Inventory.ProductID = OLD.ProductID and Inventory.Quantity=0) ISNULL)
    THEN    
           RAISE(ABORT,'Error code 82')
    Else   
          DELETE from inventory where inventory.ProductID=OLD.ProductID;
    END;

END;

近删除语句中出现错误

Answer 1

您不能将诸如 DELETE 之类的语句放入诸如 CASE 之类的表达式中。

在一般情况下，您可以使用 WHEN clause 使触发器成为有条件的:

CREATE TRIGGER DeleteProductError
BEFORE DELETE ON Product
WHEN NOT EXISTS (SELECT InventoryID
                 FROM Inventory
                 WHERE ProductID = OLD.ProductID
                   AND Quantity = 0)
BEGIN
    SELECT RAISE(ABORT, 'Error code 82');
END;

CREATE TRIGGER DeleteProduct
BEFORE DELETE ON Product
WHEN EXISTS (SELECT InventoryID
             FROM Inventory
             WHERE ProductID = OLD.ProductID
               AND Quantity = 0)
BEGIN
    DELETE FROM Inventory
    WHERE ProductID = OLD.ProductID;
END;