Good attempt to make a kardex and the process of movements I want to do with a stored procedure.
This is the Relational Model of the database:
Where product_cod of the kardex table is the product_id of the product table and the concept field is of type enum ('Input', 'Output') of the kardex table.
The procedure I need to do is that every time I enter the kardex table a product with its axis quantity field: 1 and in the concept option it is equal to 'Input' I update the product table with the amount entered. The same with the exit option.
Axis: if in the product table I have a product with the quantity 5 and in the kardex table I select the product and I give it the entry option and in the amount income 3 then the product table of that product the quantity would be 8 , and likewise in exit since in this it is subtracted.
I currently have this code:
DELIMITER $$
CREATE PROCEDURE ACTUALIZA_STOCK (n_cantidad int, n_concepto enum, id int)
BEGIN
if n_concepto='Entrada' then
UPDATE producto set cantidad=cantidad+n_cantidad WHERE id_producto=id;
end if
if n_concepto='Salida' then
UPDATE producto set cantidad=cantidad-n_cantidad WHERE id_producto=id;
end if;
END $$
but I get the second error:
# 1064 - Something is wrong in its syntax near 'id int) BEGIN if n_concepto = 'Entrada' then UPDATE product set quantity = cant 'on line 1
Your help would be appreciated.