root@localhost [bctr]>select * from information_schema.triggers where trigger_schema='bctr'\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: bctr
              TRIGGER_NAME: TGR_BOLSA_FACTURA_INS
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: bctr
        EVENT_OBJECT_TABLE: bolsa_factura
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
     INSERT INTO HISTORICO_BOLSA_FACTURA(id_bolsa, id_factura, descripcion, fecha_telcel)
    VALUES(NEW.id_bolsa, NEW.id_factura, 'INSERCION', CURRENT_TIMESTAMP);
END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@%
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
*************************** 2. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: bctr
              TRIGGER_NAME: TGR_BOLSA_FACTURA_DEL
        EVENT_MANIPULATION: DELETE
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: bctr
        EVENT_OBJECT_TABLE: bolsa_factura
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
     INSERT INTO HISTORICO_BOLSA_FACTURA(id_bolsa, id_factura, descripcion, fecha_telcel)
    VALUES(OLD.id_bolsa, OLD.id_factura, 'ELIMINACION', CURRENT_TIMESTAMP);
END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@%
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
*************************** 3. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: bctr
              TRIGGER_NAME: TRG_FACTURA_DEL
        EVENT_MANIPULATION: UPDATE
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: bctr
        EVENT_OBJECT_TABLE: factura
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
   IF OLD.monto_resta <> 0 AND NEW.monto_resta = 0 THEN
      DELETE FROM BOLSA_FACTURA
      WHERE id_factura = OLD.id_factura;
   else
   IF OLD.monto_resta <= 0 AND NEW.monto_resta > 0 THEN
        INSERT INTO BOLSA_FACTURA(id_bolsa, id_factura, fecha_telcel)(SELECT id_bolsa, id_factura, CURRENT_TIMESTAMP
         FROM HISTORICO_BOLSA_FACTURA
         WHERE id_factura = OLD.id_factura
         LIMIT 1);
    END IF;
   END IF;
END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@%
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
*************************** 4. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: bctr
              TRIGGER_NAME: LIMITE_CREDITO_TRIGGER
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: bctr
        EVENT_OBJECT_TABLE: historico_limite_credito
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
   DECLARE AUX_REGION TINYINT DEFAULT 0;
   DECLARE AUX_ID_CLIENTE INT DEFAULT 0;
   UPDATE LIMITE_CREDITO
   SET FECHA = NEW.FECHA_MOV
   WHERE CUENTA = NEW.CUENTA;

   SELECT ID_CLIENTE, REGION
   INTO AUX_ID_CLIENTE,AUX_REGION
   FROM LIMITE_CREDITO
   WHERE CUENTA = NEW.CUENTA;

   INSERT INTO LOG_LDC(ID_CLIENTE, REGION, MONTO, FECHA, BLOQUEO_MORA)
   VALUES(AUX_ID_CLIENTE, AUX_REGION, NEW.DISPONIBLE, NEW.FECHA_MOV, NEW.BLOQUEO_MORA);

