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;