sábado, 3 de diciembre de 2011

STOCK - Crear un trigger en Mysql

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:

Salomon dijo...

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

Adalberto Chavez dijo...

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.

andy santi almeida dijo...

Podrias explicarme como Probar IF New.Clase='I THEN
...
donde defino 'I'
Por Favor.

Adalberto Chavez dijo...

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.

Cesar Edward Vasquez Manrique dijo...

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;

El Vivo del Futbol dijo...

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;

Fernando rodriguez olivas dijo...
Este comentario ha sido eliminado por el autor.
Fernando rodriguez olivas dijo...

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

Adalberto Chavez dijo...

La cantidad, debes validarlo al momento de registrar el ítem, si el stock no cumple con el requerimiento, no deberías insertar el ítem.

Fernando rodriguez olivas dijo...

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?

Adalberto Chavez dijo...

Puedes publicar todo el texto del mensaje de error?

Fernando rodriguez olivas dijo...

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

Fernando rodriguez olivas dijo...

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

Fernando rodriguez olivas dijo...

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

Adalberto Chavez dijo...

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.

Adalberto Chavez dijo...

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;

Unknown dijo...

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.

Augusto carvalho dijo...

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