DELIMITER //
CREATE TRIGGER nombre_disparador
AFTER INSERT
ON nombre_tabla FOR EACH ROW
BEGIN
-- declaracion de variables
DECLARE vUsusario varchar(50);
-- código fuente
SELECT USER() INTO vUsusario;
INSERT INTO log_usuario
( concepto, tabla, fecha, usuario)
VALUES
( 'Ha insertado un registro','nombre_tabla', SYSDATE(), vUsusario);
END;//
DELIMITER ;
DELIMITER $$
CREATE TRIGGER insert_empleados
after insert ON EMPLEADOS FOR EACH ROW
BEGIN
INSERT INTO log_usuarios
( concepto, tabla, fecha, usuario)
VALUES
( CONCAT(CONCAT(NEW.nif," | "),NEW.nombre),
'EMPLEADOS', SYSDATE(), USER());
END; $$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER delete_empleados
after delete ON EMPLEADOS FOR EACH ROW
BEGIN
INSERT INTO log_usuarios
( concepto, tabla, fecha, usuario)
VALUES
( CONCAT('DELETE: ',CONCAT(CONCAT(old.nif,' | '),old.nombre),
'EMPLEADOS', SYSDATE(), USER()));
END; $$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER update_empleados
after update ON EMPLEADOS FOR EACH ROW
BEGIN
IF (old.nif<>new.nif) THEN
INSERT INTO log_usuarios
(concepto, tabla, fecha, usuario)
VALUES
(CONCAT('UPDATE: ',CONCAT(CONCAT(old.nif,' -> '),new.nif),
'EMPLEADOS', SYSDATE(), USER()));
END IF;
IF (old.nombre<>new.nombre) THEN
INSERT INTO log_usuarios
(concepto, tabla, fecha, usuario)
VALUES
(CONCAT('UPDATE: ',CONCAT(CONCAT(old.nombre,' -> '),new.nombre),
'EMPLEADOS', SYSDATE(), USER()));
END IF;
IF (old.movil<>new.movil OR old.email<>new.email OR old.superusuario<>new.superusuario) THEN
INSERT INTO log_usuarios
(concepto, tabla, fecha, usuario)
VALUES
(CONCAT('UPDATE: ALGO',(new.NIF)),
'EMPLEADOS', SYSDATE(), USER())update_empleadosupdate_empleados;
END IF;
END; $$
DELIMITER ;
Ejercicio 1. En la tabla contratos añade una nueva columna que llamarás “precio” de tipo DOUBLE y guardará el producto de precio_mes x meses. Se trata de un acampo calculado de forma que primero a crear una sentencia UPDATE que acutlice este nuevo campo, luego crearemos un TRIGGER para que actualice el campo en caso de que se modifique el precio_mes o el campo meses.
DELIMITER //
CREATE TRIGGER actualiza_contratos
BEFORE UPDATE ON contratos
FOR EACH ROW
BEGIN
IF (NEW.precio_mes <> OLD.precio_mes) or
(NEW.meses <> OLD.meses)
THEN
SET NEW.precio = NEW.precio_mes * NEW.meses;
END IF;
END; //
DELIMITER ;
Ejercicio 2. Crea una nueva tabla (recibos) para almacenar los recibos generados a partir de un contrato, un recibo consta de una fecha, un concepto, un número de recibo y un importe, además tener en cuenta que debe estar relacionado con un contrato.
create trigger tgr_table1 after insert on table1
for each row
begin
declare v_index int default 1;
while v_index <= new.count_column do
insert into table3(id) values (concat(substr(new.id,1,7),lpad(v_index,3,'0')));
set v_index := v_index + 1;
end while;
end;
Ejercicio 3: Después de crear un contrato se deberá programar un trigger para que genere tantos recibos (en la tabla anteriormente creada) como meses incluya dicho contrato, dicho recibo deberá estar relacionado lógicamente con un determinado contrato, y cada contrato podrá tener 1 o varios recibos, tantos como meses incluya dicho contrato.
/*SELECT year(SYSDATE());*/
/*SELECT STR_TO_DATE("2017 04 10 ", "%Y %m %d "); */
/*SELECT CONVERT("2017-08-29", DATE)*/
SELECT STR_TO_DATE(
CONCAT(YEAR(SYSDATE())," ",MONTH(SYSDATE())," ","28"),
"%Y %m %d");
DELIMITER $$
create trigger crea_recibos after insert on contratos
for each ROW BEGIN
DECLARE i INT DEFAULT 1;
DECLARE fecha DATE DEFAULT SYSDATE();
SET fecha = NEW.fecha_inicio;
if (day(fecha) > 28) then
SELECT STR_TO_DATE(CONCAT(
YEAR(NEW.fecha_inicio)," ",
MONTH(NEW.fecha_inicio)," ","28"),
"%Y %m %d") INTO fecha;
END if;
while i <= NEW.meses do
INSERT INTO recibos (num_recibo,fk_contrato,concepto,fecha, importe)
VALUES (i,NEW.id,CONCAT('Recibo ',i," de ",NEW.meses,"(",NEW.id,")"),
date_add(fecha,interval ROUND((30.41666666667*(i-1)),0) DAY) ,NEW.precio);
SET i = i + 1;
END while;
END;$$
DELIMITER ;
Ejercicio 4: Modifica el anterior trigger para que cuando se cambie algún dato que implique a los recibos estos queden también actualizados de forma coherente.
BEGIN
DECLARE reconstruye BOOLEAN DEFAULT FALSE;
DECLARE i INT DEFAULT 1;
DECLARE fecha DATE DEFAULT SYSDATE();
SET fecha = NEW.fecha_inicio;
if NEW.fecha_inicio <> OLD.fecha_inicio then
set reconstruye = TRUE;
else
if NEW.meses <> OLD.meses then
set reconstruye = TRUE;
ELSE
if NEW.precio_mes <> OLD.precio_mes then
set reconstruye = TRUE;
end if;
END if;
END if;
if reconstruye then
if (day(fecha) > 28) then
SELECT STR_TO_DATE(CONCAT(
YEAR(NEW.fecha_inicio)," ",
MONTH(NEW.fecha_inicio)," ","28"),
"%Y %m %d") INTO fecha;
if fecha=NULL then
SET fecha = NEW.fecha_inicio;
END if;
END if;
while i <= NEW.meses do
INSERT INTO recibos (num_recibo,fk_contrato,concepto,fecha, importe)
VALUES (i,NEW.id,CONCAT('Recibo ',i," de ",NEW.meses,"(",NEW.id,")"),
date_add(fecha,interval ROUND((30.41666666667*(i-1)),0) DAY) ,NEW.precio);
SET i = i + 1;
END while;
END if;
END
Function …. le pasamos una fecha y nos la ajusta al día 28
CREATE DEFINER=`publicidad`@`%` FUNCTION `ArreglaFecha`(
`fecha` date
)
RETURNS date
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE NewFecha date;
SET Newfecha = fecha;
if (day(fecha) > 28) then
SELECT STR_TO_DATE(CONCAT(
YEAR(fecha)," ",
MONTH(fecha)," ","28"),
"%Y %m %d") INTO Newfecha;
END if;
RETURN Newfecha;
END
Procedure CreaRecibos
CREATE DEFINER=`publicidad`@`%` PROCEDURE `ProcCreaRecibos`(
IN `meses` INT,
IN `id` INT,
IN `fecha` DATE,
IN `precio_mes` DOUBLE
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE i INT;
SET i = 1;
while i <= meses do
INSERT INTO recibos (num_recibo,fk_contrato,concepto,fecha, importe)
VALUES (i,id,CONCAT('Recibo ',i," de ",meses,"(",id,")"),
date_add(fecha,interval ROUND((30.41666666667*(i-1)),0) DAY) ,precio_mes);
SET i = i + 1;
END while;
END
Trigger ActualizaRecibos
CREATE DEFINER=`publicidad`@`%` TRIGGER `actualiza_recibos` AFTER UPDATE ON `contratos` FOR EACH ROW BEGIN
DECLARE reconstruye BOOLEAN DEFAULT FALSE;
DECLARE fecha DATE DEFAULT SYSDATE();
if NEW.fecha_inicio <> OLD.fecha_inicio then
set reconstruye = TRUE;
else
if NEW.meses <> OLD.meses then
set reconstruye = TRUE;
ELSE
if NEW.precio_mes <> OLD.precio_mes then
set reconstruye = TRUE;
end if;
END if;
END if;
if reconstruye then
DELETE FROM recibos WHERE fk_contrato = NEW.id;
set fecha = ArreglaFecha(NEW.fecha_inicio);
call ProcCreaRecibos(
NEW.meses,NEW.id,fecha,NEW.precio_mes);
END if;
END
Crea_Recibos
CREATE DEFINER=`publicidad`@`%` TRIGGER `crea_recibos` AFTER INSERT ON `contratos` FOR EACH ROW BEGIN
DECLARE fecha DATE DEFAULT SYSDATE();
set fecha = ArreglaFecha(NEW.fecha_inicio);
call ProcCreaRecibos(
NEW.meses,NEW.id,fecha,NEW.precio_mes);
END
Ejercicio 5: De igual manera realiza un trigger que elimine los recibos en el caso de borrado de un contrato.
CREATE DEFINER=`publicidad`@`%` TRIGGER `contratos_before_delete` BEFORE DELETE ON `contratos` FOR EACH ROW BEGIN
DELETE FROM recibos WHERE fk_contrato=OLD.id;
END
Ejercicio 6: Crea una función/procedimiento que incremente en un X% el precio de los anuncios de una determinada provincia. Y prueba a incrementar un 2,45% el precio de los anuncios de la provincia de Alicante.
CREATE FUNCTION `Incrementa`(
`tanto` DOUBLE,
`valor` DOUBLE
)
RETURNS DOUBLE
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
RETURN tanto/100 * valor + valor;;
END
CREATE DEFINER=`publicidad`@`%` PROCEDURE `Incrementa_Precio`(
IN `tanto` DOUBLE,
IN `provincia` VARCHAR(50)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE num_prov INTEGER;
DECLARE filtro VARCHAR(50);
SELECT CONCAT("%",provincia,"%") INTO filtro;
SET num_prov =0;
SELECT id INTO num_prov FROM
provincias WHERE nombre LIKE filtro;
UPDATE anuncios SET precio=incrementa(tanto,precio)
WHERE fk_municipios IN
(SELECT id FROM municipios
WHERE fk_provincias = num_prov);
END
Ejercicio 7: Crea tres nuevos campos en la tabla ANUNCIOS para guardar el precio anterior, el incremento o decremento del precio y la fhecha y hora de la última actualización. Luego crea los triggers (INSERT y UPDATE) necesarios para gestionar dichos nuevos campos.
Función calcular incremento:
CREATE DEFINER=`publicidad`@`%` FUNCTION `CalculaIncremento`(
`Antes` DOUBLE,
`Despues` DOUBLE
)
RETURNS double
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
RETURN ROUND((Despues-Antes)*100/Antes,2);
END
ALTER TABLE anuncios
ADD COLUMN fecha_modificacion DATETIME,
ADD COLUMN PrecioOLD DOUBLE,
ADD COLUMN diferencia DOUBLE;
CREATE DEFINER=`publicidad`@`%` TRIGGER `anuncios_before_update` BEFORE UPDATE ON `anuncios` FOR EACH ROW BEGIN
if (OLD.precio<>NEW.precio) then
set NEW.PrecioOLD = OLD.precio;
set NEW.diferencia = CalculaIncremento(OLD.precio,NEW.precio);
ELSE
if ((OLD.PrecioOLD<>NEW.PrecioOLD) OR
(OLD.diferencia<>NEW.diferencia)) then
set new.PrecioOLD = OLD.PrecioOLD;
set new.Diferencia= OLD.diferencia;
END if;
END if;
SET NEW.fecha_modificacion = NOW();
END
CREATE DEFINER=`publicidad`@`%` TRIGGER `anuncios_before_insert` BEFORE INSERT ON `anuncios` FOR EACH ROW BEGIN
SET NEW.PrecioOLD = NEW.precio;
SET NEW.diferencia = 0;
SET NEW.fecha_modificacion = NOW();
END
Alternativas:
SET @offset = FLOOR(RAND() * (SELECT COUNT(*) FROM tu_tabla));
SET @query = CONCAT('SELECT @resultado := texto FROM tu_tabla LIMIT 1 OFFSET ', @offset);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Ahora @resultado contiene el texto aleatorio
SELECT @resultado;
Una opción que puede ser lenta :
ORDER BY RAND()