Otro ejemplo de trigger de actualización

5 comentarios

Como ya hemos visto en este artículo:

https://firebird21.wordpress.com/2013/05/24/registrando-los-cambios-realizados-a-los-datos-de-una-tabla/

a veces es muy conveniente registrar todas las actualizaciones realizadas a una tabla. Eso nos permitirá más adelante responder muchas preguntas relacionadas con: ¿quién lo hizo? ¿cuándo lo hizo? ¿qué hizo?

ACTUALIZACIONES3

(haciendo clic en la imagen la verás más grande)

Aquí, tenemos una tabla llamada COTIZACIONES que se utiliza para guardar las cotizaciones del dolar, tipo comprador y tipo vendedor. En el ejemplo del artículo de arriba la columna que se actualizaba era de tipo carácter, por lo tanto no había complicaciones, sin embargo ahora queremos actualizar una columna de tipo fecha y dos columnas de tipo numérico, entonces ¿cómo se grabaría en este caso?

SET TERM ^ ;

CREATE TRIGGER BIUD_COTIZACIONES_COT_IDENTI FOR COTIZACIONES
   ACTIVE BEFORE INSERT OR UPDATE OR DELETE
   POSITION 0
AS
   DECLARE VARIABLE lcUsuari TYPE OF D_NOMBRE30 COLLATE ISO8859_1;
   DECLARE VARIABLE ldTiempo TYPE OF D_FECHAHORA;
   DECLARE VARIABLE lcTablax TYPE OF D_NOMBRE30 COLLATE ISO8859_1;
   DECLARE VARIABLE lcOperac TYPE OF D_CHAR1 COLLATE ISO8859_1;
   DECLARE VARIABLE lnIdeTab TYPE OF D_IDENTIFICADOR;
   DECLARE VARIABLE lcColumn TYPE OF D_NOMBRE10 COLLATE ISO8859_1;
   DECLARE VARIABLE lcValorx TYPE OF D_NOMBRE254 COLLATE ISO8859_1;
BEGIN

   lcUsuari = CURRENT_USER;
   ldTiempo = CURRENT_TIMESTAMP;
   lcTablax = 'COTIZACIONES';

   IF (NEW.COT_IDENTI IS NULL OR NEW.COT_IDENTI = 0) THEN
      NEW.COT_IDENTI = GEN_ID(COTIZACIONES_COT_IDENTI_GEN, 1);

   IF (INSERTING) THEN BEGIN
      lcOperac = 'I';
      lnIdeTab = NEW.COT_IDENTI;
      lcColumn = 'COT_FECHAX';
      lcValorx = CAST(NEW.COT_FECHAX AS VARCHAR(10));
      INSERT INTO ACTUALIZACIONES
           VALUES(0, :lcUsuari, :ldTiempo, :lcTablax, :lcOperac, :lnIdeTab, :lcColumn, :lcValorx);
      lcColumn = 'COT_DOLCOM';
      lcValorx = CAST(NEW.COT_DOLCOM AS VARCHAR(12));
      INSERT INTO ACTUALIZACIONES
           VALUES(0, :lcUsuari, :ldTiempo, :lcTablax, :lcOperac, :lnIdeTab, :lcColumn, :lcValorx);
      lcColumn = 'COT_DOLVEN';
      lcValorx = CAST(NEW.COT_DOLVEN AS VARCHAR(12));
      INSERT INTO ACTUALIZACIONES
           VALUES(0, :lcUsuari, :ldTiempo, :lcTablax, :lcOperac, :lnIdeTab, :lcColumn, :lcValorx);
   END

END^

Para no hacerlo muy largo, solamente se muestra el código que corresponde a una inserción. O sea, cuando el usuario hace un INSERT. Podemos observar dos cosas:

  1. Se utilizó el mismo trigger que agrega un nuevo identificador a la tabla. Esto para no estar escribiendo demasiados triggers, pero ya es cuestión de gustos de cada uno usar un solo trigger o más de un trigger.
  2. Como las columnas que se guardan en la tabla ACTUALIZACIONES son de tipo fecha y de tipo numérico entonces se ejecutó la función CAST() para convertir los valores de esas columnas a tipo carácter.

