Conociendo la aplicación que insertó filas a una tabla

2 comentarios

Cuando una Base de Datos puede ser accedida desde varias aplicaciones es muy importante saber cual aplicación insertó las filas que tiene cada tabla.

¿Por qué?

Porque solamente la aplicación que insertó la fila debería tener el derecho de modificar o de borrar esa fila. Si otra aplicación pudiera hacerlo, eso solamente acarreará problemas. Imagínate que la aplicación de VENTAS registró una venta y cuando se la quiere consultar no se la encuentra por ningún lado porque CONTABILIDAD borró esa venta. Eso es inadmisible.

¿Qué necesitaremos?

  1. Una tabla APLICACIONES, para guardar los nombres de las aplicaciones que pueden usarse
  2. Una tabla GTT, para guardar los datos de la conexión actual, incluyendo el Identificador de la aplicación usada para el acceso
  3. Un trigger de Base de Datos que le inserte una fila a la tabla GTT
  4. Desde nuestra aplicación actualizar la tabla GTT para que tenga el Identificador de aplicación correcto
  5. Agregarle una columna a todas las tablas que pueden usarse por más de una aplicación
  6. Dos excepciones, para mostrar los mensajes de error
  7. Agregarle un trigger a cada tabla que puede usarse por más de una aplicación, para decidir si la fila puede ser modificada o eliminada

Tabla APLICACIONES

APLICACIÓN1

Captura 1. Si haces clic en la imagen la verás más grande

CREATE TABLE APLICACIONES (
   APL_IDENTI D_IDENTIFICADOR NOT NULL,
   APL_NOMBRE D_NOMBRE40);

   ALTER TABLE APLICACIONES ADD CONSTRAINT PK_APLICACIONES PRIMARY KEY (APL_IDENTI);

   ALTER TABLE APLICACIONES ADD CONSTRAINT UQ_APLICACIONES UNIQUE (APL_NOMBRE);

SET TERM ^ ;

CREATE TRIGGER BI_APLICACIONES_APL_IDENTI FOR APLICACIONES
   ACTIVE BEFORE INSERT
   POSITION 0
AS
BEGIN
   IF (NEW.APL_IDENTI IS NULL OR NEW.APL_IDENTI = 0) THEN
      NEW.APL_IDENTI = GEN_ID(APLICACIONES_APL_IDENTI_GEN, 1);
END^

SET TERM ; ^

El contenido de esta tabla podría ser algo así:

APLICACIÓN2

Captura 2. Si haces clic en la imagen la verás más grande

Tabla GTT: CONEXION_ACTUAL

En una tabla GTT guardamos los datos que necesitamos conocer de la conexión que actualmente está usando el usuario.

APLICACIÓN5

Captura 3. Si haces clic en la imagen la verás más grande

Trigger de Base de Datos para insertarle una fila a la tabla GTT

CREATE TRIGGER NEW_DBTRIGGER_C
   ACTIVE ON CONNECT
      POSITION 0
AS
BEGIN

   INSERT INTO
      CONEXION_ACTUAL
      (CON_IDENTI, CON_USUARI, CON_TIMEST)
   VALUES
      (CURRENT_CONNECTION, CURRENT_USER, CURRENT_TIMESTAMP) ;

END;

El valor de la columna faltante (CON_APLICA) debería ser puesto por la aplicación correspondiente.

Contenido de la tabla CONEXION_ACTUAL

APLICACIÓN6Captura 4. Si haces clic en la imagen la verás más grande

Ejemplo:

A la tabla CLIENTES pueden insertarle filas las aplicaciones de Contabilidad, Facturación, y Ventas. Si la fila fue insertada por Contabilidad entonces Facturación y Ventas no deberían tener el derecho de modificar esa fila ni de borrarla. De la misma manera, si la fila fue insertada por Facturación o por Ventas las otras aplicaciones no deberían poder cambiarla.

Cualquier aplicación puede consultar una fila, pero solamente la aplicación que la insertó puede modificarla o borrarla.

APLICACIÓN3

Captura 5. Si haces clic en la imagen la verás más grande

Y para ver los nombres de las aplicaciones que insertaron las filas podríamos escribir algo como:

SELECT
   C.CLI_IDENTI,
   C.CLI_NOMBRE,
   C.CLI_IDEAPL,
   A.APL_NOMBRE AS CLI_NOMAPL
