Error Code: 1442. Can't update table 'inventario' in stored function/trigger because it is already used by statement

Carlos David beltran 0
Viewed 30
Delimiter //
CREATE  TRIGGER disminuir_cantidades
AFTER INSERT ON inventario for each row
begin
DECLARE _salida int;
DECLARE _ID_Inventario int;

SET _salida =NEW.Salida ;
SET _ID_Inventario=NEW.ID_Inventario;
UPDATE inventario SET Disponible =Disponible-Salida where ID_Inventario=_ID_Inventario;
end
//
error-code triggers mysql
1 Answers

If you need to alter some values in the row to be inserted than you must not execute UPDATE statement (which is not allowed due to possible cyclic actions) but SET new columns values in NEW pseudotable in BEFORE trigger:

CREATE TRIGGER disminuir_cantidades
BEFORE INSERT ON inventario 
FOR EACH ROW
SET NEW.Disponible = NEW.Disponible - NEW.Salida;

The trigger contains only one statement - so neither BEGIN-END nor DELIMITER are needed.

PS. I assume that your INSERT which fires the tigger uses the value for Disponible which is taken from the row with previous state. If not then you'd use according subquery which returns needed value. For example

CREATE TRIGGER disminuir_cantidades
BEFORE INSERT ON inventario 
FOR EACH ROW
SET NEW.Disponible = ( SELECT inventario.Disponible - NEW.Salida
                       FROM inventario 
                       WHERE inventario.user_id = NEW.user_id
                       ORDER BY inventario.created_at DESC LIMIT 1 );

Of course you must take into account that the row to be inserted for a user can be the first row for him, and previous row not exists.

Akina0
Related