TRIGGERS: Como hacer un Trigger que actualice nuestro stock desde MYSQL.
Autor: Beto Chávez Rodríguez
Lugar: Lima – Perú.
Este es un pequeño aporte de mi persona para todos ustedes que están buscando algo parecido a esto. Probablemente ustedes estén buscando algo más elaborado… pero creo que esto les dará luces de cómo hacer sus Triggers para actualizar el stock.
Sin más… nos ponemos manos a la obra.
Creamos una tabla a la cual nombraremos “movi”. Esta tabla contendrá nuestra información de movimientos de inventarios, tanto entradas como salidas.
/*Creando nuestra tabla de movimientos de inventario*/
CREATE TABLE `movi` (
`Almacen` char(2) NOT NULL,
`Clase` char(1) NOT NULL,
`Nro_Voucher` int(11) NOT NULL,
`Codigo` char(10) NOT NULL,
`Cantidad` decimal(20,5) NOT NULL DEFAULT ‘0.00000’,
PRIMARY KEY (`Almacen`,`Codigo`,`Clase`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Ahora creamos nuestra tabla a la que nombraremos “auditoriainventarios”. Esta tabla guardara toda la información de los UPDATE y DELETE que se realicen sobre nuestra tabla de movimientos de inventario, la misma que nombramos como “movi”.
/*Creando nuestra tabla para auditorias*/
/* sobre las actualizaciones que se hagan*/
/*sobre nuestra tabla de movimientos de inventario*/
CREATE TABLE `auditoriainventarios` (
`Almacen` char(2) DEFAULT NULL,
`Clase` char(1) DEFAULT NULL,
`Nro_Voucher` int(11) DEFAULT NULL,
`Codigo` char(10) DEFAULT NULL,
`Cantidad_Anterior` decimal(20,5) DEFAULT ‘0.00000’,
`Cantidad_Nueva` decimal(20,5) DEFAULT ‘0.00000’,
`Usuario` char(30) DEFAULT NULL,
`Fecha_Hora` char(20) DEFAULT NULL,
`Accion` tinytext
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Y por ultimo creamos nuestra tabla “stock”. Esta tabla contendrá los stock de cada producto o articulo de por almacén o ubicación física.
/*Tabla que almacenara la informacion con los stock de nuestros almacenes*/
CREATE TABLE `stock` (
`Almacen` char(2) NOT NULL,
`Codigo` char(10) NOT NULL,
`StockActual` decimal(20,0) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`Almacen`,`Codigo`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Como ya tenemos nuestras tablas necesarias para almacenar nuestra información, procedemos a crear nuestros 3 TRIGGERS, responsables de llevar nuestro stock actualizado.
TRIGGER: Stock_Insert.
Servirá para actualizar el stock al momento en que se inserta un nuevo movimiento de inventario. También verificara si es un producto nuevo, registrándolo en nuestra tabla de stock para poder llevar el control de sus movimientos.
TRIGGER: Stock_Update.
Servirá para actualizar el stock al momento en que se Modifica un movimiento de inventario. Registrara también en nuestra tabla de auditorías, la cantidad que estuvo antes de ser modificado, como también la nueva cantidad, junto con el usuario que hizo la modificación y desde que maquina lo hizo. También registrara la fecha y hora de la operación.
TRIGGER: Stock_Delete.
Servirá para actualizar el stock al momento en que se Elimina un movimiento de inventario. Registrara también en nuestra tabla de auditorías, los datos que estuvieron antes de ser eliminado.
Aquí el código de nuestros tres TRIGGERS:
/*Creamos nuestro primer TRIGGER*/
DELIMITER $$
CREATE
/*Ejemplo realizado por Beto Chávez. Lima - Peru. */
TRIGGER Stock_Insert BEFORE INSERT ON movi
FOR EACH ROW
BEGIN
/*Evaluamos la clase de movimiento que se esta realizando*/
/*I=Para cuando ingresa Mercadería*/
/*S=Para cuando Sale Mercadería*/
IF New.Clase='I THEN /*Ingresos de Mercaderia*/
/*Verificamos si el registro existe en nuestra tabla de Stock*/
/*Si esiste, sera igual a 1 sino sera 0 */
IF (SELECT COUNT(codigo) FROM test.stock WHERE Almacen=new.Almacen AND Codigo=new.Codigo)=0 THEN
/*Insertamos el registro si no existe en nuestra tabla de stock*/
INSERT INTO test.stock (Almacen,Codigo,StockActual) VALUES (NEW.Almacen,NEW.Codigo,NEW.Cantidad);
ELSE
/*Si el registro ya existe, solo actualizamos el stock sumando la nueva cantidad*/
UPDATE test.stock SET StockActual=StockActual+New.Cantidad WHERE Almacen=NEW.Almacen AND Codigo=NEW.Codigo;
END IF;
ELSEIF New.Clase='S THEN /*Salidas de Mercaderia*/
/*Si es una salida, necesariamente el registro debe existir en nuestra tabla de stock*/
/*y solo actualizamos descontando del stock, la cantidad saliente*/
UPDATE test.stock SET StockActual=StockActual-New.Cantidad WHERE Almacen=NEW.Almacen AND Codigo=NEW.Codigo;
END IF;
END;
$$
DELIMITER ;
/*Creamos nuestro segundo TRIGGER*/
DROP TRIGGER Stock_Update;
DELIMITER $$
CREATE
/*Ejemplo realizado por Beto Chávez. Lima - Peru. */
TRIGGER Stock_Update BEFORE UPDATE ON movi
FOR EACH ROW
BEGIN
/*Evaluamos la clase de movimiento que se esta realizando*/
/*I=Para cuando ingresa Mercadería*/
/*S=Para cuando Sale Mercadería*/
IF New.Clase='I THEN /*Ingresos de Mercaderia*/
/*Solo actualizamos el stock descontando la cantidad anterior y sumando la nueva cantidad*/
UPDATE test.stock SET StockActual=(StockActual-OLD.Cantidad)+New.Cantidad WHERE Almacen=NEW.Almacen AND Codigo=NEW.Codigo;
ELSEIF New.Clase='S THEN /*Salidas de Mercaderia*/
/*Solo actualizamos el stock sumando la cantidad anterior y restando la nueva cantidad*/
UPDATE test.stock SET StockActual=(StockActual+OLD.Cantidad)-New.Cantidad WHERE Almacen=NEW.Almacen AND Codigo=NEW.Codigo;
END IF;
/*Auditamos esta modificación. Claro esto es para atrapar a quienes hacen travesuras, a veces mal intencionadas*/
INSERT INTO test.auditoriainventarios (Almacen,Clase,Nro_Voucher,Codigo,Cantidad_Anterior,Cantidad_Nueva,Usuario,Fecha_Hora,Accion)
VALUES(OLD.Almacen,OLD.Clase,OLD.Nro_Voucher,OLD.Codigo,OLD.Cantidad,NEW.Cantidad,SESSION_USER(),CONCAT(CURRENT_DATE,' ,CURRENT_TIME),'Modificación);
END;
$$
DELIMITER ;
/*Creamos nuestro tercer TRIGGER*/
DELIMITER $$
CREATE
/*Ejemplo realizado por Beto Chávez. Lima - Peru. */
TRIGGER Stock_Delete BEFORE DELETE ON movi
FOR EACH ROW
BEGIN
/*Evaluamos la clase de movimiento que se esta realizando*/
/*I=Para cuando ingresa Mercadería*/
/*S=Para cuando Sale Mercadería*/
IF OLD.Clase='I THEN /*Ingresos de Mercaderia*/
/*Solo actualizamos el stock descontando la cantidad anterior */
UPDATE test.stock SET StockActual=StockActual-OLD.Cantidad WHERE Almacen=OLD.Almacen AND Codigo=OLD.Codigo;
ELSEIF OLD.Clase='S THEN /*Salidas de Mercaderia*/
/*Solo actualizamos el stock sumando la cantidad anterior */
UPDATE test.stock SET StockActual=StockActual+OLD.Cantidad WHERE Almacen=OLD.Almacen AND Codigo=OLD.Codigo;
END IF;
/*Auditamos esta modificación. Claro esto es para atrapar a quienes hacen travesuras, a veces mal intencionadas*/
INSERT INTO test.auditoriainventarios (Almacen,Clase,Nro_Voucher,Codigo,Cantidad_Anterior,Cantidad_Nueva,Usuario,Fecha_Hora,Accion)
VALUES(OLD.Almacen,OLD.Clase,OLD.Nro_Voucher,OLD.Codigo,OLD.Cantidad,0,SESSION_USER(),CONCAT(CURRENT_DATE,' ,CURRENT_TIME),'Eliminación);
END;
$$
DELIMITER ;
Ahora, solamente nos queda probar nuestro trigger.
18 comentarios:
Y QUE SUCEDE CUANDO DOS O MAS USUARIOS VENDEN UN MISMO PRODUCTO AL MISMO TIEMPO Y UNO O MAS SE QUEDAN SIN STOCK OJO CADA FACTURA PUEDE CONTENER MUCHOS PRODUCTOS A VENDERSE
Lo que podrías hacer es una consulta de verificación de stock antes de insertar un ítem que descargue stock.
Cuando 2 usuarios intentan descargar un ultimo producto (al mismo tiempo como indicas) necesariamente uno de los 2 debe llegar primero con su petición, y este será el que tome el ultimo producto.
Podrias explicarme como Probar IF New.Clase='I THEN
...
donde defino 'I'
Por Favor.
Perdona por la demora: Sería así: IF New.Clase='I' THEN donde I seria el identificador de movimiento, es decir, I cuando es Ingreso y S cuando es salida, de esta manera el sistema sabra que hacer con el stock.
hola, por favor no se si le pudieras echar un vistazo a mi codigo no corre, gracias
CREATE TRIGGER actulizarstockentraspasos AFTER INSERT ON detalletraspaso
FOR EACH ROW
BEGIN
DECLARE mio INTEGER;
IF (SELECT almacenprendas.stock INTO mio FROM almacenprendas WHERE almacenprendas.idprendas=new.idprendas AND almacenprendas.idalmacen=new.idalmacenentrada) = 0 THEN
INSERT INTO almacenprendas (idalmacenprenda,idalmacen,idprendas,stock) VALUES (NULL,new.idalmacenentrada,new.idprendas,new.Cantidad);
SET almacenprendas.stock = (almacenprendas.stock - new.cantidad) WHERE almacenprendas.idalmacen=new.idalmacensalida AND almacenprendas.idprendas = new.idprendas;
ELSE
SET almacenprendas.stock = (almacenprendas.stock + new.cantidad) WHERE almacenprendas.idalmacen=new.idalmacenentrada AND almacenprendas.idprendas = new.idprendas;
SET almacenprendas.stock = (almacenprendas.stock - new.cantidad) WHERE almacenprendas.idalmacen=new.idalmacensalida AND almacenprendas.idprendas = new.idprendas;
END IF;
END;
Esto esta mal, actualizas el stock sin verificar si esingreso o salida. lo que estas haciendo es sumar x cantidad e inmediatamente quitas una x cantidad de tal manera que tu stock va a permanecer igual, como si no hubieras hecho nada.
SET almacenprendas.stock = (almacenprendas.stock + new.cantidad) WHERE almacenprendas.idalmacen=new.idalmacenentrada AND almacenprendas.idprendas = new.idprendas;
SET almacenprendas.stock = (almacenprendas.stock - new.cantidad) WHERE almacenprendas.idalmacen=new.idalmacensalida AND almacenprendas.idprendas = new.idprendas;
hey que tal estoy tratando de implementar un trigger en mysql pero tengo una duda,
has de cuenta quiero que cuando el usuario registre las salidas de inventario desde una aplicación java y si escribe que salieron 101 pero en la bd solo cuenta con 100 que notifique al usuario y que no se ejecute el trigger
La cantidad, debes validarlo al momento de registrar el ítem, si el stock no cumple con el requerimiento, no deberías insertar el ítem.
DECLARE inv_a int;
SELECT cantidad INTO inv_a FROM inventario WHERE id = new.id_prod;
IF inv_a >= new.cantidad THEN
update inventario set cantidad = cantidad - new.cantidad where id = new.id_prod
COMMIT;
ELSE
ROLLBACK;
END IF
hice este trigger pero no me deja me sale error en el commit , saben porque?
Puedes publicar todo el texto del mensaje de error?
Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COMMIT;
ELSE
ROLLBACK;
END IF
END' at line 12
le empece a mover mas y ahora me sale esto
Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELSE
ROLLBACK
END IF' at line 15
he estado buscando información y se me hace que estoy mal, en los triggers no se permite utilizar commit y rollback, pero quisiera saber si me podrías explicar lo de old y new
Me parece que te falta ; al final de esta linea.
Old. Es el dato grabado en la tabla, New es el nuevo dato que ingresara por primera ves o a reemplazar a un dato OLD.
Disculpa ; al final update inventario set cantidad = cantidad - new.cantidad where id = new.id_prod.
osea, debería ser asi: update inventario set cantidad = cantidad - new.cantidad where id = new.id_prod;
Hola amigo tengo algunas dudas. Cuando una compra registrada (clase I) se desea modificar la cantidad de un producto, este registro debería ser ingreso (I) o salida (S) en la tabla movimiento. espero tu ayuda . gracias.
Excelente felicidades.... queria que me ayude en una cosa tengo 2 campos que necesito controlar uno stock y
el otro cantidad_x_unidad es para vender por unidad el caso esque cuando se me acaben las cantidad por unidad recien
descuente el stock caso contrario si es venta normal se me descuenta automatico el stock..
si habria una forma de saber si el usuario ingreso cantidad x unidad para que se descuente el de caso contrario
que se descuente el stock me ayudas xfa
Publicar un comentario