FROM
   CLIENTES C
JOIN
   APLICACIONES A
      ON C.CLI_IDEAPL = A.APL_IDENTI

que nos dará como resultado algo similar a:

APLICACIÓN4

Captura 6. Si haces clic en la imagen la verás más grande

Aquí como puedes ver las filas de MARCELA, DIANA y MIRTA fueron insertadas por CONTABILIDAD, en cambio la fila de SILVIA fue insertada por FACTURACIÓN. Eso implica que si debemos modificar o borrar las filas de MARCELA, DIANA, o MIRTA deberemos hacerlo desde la CONTABILIDAD y si queremos modificar o borrar la fila de SILVIA deberemos hacerlo desde FACTURACIÓN.

Esto nos asegura que nunca una aplicación modifique o borre las filas que no le corresponden.

Las excepciones para mostrar los mensajes de error

CREATE EXCEPTION E_UPDATE_NO_ADMITIDO 'No puedes modificar el contenido de esta fila';

CREATE EXCEPTION E_DELETE_NO_ADMITIDO 'No tienes permiso para borrar filas de esta tabla';

El trigger para modificar o borrar filas de la tabla CLIENTES

CREATE TRIGGER BUD_CLIENTES1 FOR CLIENTES
   ACTIVE BEFORE UPDATE OR DELETE
      POSITION 0
AS
   DECLARE VARIABLE lnAplicacion SMALLINT;
BEGIN

   lnAplicacion = (SELECT CON_APLICA FROM CONEXION_ACTUAL);

   IF (UPDATING AND NEW.CLI_IDEAPL <> lnAplicacion) THEN
      EXCEPTION E_UPDATE_NO_ADMITIDO;

   IF (DELETING AND OLD.CLI_IDEAPL <> lnAplicacion) THEN
      EXCEPTION E_DELETE_NO_ADMITIDO;

END;

Terminando:

Y listo, eso es todo, ahora solamente la aplicación que insertó una fila podrá modificar o borrar esa fila. Los intentos realizados por las demás aplicaciones serán  rechazados, como tiene que ser.

Conclusión:

Siempre es muy importante delimitar exactamente que puede hacer cada aplicación que se conecta a nuestra Base de Datos, no podemos permitir que una aplicación modifique o borre filas que fueron insertadas por otra aplicación porque eso solamente nos traerá problemas y ningún beneficio.

Artículos relacionados:

Los triggers de la Base de Datos

El índice del blog Firebird21

Capturando excepciones del usuario

10 comentarios

Así como podemos capturar las excepciones del Firebird, las cuales tienen un número en SQLCODE o un código en GDSCODE también podemos capturar nuestras propias excepciones.

Ejemplo:

Tenemos una tabla llamada PRODUCTOS en la cual (además de otros datos) guardamos el precio de costo y el precio de venta de cada producto. Es política de la empresa que nunca el precio de venta pueda ser menor que el precio de costo aumentado en un 5%. En otras palabras, que el porcentaje de ganancia siempre debe ser 5% o más.

Esta es nuestra tabla PRODUCTOS:

CAPTURA1

Captura 1. Si haces clic en la imagen la verás más grande

Y esta es la tabla ERRORES, donde registramos todos los errores que vamos encontrando:

CAPTURA2

Captura 2. Si haces clic en la imagen la verás más grande

Esta es una excepción, que será lanzada cuando el precio de venta sea menor que el precio de costo aumentado en un 5%

CREATE EXCEPTION E_PORCENTAJE_MENOR_5 'El porcentaje de ganancia es menos que el 5%.';

Y este es un trigger de la tabla PRODUCTOS donde validamos que los precios de venta siempre sean al menos un 5% superiores al precio de costo. Y cuando no ocurre eso, insertamos una fila en la tabla ERRORES.

CREATE TRIGGER PRODUCTOS_BIU FOR PRODUCTOS
       ACTIVE BEFORE INSERT OR UPDATE
       POSITION 1
AS
BEGIN

   IF (NEW.PRD_PREVTA * 100 / NEW.PRD_PRECTO - 100 < 5) THEN
      EXCEPTION E_PORCENTAJE_MENOR_5
      'El porcentaje de ganancia es menor que el 5%. Producto = ' || NEW.PRD_NOMBRE ;

   WHEN EXCEPTION E_PORCENTAJE_MENOR_5 DO
      IN AUTONOMOUS TRANSACTION DO
         INSERT INTO ERRORES
               (ERR_MODULO, ERR_COMENT)
        VALUES ('PRODUCTOS_BIU' , 'El % de ganancia del producto *' || TRIM(NEW.PRD_NOMBRE) || '* es menor que 5%') ;

