CODEWITHSUNDEEP
mysqltriggers
AlexKangaroo
AlexKangaroo

Reputation: 9

MySQL how to decrease value by 1 with a trigger

I'm building a simple videogame store inventory and sales database with MySQL. At the moment I have an Inventory table:

game_id      count
1            5
2            2
3            1
4            7

and a sales table

game_id      price     otherstuff 
3            xxx       xxxxx
2            xxxx      xxxxxxxx

I would like to create a trigger that checks what game_id is added to the sales table. Then decreases the count by -1 in the inventory table. I would guess its "After update".

CREATE DEFINER=`root`@`localhost` TRIGGER `sales_AFTER_INSERT` AFTER INSERT ON `sales` FOR EACH ROW BEGIN
update inventory set count = count-1 where game_id = game_id;
END

After testing this script it just decreases every game_id count by -1 everytime I insert some new sales.

Upvotes: 0

Views: 523

Answers (2)

Maksym Fedorov
Maksym Fedorov

Reputation: 6456

You have incorrect game_id in your query. If you want to get game_id from a modified record you should use NEW.game_id

CREATE DEFINER =`root`@`localhost`
  TRIGGER `sales_AFTER_INSERT`
  AFTER INSERT
  ON `sales`
  FOR EACH ROW
BEGIN
  UPDATE inventory SET count = count - 1 WHERE game_id = NEW.game_id;
END

Upvotes: 1

farbiondriven
farbiondriven

Reputation: 2468

You need to use NEW:

CREATE DEFINER=`root`@`localhost` 
   TRIGGER `sales_AFTER_INSERT` 
   AFTER INSERT ON `sales` FOR EACH ROW 
   BEGIN
        update inventory set count = count-1 where game_id = new.game_id;
END

Upvotes: 1

Related Questions