Este artículo está basado en el FAQ1 escrito por Karol Bieniaszewski:

http://itstop.pl/en-en/Porady/Firebird/FAQ1/TABLE-MODIFICATION-TIMESTAMP

A veces puede ser útil para nosotros conocer cual fue la última vez que se realizó un INSERT, UPDATE, o DELETE a una tabla. Tenemos 3 formas de obtener esa información:

  1. Guardar el TIMESTAMP en cada fila de la tabla
  2. Crear otra tabla y guardar en ella el Nombre de la Tabla y el TIMESTAMP
  3. Usar generadores

Opción 1. Guardar el TIMESTAMP en cada fila de la tabla

La opción 1. es muy fácil de implementar (simplemente le agregamos una columna TIMESTAMP a la tabla) y también puede ser muy conveniente porque sabremos no solamente cuando se actualizó por última vez la tabla sino también cada fila de ella. Lo único que necesitamos hacer es un SELECT y en la cláusula ORDER BY elegimos la columna donde guardamos el TIMESTAMP y la ordenamos descendentemente con DESC. Por ejemplo:

SELECT
   PRD_TIMEST
FROM
   PRODUCTOS
ORDER BY
   PRD_TIMEST DESC
ROWS
   1

En este caso, a la columna donde guardamos el TIMESTAMP la hemos llamado PRD_TIMEST. El gran problema con esta solución es cuando las filas de nuestra tabla se cuentan por muchos millones. Si no tenemos un índice DESC sobre la columna PRD_TIMEST entonces obtener el resultado puede demorar una eternidad. Y si tenemos un índice DESC sobre esa columna entonces cada INSERT o UPDATE modificará el contenido de esa columna y también al índice asociado, lo cual causará una demora. En otras palabras, si no tenemos un índice DESC el SELECT será muy lento, y si tenemos un índice DESC entonces el INSERT y el UPDATE serán lentos. No es una buena situación cuando la tabla tiene millones de filas.

Opción 2. Crear otra tabla y guardar en ella el Nombre de la Tabla y el TIMESTAMP

Supongamos que creamos una tabla llamada TIEMPOS con las columnas TIE_NOMBRE y TIE_TIMEST, para guardar en esas columnas el nombre de la tabla y el TIMESTAMP de su última modificación.

Aquí podemos tener problemas de lentitud si se hacen muchos INSERT y UPDATE simultáneamente a la tabla original (a la tabla PRODUCTOS, en nuestro ejemplo). ¿Por qué? porque nuestra tabla TIEMPOS tendrá problemas de concurrencia cuando dos o más transacciones quieran actualizar la misma fila. Supongamos que 20 usuarios están actualizando a la tabla PRODUCTOS, entonces cada uno de esos 20 usuarios quiere guardar el TIMESTAMP de su modificación a la tabla PRODUCTOS en una fila de la tabla TIEMPOS. Como la fila es una sola (porque el TIMESTAMP de la tabla PRODUCTOS se guarda en una sola fila de la tabla TIEMPOS) entonces 1 solo usuario podrá guardar el TIMESTAMP, los restantes 19 tendrán que esperar. Luego, el usuario 2 modifica el TIMESTAMP y los restantes 18 tienen que seguir esperando. Y así sucesivamente. No es una buena situación tampoco.

Opción 3. Usando generadores

Esta solución es más complicada de implementar que las dos anteriores pero tiene la gran ventaja de que es rapidísima en todas las situaciones.

Paso 1. Crear un generador para la tabla original

CREATE GENERATOR ULTIMA_MODIFICACION_PRODUCTOS

En este generador se guardará el TIMESTAMP que nos interesa

Paso 2. Crear un trigger

CREATE TRIGGER AIU_PRODUCTOS FOR PRODUCTOS
   ACTIVE AFTER INSERT OR UPDATE
   POSITION 1
AS
   DECLARE VARIABLE ltTimeStamp TIMESTAMP;
   DECLARE VARIABLE lnNumero    BIGINT;
