Está en: » Artículos »

Triggers (disparadores) en mySQL

Triggers (disparadores) en mySQL

Hoy en día, prácticamente todos los motores de bases de datos populares y en mantenimiento incorporan una gran utilidad llamada triggers. Esta utilidad viene a ser como una acción a realizar automáticamente cuando hagamos una manual.

Básicamente un trigger realiza una acción cuando hacemos un insert, update o delete sobre una tabla determinada (a la que le hemos asignado dicho trigger).

Partamos de un ejemplo y lo explico:

DELIMITER |
 
CREATE TRIGGER mailtransport_insert AFTER INSERT ON mail_domain
	FOR EACH ROW BEGIN
		INSERT INTO mail_transport
			(sys_userid, sys_groupid, sys_perm_user, sys_perm_group, server_id, domain_id, domain,
			transport, sort_order, active)
		VALUES
			(1, 1, 'ruid', 'ruid', 100, NEW.domain_id, NEW.domain, concat('smtp:mail.', NEW.domain), 1, 'y');
	END
|
 
DELIMITER ;
  • Empezamos definiendo que el delimitador de fin de línea sql es ‘|’.
  • Creamos un trigger en la tabla mailtransport_insert de forma que se ‘lance’ posteriormente a la inserción de algún dato en dicha tabla.
  • Indicamos que para cada inserción que se realice (FOR EACH ROW BEGIN), debe realizar lo que haya en el interior del bucle (hasta END).

Como veis, es realmente sencillo e intuitivo. Si os fijáis en los valores a insertar dentro del bucle, veréis que hay unos campos con ‘NEW.’ delante. Esto quiere decir que debe recoger el nuevo valor para ese campo (osease, el que hemos insertado).

Para terminar, deberemos volver a definir el delimitador de fin de sentencia sql a ‘;’ (que suele ser el valor por defecto). Si no hubiésemos cambiado el delimitador al principio, justo al llegar a la línea de VALUES antes de END se hubiese ejecutado la consulta, y al estar sin terminar, nos hubiese retornado un estrepitoso error de sentencia mysql.

Como indiqué al principio, este “lanzador” podemos utilizarlo también para actualizaciones (UPDATE) y eliminaciones (DELETE). Os dejo un ejemplo de cada:

DELIMITER |
 
CREATE TRIGGER mailtransport_update AFTER UPDATE ON mail_domain
	FOR EACH ROW BEGIN
		UPDATE mail_transport
		SET
			domain = NEW.domain,
			transport = concat('smtp:mail.', NEW.domain)
		WHERE
			domain_id = NEW.domain_id;
	END
|
 
DELIMITER ;
DELIMITER |
 
CREATE TRIGGER mailtransport_delete BEFORE DELETE ON mail_domain
	FOR EACH ROW BEGIN
		DELETE FROM mail_transport WHERE domain = OLD.domain;
	END
|
 
DELIMITER ;

Para aquellos a los que le pique la curiosidad de el porqué de esos nombres de tablas, diré en mi defensa que es para que cada vez que se de de alta un nuevo correo electrónico (en el panel de control de ispconfig versión 3), lo añada en la tabla de transportes con un id de servidor 100.

Básicamente es porque me vi en la necesidad de poner un servidor de reserva de correo, de forma que si el principal caía, el servidor de reserva recogía el correo y lo almacenaba hasta que el principal estuviese de nuevo “online”.

Así pues, gracias a openVPN y a la replicación de tablas de mysql, en cuestión de un par de horas tenía el sistema funcionando correctamente, sin tener que hacer ningún cambio en el servidor principal, y teniendo el servidor de reserva “auto actualizado” fácilmente sin tener que hacer cambios en el código de dicho panel.

Comentarios

  1. Mario dice:

    Hola Miguel, este artículo me ayudó bastante al principio.Pero ahora me surge una duda.
    Estoy intentando hacer un trigger INSERT UPDATE con una subquery.

    DELIMITER |

    CREATE TRIGGER [nombre_trigger] AFTER INSERT ON tabla_1
    FOR EACH ROW BEGIN
    INSERT INTO tabla_2
    (id_A)
    SELECT id_A FROM tabla_3 WHERE id_B=NEW.id_B
    END
    |

    DELIMITER ;

    Si ejecuto la sentencia insert con el select anidado fuera del trigger, funciona. Si lo hago dentro del trigger, no funciona, me marca un error de sintaxis. Lo que quiero saber es si los triggers soportan subqueries.
    Sería una gran ayuda si me respondes, gracias.

    • En principio sólo veo un fallo (falta un ; ) y una recomendación.
      Te pongo lo que yo pondría (ojo, que no lo he probado):

      DELIMITER |

      CREATE TRIGGER [nombre_trigger] AFTER INSERT ON tabla_1
      FOR EACH ROW BEGIN
      INSERT INTO tabla_2
      (id_A)
      SELECT tabla_3.id_A FROM tabla_3 WHERE tabla_3.id_B=NEW.id_B;
      END
      |

      DELIMITER ;

      Prueba estos retoques y nos comentas…

    • Mario dice:

      Sí me faltaba el “;”. Funciona especificando la tabla o así como lo puse yo. De todas formas gracias. Es que me estoy volviendo loco y cuando parece que ya uno no sabe dónde está el error, resulta que es una estupidez.
      Te pido disculpas por la molestia que te tomaste en contestarme. Hace poco empecé con mysql y estoy construyendo una base de datos para mi negocio. Por eso a veces no sé si es un error de distracción o si estoy ignorando algo de la sintaxis.
      Muchas gracias.

    • No hace falta disculparse. Estas son las cosas que me hacen sentir que es de utilidad este blog.

      El poner la tabla es simplemente para una mejor legibilidad del código en un futuro. Aparte, algunas veces se ahorran algunos quebraderos de cabeza.

      Un saludo

  2. Rox_Eli dice:

    hola amigo me puedes ayudar con este ejemplo de disparador soy novata en esto… me aparece un error #1064 lo que quiero es almacenar en el campo dias_restantes de la tabla_socio la diferencia entre la fecha actual y la fecha de llegada (fecha_llegada) q esta alojada en la tabla_reservacion

    CREATE TRIGGER `tiempo_trigger` AFTER INSERT ON `tabla_reservacion` FOR EACH ROW UPDATE
    UPDATE tabla_socio SET dias_restantes = DATEDIFF(CURDATE(),tabla_reservacion.fecha_llegada)
    WHERE tabla_socio INNER JOIN tabla_reservacion ON (tabla_socio.id_socio = tabla_reservacion .id_socio )

Deje su comentario

Previsualización de comentario
  1. Anónimo dice:





Pings para esta entrada