Foro de elhacker.net

Programación => Bases de Datos => Mensaje iniciado por: Baal_30 en 20 Junio 2015, 00:21



Título: Trigger sencillo MySQL
Publicado por: Baal_30 en 20 Junio 2015, 00:21
Muy buenas, tengo una tabla que se llama "personaje" que tiene una columna que es "reino" y es un INT, también tengo una tabla que se llama "reino" y tiene una columna que se llama "id" y es un INT también.

Estoy probando a crear este trigger pero me da error en la linea 7 dice (donde la sentencia del DELETE) :/

Código
  1. CREATE TRIGGER borrarPersonaje
  2. BEFORE DELETE
  3. ON personaje
  4. FOR EACH ROW
  5. BEGIN
  6.  
  7. DELETE FROM reino WHERE id = OLD.reino;
  8.  
  9. END;

¿Que estoy haciendo mal?

Un saludo.


Título: Re: Trigger sencillo MySQL
Publicado por: fran800m en 20 Junio 2015, 14:54
Hace siglos que no hago trigger y en MySql ni siquiera lo he hecho en la vida, ¿qué error te da?

Un saludo,


Título: Re: Trigger sencillo MySQL
Publicado por: Baal_30 en 20 Junio 2015, 17:27
Me dice : "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7"

Y nada más :/


Título: Re: Trigger sencillo MySQL
Publicado por: fran800m en 20 Junio 2015, 20:54
Prueba con
Código:
delimiter //
CREATE TRIGGER borrarPersonaje
    BEFORE DELETE
    ON personaje
    FOR EACH ROW
    BEGIN
     
    DELETE FROM reino WHERE id = OLD.reino;
     
    END;//
delimiter ;
   


Título: Re: Trigger sencillo MySQL
Publicado por: Baal_30 en 25 Junio 2015, 17:26
He probado y se me queda bloqueado el phpmyadmin con el cartelito de "cargando..." :/


Título: Re: Trigger sencillo MySQL
Publicado por: WHK en 27 Junio 2015, 20:06
Debes tener un conflicto en tu tabla, tendriamos que ver la estructura completa de tu base de datos para saber porque se está bloqueando. Por ejemplo talves tienes una acción on delete que hace una llamada recursiva infinita, me ha pasado varias veces y pars solucionarlo debo crear tablas intermedias o eliminar una acción.


Título: Re: Trigger sencillo MySQL
Publicado por: Baal_30 en 27 Junio 2015, 22:47
No puse ningún ON DELETE ni ON UPDATE, estarán por defecto todos supongo :/


Título: Re: Trigger sencillo MySQL
Publicado por: WHK en 27 Junio 2015, 23:11
Si es una base de datos relacional con foreign keys entonces vas a tener problemas si no estableces una acción ya que si tienes una tabla de relaciones entre una tabla A y tabla B cuando uno de estos sea eliminado o modificado vas a tener un valor nulo o corrupto en la tabla de relaciones ya que estará apuntando a un registro que ya no existe.

Eso de eliminar una fila cuando un registro de otra tabla es eliminado no se hace a traves de triggers sino con tablas relacinales y foreign keys.

Dale un vistazo a esta base de datos:
http://foro.elhacker.net/bases_de_datos/ayuda_amigo_necesito_crear_una_base_de_datos-t437701.0.html;msg2023594#msg2023594

Todos los puntos de color rojos son llaves foraneas que tienen como acción "cascade" lo cual indica que cuando se elimina o se modifica este tambien debe eliminarse o actualizarse desde la tabla de relaciones.

En algunos casos cuando tienes una tabla con una columna que apunta a otra tabla (de uno a uno) entonces la acción es set null para que el valor se vuelva nulo y no te elimine todo el registro:

Código:
CREATE TABLE IF NOT EXISTS `usuarios_telefonos` (
  `usuario_id` INT(11) NOT NULL AUTO_INCREMENT,
  `codigo_pais` INT(11) NOT NULL,
  `codigo_ciudad` INT(11) NOT NULL,
  `numero` INT(11) NOT NULL,
  `es_fijo` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`usuario_id`),
  CONSTRAINT `fk_usuarios_telefonos_usuarios1`
    FOREIGN KEY (`usuario_id`)
    REFERENCES `usuarios` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

