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.

Anuncios