BEGIN

   ltTimeStamp = (SELECT CURRENT_TIMESTAMP FROM RDB$DATABASE);

   lnNumero = CAST(EXTRACT(YEAR        FROM ltTimeStamp) ||
                   LPAD(EXTRACT(MONTH  FROM ltTimeStamp), 2, '0') ||
                   LPAD(EXTRACT(DAY    FROM ltTimeStamp), 2, '0') ||
                   LPAD(EXTRACT(HOUR   FROM ltTimeStamp), 2, '0') ||
                   LPAD(EXTRACT(MINUTE FROM ltTimeStamp), 2, '0') ||
                   LPAD(CAST(EXTRACT(SECOND FROM ltTimeStamp) * 10000 AS INTEGER), 6, '0')
              AS BIGINT);

   lnNumero = GEN_ID(ULTIMA_MODIFICACION_PRODUCTOS, -GEN_ID(ULTIMA_MODIFICACION_PRODUCTOS, 0) + lnNumero);

END;

Lo que hace este trigger es:

  1. Obtener la fecha y hora actuales
  2. Convertir la fecha y hora en un número BIGINT
  3. Actualizar el generador para guardar en él el número BIGINT obtenido en el punto 2.

Cuando finaliza el trigger tendremos en el generador ULTIMA_MODIFICACION_PRODUCTOS la fecha y la hora en que se realizó el último INSERT o el último UPDATE a la tabla PRODUCTOS.

Por supuesto que si lo deseamos podemos tener tres generadores: uno para guardar los INSERT, otro para guardar los UPDATE y otro para guardar los DELETE, eso ya dependerá de nosotros.

Paso 3. Convertir el valor BIGINT del generador en un TIMESTAMP

El generador cuyo valor establecimos con el trigger será algo así: 201403112149285420

O sea, año 2014, mes 03, día 11, hora 21, minutos 49, segundos 28, milisegundos 5420

Pero en general no nos interesará ver un número entero sino un TIMESTAMP y para ello necesitamos escribir un stored procedure seleccionable que se encargue de convertir al número BIGINT en un TIMESTAMP.

CREATE PROCEDURE OBTENER_TIMESTAMP(
   tnGenerador BIGINT)
RETURNS(
   fttTimeStamp TIMESTAMP)
AS
BEGIN

   IF (tnGenerador = 0) THEN
      fttTimeStamp = NULL;
   ELSE
      fttTimeStamp = CAST(SUBSTRING(tnGenerador FROM  1 FOR 4) || '-' ||
                          SUBSTRING(tnGenerador FROM  5 FOR 2) || '-' ||
                          SUBSTRING(tnGenerador FROM  7 FOR 2) || '-' ||
                          SUBSTRING(tnGenerador FROM  9 FOR 2) || '-' ||
                          SUBSTRING(tnGenerador FROM 11 FOR 2) || '-' ||
                          SUBSTRING(tnGenerador FROM 13 FOR 2) || '-' ||
                          SUBSTRING(tnGenerador FROM 15 FOR 4)
                     AS TIMESTAMP);

   SUSPEND;

END;

Paso 4. Obtener el valor del generador

Ahora, queremos conocer el TIMESTAMP de la última modificación a la tabla PRODUCTOS, lo conseguimos con:

SELECT
   fttTimeStamp
FROM
   OBTENER_TIMESTAMP(GEN_ID(ULTIMA_MODIFICACION_PRODUCTOS, 0))

Y obtendremos algo como:

TIMESTAMP1

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

que es justamente lo que estábamos necesitando.

Conclusión:

Usar el método de los generadores para obtener el TIMESTAMP de la última modificación a una tabla es lo más rápido que podemos tener. Si la tabla no tiene muchas filas o si se la actualiza raramente entonces se podría emplear cualquiera de los otros dos métodos también, pero en tablas grandes y que son actualizadas muy frecuentemente el mejor método es el de los generadores.

Artículo relacionado:

El índice del blog Firebird21