Is it possible to use where statements like I am trying to right now?

Question

I have 4 tables a "IN SHOP" table which has a variable "How many games in shop", no primary keys and 2 foreign keys of "Shop" where the primary key is ID and "Game" where the primary key is also ID.

Now I have a table "Purchase" which has a primary key of ID and foreign keys of "Shop ID" and "Game ID". Now I made a trigger that once a new PURCHASe has been added to the "PURCHASE table then every game number drops however I would like it to drop for one specific game in one specific shop.

Right now I have:

CREATE TRIGGER delete 
AFTER INSERT ON purchase
BEGIN
    UPDATE In_SHOP
    SET HMGIS = HMGIS - 1
    WHERE IN_SHOP.SHOP_ID = PURCHASE.SHOP_ID
      AND IN_SHOP.GAME_ID = PURCHASE.ID_GAME
END

Answer 1

You need to use the inserted view:

CREATE TRIGGER delete AFTER INSERT ON purchase
BEGIN
    UPDATE In_SHOP
        SET HMGIS= HMGIS-1
        FROM inserted i
    WHERE IN_SHOP.SHOP_ID = i.SHOP_ID AND IN_SHOP.GAME_ID = i.ID_GAME;
END;

You specifically asked about a WHERE clause. This would often be written using JOIN:

CREATE TRIGGER delete AFTER INSERT ON purchase
BEGIN
    UPDATE s
        SET HMGIS = HMGIS - 1
        FROM IN_SHOP s JOIN
             inserted i
             ON s.SHOP_ID = i.SHOP_ID AND s.GAME_ID = i.ID_GAME;
END;