miércoles, 29 de octubre de 2014

Ejemplos de Triggers en MySQL

En esta entrada voy a poner ejemplos prácticos de casos que podemos resolver con triggers en MySQL. Los triggers son pequeños programas que ejecuta la base de datos respondiendo a acciones sobre la misma.  En este caso no me voy a enrollar explicando ni teoría, ni código... voy a partir directamente de los ejemplos que son autoexplicativos.

Para verlo partiremos de una base de datos con la siguiente estructura:


Qué tenemos aquí?

Pues es bien sencillo, tenemos una base de datos de una filmoteca personal, en la que podemos asociar cada vídeo a una tabla de actores (varios actores por vídeo) y a las categorías a las que pertenece la peli (que también pueden ser más de una, p.ej. animación / terror). Además tanto actores como categorías tienen un contador que nos indica el número vídeos que tienen relacionados.

Borrado en cascada

Imaginamos que borramos un vídeo. Deberíamos también borrar las tablas de relaciones con actores y categorías. Esto lo podríamos hacer por código desde luego, pero lo podemos poner también como un trigger de la tabla video. Esto tiene la ventaja de que reduce accesos al servidor. Además la Base de Datos trabaja más rápido ya que se entiende consigo misma. Si además borramos vídeos desde el gestor de base de datos phpMyAdmin, ya no tendremos que preocuparnos por las relaciones.

Esto sería así:

DROP TRIGGER IF EXISTS `deleteVideo`;
DELIMITER //
CREATE TRIGGER `deleteVideo` AFTER DELETE ON `Video`
 FOR EACH ROW begin
DELETE FROM relVideoCategory WHERE relVideoCategory.idVideo = old.idVideo;
        DELETE FROM relVideoActor WHERE relVideoActor.idVideo = old.idVideo;
END
//
DELIMITER ;

Actualización de un registro

Cada vez que relacionamos un vídeo con un actor, creamos una entrada en RelVideoActor. Podemos utilizar este hecho para que un nuevo registro en RelVideoActor, aumente el contador de actores. Y es más podemos hacer que una baja de ese registro disminuya el contador. 

delimiter |
CREATE TRIGGER inserRelVideoCategory AFTER INSERT ON relVideoCategory
  FOR EACH ROW
  BEGIN
     UPDATE categories SET categoryCount = categoryCount + 1 WHERE idCategory = NEW.idCategory;
  END;
|

delimiter ;


delimiter |
CREATE TRIGGER deleteRelVideoCategory AFTER DELETE ON relVideoCategory
  FOR EACH ROW
  BEGIN
     UPDATE categories SET categoryCount = categoryCount - 1 WHERE idCategory = OLD.idCategory;
  END;
|

delimiter ;

Autodestrucción

Quizá querríamos también mantener nuestra tabla de actores de manera dinámica. Es decir, los actores sólo permanecerían en la tabla mientras hubiera películas en las que aparecieran. Si su contador llegase a cero, el actor, ejecutaría su mejor papel suicidándose.

Pues bien... podría poneros aquí el código y demás y no daría error... pero al ejecutarlo nos diría que nones, que lo del suicidio no está bien visto por los ojos del señor, ya que no podemos borrar un registro de la misma tabla que dispara el trigger.

Así que deberemos acudir al trigger que elimina la relación y decrementa el contador y contratar ahí a nuestro sicario:

delimiter |
CREATE TRIGGER deleteRelVideoActor AFTER DELETE ON relVideoActor
  FOR EACH ROW
  BEGIN
     UPDATE actor SET actorCount = actorCount - 1 WHERE idActor = OLD.idActor;
     DELETE FROM actor WHERE idActor = OLD.idActor AND actorCount = 0;
  END;
|

delimiter ;

Disable Triggers

Otro caso... imaginemos que queremos borrar categorías, pero ya tenemos vídeos asignados a las mismas.  Podemos  (y no es propaganda del coletas):
  • Comprobar si hay vídeos asignados a la categoría, y si ese es el caso impedir la baja.
  • Borrar la categoría y también todos les registros de RelVideoCategory que la contengan. (que es lo que vamos a hacer)
Relamente no hace falta que ponga el código, sería como en el primer ejemplo de este artículo, el borrado en cascada aunque no funcionaría... ¿y por qué?

Pensad en lo que haríamos:
  1. Borraríamos la categoría cosa que dispararía el borrado de RelVideoCategory
  2. Borraríamos RelVideoCategory cosa que dispararía el borrado de categorías...    :-)
Es decir, la cosa está en que deberíamos impedir que al borrarse RelVideoCategory se disparara su trigger... y para eso necesitamos un comando disable triggers que MySQL no tiene...  :-)

Resolveremos el trigger con el siguiente trick:

DELIMITER ;;
CREATE TRIGGER deleteCategory AFTER DELETE ON category
  FOR EACH ROW begin
        SET @disable_triggers = 1;
  DELETE FROM relVideoCat WHERE relVideoCat.idCategory = old.idCategory;
                SET @disable_triggers = NULL;
END ;;
        
DELIMITER ;

DELIMITER ;;
CREATE TRIGGER deleteRelVideoCat AFTER DELETE ON relVideoCategory
       FOR EACH ROW
        BEGIN
             IF @disable_triggers IS NULL THEN
        UPDATE category SET categoryCount = categoryCount - 1 WHERE idCategory = OLD.idCategory;
             end if;
        END ;;
DELIMITER ;




Y hasta aquí estos pequeños apuntes sobre disparadores. Como siempre esperando a que a alguien le sea útil.

Os dejo que tengo prácticas de tiro.

5 comentarios:

  1. gracias me salvaste en mi examen ;) muy buena explicacion!!

    ResponderEliminar
  2. Buenas,
    Tendrá alguien un ejemplo para un Trigger AFTER UPDATE ON que haga un UPDATE en .

    Me explico, tengo dos (2) bases de datos (BD) y una tabla de proveedores en ambos repositorios, ya hice un Trigger que al insertar en una BD me replica ese proveedor a la otra BD, ahora quiero que al modificar en una de las BD los cambios también se guarden en la otra.

    Muchas gracias de antemano..

    ResponderEliminar
  3. Como uso trigger para generar rfc automaticamente, conoces aulgun post?

    ResponderEliminar