END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@%
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
*************************** 5. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: bctr
              TRIGGER_NAME: TGR_LOG_FAC_SAP
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: bctr
        EVENT_OBJECT_TABLE: log_fac_sap
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: SWL_return:
 BEGIN
   DECLARE v_contador MEDIUMINT;
   DECLARE v_monto_parcial DECIMAL(15,2);

   DECLARE v_tipo_bolsa TINYINT(4);
   DECLARE v_id_bolsa INT(11);
   DECLARE v_id_cliente INT(11);
   DECLARE v_reg INT;
   DECLARE v_monto_resta decimal(15,2);
   DECLARE NO_DATA INT DEFAULT 0;

   DECLARE BOLSA_FAC_POS  CURSOR FOR SELECT * FROM V_BOLSA_FACTURA
   WHERE id_bolsa = v_id_bolsa
   AND monto_resta > 0;

   DECLARE BOLSA_FAC_NEG  CURSOR FOR SELECT * FROM V_BOLSA_FACTURA
   WHERE id_bolsa = v_id_bolsa
   AND monto_resta < 0;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_DATA = -1;

   SELECT COUNT(rfc) INTO v_contador
   FROM CLIENTE WHERE rfc =  NEW.rfc  and  tipo_bolsa = 1;

   IF (v_contador = 0) THEN
      SET NEW.status = 'C';
      SET NEW.observaciones = 'NO EXISTE CLIENTE, VERIFIQUE SU INFORMACION';
      LEAVE SWL_return;
   END IF;

   SELECT tipo_bolsa, id_cliente INTO v_tipo_bolsa,v_id_cliente
   FROM CLIENTE WHERE rfc =  NEW.rfc  and  tipo_bolsa = 1;


   IF (v_tipo_bolsa = 2) THEN
      SET NEW.status = 'C';
      SET NEW.observaciones = 'EL CLIENTE ES CREDITO ABIERTO, VERIFIQUE SU INFORMACION';
      LEAVE SWL_return;
   END IF;

   SELECT COUNT(id_cliente)
   INTO v_contador
   FROM REGION_BOLSA
   WHERE id_cliente = v_id_cliente
   AND region = NEW.region;

   IF (v_contador = 0) THEN
      SET NEW.status = 'C';
      SET NEW.observaciones = 'NO EXISTE BOLSA PARA ESTE CLIENTE, VERIFIQUE SU INFORMACION';
      LEAVE SWL_return;
   END IF;

   SELECT tipo_bolsa, id_bolsa
   INTO v_tipo_bolsa,v_id_bolsa
   FROM REGION_BOLSA
   WHERE id_cliente = v_id_cliente
   AND region = NEW.region;

   IF (v_tipo_bolsa = 2) THEN
      SET NEW.status = 'C';
      SET NEW.observaciones = 'EL CLIENTE ES DE CREDITO ABIERTO, VERIFIQUE SU INFORMACION';
      LEAVE SWL_return;
   END IF;

   IF (v_id_bolsa IS NULL) THEN
      SET NEW.status = 'C';
      SET NEW.observaciones = 'NO EXISTE BOLSA PARA ESTE CLIENTE, VERIFIQUE SU INFORMACION';
      LEAVE SWL_return;
   END IF;

   SELECT COUNT(id_factura)
   INTO v_contador
   FROM FACTURA
   WHERE id_factura = NEW.factura;

   IF(v_contador <> 0) THEN
      SET NEW.status = 'C';
      SET NEW.observaciones = 'FACTURA EXISTENTE, VERIFIQUE SU INFORMACION';
      LEAVE SWL_return;
   END IF;

   SET v_monto_resta = NEW.monto_compra;
   SET v_monto_parcial = 0;

   IF(NEW.monto_compra > 0) THEN
      SET v_id_bolsa = v_id_bolsa;
      OPEN BOLSA_FAC_NEG;
      SET NO_DATA = 0;
      FETCH BOLSA_FAC_NEG INTO v_id_bolsa;
      WHILE NO_DATA = 0 DO
         IF(v_monto_resta > 0) THEN
            IF (-SWV_bolsa_fac_neg_MONTO_RESTA > v_monto_resta) THEN
               UPDATE FACTURA
               SET monto_resta = monto_resta+v_monto_resta
               WHERE id_factura = SWV_bolsa_fac_neg_ID_FACTURA;
               SET v_monto_resta = 0;
         ELSE
               SET v_monto_resta = CONCAT(v_monto_resta,SWV_bolsa_fac_neg_MONTO_RESTA);
               UPDATE FACTURA
               SET monto_resta = 0
               WHERE id_factura = SWV_bolsa_fac_neg_ID_FACTURA;
            END IF;
         END IF;
         SET NO_DATA = 0;
         FETCH BOLSA_FAC_NEG INTO v_id_bolsa;
      END WHILE;
      SET NO_DATA = 0;
      CLOSE BOLSA_FAC_NEG;
   END IF;

   IF (NEW.monto_compra < 0) THEN
      SET v_id_bolsa = v_id_bolsa;
      OPEN BOLSA_FAC_POS;
      SET NO_DATA = 0;
      FETCH BOLSA_FAC_POS INTO v_id_bolsa;
      WHILE NO_DATA = 0 DO
         IF (v_monto_resta < 0) THEN
            IF SWV_bolsa_fac_pos_MONTO_RESTA > -v_monto_resta THEN
               UPDATE FACTURA
               SET monto_resta = monto_resta+v_monto_resta
               WHERE id_factura = SWV_bolsa_fac_pos_ID_FACTURA;
               SET v_monto_resta = 0;
         ELSE
               SET v_monto_resta = CONCAT(v_monto_resta,SWV_bolsa_fac_pos_MONTO_RESTA);
               UPDATE FACTURA
               SET monto_resta = 0
               WHERE id_factura = SWV_bolsa_fac_pos_ID_FACTURA;
            END IF;
         END IF;
         SET NO_DATA = 0;
         FETCH BOLSA_FAC_POS INTO v_id_bolsa;
      END WHILE;
      SET NO_DATA = 0;
      CLOSE BOLSA_FAC_POS;
   END IF;

   INSERT INTO FACTURA(id_factura, monto_compra, monto_resta, fecha_telcel)
    VALUES(NEW.factura, NEW.monto_compra, v_monto_resta, CURRENT_TIMESTAMP);


   IF (v_monto_resta <> 0) THEN
        INSERT INTO BOLSA_FACTURA(id_bolsa, id_factura, fecha_telcel)
        VALUES(v_id_bolsa, NEW.factura, CURRENT_TIMESTAMP);
   END IF;

   SET NEW.status = 'Y';

   SET NEW.observaciones = 'FACTURA CARGADA EXITOSAMENTE';
