How to create a mysql Trigger to take stock?

Question

First I will present the database structure:

Order table:

id
cliente
data
status

Table Items:

id
produto
tamanho = Estoque.id
quantidade
pedido = Pedidos.id

Stock Table

id
tamanho
quantidade
chave = Produto.id

Product Table

id
codigo
nome
preco

It is a clothing store, so a products can have 1 or N sizes and each size can have 0 or N pieces in stock

I would like to create a Rigger, so that when updating the order status to 3(Paid), the stock table is updated, decreasing the quantity of products purchased in this order

would be something like

"update stock quantity=(quantity-items.quantity) Where items.id...

Then I don’t know if the beginning has any meaning when it comes to mysql and how to continue after that so that everything happens correctly...

Thank you in advance

Answer 1

Mysql Trigger does not work for a table column but for a row.

Therefore, you will need to check if the line contains new values and proceed accordingly.

Create a Trigger

To syntax to create a Trigger is relatively simple:

DELIMITER $$;
CREATE TRIGGER baixaDeEstoque
AFTER UPDATE ON pedidos
FOR EACH ROW
BEGIN
   --código aqui
END$$

Create a Trigger verifying values

How do you intend to react if the state is pago, you should add this check to the Trigger making use of NEW and OLD which gives you the new value and the old value respectively for the given column:

if NEW.minhaColuna <=> OLD.minhaColuna

With the addition of a confirmation where we evaluate whether the new column value status is actually pago. This is because if it is another state that has been applied, we do not want to lower the stock.

Let us therefore choose the following code::

DELIMITER $$;
CREATE TRIGGER baixaDeEstoque
AFTER UPDATE ON pedidos
FOR EACH ROW
if (NEW.status <=> OLD.status) AND NEW.status = 'pago' 
BEGIN
   --código aqui
END$$

Final Code

Your final code would look something like this:

DELIMITER $$;
CREATE TRIGGER baixaDeEstoque
AFTER UPDATE ON pedidos
FOR EACH ROW
if NEW.status <=> OLD.status AND NEW.status = 'pago' 
BEGIN
   UPDATE estoque
   INNER JOIN itens ON estoque.id = itens.tamanho
   SET quantidade = quantidade-1
   WHERE itens.pedido = NEW.id;
END$$