To start I will show the structure of my 3 tables
este tabla seria los productos
Producto (
CODIGO int(11),
CANTIDAD int(11)
)
this would be requested header
Pedido (
codigo int(11),
nombre varchar(50),
unix varchar(100)
)
and this would be the order detail table
Detalle (
codigo int(11),
producto int(11),
cantidad` int(11)
)
Finally, when registering an order, it registers the header and the detail, it does it well
my problem is that when I delete an order I need the quantity of the products registered in the order detail to be returned to the Product table, I made a trigger that in theory would do that but I guess I failed in the code because I delete an Order and it does not update the product table
CREATE TRIGGER Devolver_Productos_Prueba AFTER DELETE ON Detalle FOR EACH ROW
Update Producto
set Producto.CANTIDAD = (Producto.CANTIDAD + old.cantidad)
where Producto.CODIGO = old.codigo;
I would like you to help me, thanks in advance for your help
You must remember that the trigger is executed for each row of the table
Detalle
that is deleted, thereforeOLD
it refers to the record of the tableDetalle
that is being deletedIn the update that you show there is one
old.cant
that is incorrect based on the tableDetalle
, the correct thing would beOLD.cantidad
The same with
old.cod
, the correct thing would beOLD.codigo
And this would be the final result
In the detail table I see that you put the code , product , and quantity fields , which makes me understand that the code field is probably the primary key of the detail table. Now look at the trigger in this part: where Product.CODE = old .code; shouldn't it be old.product ? that is, you should put it like this: where Product.CODE = old.product;