END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@%
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
*************************** 6. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: bctr
              TRIGGER_NAME: TGR_LOG_LDC
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: bctr
        EVENT_OBJECT_TABLE: log_ldc
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: SWL_return:
BEGIN
   DECLARE v_contador MEDIUMINT;
   DECLARE v_monto_ini DECIMAL(20,2);
   DECLARE v_monto_resta DECIMAL(20,2);
   DECLARE v_monto DECIMAL(20,2);
   DECLARE v_tipo_bolsa VARCHAR(255);
   DECLARE v_id_bolsa VARCHAR(255);
   SET NEW.fecha_telcel = CURRENT_TIMESTAMP;
   SET NEW.mensaje = 1;

   SELECT COUNT(id_cliente) INTO v_contador
   FROM CLIENTE WHERE id_cliente = NEW.id_cliente;

   IF (v_contador = 0) THEN
      SET NEW.status = 2;
      SET NEW.observaciones = CONCAT('LDC NO SE CARGO CORRECTAMENTE DEBIDO A QUE NO EXISTE EL CLIENTE ',
      NEW.ID_CLIENTE,'FECHA DE CARGA ',NEW.FECHA,', MONTO ',NEW.MONTO);
      LEAVE SWL_return;
   END IF;

   SELECT COUNT(id_cliente) INTO v_contador FROM REGION_BOLSA WHERE id_cliente = NEW.id_cliente AND region = NEW.region;

   IF (v_contador = 0) THEN
      SET NEW.status = 2;
      SET NEW.observaciones = CONCAT('LDC NO SE CARGO CORRECTAMENTE DEBIDO A QUE EL CLIENTE ',NEW.ID_CLIENTE,
      'SNO TIENE CONFIGURADA LA REGION ',NEW.REGION,'SQLWAYS_EVAL# ',NEW.FECHA,
      ', MONTO ',NEW.MONTO);
      LEAVE SWL_return;
   END IF;

   SELECT tipo_bolsa, id_bolsa INTO v_tipo_bolsa,v_id_bolsa FROM REGION_BOLSA WHERE id_cliente = NEW.id_cliente AND region = NEW.region;

   SELECT COUNT(id_bolsa) INTO v_contador FROM LDC WHERE ID_BOLSA = v_id_bolsa;

   IF (v_contador = 0) THEN
        INSERT INTO HISTORICO_LDC(ID_BOLSA, FECHA, MONTO_INI, FECHA_TELCEL, MONTO_RESTA_ANTES,MONTO_RESTA_DESPUES, BLOQUEO_MORA)
        VALUES(v_id_bolsa, CURRENT_TIMESTAMP, NEW.monto, NEW.fecha,0,NEW.monto, NEW.bloqueo_mora);

      INSERT INTO LDC(id_bolsa, fecha, monto_ini, monto_resta, BLOQUEO_MORA) VALUES(v_id_bolsa, CURRENT_TIMESTAMP, NEW.monto, NEW.monto, NEW.bloqueo_mora);

      SET NEW.status = 0;
      SET NEW.observaciones = CONCAT('PRIMER LDC DEL CLIENTE ',NEW.ID_CLIENTE,'CARGADO EXITOSAMENTE, FECHA DE CARGA ',
      NEW.FECHA,', MONTO ',NEW.MONTO);
      LEAVE SWL_return;
   END IF;

   SELECT COUNT(id_bolsa) INTO v_contador  FROM LDC WHERE id_bolsa = v_id_bolsa
   AND STR_TO_DATE(CONCAT(DATE_FORMAT(fecha,'%d%m%Y'),'000000'),'%d%m%Y%H%i%s') = STR_TO_DATE(CONCAT(DATE_FORMAT(CURRENT_TIMESTAMP,'%d%m%Y'),'000000'),
   '%d%m%Y%H%i%s');

   IF(v_contador = 0) THEN
      SELECT MONTO_RESTA INTO v_monto_resta FROM LDC WHERE ID_BOLSA = v_id_bolsa;
      INSERT INTO HISTORICO_LDC(ID_BOLSA, FECHA, MONTO_INI, FECHA_TELCEL, MONTO_RESTA_ANTES,MONTO_RESTA_DESPUES, BLOQUEO_MORA)
        VALUES(v_id_bolsa, CURRENT_TIMESTAMP, NEW.monto, NEW.fecha,v_monto_resta,NEW.monto, NEW.bloqueo_mora);

      UPDATE LDC SET MONTO_INI = NEW.monto,MONTO_RESTA = NEW.monto,FECHA = CURRENT_TIMESTAMP,
      BLOQUEO_MORA = NEW.bloqueo_mora WHERE ID_BOLSA = v_id_bolsa;
      SET NEW.status = 0;
      SET NEW.observaciones = CONCAT('PRIMER LDC DEL DIA DEL CLIENTE ',NEW.ID_CLIENTE,'CARGADO EXITOSAMENTE, FECHA DE CARGA ',
      NEW.FECHA,', MONTO ',NEW.MONTO);
      LEAVE SWL_return;
   END IF;

   SELECT MONTO_INI,MONTO_RESTA INTO v_monto_ini,v_monto_resta FROM LDC WHERE ID_BOLSA = v_id_bolsa;

   IF (v_monto_resta = 0) THEN
      SELECT MONTO_RESTA_DESPUES INTO v_monto_resta FROM HISTORICO_LDC
      WHERE ID_BOLSA = v_id_bolsa and FECHA =(SELECT max(FECHA) FROM HISTORICO_LDC WHERE ID_BOLSA = v_id_bolsa);
      IF (v_monto_resta > 0) THEN
         SET v_monto_resta = 0;
      END IF;
      SET v_monto = NEW.monto -v_monto_ini+v_monto_resta;
ELSE
      SET v_monto = NEW.monto -v_monto_ini+v_monto_resta;
   END IF;

   INSERT INTO HISTORICO_LDC(ID_BOLSA, FECHA, MONTO_INI, FECHA_TELCEL, MONTO_RESTA_ANTES,MONTO_RESTA_DESPUES, BLOQUEO_MORA)
    VALUES(v_id_bolsa, CURRENT_TIMESTAMP, NEW.monto, NEW.fecha,v_monto_resta,v_monto, NEW.bloqueo_mora);


   IF (v_monto <= 0) THEN
      SET v_monto = 0;
   END IF;

   UPDATE LDC SET MONTO_INI = NEW.monto,MONTO_RESTA = v_monto,FECHA = CURRENT_TIMESTAMP,
   bloqueo_mora = NEW.bloqueo_mora WHERE ID_BOLSA = v_id_bolsa;

   SET NEW.status = 0;
   SET NEW.observaciones = CONCAT('ACTUALIZACION DEL LDC DEL CLIENTE ',NEW.ID_CLIENTE,'CARGADA EXITOSAMENTE, FECHA DE CARGA ',
   NEW.FECHA,', MONTO ',NEW.MONTO);

END;
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@%
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
6 rows in set (0.11 sec)