END

Fíjate que se sobreescribe el mensaje de la excepción y usamos un nuevo mensaje, personalizado, para indicar el nombre del producto que tuvo el problema. Desde luego que no es obligatorio sobreescribir el mensaje de la excepción, podríamos haber usado el mensaje por defecto, pero al sobreescribirlo tenemos más información. También cuando atrapamos la excepción indicamos el nombre del producto problemático para que al revisar la tabla ERRORES sea fácil saber cual fue ese producto.

Si ahora escribimos:

INSERT INTO PRODUCTOS (PRD_IDENTI, PRD_NOMBRE, PRD_PRECTO, PRD_PREVTA)
VALUES (0, 'COCA COLA 1 LITRO', 45, 60)

estará todo bien, ningún problema porque el porcentaje de ganancia es mayor que el 5% (es del 33%)

Sin embargo, si escribimos:

INSERT INTO PRODUCTOS (PRD_IDENTI, PRD_NOMBRE, PRD_PRECTO, PRD_PREVTA)
VALUES (0, 'FANTA NARANJA 1 LITRO', 45, 46)

no se insertará el producto ‘FANTA NARANJA 1 LITRO’ en la tabla PRODUCTOS. ¿Por qué no? porque ocurrió una excepción, (el porcentaje de ganancia es menor que el 5%) y cuando ocurre una excepción todo lo que se había hecho hasta ese momento se desecha. Por lo tanto el INSERT a la tabla PRODUCTOS es desechado. Pero el INSERT a la tabla ERRORES sí funciona, porque ese INSERT estaba dentro de una IN AUTONOMOUS TRANSACTION y por lo tanto se guarda sí o sí.

Si ahora revisamos el contenido de la tabla ERRORES veremos esto:

CAPTURA3

Captura 3. Si haces clic en la imagen la verás más grande

Y hemos conseguido lo que buscábamos: que cuando ocurra una excepción del usuario la capturemos.

Conclusión:

Así como podemos capturar las excepciones del Firebird (las que usan SQLCODE y GDSCODE) también podemos capturar nuestras propias excepciones. Siempre es conveniente registrar en una tabla las excepciones ocurridas para más adelante poder revisar esa tabla y descubrir que fue lo que se hizo mal.

Artículos relacionados:

Capturando excepciones

Capturando excepciones. Códigos de error predeterminados

¿Cuáles errores no se pueden atrapar?

El índice del blog Firebird21

El foro del blog Firebird21

 

¿Cuáles errores no se pueden atrapar?

3 comentarios

No todos los errores podemos atrapar con las excepciones. Eso sería lo ideal pero el estándar SQL supone que algunos errores serán evitados a nivel de lenguaje de programación, no a nivel de la Base de Datos.

Veamos un ejemplo:

Tenemos una tabla llamada NOMBRES, con esta estructura:

ERRORES1

Captura 1. Si haces clic en la imagen la verás más grande

y una tabla para registrar los ERRORES que tiene esta estructura:

ERRORES2

Captura 2. Si haces clic en la imagen la verás más grande

También un stored procedure para actualizar o insertar filas a la tabla NOMBRES, cuyo código fuente es el siguiente:

SET TERM ^ ;

CREATE PROCEDURE GRABAR_NOMBRES(
   IDENTI TYPE OF COLUMN NOMBRES.NOM_IDENTI,
   NOMBRE TYPE OF COLUMN NOMBRES.NOM_NOMBRE)
AS
BEGIN

   UPDATE OR INSERT INTO NOMBRES
                   (NOM_IDENTI, NOM_NOMBRE)
            VALUES (:Identi   , :Nombre ) ;

   WHEN ANY DO
      IN AUTONOMOUS TRANSACTION DO
         INSERT INTO ERRORES
                    (ERR_MODULO      , ERR_COMENT)
             VALUES ('GRABAR_NOMBRES', 'No se grabó el nombre');

END^

SET TERM ; ^

La idea sería, que si hay un error en el UPDATE OR INSERT se agregue una fila a la tabla ERRORES para tener registrado que ocurrió ese error. Sin embargo, si escribimos algo como:


