How do I create a mysql trigger to drop inventory?

Question

How do I create a mysql trigger to drop inventory?

Navigation

#1 by (6 votes)

4

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

mysql

asked by anonymous 27.06.2014 / 22:06

1 answer

How to change the value of a property given its name PHP value rounding error

score 6 · Accepted Answer

MySQL Trigger does not work for a table column but for one line.

So you have to check if the line contains new values and proceed accordingly.

Create a trigger

The 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 checking values

As you want to react if the state is pago , you should add this check in trigger using NEW and OLD which gives you the new value and the old value respectively the column indicated:

if NEW.minhaColuna <=> OLD.minhaColuna

With the addition of a commit 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 give up inventory.

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$$