I have a table item with the following fields:
( id_item - id_pedido- id_produto - qtde - subtotal - valor _unitario )
and the following requested table with the following fields:
( id_pedido - data - id_cliente - valor_total - desconto )
And I have two more product and customer registration tables that I will not put here. My question is how to do a trigger that calculates items selected in the item table and throws the subtotal of all items to the requested table ( valor_total
).
Remembering that there can be multiple items (add this total of items to the requested table in the valor _total
field). I get with the triggers that I have just play the full value of the item, but if I add another item, it will not.
Here's the trigger I'm using:
DELIMITER $$
CREATE TRIGGER 'inserir' AFTER INSERT ON 'item' FOR EACH ROW update pedido set 'valor_total'=NEW.subtotal WHERE pedido.id_pedido = NEW.id_pedido
$$
DELIMITER ;
This one I placed just throws the subtotal of 1 item into the requested table field valor_total
.