¡Hola! Me parece que andas usando un bloqueador de anuncios =( ¿Nos desbloqueas? ¿Por qué?
F13

Funcion 13

Auto-actualización de TIMESTAMP en MySQL con triggers

Hace tiempo terminé en mi oficina una nueva herramienta que servía para gestionar muchas cosas. A la hora de impartir la formación sobre lo desarrollado, surgió un pequeño miedo entre los asistentes.

¿Cuando tocamos algo se ve quién ha sido? ¡Qué yo no quiero problemas! Un usuario preocupado

Es un miedo razonable. Cuando tocas algo que puede escogonciar el servicio para varios clientes, hay que saber quién ha metido la pata. Para echarlo, por supuesto explicarle bien  el funcionamiento de la herramienta y el procedimiento a seguir.

El caso es que aquella no me la traía preparada, error de novato. Así que me tocó hacer algunos ajustes aquí, un poco de cinta americana por allá para que, al cambiar algo en Base de Datos, se quedara grabado cuándo se tocó eso y quién había sido.

Desde entonces, el sistema mejoró y ya viene por defecto en cualquier aplicación que tengo a bien el desarrollar. Veamos cómo se hace.

Preparando la tabla

Lo primero de todo, la tabla debe tener 3 campos (o 2 como mínimo), los pillaréis en seguida:

  • fecha_creacion de tipo TIMESTAMP
  • fecha_modificacion de tipo TIMESTAMP
  • modificado_por del tipo INT para apuntar a la tabla de usuarios. (Este es opcional).

Los campos de fecha deben permitir valores del tipo NULL y, por defecto, no les pondremos que pongan nada. Este es el código de ejemplo para una tabla de usuarios:

CREATE TABLE IF NOT EXISTS `usuarios`  
  ( 
     `id`                 INT(11) NOT NULL auto_increment, 
     `nombre`             VARCHAR(50) NOT NULL, 
     `apellidos`          VARCHAR(100) NOT NULL, 
     `correo`             VARCHAR(100) NOT NULL, 
     `movil`              VARCHAR(20) NOT NULL, 
     `fecha_creacion`     TIMESTAMP NULL DEFAULT NULL, 
     `fecha_modificacion` TIMESTAMP NULL DEFAULT NULL, 
     `modificado_por`     INT(11) NOT NULL, 
     PRIMARY KEY (`id`) 
  )
engine=innodb  
DEFAULT charset=utf8  
auto_increment=1;  

Creando los TRIGGERS

Siguiendo con mi historia, lo primero que intenté fue el añadir algo así en la creación de la tabla:

fecha_creacion TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, fecha_modificacion TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP

Pero, vi mi gozo en un pozo ya que aparece el siguiente error, usando MySQL 5.5:

1293 – Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Lo que viene a decir es que solo puede haber una columna del tipo TIMESTAMP que se auto-rellene con CURRENT_TIMESTAMP. Vaya gracia…

Así que me acordé de los triggers. Para los que no sepáis lo que son, los triggers son disparadores asociados a una tabla, que se activan cuando ocurre un evento en particular. Estos solo nos permiten ejecutar código SQL y se activan con las cláusulas INSERT, DELETE o UPDATE y podemos ejecutarlo antes (BEFORE) o después (AFTER) de que se ejecute la sentencia.

¡Vaya! ¡Esto nos viene de perlas! Veamos cómo crear el primero. En la creación del elemento:

CREATE TRIGGER insercion_nuevo_usuario BEFORE INSERT  
ON usuarios  
FOR EACH row  
  SET new.fecha_creacion = ifnull(new.fecha_creacion, now()), 
  new.fecha_modificacion = ifnull(new.fecha_modificacion, now()); 

Como dijo Jack, vayamos por partes. Estamos creando un trigger que se llama insercion_nuevo_usuario. Este nombre es arbitrario y podemos ponerle lo que nos apetezca, pero es mejor ser descriptivos. Éste se ejecutará antes de que sea insertado un registro en la tabla usuarios.

Para cada fila establecemos lo siguiente:

  • Que la nueva fecha de creación (NEW), en caso de que sea NULL, será igual a NOW(). Es decir, que si no indicamos lo contrario, al crear un registro el campo fecha_creacion llevará la marca de tiempo que nos devuelva en ese momento NOW().
  • En mi caso, quiero que la fecha de modificación sea la misma que la de creación, pero se puede eliminar la línea directamente en caso de que no queramos poner nada o forzarla a algún valor, sea el que sea.

La palabra clave NEW, nos permite acceder al valor que va a ser insertado y modificarlo a placer. En el caso de las actualizaciones con UPDATE, podemos acceder a OLD también, para acceder al valor que en Base de Datos, antes de ser modificado.

Finalmente, veamos el segundo trigger, que como verás es un hermano pequeño del anterior.

CREATE TRIGGER actualizar_usuario BEFORE UPDATE  
ON usuarios  
FOR EACH row  
  SET new.fecha_modificacion = ifnull(new.fecha_modificacion, now()); 

¡Ale! Pues esto es todo en este pequeño, aunque espero que útil, artículo. Recuerda que siempre es buena idea guardar un registro de todas estas cosas, aunque no lo muestres en ningún sitio, siempre te guardas un as en la manga para poder tirar del hilo si surgen problemas.

Si el artículo te pareció interesante, útil o incluso equivocado, por favor considera el dejar un comentario. ¡Lo apreciaré mucho!

Programador Front-end en First + Third y Potato. Trabajando con JavaScript y HTML5 desde el corazón de Sevilla.

Comentarios ¡Únete a la conversación!