En la imagen de abajo vemos el contenido de la tabla ACTUALIZACIONES luego de insertarle una nueva fila a la tabla COTIZACIONES, con FechaCotización = 24/05/2013, Dolar Compra = 4.050,50 y Dolar Venta = 4.120,30

ACTUALIZACIONES4

(haciendo clic en la imagen la verás más grande)

Como puedes ver, se puede saber muy bien quien lo hizo, cuando lo hizo y que hizo.

Anuncios

Registrando los cambios realizados a los datos de una tabla

7 comentarios

A veces, puede ser muy útil saber cuando se insertó, modificó o borró el contenido de una tabla. De esta manera podríamos responder preguntas como:

  • ¿Cuáles son los productos nuevos que tenemos desde el mes pasado?
  • ¿Cuál fue la última vez que se cambió el precio de venta de este producto?
  • ¿Cuántos clientes nuevos tenemos este año?
  • ¿Cuál era el precio de costo de este producto el 1 de enero de este año?
  • ¿Cuáles son los alumnos que se inscribieron este año y que el año pasado no eran alumnos de la Institución?
  • ¿Quién fue el idiota que le puso a este producto un precio de venta tan bajo?
  • ¿Quién fue el descerebrado que borró esa cobranza que hicimos ayer?

En todos estos casos y muchos más similares necesitamos guardar la fecha en la cual se insertó, se modificó, o se borró una fila. Una forma de solucionar el problema es la siguiente:

  1. Creamos una tabla, llamada ACTUALIZACIONES o algo así, que tenga las siguientes columnas:
    1. Identificador de la fila
    2. Nombre del usuario
    3. Fecha y hora de la operación
    4. Nombre de la tabla
    5. Operación realizada (insert/delete/update)
    6. Identificador de la fila en la fila actualizada
    7. Nombre de la columna
    8. Valor de la columna
  2. Creamos un trigger en cada una de las tablas cuyas actualizaciones nos interesa conocer para que registre en la tabla ACTUALIZACIONES la operación ocurrida

Estructura de la tabla ACTUALIZACIONES

ACTUALIZACIONES1

(haciendo clic en la imagen la verás más grande)

Trigger en la tabla BANCOS para registrar las actualizaciones

SET TERM ^ ;

CREATE TRIGGER BANCOS_ACTUALIZAR_BIUD FOR BANCOS
   ACTIVE BEFORE INSERT OR UPDATE OR DELETE
   POSITION 1
AS
   DECLARE VARIABLE lcUsuari TYPE OF D_NOMBRE30 COLLATE ISO8859_1;
   DECLARE VARIABLE ldTiempo TYPE OF D_FECHAHORA;
   DECLARE VARIABLE lcTablax TYPE OF D_NOMBRE30 COLLATE ISO8859_1;
   DECLARE VARIABLE lcOperac TYPE OF D_CHAR1 COLLATE ISO8859_1;
   DECLARE VARIABLE lnIdeTab TYPE OF D_IDENTIFICADOR;
   DECLARE VARIABLE lcColumn TYPE OF D_NOMBRE10 COLLATE ISO8859_1;
   DECLARE VARIABLE lcValorx TYPE OF D_NOMBRE254 COLLATE ISO8859_1;
BEGIN

   lcUsuari = CURRENT_USER;
   ldTiempo = CURRENT_TIMESTAMP;
   lcTablax = 'BANCOS';

   IF (INSERTING) THEN BEGIN
      lcOperac = 'I';
      lnIdeTab = NEW.BAN_IDENTI;
      lcColumn = 'BAN_NOMBRE';
      lcValorx = NEW.BAN_NOMBRE;
   END

   IF (DELETING) THEN BEGIN
      lcOperac = 'D';
      lnIdeTab = OLD.BAN_IDENTI;
      lcColumn = 'BAN_NOMBRE';
      lcValorx = OLD.BAN_NOMBRE;
   END

   IF (UPDATING) THEN BEGIN
      lcOperac = 'U';
      lnIdeTab = OLD.BAN_IDENTI;
      lcColumn = 'BAN_NOMBRE';
      lcValorx = NEW.BAN_NOMBRE;
   END

   INSERT INTO ACTUALIZACIONES
        VALUES(0, :lcUsuari, :ldTiempo, :lcTablax, :lcOperac, :lnIdeTab, :lcColumn, :lcValorx);

