MySQL crear Trigger y Cursor

En este artículo vamos a ver el uso y la ventajas al usar trigger y crear cursor dentro del mismo, para ello nos vamos a basar en 4 tablas (TbMovimiento, TbDetMovimiento, TbExistencia y TbKardex). Desde mi punto de vista los trigger ayudan a que nos ahorremos hacer procesos en el lenguaje de desarrollo que estemos usando, por ejemplo si insertamos un detalle y a la vez queremos que ese producto o lo que se esta insertando actualice la existencia y también se registra en el kardex, fácilmente se puede hacer con un trigger y esto nos ayudara a desarrollar de manera mas abierta que quiero decir con esto, desde cualquier herramienta de desarrollo que hagamos un INSERT INTO funcionara ahorrándonos en estar programando en el mismo lenguaje y no ser reutilizado desde otra herramienta.

Si alguien no entendió con mi pequeña explicación a continuación viene todo lo que es referente a un trigger. 

Definición: Un trigger (o disparador) en una Base de datos, es un procedimiento que se ejecuta cuando se cumple una condición establecida al realizar una operación. Dependiendo de la base de datos, los triggers pueden ser de inserción (INSERT), actualización (UPDATE) o borrado (DELETE). Algunas bases de datos pueden ejecutar triggers al crear, borrar o editar usuarios, tablas, bases de datos u otros objetos.

Usos: Son usados para mejorar la administración de la Base de datos, sin necesidad de contar con que el usuario ejecute la sentencia de SQL. Además, pueden generar valores de columnas, previene errores de datos, sincroniza tablas, modifica valores de una vista, etc. Permite implementar programas basados en paradigma lógico (sistemas expertos, deducción).

Componentes principales: La estructura básica de un trigger es:

  • Llamada de activación: es la sentencia que permite "disparar" el código a ejecutar.
  • Restricción: es la condición necesaria para realizar el código. Esta restricción puede ser de tipo condicional o de tipo nulidad.
  • Acción a ejecutar: es la secuencia de instrucciones a ejecutar una vez que se han cumplido las condiciones iniciales.

Tipos: Existen dos tipos de disparadores que se clasifican según la cantidad de ejecuciones a realizar:

  • Row Triggers (o Disparadores de fila): son aquellas que se ejecutaran n-veces si se llama n-veces desde la tabla asociada al trigger
  • Statement Triggers (o Disparadores de secuencia): son aquellos que sin importar la cantidad de veces que se cumpla con la condición, su ejecución es única.Pueden ser de sesión y almacenados; pero no son de fiar
Efectos y características

  • No aceptan parámetros o argumentos (pero podrían almacenar los datos afectados en tablas temporales)
  • No pueden ejecutar las operaciones COMMIT o ROLLBACK por que estas son parte de la sentencia SQL del disparador (únicamente a través de transacciones autónomas)
  • Pueden causar errores de mutaciones en las tablas, si se han escrito de manera deficiente.
Estructura de las tablas:

