First of all I will present the structure of the database:
Orders Table:
id
cliente
data
status
Items table:
id
produto
tamanho = Estoque.id
quantidade
pedido = Pedidos.id
Inventory table
id
tamanho
quantidade
chave = Produto.id
Product Table
id
codigo
nome
preco
This 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 when updating the order status to 3 (Payment), the stock table is updated, reducing the amount of products purchased in that order
would be something like
"update inventory set quantity = (quantity-items.quantity) where itens.id ...
I do not know if the beginning has any meaning to be mysql or how to continue after this so that everything happens correctly ...
Thank you in advance