END^

SET TERM ; ^

Cada vez que se inserte, borre, o modifique una fila de la tabla BANCOS se insertará una fila en la tabla ACTUALIZACIONES. Luego, ya será muy fácil consultar esta tabla y responder preguntas similares a las planteadas al principio de este artículo.

Ejemplo. Agregando, modificando, y borrando una fila de la tabla BANCOS

Para este ejemplo se agregó una fila a la tabla BANCOS. Como ya existían 16 bancos el identificador de la nueva fila es 17. El nombre del Banco que se agregó es “PRUEBA PARA EL BLOG”. Luego se modificó ese nombre para que sea: “PRUEBA PARA EL BLOG MODIFICADO”. Luego se borró esa fila. Después de realizar esas tres operaciones el contenido de la tabla ACTUALIZACIONES es el siguiente:

ACTUALIZACIONES2

(haciendo clic en la imagen la verás más grande)

 Como puedes ver, las tres operaciones realizadas fueron registradas, junto con el nombre del usuario, la fecha de la operación, el nombre de la tabla de origen, la operación que se realizó en dicha tabla, el identificador de la tabla de origen, el nombre de la columna que nos interesa, y el valor  que tenía esa columna.

Observaciones:

El autor de este blog tiene por costumbre que los nombres de las columnas de todas sus tablas tengan exactamente 10 caracteres, ni más ni menos, por ese motivo la columna ACT_COLUMN está declarada como VARCHAR(10), si los nombres de tus columnas pueden tener una longitud mayor que 10 entonces deberías modificar la declaración de la columna ACT_COLUMN acordemente.

Un detalle a tener en cuenta es que la columna ACT_VALORX es de tipo caracter, eso implica que si la columna cuyo valor deseas guardar es de tipo numérico o de tipo fecha debes previamente convertirla a caracter antes de grabarla.

Conclusión:

Registrar las operaciones que se realizaron en cada tabla puede ser muy importante en muchos casos ya que nos permite conocer el estado de la Base de Datos en cualquier momento que nos interese. Podríamos así reconstruirla a cualquier instante, por ejemplo para tenerla como era exactamente el día 22 de abril de 2013 a las 10:23:00

También nos permite responder a preguntas similares a las planteadas al principio de este artículo.

Actualizando el valor de todos los generadores (secuencias)

3 comentarios

Como seguramente sabes, los generadores (también llamados secuencias) están afuera de las transacciones, eso significa que una vez que obtienen su valor éste permanece, sin importar si después la transacción es grabada o es desechada.

Eso está muy bien, así tiene que ser, sin embargo a veces más bien por una cuestión de estética queremos cambiarlos manualmente.

Ejemplo 1. Se agregaron y se borraron filas a una tabla

GENERADOR1

(haciendo click en la imagen la verás más grande)

Aquí hemos realizado una consulta a la tabla de BANCOS y esto fue lo que obtuvimos. Ahora, queremos agregar una nueva fila. Supuestamente su identificador debería ser 17 pero como a esta tabla se le estuvieron agregando y borrando varias filas durante estos días cuando insertamos una nueva fila esto es lo que obtenemos:

GENERADOR2

(haciendo click en la imagen la verás más grande)

El identificador del Banco no es 17 sino 21, ¿por qué? porque cada vez que se insertó una nueva fila el generador fue aumentando su valor y lo mantuvo aunque luego se haya hecho el ROLLBACK o el DELETE de esa fila.

Ejemplo 2. Se borraron todas las filas de una tabla

DELETE FROM CONCEPTOS

Después de escribir este comando y su correspondiente COMMIT todas las filas de la tabla de CONCEPTOS fueron eliminadas. Después, al insertar un nuevo concepto esto es lo que obtenemos:

GENERADOR3