EXECUTE PROCEDURE GRABAR_NOMBRES(0, 'JUAN567890123456789012345');

no se inserta una fila en la tabla ERRORES. Tampoco se insertará si en lugar de un stored procedure escribimos un trigger:

SET TERM ^ ;

CREATE TRIGGER NOMBRES_BIU FOR NOMBRES
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
AS
BEGIN

   IN AUTONOMOUS TRANSACTION DO
      INSERT INTO ERRORES
                 (ERR_MODULO   , ERR_COMENT)
          VALUES ('NOMBRES_BIU', 'Ocurrió un error');

END^

SET TERM ; ^

¿Por qué, qué pasó, por qué en ninguno de esos casos conseguimos que se inserte una fila en la tabla ERRORES?

La respuesta es que el Firebird (y todos los SGBD que cumplen con el estándar SQL) cuando encuentra que hay un error en los tipos de datos de un INSERT o de un UPDATE ya rechaza esa inserción o actualización, no continúa.

En nuestro ejemplo el dato que queríamos insertar en la columna NOM_NOMBRE tenía más de 20 caracteres y la columna NOM_NOMBRE está definida como VARCHAR(20). Lo mismo hubiera sucedido si por ejemplo en una columna numérica queremos guardar un dato alfabético: el Firebird lo rechaza sin contemplaciones.

Y eso implica que no podremos agregarle una fila a la tabla ERRORES que nos diga algo como: “la cantidad de caracteres que se quisieron grabar en la columna NOM_NOMBRE es mayor que 20”.

Por lo tanto, la única alternativa posible es que en nuestro programa, en nuestro lenguaje de programación, realicemos esa validación.

Conclusión:

Hay algunos errores que no se pueden atrapar con la construcción WHEN …. DO, y esos son los errores debidos a tipos de datos incorrectos. Por ejemplo, si en una columna definida como VARCHAR(20) queremos guardar más de 20 caracteres nuestro intento será rechazado pero no se ejecutarán las instrucciones del WHEN … DO que tengamos a continuación. Lo mismo ocurriría si en una columna de tipo numérico queremos insertar caracteres alfabéticos. O de tipo fecha. O de tipo hora, etc.

Artículo relacionado:

El índice del blog Firebird21

Capturando excepciones

6 comentarios

Las excepciones son una importante herramienta que el Firebird pone a nuestra disposición para que las utilicemos cuando ocurre un error dentro de un stored procedure o de un trigger.

¿Qué significa “capturar una excepción”?

Que podemos decidir nosotros lo que se hará cuando ocurre un error, en lugar de dejar que el Firebird haga sus acciones predeterminadas.

Por lo tanto:

    • Si no capturamos la excepción, el Firebird decide que acción tomar
    • Si capturamos la excepción, nosotros decidimos que acción tomar

¿Dónde se pueden capturar las excepciones?

En los stored procedures y en los triggers

¿Cómo se llama el bloque de comandos que nosotros escribimos para decidir qué acción tomar cuándo ocurre un error?

Manejador de la excepción

¿Por qué necesitamos escribir manejadores de excepción?

Porque así podemos responder al error y quizás corregirlo

¿Cómo se captura una excepción?

Usando la palabra clave WHEN y un bloque BEGIN … END

¿Cómo se le dice al Firebird cuáles errores se desea controlar?

Con una lista de esos errores, que puede ser:

  1. Código de error SQL de una excepción del sistema
  2. Nombre de una excepción del sistema
  3. Nombre de una excepción del usuario
  4. Escribiendo “ANY” que significa que todos los errores serán manejados

¿Cuáles son las acciones que toma el Firebird cuándo ocurre un error?

  • Busca un manejador para la excepción en el stored procedure o trigger actual
  • Deshace todas las acciones que había realizado hasta ese momento en el stored procedure o trigger actual
  • Regresa al nivel superior y busca un manejador de la excepción allí. Continúa subiendo niveles hasta que encuentra un manejador para la excepción. Si ningún manejador de excepción es encontrado entonces devuelve el control al programa llamador, informándole del error ocurrido
  • Si encontró un manejador para la excepción entonces ejecuta todas las instrucciones que hay en ese manejador de excepción y luego continúa con la línea siguiente a la que invocó al stored procedure o trigger que tenía el error. Si ese stored procedure o trigger estaba en el nivel más alto, entonces el control regresa a la aplicación