CREATE TABLE `tbmovimiento` (
  `NumMov` CHAR(10) NOT NULL,
  `TipMov` CHAR(2) NOT NULL,
  `CodSucursal` CHAR(2) NOT NULL,
  `Fecha` DATETIME DEFAULT NULL,
  `CodCli` CHAR(6) NOT NULL,
  `Total` DECIMAL(14,2) DEFAULT '0.00',
  `TotalCant` DECIMAL(10,0) DEFAULT '0',
  `lCancelado` INT(1) DEFAULT '0',
  PRIMARY KEY (`NumMov`,`TipMov`,`CodSucursal`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;


CREATE TABLE `tbdetmovimiento` (
  `NumMov` CHAR(10) NOT NULL,
  `TipMov` CHAR(2) NOT NULL,
  `CodSucursal` CHAR(2) NOT NULL,
  `CodProd` CHAR(6) NOT NULL,
  `Cantidad` DECIMAL(6,0) DEFAULT '0',
  `Precio` DECIMAL(14,2) DEFAULT '0.00'

) ENGINE=INNODB DEFAULT CHARSET=latin1;


CREATE TABLE `tbexistencia` (
  `CodProd` CHAR(6) NOT NULL,
  `CodSucursal` CHAR(2) NOT NULL,
  `Cantidad` DECIMAL(10,0) DEFAULT '0',
  `FechaRevInv` DATETIME DEFAULT NULL,
  PRIMARY KEY (`CodProd`,`CodSucursal`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;


CREATE TABLE `tbkardex` (
  `CodKardex` INT(11) NOT NULL AUTO_INCREMENT,
  `CodSucursal` CHAR(2) DEFAULT NULL,
  `NumMov` CHAR(10) DEFAULT NULL,
  `TipMov` CHAR(2) DEFAULT NULL,
  `Fecha` DATETIME DEFAULT NULL,
  `CodProd` CHAR(6) DEFAULT NULL,
  `Ingreso` DECIMAL(10,0) DEFAULT '0',
  `Salida` DECIMAL(10,0) DEFAULT '0',
  PRIMARY KEY (`CodKardex`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;
Las tablas que se usa en este ejemplo solo son demostrativos y no trato de decir nada con esto ya que cada diseñador de base de dato sabe las necesidades que puede tener y a la vez la estructura de sus tablas, digo es porque en algun momento hice otro ejemplo en un foro y se armo un debate porque pensaron que yo esta afirmando que asi debe ser el diseño de las tablas. 

Los campos que se repiten como TipMov, CodSucursal, es para poder controlar en una misma tabla mas de un documento y a la vez también registrar mas de una tienda a esto añadimos el NumMov que seria el numero de cada documento según la tienda, sucursal o local que puedan tener.

Vamos a comenzar a crear los trigger para la tabla DetMovimiento con este trigger vamos a ingresar los datos a la tabla TbExisistencia y a la Tabla TbKardex

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `malkasoftadpi`.`TbDetMovInsertKardexExistencia`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `TbDetMovInsertKardexExistencia` AFTER INSERT ON `tbdetmovimiento` 
    FOR EACH ROW BEGIN
DECLARE nIng NUMERIC(6,0);
DECLARE nSal NUMERIC(6,0);
DECLARE nCant NUMERIC(6,0);
DECLARE cTipMov CHAR(2);
DECLARE cCodProd CHAR(6);
DECLARE cCodSuc CHAR(2);
DECLARE cNumMov CHAR(10);
SET nIng = 0;
SET nSal = 0;
SET nCant = NEW.Cantidad;
SET cTipMov = NEW.TipMov;
SET cCodProd = NEW.CodProd;
SET cCodSuc = NEW.CodSucursal;
SET cNumMov = NEW.NumMov;
/*En esta parte vamos a verificar si exite o no el producto en la tabla TbExistencia*/
IF EXISTS(SELECT CodProd FROM tbexistencia WHERE CodProd=cCodProd AND CodSucursal=cCodSuc) THEN 
  IF cTipMov='IN' THEN
UPDATE tbexistencia SET Cantidad = Cantidad+nCant 
WHERE CodProd=cCodProd AND CodSucursal=cCodSuc;
SET nIng = nCant;
   ELSEIF cTipMov='SA' THEN
UPDATE tbexistencia SET Cantidad = Cantidad-nCant 
WHERE CodProd=cCodProd AND CodSucursal=cCodSuc;
SET nSal = nCant;
   END IF;
ELSE
   INSERT INTO tbexistencia(CodProd,CodSucursal,Cantidad) VALUES(cCodProd,cCodSuc,nCant); 
   IF cTipMov='IN' THEN
SET nIng = nCant;
   ELSEIF cTipMov='SA' THEN
SET nSal = nCant;
   END IF;
END IF; 
INSERT INTO TbKardex(CodSucursal,NumMov,TipMov,Fecha,CodProd,Ingreso,Salida) VALUES(cCodSuc,cNumMov,cTipMov,NOW(),cCodProd,nIng,nSal); 
    END;
$$

DELIMITER ;

El siguiente trigger se accionara desde la tabla TbMovimiento cuando se cancela un documento, esto ara que elimine registros en la tabla TbDetMovimiento y a  su vez eliminara en la tabla TbKardex.

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `malkasoftadpi`.`TbDetMovDelKardexUpdateExist` AFTER DELETE
    ON `malkasoftadpi`.`tbdetmovimiento`
    FOR EACH ROW BEGIN
DECLARE nCodKardex INT(11);

DECLARE nCant NUMERIC(6,0);
DECLARE cTipMov CHAR(2);
DECLARE cCodProd CHAR(6);
DECLARE cCodSuc CHAR(2);
DECLARE cNumMov CHAR(10);

SET nCant = OLD.Cantidad;
SET cTipMov = OLD.TipMov;
SET cCodProd = OLD.CodProd;
SET cCodSuc = OLD.CodSucursal;
SET cNumMov = OLD.NumMov;
/*Verificamos si si son los documento que estamos trabajando*/
IF cTipMov='IN' OR cTipMov='SA' THEN  

IF cTipMov='IN' THEN 
UPDATE TbExistencia SET Cantidad=Cantidad-nCant
WHERE CodProd = cCodProd AND CodSucursal=cCodSuc;

ELSE  
UPDATE TbExistencia SET Cantidad=Cantidad+nCant 
WHERE CodProd = cCodProd AND CodSucursal=cCodSuc;

END IF;

SELECT CodKardex INTO nCodKardex FROM tbkardex WHERE NumMov=cNumMov AND TipMov=cTipMov AND CodSucursal=cCodSuc AND CodProd=cCodProd;

DELETE FROM tbkardex WHERE NumMov=cNumMov AND TipMov=cTipMov AND CodSucursal=cCodSuc AND CodProd=cCodProd;
END IF; 
    END$$

DELIMITER ;

El siguiente trigger es para cancelar un documento, a su vez crea un cursor de todo el detalle que este relacionado con dicho documento que estamos cancelando.


DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `malkasoftadpi`.`TbMovCancelarDocumento` AFTER UPDATE
    ON `malkasoftadpi`.`tbmovimiento`
    FOR EACH ROW BEGIN
  DECLARE cNumMov CHAR(10);
  DECLARE cCodSuc CHAR(2);
  DECLARE cTipMov CHAR(2);
  DECLARE cCodProd CHAR(6);
  
  DECLARE TmpProdCan CURSOR FOR SELECT NumMov,CodSucursal,TipMov,CodProd FROM 
  TbDetMovimiento WHERE NumMov=OLD.NumMov AND CodSucursal=OLD.CodSucursal AND TipMov=OLD.TipMov;
  
  SET cNumMov = OLD.NumMov;
  SET cCodSuc = OLD.CodSucursal;
  SET cTipMov = OLD.TipMov;
  IF NEW.lCancelado!=OLD.lCancelado THEN      
OPEN TmpProdCan;
BEGIN 
DECLARE EXIT HANDLER FOR SQLSTATE '02000' BEGIN END; 
LOOP
  FETCH TmpProdCan INTO cNumMov,cCodSuc,cTipMov,cCodProd;
  DELETE FROM TbDetMovimiento WHERE NumMov=cNumMov AND CodSucursal=cCodSuc 
  AND TipMov=cTipMov AND CodProd=cCodProd;   
END LOOP;
END;
CLOSE TmpProdCan;
 END IF;
    END$$

DELIMITER ;

hasta aquí el ejemplo de como crear y utilizar cursor dentro de un trigger y a su vez vemos como nos ayuda a ingresar registros de manera sencilla y fácil.






No hay comentarios:

Publicar un comentario