(haciendo click en la imagen la verás más grande)

 ¿Por qué el identificador del concepto es 1.792 siendo que la tabla de CONCEPTOS tiene una sola fila? Nuevamente la respuesta es la misma: el generador o secuencia una vez que obtiene su valor lo retiene. Como anteriormente se habían insertado 1.791 filas, al insertar una nueva fila su identificador (el cual es siempre igual al que tiene el generador en el momento de la inserción) se incrementó en 1.

¿Es posible cambiar manualmente el valor de un generador o secuencia?

Sí, es posible. Para ello se utiliza el comando SET GENERATOR (se puede usar también ALTER SEQUENCE NombreSecuencia RESTART WITH ValorNumérico). Por ejemplo:

SET GENERATOR
   CONCEPTOS_CON_IDENTI_GEN
TO
   0

Aquí, al generador o secuencia (son sinónimos) llamado CONCEPTOS_CON_IDENTI_GEN se le cambió el valor, su nuevo valor es 0 (cero). Desde luego que puedes escribir cualquier valor que quieras: 1, 2, 10, 2540, 3871, etc.

¿Se puede actualizar el valor de todos los generadores de una sola vez?

Sí, con el siguiente stored procedure basado en el que escribió Daniel P. Guimaraes de Tecnobyte Informática, lo conseguirás. La condición para que funcione es que los nombres de los generadores sean:

NombreTabla_NombreColumna_Gen

si usas el programa EMS SQL Manager y creas los generadores automáticamente porque tus identificadores son autoincrementales entonces ya los nombres de los generadores serán los correctos. Si no usas ese programa o si nombras de forma diferente a tus generadores entonces tendrás que hacerles algunos pequeños cambios a esta rutina.

¿Qué hace este stored procedure?

Actualiza los valores de todos los generadores (secuencias) basándose en el mayor valor guardado actualmente en la columna asociada al generador. La versión del Firebird debe ser 2.0 ó posterior.

SET TERM ^ ;

CREATE PROCEDURE ACTUALIZAR_GENERADORES
AS
   DECLARE VARIABLE lcNombreTabla     VARCHAR(32);
   DECLARE VARIABLE lcNombreColumna   VARCHAR(32);
   DECLARE VARIABLE lcNombreGenerador VARCHAR(32);
   DECLARE VARIABLE lnValorMaximo     BIGINT;
   DECLARE VARIABLE lcComando         VARCHAR(200);
BEGIN

   FOR SELECT
      TRIM(R.RDB$RELATION_NAME),
      TRIM(F.RDB$FIELD_NAME),
      TRIM(G.RDB$GENERATOR_NAME)
   FROM
      RDB$RELATIONS R
   JOIN
      RDB$RELATION_FIELDS F
      ON R.RDB$RELATION_NAME = F.RDB$RELATION_NAME
   JOIN
      RDB$GENERATORS G
         ON G.RDB$GENERATOR_NAME = TRIM(R.RDB$RELATION_NAME) || '_' ||
                                   TRIM(F.RDB$FIELD_NAME) || '_' ||
                                   'GEN'
   INTO
      :lcNombreTabla,
      :lcNombreColumna,
      :lcNombreGenerador
   DO BEGIN
      -- Se halla el valor máximo de la columna asociada al generador
      lcComando = 'SELECT MAX(' || lcNombreColumna || ') FROM ' || lcNombreTabla;
      EXECUTE STATEMENT lcComando INTO lnValorMaximo;
      -- Se actualiza el generador con el valor máximo hallado
      lcComando = 'SET GENERATOR ' || lcNombreGenerador || ' TO ' || COALESCE(lnValorMaximo, 0);
      EXECUTE STATEMENT lcComando;
   END

END^

Después de ejecutar este stored procedure todos los generadores tendrán el valor máximo que tiene la columna asociada a ellos. Este stored procedure es muy útil para no dejar huecos en la numeración de los identificadores. Realmente eso es más bien una cuestión de estética porque al Firebird no le importa si entre un identificador y el siguiente la diferencia es de 1 ó mayor que 1, pero si usamos al identificador como Primary Key lo único que le importa es que no se repitan los números.