mardi 8 mars 2016

Not updating SQLite table row when a certain column has been updated

In my android SQLite project I have a trigger where I want to update the OrderNumber of a product to -1 when any columns other than the OrderNumber have been changed. I have created the following:

CREATE TRIGGER IF NOT EXISTS Reset_OrderNumber AFTER UPDATE
ON Products
BEGIN
   IF NOT UPDATE(OrderNumber)
   BEGIN
        UPDATE Products SET OrderNumber = -1 WHERE OrderNumber = NEW.OrderNumber;
   END
END
GO

But I'm getting the following error:

android.database.sqlite.SQLiteException: near "IF": syntax error (code 1): , while compiling:     CREATE TRIGGER IF NOT EXISTS Reset_OrderNumber AFTER UPDATE
    ON Products
    BEGIN
       IF NOT UPDATE(OrderNumber)
       BEGIN
            UPDATE Products SET OrderNumber = -1 WHERE OrderNumber = NEW.OrderNumber;
       END
    END
    GO

What am I doing wrong? Is "IF NOT UPDATE" not available in Sqlite synthax? And if so is there another way of doing this?

Aucun commentaire:

Enregistrer un commentaire