CODEWITHSUNDEEP
sqlitetriggerscaseconditional-statements
Chaitanya
Chaitanya

Reputation: 37

Case statement in SQLite

Please solve my query 

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;

ERROR in near Delete Statement

Upvotes: 1

Views: 4241

Answers (2)

CL.
CL.

Reputation: 180060

You cannot put a statement like DELETE into an expression like CASE.

In the general case, you can make triggers conditional by using the 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;

Upvotes: 2

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

Try this:

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) IS NULL
    THEN RAISE(ABORT,'Error code 82')
    END;
    -- If RAISE was called, next isntructions are not executed.
    DELETE from inventory where inventory.ProductID=OLD.ProductID;
END;

Upvotes: 1

Related Questions