Si un error es capturado en un manejador de excepción, entonces no se informa de ese error a la aplicación.

¿Qué significa “relanzar la excepción”?

Que después de capturar la excepción y de manejar el error ocurrido podemos pedirle al Firebird que continúe con sus acciones por defecto, o sea las que hubiera hecho de no haber sido capturada la excepción.

¿Cómo se relanza una excepción?

Escribiendo la palabra:

EXCEPTION;

Sin nada más a su derecha que el punto y coma

¿Qué podemos hacer cuándo ocurre un error?

  1. Nada. En este caso el Firebird se encargará de su manejo
  2. Capturarlo. En este caso nosotros nos encargaremos de su manejo
  3. Capturarlo y relanzarlo. Que es una mezcla de los dos anteriores. Primero, nosotros nos encargamos de su manejo y luego le pedimos al Firebird que se encargue él

Ejemplo 1:

CREATE PROCEDURE CAPTURAR_EXCEPCION_1
AS
   DECLARE VARIABLE lnMiNumero INTEGER;
   DECLARE VARIABLE lnResultado INTEGER;
BEGIN

   lnMiNumero = 21;

   lnResultado = lnMiNumero / 0;

END

Como puedes ver, aquí hay una división por cero, la cual como seguramente sabes no está permitida en Matemática. Entonces, si ejecutamos ese stored procedure el Firebird nos mostrará el mensaje:

“Arithmetic exception, numeric overflow, or string truncation.

Integer divide by zero. The code attempted to divide an integer value by an integer divisor of zero.
At procedure ‘CAPTURAR_EXCEPCION_1’ line: 9, col: 4.

SQL Code: -802
IB Error Number: 335544321″

¿Qué podríamos escribir para capturar ese error?

Ejemplo 2:

CREATE PROCEDURE CAPTURAR_EXCEPCION_2
AS
   DECLARE VARIABLE lnMiNumero INTEGER;
   DECLARE VARIABLE lnResultado INTEGER;
BEGIN

   lnMiNumero = 21;

   lnResultado = lnMiNumero / 0;

   WHEN GDSCODE ARITH_EXCEPT DO BEGIN -- Ocurrió una excepción aritmética

   END

END

Aquí le estamos diciendo al Firebird que si ocurre un error aritmético, cualquiera sea ese error, que lo ignore. Como dentro del manejador de la excepción (el bloque WHEN … END es el manejador de la excepción) no hemos escrito algún comando, entonces nada se hará, el efecto será ignorarlo al error ocurrido. Por supuesto que podríamos escribir algo, como vemos a continuación:

Ejemplo 3:

CREATE PROCEDURE CAPTURAR_EXCEPCION_3
AS
   DECLARE VARIABLE lnMiNumero INTEGER;
   DECLARE VARIABLE lnResultado INTEGER;
BEGIN

   lnMiNumero = 21;

   lnResultado = lnMiNumero / 0;

   WHEN GDSCODE ARITH_EXCEPT DO BEGIN -- Ocurrió una excepción aritmética
      lnResultado = -999;
   END

END

Ahora le estamos diciendo que si ocurre un error de división por cero le asigne a la variable lnResultado el valor -999

Ejemplo 4:

CREATE PROCEDURE CAPTURAR_EXCEPCION_4
AS
   DECLARE VARIABLE lnMiNumero INTEGER;
   DECLARE VARIABLE lnResultado INTEGER;
BEGIN

   lnMiNumero = 21;

   lnResultado = lnMiNumero / 0;

   WHEN GDSCODE ARITH_EXCEPT DO BEGIN -- Ocurrió una excepción aritmética
      lnResultado = -999;
      EXCEPTION;
   END

END

Aquí estamos haciendo dos cosas cuando ocurre una división por cero. Primero, le asignamos a la variable lnResultado el valor -999 y segundo, le pedimos al Firebird que haga lo que normalmente hace cuando encuentra una división por cero.

Conclusión:

Si capturamos los errores entonces tenemos muchísimo más control sobre lo que ocurre dentro de los stored procedures y de los triggers. Hemos visto varias formas de manejar las excepciones (o sea, los errores) y en siguientes artículos veremos aún más.

Artículos relacionados:

Entendiendo las excepciones

El índice del blog Firebird21

El foro del blog Firebird21