Enreas
Registrarse
Advertisement

Base de datos de ejemplo[]

Creacion:

DROP DATABASE IF EXISTS videoteca;
CREATE DATABASE videoteca;

Tablas:

USE videoteca;

CREATE TABLE actor (
  id INTEGER UNSIGNED NOT NULL
    AUTO_INCREMENT,
  nombre VARCHAR(64) NOT NULL,
  apellidos VARCHAR(64) NOT NULL,
  PRIMARY KEY(id)
)
ENGINE = InnoDB;

CREATE TABLE actor_ext (
  idactor INTEGER UNSIGNED NOT NULL,
  imdb VARCHAR(32) NOT NULL,
  carpeta VARCHAR(255) NOT NULL,
  PRIMARY KEY(idactor),
  INDEX ae_FK(idactor),
  FOREIGN KEY(idactor)
    REFERENCES actor(id)
)
ENGINE = InnoDB;

Inserciones:

USE videoteca;

INSERT INTO actor(nombre, apellidos)
VALUES('Harrison', 'Ford');

INSERT INTO actor(nombre, apellidos)
VALUES('Russell', 'Crowe');

INSERT INTO actor_ext(idactor, imdb, carpeta)
VALUES(1, 'nm0000148', 'harrison_ford');

INSERT INTO actor_ext(idactor, imdb, carpeta)
VALUES(2, 'nm0000128', 'russell_crowe');

Desencadenador de inserción[]

USE videoteca;

DELIMITER //
CREATE TRIGGER dc_actor_insertar
AFTER INSERT ON actor
FOR EACH ROW BEGIN

DECLARE nuevo_carpeta VARCHAR(255);

SET nuevo_carpeta = CONCAT(
  LOWER(NEW.nombre),
  '_',
  LOWER(NEW.apellidos)
);

INSERT INTO actor_ext(idactor, imdb, carpeta)
VALUES(NEW.id, '', nuevo_carpeta);

END
//
DELIMITER ;

Inserción de un nuevo actor:

USE videoteca;
INSERT INTO actor(nombre, apellidos)
VALUES('Tim','Robins');

Desencadenador de actualización[]

USE videoteca;

DELIMITER //
CREATE TRIGGER dc_actor_actualizar
AFTER UPDATE ON actor
FOR EACH ROW BEGIN

DECLARE nuevo_carpeta VARCHAR(255);

SET nuevo_carpeta = CONCAT(
  LOWER(NEW.nombre),
  '_',
  LOWER(NEW.apellidos)
);

UPDATE
  actor_ext
SET
  carpeta = nuevo_carpeta
WHERE
  idactor = NEW.id;

END
//
DELIMITER ;

Cambiar el nombre del actor:

USE videoteca;

UPDATE
  actor
SET
  apellidos = 'Robbins'
WHERE
  id = 3;

Borrado[]

USE videoteca;

DELIMITER //
CREATE TRIGGER dc_actor_eliminar
BEFORE DELETE ON actor
FOR EACH ROW BEGIN

DELETE FROM
  actor_ext
WHERE
  idactor = OLD.id;
  
END
//
DELIMITER ;

Borrar un actor:

USE videoteca;
DELETE FROM actor WHERE id = 3;
Advertisement