TEORÍA

En MySQL, desde el año 2005, está reportado un bug bastante preocupante que viola los principios ACID de una base de datos (concretamente el de la «Consistencia»).

Este bug consiste básicamente en que los triggers que se asignen a tablas para que se ejecuten antes o después de algún evento (por ejemplo, AFTER DELETE ON <table>), no se ejecutan si dichas tablas son hijas de otra que provoca dicho evento en cascada (por ejemplo, un ON DELETE CASCADE). Esto es:

• Si una entidad ‘B’ (siguiendo el modelo entidad-relación) es débil respecto a otra entidad ‘A’ y…
• Si ‘B’ tiene un trigger del tipo AFTER <evento> ON ‘B’…
• Al producirse el evento <evento> en una tupla de ‘A’ (que provocará una reacción en cascada en la tupla de ‘B’ relacionada con la tupla de ‘A’) el trigger de ‘B’ nunca será ejecutado, pero la tupla de ‘B’ sí que se verá afectada por el evento en cascada.

Por ejemplo, si ‘B’ tiene un trigger AFTER DELETE ON ‘B’, borrar una tupla de ‘A’ implicará el borrado de una tupla de ‘B’ pero nunca ejecutará el trigger de ‘B’.

PRÁCTICA

Bien, una vez entendido esto vamos a plantearnos un ejemplo práctico (entre otras cosas para que entendáis lo grave del asunto).

Imaginemos que queremos hacer un sistema de log de los cambios que se han realizado en una tabla. Para ello, cada vez que se inserte, modifique o se borre una tupla de cada tabla, se van a guardar los datos de dicha tupla en otra tabla de log que incluirá (a parte de los propios atributos de la tabla que estamos haciendo el histórico) 3 atributos:

• idLog: valor autoincremental de la tabla histórica.
• dateTimeLog: la fecha/hora en la que se realizó el log.
• operation: qué operación ha provocado su inserción en esta tabla histórica (‘DELETE’, ‘UPDATE’ ó ‘INSERT’).

Entonces creamos una BD de 4 tablas: 2 de ellas siguen el ejemplo de la teoría, tabla_padre y tabla_hija (que es entidad débil respecto de la tabla_padre).

Por otro lado, para el histórico tenemos sus tablas análogas: tabla_padre_log y tabla_hija_log. Ambas completamente aisladas del resto de entidades. Estas tablas tienen los 3 atributos antes indicados más los que contengan sus tablas análogas.

ATENCIÓN, no deberíamos tener entidades aisladas: Siendo puristas, las entidades nunca deben estar aisladas del resto del modelo E-R, pero vamos a «romper las normas» para este ejemplo.

Para conseguir que la tabla_hija sea entidad débil respecto de la tabla_padre, creamos una relación ON DELETE CASCADE en el atributo «tabla_hija.padre_id» (que apuntará al «id» de la tabla_padre).

[sql]
ALTER TABLE `tabla_hija`
ADD CONSTRAINT `hija pertenece a padre`
FOREIGN KEY (`padre_id`)
REFERENCES `tabla_padre` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE;
[/sql]

Finalmente creamos los triggers para ambas tablas (para la tabla_padre y la tabla_hija) que son del tipo:

[sql]
/*TABLA_PADRE*/
/*DELETE*/
CREATE TRIGGER `log_delete_fila_padre`
AFTER DELETE ON `tabla_padre`
FOR EACH ROW
INSERT INTO `tabla_padre_log` (
`idLog`, `dateTimeLog`, `operation`, `id`, `attr1`, `attr2`, `attr3`)
VALUES
(NULL, NOW(), ‘DELETE’, OLD.id, OLD.attr1, OLD.attr2, OLD.attr3);

…

/*TABLA_HIJA*/
/*DELETE*/
CREATE TRIGGER `log_delete_fila_hija`
AFTER DELETE ON `tabla_hija`
FOR EACH ROW
INSERT INTO `tabla_hija_log` (
`idLog`, `dateTimeLog`, `operation`, `id`, `padre_id`, `attr2`, `attr3`)
VALUES
(NULL, NOW(), ‘DELETE’, OLD.id, OLD.padre_id, OLD.attr2, OLD.attr3)
[/sql]

Y ya solo queda hacer la prueba:

Si en la tabla padre insertarmos una fila y en la tabla hija insertamos dos filas que apunten a la fila de la tabla padre:

Y actualizamos attr3 con el valor 7, y luego borramos la fila con id=2, podemos ver en el log de la tabla_hija que los cambios se han registrado:

Ahora vamos a probar a borrar la fila de la tabla padre y veremos cómo se registra el borrado de esta, pero no se registra el borrado de la fila de la tabla hija con la que está relacionada (la fila con id 1); sin embargo la tupla de la tabla hija es borrada igualmente:

DESCARGA EL EJEMPLO

En el siguiente enlace podéis descargar el archivo comprimido con la base de datos del ejemplo para que lo probéis vosotros mismos 😉