Question:

First, I will present the structure of the database:

Order Table:

id
cliente
data
status

Items Table:

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 product can have 1 or N sizes and each size can have 0 or N pieces in stock

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

would be something like

"update stock set quantity=(quantity-items.quantity) where items.id…

Then I don't know if the beginning makes any sense when it comes to mysql and I don't know how to continue after that so that everything happens correctly…

thanks in advance

Answer:

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

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

Create a trigger

The syntax for creating a trigger is relatively simple:

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

Create a trigger by checking values

How do you intend to react if the status is pago , you must add this verification in the trigger using NEW and OLD which gives you the new value and the old value respectively for the indicated column:

if NEW.minhaColuna <=> OLD.minhaColuna

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

So let's 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$$