(http://i.stack.imgur.com/gkyG7.png)


Título: Re: Trigger sencillo MySQL
Publicado por: Baal_30 en 29 Junio 2015, 13:57
ummm muchas gracias por la explicación ¡! Es que creía que si no ponías nada en el ON DELETE se ponía por defecto CASCADE, pero no es así por lo que se ve ¿verdad?

Es aconsejable poner SIEMPRE el ON DELETE y ON CASCADE pues...¿no?

¿Y está mal hacer lo que busco con un trigger? ¿Siempre mejor con tablas? ¿Y borrando el registro de la tabla esa directamente y au?


Título: Re: Trigger sencillo MySQL
Publicado por: WHK en 29 Junio 2015, 15:41
Pues MySQL con el motor innodb están diseñados para ese tipo de trabajo de manera automática y no, no siempre se debe poner on cascade, debes tomar cada relación y pensar bien que debería suceder cuando se elimina o actualiza un registro, la mayoría de las veces son on cascade, eso si, pero no siempre es así, hay veces que necesitas establecer a null, yo en lo personal nunca he utilizado el restringir el valor o la fila, siempre uso cascade o set null dependiendo del caso, en el caso de una tabla relacional de muchos a muchos si es necesario hacerlo con cascade y no necesitarás triggers, de hecho de mis años programando jamás he necesitado utilizar triggers o funciones procedure, de hecho intento jamás usarlos ya que dejo ese trabajo de calculo al lenguaje de programación del lado del código y no de la base de datos ya que cuando necesitas hacer un flujo de una aplicación, documentarla y llevar un cierto orden no puedes dejar toda la lógica desparramada entre la base de datos y el código, para mi la base de datos es base de datos y el calculo de recoger y buscar datos se lo dejo al código y libero la carga del servidor (útil cuando trabajas con separación de capas).

Mira esto:

Código
  1. -- MySQL Workbench Synchronization
  2. -- Generated: 2015-06-29 10:30
  3. -- Model: New Model
  4. -- Version: 1.0
  5. -- Project: Name of the project
  6. -- Author: WHK
  7.  
  8. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  9. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  10. SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
  11.  
  12. CREATE TABLE IF NOT EXISTS `blank`.`personaje` (
  13.  `id` INT(11) NOT NULL AUTO_INCREMENT,
  14.  `nombre` VARCHAR(256) NOT NULL,
  15.  `reino_id` INT(11) NULL DEFAULT NULL,
  16.  PRIMARY KEY (`id`),
  17.  INDEX `fk_personaje_reinos_idx` (`reino_id` ASC),
  18.  CONSTRAINT `fk_personaje_reinos`
  19.    FOREIGN KEY (`reino_id`)
  20.    REFERENCES `blank`.`reinos` (`id`)
  21.    ON DELETE SET NULL
  22.    ON UPDATE CASCADE)
  23. ENGINE = InnoDB
  24. DEFAULT CHARACTER SET = utf8
  25. COLLATE = utf8_general_ci;
  26.  
  27. CREATE TABLE IF NOT EXISTS `blank`.`reinos` (
  28.  `id` INT(11) NOT NULL AUTO_INCREMENT,
  29.  `nombre` VARCHAR(256) NOT NULL,
  30.  PRIMARY KEY (`id`))
  31. ENGINE = InnoDB
  32. DEFAULT CHARACTER SET = utf8
  33. COLLATE = utf8_general_ci;
  34.  
  35.  
  36. SET SQL_MODE=@OLD_SQL_MODE;
  37. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  38. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  39.  

En esta base de datos tienes personaje y reinos y la tabla de personajes tiene una columna con el id de reino, esto quiere decir que el personaje va a pertenecer a un solo reino o ninguno, por este motivo el valor por defecto es null, ahora, este valor tiene una acción de cascade cuando se actualiza el registro o set null cuando se elimina... como funciona esto?:

Cuando se actualiza el registro del reino entonces se actualiza tambien el valor en la fila del personaje, pero que pasa si eliminas el reino? si le pones cascade se va a eliminar el personaje, por eso lo estableces a null, ahora, si necesitas que el personaje siempre tenga un reino entonces le das la opcioon de bloqueo y restringes la fila, de esta manera no podra usarse el personaje hasta que manualmente le asignes un nuevo reino y no tendras problemas en la selección o actualización de registros.

Ahora, digamos que necesitas que el personaje pueda estar en mas de un reino asociado:

Código
  1. -- MySQL Workbench Synchronization
  2. -- Generated: 2015-06-29 10:35
  3. -- Model: New Model
  4. -- Version: 1.0
  5. -- Project: Name of the project
  6. -- Author: WHK
  7.  
  8. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  9. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  10. SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
  11.  
  12. CREATE TABLE IF NOT EXISTS `blank`.`personaje` (
  13.  `id` INT(11) NOT NULL AUTO_INCREMENT,
  14.  `nombre` VARCHAR(256) NOT NULL,
  15.  PRIMARY KEY (`id`))
  16. ENGINE = InnoDB
  17. DEFAULT CHARACTER SET = utf8
  18. COLLATE = utf8_general_ci;
  19.  
  20. CREATE TABLE IF NOT EXISTS `blank`.`reinos` (
  21.  `id` INT(11) NOT NULL AUTO_INCREMENT,
  22.  `nombre` VARCHAR(256) NOT NULL,
  23.  PRIMARY KEY (`id`))
  24. ENGINE = InnoDB
  25. DEFAULT CHARACTER SET = utf8
  26. COLLATE = utf8_general_ci;
  27.  
  28. CREATE TABLE IF NOT EXISTS `blank`.`personajes_reinos_relacion` (
  29.  `personaje_id` INT(11) NOT NULL,
  30.  `reino_id` INT(11) NOT NULL,
  31.  PRIMARY KEY (`personaje_id`, `reino_id`),
  32.  INDEX `fk_personajes_reinos_relacion_reinos1_idx` (`reino_id` ASC),
  33.  CONSTRAINT `fk_personajes_reinos_relacion_personaje`
  34.    FOREIGN KEY (`personaje_id`)
  35.    REFERENCES `blank`.`personaje` (`id`)
  36.    ON DELETE CASCADE
  37.    ON UPDATE CASCADE,
  38.  CONSTRAINT `fk_personajes_reinos_relacion_reinos1`
  39.    FOREIGN KEY (`reino_id`)
  40.    REFERENCES `blank`.`reinos` (`id`)
  41.    ON DELETE CASCADE
  42.    ON UPDATE CASCADE)
  43. ENGINE = InnoDB
  44. DEFAULT CHARACTER SET = utf8
  45. COLLATE = utf8_general_ci;
  46.  
  47.  
  48. SET SQL_MODE=@OLD_SQL_MODE;
  49. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  50. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  51.  

En este caso hay una tabla intermedia de muchos a muchos donde un personaje puede estar asociado a mas de un mundo, ahora, si te fijas ambas columnas de esta tablas son llaves, esto quiere decir que no se podrán repetir los mundos con los personajes, esto quiere decir que si hay dos reinos entonces un personaje no podrá tener mas de dos reinos y si intentas asociar un reino por segunda ves la base de datos te arrojará un error diciendo que el registro está duplicado. Esto es súper útil si deseas mantener la integridad de datos de tu aplicación ya que si permites duplicidades vas a tener problemas muy serios al momento de manejar tus datos, esto te restringe en parte a diseñar una buena aplicación.

Si te fijas, en este ejemplo ambas funciones son cascade en ambas columnas ya que si eliminas un personaje o eliminas un reino entonces la relación desaparece, si eliminas un reino el personaje ya no estará asociado a el y eso es lo normal que pase y no que quede un registro fantasma que te diga que pertenece a un reino que no existe.

Bueno.... todo eso lo hace innodb de manera automática e innodb viene integrado por defecto con mysql.

Saludos.


Título: Re: Trigger sencillo MySQL
Publicado por: Baal_30 en 30 Junio 2015, 14:17
Genial toda la info :) Una última pregunta, en el caso del primer ejemplo, en el que un personaje sólo puede tener un reino, si borras el reino en el personaje el id del reino se vuelve a null, ¿pero que pasaría si borrases el personaje? ¿el reino seguiría existiendo pero no estaría asignado a ningún personaje?


Título: Re: Trigger sencillo MySQL
Publicado por: WHK en 30 Junio 2015, 23:40
Al reino no le pasa nada porque la relación la tiene el registro del personaje, si quisieras que se elimine el reino deberías entonces hacer una relación en la tabla de reinos.


Título: Re: Trigger sencillo MySQL
Publicado por: Baal_30 en 1 Julio 2015, 19:25
Ahh de acuerdo, gracias WHK¡!