Ejemplos Avanzados de Programación y Administración en MySQL

Ejemplos de Programación y Administración en MySQL

Eventos Programados (MySQL Events)

Los eventos programados permiten ejecutar tareas SQL de forma recurrente en la base de datos.

Ejemplo: Archivo de Noticias Antiguas

Este evento archiva noticias con más de 30 días de antigüedad en una tabla histórica y las elimina de la tabla original.

DELIMITER $$

CREATE EVENT archivo_noticias
ON SCHEDULE EVERY 1 MONTH
STARTS '2012-03-01 00:00:00' ENABLE
DO
BEGIN
    INSERT INTO historico_noticias
    SELECT * FROM noticias WHERE fecha <= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);

    DELETE FROM noticias WHERE fecha <= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);
END;$$

DELIMITER ;

Ejercicio 1: Comisión sobre Cuentas en Números Rojos

Crea un evento que cargue una comisión del 2% sobre las cuentas en números rojos cada primero de mes, comenzando el 13 de febrero de 2012.

DELIMITER $$

CREATE EVENT e_comision
ON SCHEDULE EVERY 1 MONTH
STARTS '2012-02-13 00:00:00'
DO
    UPDATE cuentas SET saldo = saldo + saldo * 0.02 WHERE saldo < 0;$$

DELIMITER ;

Ejercicio 2: Registro Diario de Movimientos Grandes

Crea un evento que registre diariamente los movimientos superiores a 1000€ en una tabla específica.

-- Primero, crea la tabla para el registro si no existe
CREATE TABLE IF NOT EXISTS registro_movimientos
(
    id_registro INT AUTO_INCREMENT PRIMARY KEY,
    id_movimiento INT(11),
    fecha DATE,
    cantidad DOUBLE,
    dni INT(11),
    cod_cuenta INT(11)
);

DELIMITER $$

CREATE EVENT e_movimientos
ON SCHEDULE EVERY 1 DAY
DO
    INSERT INTO registro_movimientos (id_movimiento, fecha, cantidad, dni, cod_cuenta)
    SELECT id_movimientos, CURDATE(), cantidad, dni, cod_cuenta
    FROM movimientos
    WHERE cantidad > 1000 AND DATE(fechahora) = CURDATE();$$

DELIMITER ;

Funciones Almacenadas (Stored Functions)

Las funciones almacenadas realizan cálculos y devuelven un único valor.

Ejercicio: Comprobar si un Número es Par

Realizar una función que reciba un número como entrada devolviendo 1 (verdadero) si es par y 0 (falso) si es impar. Utilizar la función MOD (resto de una división entera).

Para llamar a la función desde la línea de comandos podemos hacerlo de dos formas:

SET @x = nombre_funcion(42);
SELECT nombre_funcion(42);

DELIMITER $$

CREATE FUNCTION espar(entrada INT)
RETURNS INT
BEGIN
    DECLARE valor INT;
    SET valor = MOD(entrada, 2);
    IF valor = 0 THEN
        RETURN 1; -- TRUE
    ELSE
        RETURN 0; -- FALSE
    END IF;
END;$$

DELIMITER ;

Procedimientos Almacenados (Stored Procedures)

Los procedimientos almacenados ejecutan una secuencia de sentencias SQL y pueden tener parámetros de entrada, salida o entrada/salida.

Llamada a una Función desde un Procedimiento

Llamar a la función "espar" desde un procedimiento que nos da el resultado por pantalla. Utiliza la función CONCAT().

DELIMITER $$

CREATE PROCEDURE parimpar (IN numero INT)
BEGIN
    IF (espar(numero))
    THEN
        SELECT CONCAT(numero, ' es par');
    ELSE
        SELECT CONCAT(numero, ' es impar');
    END IF;
END;$$

DELIMITER ;

Comparación de Dos Números

Escribe una función que acepte dos números enteros, los compare y nos escriba según sea:

• num1 > num2
• num1 < num2
• num1 = num2

(Utilizar solo una sentencia de salida de datos).

Ejecutar la función:

1. Llamándola desde la línea de comandos.
2. Llamándola desde un procedimiento.

Nota: Una función debe devolver un valor, no realizar una selección directa para mostrar por pantalla. La salida se gestiona al llamar a la función (ej. SELECT compara(10, 5);). Si se necesita mostrar por pantalla dentro de la lógica, un procedimiento es más adecuado. Adaptaremos el ejemplo para que la función devuelva el string de comparación.

DELIMITER $$

CREATE FUNCTION compara (num1 INT, num2 INT)
RETURNS VARCHAR(20)
BEGIN
    DECLARE resultado VARCHAR(20);
    IF (num1 > num2) THEN
        SET resultado = CONCAT(num1, ' > ', num2);
    ELSEIF (num1 < num2) THEN
        SET resultado = CONCAT(num1, ' < ', num2);
    ELSE
        SET resultado = CONCAT(num1, ' = ', num2);
    END IF;
    RETURN resultado;
END;$$

DELIMITER ;

Función con Sentencia CASE

Realizar una función que recibe un valor de estado como entrada (C, A, N) y comprueba su valor. Según cual sea, la función nos devolverá 'Caducado', 'Activado', 'Nuevo' respectivamente. (Utiliza la sentencia de control CASE).

DELIMITER $$

CREATE FUNCTION caduca (val VARCHAR(10))
RETURNS VARCHAR(10)
BEGIN
    DECLARE estado_texto VARCHAR(10);
    CASE val
        WHEN 'C' THEN
            SET estado_texto = 'Caducado';
        WHEN 'A' THEN
            SET estado_texto = 'Activado';
        WHEN 'N' THEN
            SET estado_texto = 'Nuevo';
        ELSE
            SET estado_texto = 'Desconocido'; -- Añadir un caso por defecto es buena práctica
    END CASE;
    RETURN estado_texto;
END;$$

DELIMITER ;

Ejemplos de Parámetros en Procedimientos

Parámetro IN

DELIMITER $$

CREATE PROCEDURE proc2(IN p INT)
BEGIN
    SET @x = p;
END;$$

DELIMITER ;

-- Ejemplo de llamada desde cliente:
-- CALL proc2(1258);
-- SELECT @x;

Parámetro OUT

DELIMITER $$

CREATE PROCEDURE proc3(OUT p INT)
BEGIN
    SET p = 5;
END;$$

DELIMITER ;

-- Ejemplo de llamada desde cliente:
-- CALL proc3(@y);
-- SELECT @y;

Manejo de Errores (Handlers)

Los handlers permiten controlar el flujo de ejecución cuando ocurren errores o advertencias.

Ejemplo: Procedimiento con Cursor

A partir de una tabla personal, hacer un procedimiento que nos saque el total de empleados de la empresa utilizando un cursor.

Nota: Contar filas con SELECT COUNT(*) es mucho más eficiente que usar un cursor para este propósito. Este ejemplo ilustra el uso de cursores.

-- Asumimos una tabla 'personal' con columna 'dni'
-- CREATE TABLE personal (dni INT PRIMARY KEY, apellidos VARCHAR(30), funcion VARCHAR(15), salario INT);

DELIMITER $$

CREATE PROCEDURE total_emp()
BEGIN
    DECLARE p INT; -- Variable para el DNI
    DECLARE done INT DEFAULT 0;
    DECLARE contador INT DEFAULT 0;
    -- Declarar cursor para seleccionar DNI de la tabla personal
    DECLARE cursor_cuenta CURSOR FOR SELECT dni FROM personal;
    -- Declarar handler para NOT FOUND (fin del cursor)
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cursor_cuenta;

    read_loop: LOOP
        FETCH cursor_cuenta INTO p;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET contador = contador + 1;
    END LOOP;

    CLOSE cursor_cuenta;
    SELECT contador AS total_empleados;
END;$$

DELIMITER ;

Ejemplo: Función con Cursor

El mismo ejemplo anterior, pero implementado como función.

DELIMITER $$

CREATE FUNCTION f_contar()
RETURNS INT
BEGIN
    DECLARE p INT;
    DECLARE done INT DEFAULT 0;
    DECLARE contador INT DEFAULT 0;
    DECLARE cursor_cuenta CURSOR FOR SELECT dni FROM personal;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cursor_cuenta;

    read_loop: LOOP
        FETCH cursor_cuenta INTO p;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET contador = contador + 1;
    END LOOP;

    CLOSE cursor_cuenta;
    RETURN contador;
END;$$

DELIMITER ;

Control de Errores al Insertar (Clave Duplicada)

Ejemplo sin control de error:

DELIMITER $$

CREATE PROCEDURE insertar_sin_handler (v_DNI INT(10), v_apellidos VARCHAR(30), v_funcion VARCHAR(15), v_salario INT(10))
BEGIN
    INSERT INTO personal VALUES (v_DNI, v_apellidos, v_funcion, v_salario);
END;$$

DELIMITER ;

-- 1. Realizar una llamada al procedimiento insertando un empleado con un DNI que ya existe
-- CALL insertar_sin_handler(123, 'Apellido', 'Funcion', 1000);
-- CALL insertar_sin_handler(123, 'Otro Apellido', 'Otra Funcion', 2000); -- Esto generará un error

-- 2. Registrar el código de error que genera el sistema -> ERROR 1062 (23000): Duplicate entry

Ejemplo con control de errores (CONTINUE HANDLER):

DELIMITER $$

CREATE PROCEDURE insertar_con_handler (v_DNI INT(10), v_apellidos VARCHAR(30), v_funcion VARCHAR(15), v_salario INT(10),
                                     OUT estado VARCHAR(45))
BEGIN
    -- Declarar handler para el error 1062 (Duplicate entry)
    DECLARE CONTINUE HANDLER FOR 1062 SET estado = 'Error: DNI duplicado';

    -- Intentar la inserción
    INSERT INTO personal VALUES (v_DNI, v_apellidos, v_funcion, v_salario);

    -- Si el handler no se activó, la inserción fue exitosa
    IF estado IS NULL THEN
        SET estado = 'Registro insertado correctamente';
    END IF;

END;$$

DELIMITER ;

-- Ejemplo de llamada:
-- CALL insertar_con_handler(123, 'Apellido', 'Funcion', 1000, @mensaje);
-- SELECT @mensaje; -- Muestra 'Registro insertado correctamente'
-- CALL insertar_con_handler(123, 'Otro Apellido', 'Otra Funcion', 2000, @mensaje);
-- SELECT @mensaje; -- Muestra 'Error: DNI duplicado'

Ejemplos de Handlers (CONTINUE vs EXIT)

Creamos una tabla simple para probar los handlers.

CREATE TABLE IF NOT EXISTS test.t
(s1 INT, PRIMARY KEY (s1));

1. Procedimiento sin Handler

DELIMITER $$

CREATE PROCEDURE handlerdemo()
BEGIN
    SET @x = 1;
    INSERT INTO test.t VALUES(1);
    SET @x = 2;
    -- La siguiente inserción fallará porque s1=1 ya existe
    INSERT INTO test.t VALUES(1);
    SET @x = 3; -- Esta línea no se ejecutará si no hay handler
END;$$

DELIMITER ;

-- Llamada:
-- CALL handlerdemo();

2. Comprobar el valor de la variable @x

Después de ejecutar handlerdemo(), el valor de @x será 2, ya que la ejecución se detiene en el segundo INSERT debido al error.

-- SELECT @x;
-- Resultado: 2

3. Procedimiento con CONTINUE HANDLER

DELIMITER $$

CREATE PROCEDURE handlerdemo2()
BEGIN
    -- Declarar un CONTINUE handler para cualquier error SQLSTATE '23000' (violación de integridad)
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
    BEGIN
        -- Opcional: registrar el error o realizar alguna acción
        SET @x2 = 1; -- Indicador de que el handler se activó
    END;

    SET @x = 1;
    INSERT INTO test.t VALUES(1); -- Éxito (si la tabla estaba vacía o 1 no existía)
    SET @x = 2;
    INSERT INTO test.t VALUES(1); -- Fallará (clave duplicada), pero el handler permitirá continuar
    SET @x = 3; -- Esta línea SÍ se ejecutará
END;$$

DELIMITER ;

-- Llamada:
-- CALL handlerdemo2();

4. Comprobar el valor de la variable @x

Después de ejecutar handlerdemo2(), el valor de @x será 3, ya que el CONTINUE HANDLER permitió que la ejecución continuara después del error.

-- SELECT @x;
-- Resultado: 3

5. Procedimiento con EXIT HANDLER

DELIMITER $$

CREATE PROCEDURE handlerdemo3()
BEGIN
    -- Declarar un EXIT handler para cualquier error SQLSTATE '23000'
    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    BEGIN
        -- Opcional: registrar el error o realizar alguna acción
        SET @x2 = 1; -- Indicador de que el handler se activó
    END;

    SET @x = 1;
    INSERT INTO test.t VALUES(2); -- Éxito (si la tabla estaba vacía o 2 no existía)
    SET @x = 2;
    -- La siguiente inserción fallará porque s1=2 ya existe
    INSERT INTO test.t VALUES(2);
    SET @x = 3; -- Esta línea NO se ejecutará porque el EXIT handler detiene el bloque BEGIN
END;$$

DELIMITER ;

-- Llamada:
-- CALL handlerdemo3();

6. Comprobar el valor de la variable @x

Después de ejecutar handlerdemo3(), el valor de @x será 2, ya que el EXIT HANDLER detuvo la ejecución del bloque BEGIN al ocurrir el error.

-- SELECT @x;
-- Resultado: 2

Controlando el Error de Clave Duplicada con Mensaje al Usuario

Procedimiento auxiliar para insertar (puede fallar):

DELIMITER $$

-- Este procedimiento intenta insertar y usa un OUT parámetro para indicar si hubo error (aunque no es la forma más estándar)
-- Una forma más común sería que el procedimiento principal maneje el error directamente o use un handler.
-- Adaptamos el original para que el OUT parámetro 'done' indique si *no* se encontró el fin de datos (0) o sí (1),
-- lo cual no es adecuado para errores de inserción. Reinterpretamos 'done' como un indicador de éxito (0) o error (1).

CREATE PROCEDURE insertar_aux (v_DNI INT(10), v_apellidos VARCHAR(30), v_funcion VARCHAR(15), v_salario INT(10), OUT done INT)
BEGIN
    -- Inicializar done a 0 (éxito por defecto)
    SET done = 0;
    -- Declarar un handler para el error 1062 (clave duplicada)
    DECLARE CONTINUE HANDLER FOR 1062 SET done = 1; -- Si ocurre el error, establecer done a 1

    -- Intentar la inserción
    INSERT INTO personal (dni, apellidos, funcion, salario) VALUES (v_DNI, v_apellidos, v_funcion, v_salario);

    -- Si el handler se activó, done será 1. Si no, done seguirá siendo 0.
END;$$

DELIMITER ;

Procedimiento principal que llama al auxiliar y muestra el resultado:

DELIMITER $$ -- Asegurarse de que el delimitador esté configurado

CREATE PROCEDURE insertar_y_notificar (v_DNI INT(10), v_apellidos VARCHAR(30), v_funcion VARCHAR(15), v_salario INT(10))
BEGIN
    DECLARE estado_insercion INT; -- Variable para recibir el estado del procedimiento auxiliar

    -- Llamar al procedimiento auxiliar que intenta la inserción y maneja el error 1062
    CALL insertar_aux(v_DNI, v_apellidos, v_funcion, v_salario, estado_insercion);

    -- Comprobar el estado devuelto por el procedimiento auxiliar
    IF estado_insercion = 0 THEN
        SELECT CONCAT('Registro completado para DNI: ', v_DNI) AS mensaje;
    ELSE
        SELECT CONCAT('Error al insertar el DNI: ', v_DNI, '. Ya existe.') AS warning;
    END IF;
END;$$

DELIMITER ;

-- Ejemplos de llamada:
-- CALL insertar_y_notificar(123, 'Nuevo Apellido', 'Nueva Funcion', 3000);
-- CALL insertar_y_notificar(123, 'Otro Apellido', 'Otra Funcion', 4000); -- Intentar insertar el mismo DNI

Control de Errores de Integridad Referencial

Realizar un ejemplo de control de errores al producirse error de integridad referencial al insertar un registro en una tabla. Para manejar el error, crearemos un handler que almacenará el error en una tabla de log.

Creamos las tablas necesarias:

CREATE TABLE IF NOT EXISTS t2
(s1 INT, PRIMARY KEY (s1))
ENGINE=InnoDB;$$

CREATE TABLE IF NOT EXISTS t3
(
    s1 INT,
    PRIMARY KEY (s1),
    FOREIGN KEY (s1) REFERENCES t2(s1)
)
ENGINE=InnoDB;$$

-- Intentar insertar en t3 un valor que no existe en t2 (esto fallará con error 1452)
-- INSERT INTO t3 VALUES(5);$$

CREATE TABLE IF NOT EXISTS error_log (error_message VARCHAR(255));$$

Creamos el procedimiento con handler:

DELIMITER $$ -- Asegurarse de que el delimitador esté configurado

CREATE PROCEDURE procedimientoconhandler(parametro1 INT)
BEGIN
    -- Declarar un EXIT handler para el error 1452 (Cannot add or update a child row: a foreign key constraint fails)
    DECLARE EXIT HANDLER FOR 1452
    BEGIN
        INSERT INTO error_log (error_message) VALUES (CONCAT('Time: ', NOW(), '. Error de clave ajena para el valor = ', parametro1));
    END;

    -- Intentar la inserción en t3
    INSERT INTO t3 (s1) VALUES (parametro1);

    -- Si la inserción fue exitosa (el handler no se activó), puedes añadir un mensaje de éxito opcional aquí
    -- SELECT CONCAT('Inserción exitosa para el valor = ', parametro1) AS mensaje;

END;$$

DELIMITER ;

-- Ejemplos de llamada:
-- INSERT INTO t2 VALUES (10); -- Insertar un valor válido en la tabla padre
-- CALL procedimientoconhandler(10); -- Esto debería tener éxito
-- CALL procedimientoconhandler(99); -- Esto fallará la FK y activará el handler, registrando el error
-- SELECT * FROM error_log; -- Comprobar el log de errores

Ejercicios Adicionales con Procedimientos

Insertar Profesores en una Nueva Tabla

Crear un procedimiento que, a partir de la tabla personal, introduzca a todos los que sean 'PROFESOR' en una nueva tabla profesores, incrementando su salario en 200.

Nota: Asumimos que la tabla profesores ya existe con columnas compatibles (ej. dni, apellidos, salario).

-- Asumimos la tabla profesores existe:
-- CREATE TABLE profesores (dni INT PRIMARY KEY, apellidos VARCHAR(30), salario INT);

DELIMITER $$

CREATE PROCEDURE p_profesor()
BEGIN
    -- Insertar profesores desde la tabla personal
    INSERT INTO profesores (dni, apellidos, salario)
    SELECT dni, apellidos, salario + 200
    FROM personal
    WHERE funcion = 'PROFESOR';

    -- Mostrar el número de filas afectadas (insertadas)
    SELECT ROW_COUNT() AS filas_insertadas;
END;$$

DELIMITER ;

Clasificar Socios por Edad

Crear un procedimiento almacenado (llamado "datos_socios") que inserta un registro (en una tabla creada con anterioridad, ej. p_socios2) con un valor en función del parámetro recibido. Si el parámetro de entrada está entre 0 y 17, el valor del registro es: "menor de edad", y si el parámetro es >= a 18, el valor del registro es "mayor de edad".

Nota: Asumimos que la tabla p_socios2 existe y tiene una columna para almacenar el valor (ej. estado_edad VARCHAR(20)).

-- Asumimos la tabla p_socios2 existe:
-- CREATE TABLE p_socios2 (estado_edad VARCHAR(20));

DELIMITER $$

CREATE PROCEDURE datos_socios (IN vedad INT)
BEGIN
    DECLARE valor VARCHAR(20);
    IF vedad < 18 THEN
        SET valor = 'menor de edad';
    ELSE -- vedad >= 18
        SET valor = 'mayor de edad';
    END IF;
    INSERT INTO p_socios2 (estado_edad) VALUES (valor);
END;$$

DELIMITER ;

-- Ejemplo de llamada:
-- CALL datos_socios(15);
-- CALL datos_socios(25);
-- SELECT * FROM p_socios2;

Clasificar Personas por Edad en Tablas Separadas

Crear dos tablas: nino y adulto, para almacenar el nombre y la edad de una serie de personas. Realizar un procedimiento que permita capturar el nombre y la edad de una persona y almacenar sus datos en la tabla correspondiente a su edad (niño < 18, adulto >= 18).

CREATE TABLE IF NOT EXISTS nino
(
    nombre VARCHAR(50),
    edad INT
);$$

CREATE TABLE IF NOT EXISTS adulto
(
    nombre VARCHAR(50),
    edad INT
);$$

DELIMITER $$ -- Asegurarse de que el delimitador esté configurado

CREATE PROCEDURE p_edad (vnom VARCHAR(50), vedad INT)
BEGIN
    IF vedad < 18 THEN
        INSERT INTO nino (nombre, edad) VALUES (vnom, vedad);
    ELSE
        INSERT INTO adulto (nombre, edad) VALUES (vnom, vedad);
    END IF;
END;$$

DELIMITER ;

-- Ejemplos de llamada:
-- CALL p_edad('Ana', 10);
-- CALL p_edad('Juan', 30);
-- SELECT * FROM nino;
-- SELECT * FROM adulto;

Ejercicios de Procedimientos y Variables de Sesión

Revisar los siguientes puntos:

1. Mostrar el Día de la Semana Actual

Sobre la base de datos test, crea un procedimiento para mostrar el día de la semana actual (Función DAYNAME(CURDATE())).

Nota: La función DAYDATE() mencionada en el original no es una función estándar de MySQL. Usaremos DAYNAME().

DELIMITER $$

CREATE PROCEDURE mostrar_dia_semana()
BEGIN
    SELECT DAYNAME(CURDATE()) AS dia_actual;
END;$$

DELIMITER ;

-- Llamada:
-- CALL mostrar_dia_semana();

2. Crear y Mostrar una Variable de Usuario de Sesión

Crea y muestra una variable de usuario de sesión con SET.

SET @US = 'Ricardo';
SELECT @US;

3. Incrementar una Variable de Sesión

Crea un procedimiento que sume 1 a la variable anterior cada vez que se ejecute. (La variable debe ser de entrada-salida).

Nota: Las variables de sesión (@variable) son accesibles directamente dentro de procedimientos y no necesitan ser pasadas como parámetros INOUT a menos que se quiera encapsular la lógica de modificación de una variable *específica* pasada como parámetro. Un procedimiento puede simplemente modificar una variable de sesión existente.

DELIMITER $$

CREATE PROCEDURE incrementar_variable_sesion()
BEGIN
    -- Asegurarse de que la variable existe, inicializándola si es necesario
    SET @contador_ejecuciones = IFNULL(@contador_ejecuciones, 0) + 1;
    -- O simplemente:
    -- SET @contador_ejecuciones = @contador_ejecuciones + 1; -- Si ya se sabe que existe

    SELECT @contador_ejecuciones AS contador_actual;
END;$$

DELIMITER ;

-- Ejemplos de llamada:
-- SET @contador_ejecuciones = 0; -- Inicializar la variable de sesión
-- CALL incrementar_variable_sesion(); -- Primera llamada, muestra 1
-- CALL incrementar_variable_sesion(); -- Segunda llamada, muestra 2

4. Concatenar Cadenas en Minúsculas

Crea un procedimiento que muestre dos cadenas como parámetros concatenadas y en minúsculas. (Función LOWER(cadena) o LCASE(cadena)).

DELIMITER $$

CREATE PROCEDURE cadena_min (v_cad1 VARCHAR(255), v_cad2 VARCHAR(255))
BEGIN
    SELECT CONCAT(LOWER(v_cad1), LOWER(v_cad2)) AS cadena_concatenada_minusculas;
END;$$

DELIMITER ;

-- Ejemplo de llamada:
-- CALL cadena_min('HOLA ', 'MUNDO');

Disparadores (Triggers)

Los triggers son bloques de código que se ejecutan automáticamente en respuesta a eventos (INSERT, UPDATE, DELETE) en una tabla específica.

Auditoría de Modificaciones de Salario

Construir un disparador que permita auditar las operaciones de modificación de salario que se realicen en la tabla personal según las siguientes especificaciones:

En primer lugar, se creará la tabla auditar con la columna col1 VARCHAR(255).

Cuando se produzca cualquier manipulación (en este caso, UPDATE), se insertará una fila en dicha tabla que contendrá:

• Fecha y hora
• DNI de personal
• Apellido
• La operación de actualización ('Actualización de Salario')
• Salario sin actualizar (valor antiguo)
• Salario actualizado (valor nuevo)

-- Crear la tabla de auditoría si no existe
CREATE TABLE IF NOT EXISTS auditar (col1 VARCHAR(255));

-- Eliminar el trigger si ya existe para poder recrearlo
DROP TRIGGER IF EXISTS t_auditar_salario;

DELIMITER $$

CREATE TRIGGER t_auditar_salario BEFORE UPDATE ON personal FOR EACH ROW
BEGIN
    -- Insertar registro en la tabla de auditoría
    INSERT INTO auditar (col1) VALUES (
        CONCAT(
            NOW(),
            ', DNI: ', OLD.dni,
            ', Apellido: ', OLD.apellidos,
            ', Operación: Actualización de Salario',
            ', Salario Antiguo: ', OLD.salario,
            ', Salario Nuevo: ', NEW.salario
        )
    );
END;$$

DELIMITER ;

-- Ejemplo de uso (asumiendo que la tabla personal existe y tiene datos):
-- UPDATE personal SET salario = salario + 100 WHERE dni = 123;
-- SELECT * FROM auditar;

Auditoría de Borrado de Personal

Construir un disparador que permita auditar las operaciones de borrado de personal que se realicen en la tabla "personal" según las siguientes especificaciones:

En primer lugar, se creará la tabla personal_baja con la columna col1 VARCHAR(255).

Cuando se produzca el borrado, se insertará una fila en dicha tabla que contendrá:

• Fecha y hora
• DNI de personal
• Apellido
• Salario (en el momento del borrado)

-- Crear la tabla de personal_baja si no existe
CREATE TABLE IF NOT EXISTS personal_baja (col1 VARCHAR(255));

-- Eliminar el trigger si ya existe para poder recrearlo
DROP TRIGGER IF EXISTS t_auditar_borrado;

DELIMITER $$

CREATE TRIGGER t_auditar_borrado BEFORE DELETE ON personal FOR EACH ROW
BEGIN
    -- Insertar registro en la tabla personal_baja
    INSERT INTO personal_baja (col1) VALUES (
        CONCAT(
            NOW(),
            ', DNI: ', OLD.dni,
            ', Apellido: ', OLD.apellidos,
            ', Salario: ', OLD.salario
        )
    );
END;$$

DELIMITER ;

-- Ejemplo de uso (asumiendo que la tabla personal tiene datos):
-- DELETE FROM personal WHERE dni = 123;
-- SELECT * FROM personal_baja;

Trigger para Saldo Inicial No Negativo

Crea un trigger que ponga a 0 el saldo si intentamos dar de alta una nueva cuenta con saldo negativo.

-- Asumimos una tabla 'cuentas' con columna 'saldo'
-- CREATE TABLE cuentas (id_cuenta INT PRIMARY KEY, saldo DECIMAL(10, 2), ...);

DELIMITER $$

CREATE TRIGGER t_saldo_inicial BEFORE INSERT ON cuentas FOR EACH ROW
BEGIN
    IF NEW.saldo < 0 THEN
        SET NEW.saldo = 0;
    END IF;
END;$$

DELIMITER ;

Trigger para Actualizar Saldo de Cuenta tras Movimiento

Trigger que sirve para calcular campos que se pueden obtener del valor de otros campos. Actualizar el saldo de la cuenta cada vez que se hace un nuevo movimiento.

Nota: Este trigger debería ser AFTER INSERT para asegurar que el movimiento se registró correctamente antes de actualizar el saldo. También necesita identificar la cuenta afectada usando NEW.cod_cuenta.

-- Asumimos una tabla 'movimientos' con columnas 'cod_cuenta' y 'cantidad'
-- CREATE TABLE movimientos (id_movimiento INT PRIMARY KEY, cod_cuenta INT, cantidad DECIMAL(10, 2), ...);
-- Asumimos una tabla 'cuentas' con columnas 'cod_cuenta' y 'saldo'
-- CREATE TABLE cuentas (cod_cuenta INT PRIMARY KEY, saldo DECIMAL(10, 2), ...);

-- Eliminar el trigger si ya existe
DROP TRIGGER IF EXISTS t_actualizar_saldo;

DELIMITER $$

CREATE TRIGGER t_actualizar_saldo AFTER INSERT ON movimientos FOR EACH ROW
BEGIN
    -- Actualizar el saldo de la cuenta afectada sumando la cantidad del nuevo movimiento
    UPDATE cuentas
    SET saldo = saldo + NEW.cantidad
    WHERE cod_cuenta = NEW.cod_cuenta;
END;$$

DELIMITER ;

Registro Mensual de Ingresos

1. Creamos una tabla "idia" donde almacenamos el mes y cantidad.

2. Crear una función llamada existe que devuelve 1 si ya se ha hecho algún movimiento en el mes actual y 0 en caso contrario.

3. Creamos un trigger que registra los ingresos que se hacen cada mes, en tiempo real, en la tabla "idia".

1. Creación de la tabla idia:

CREATE TABLE IF NOT EXISTS idia
(
    mes VARCHAR(12) NOT NULL PRIMARY KEY, -- mes como clave primaria para asegurar unicidad
    cantidad DECIMAL(10,2) DEFAULT 0.00 -- Inicializar cantidad a 0
) ENGINE=InnoDB;$$

2. Creación de la función existe:

Nota: La lógica original de la función era incorrecta. Una forma más simple y robusta es intentar seleccionar el mes y usar un handler para NOT FOUND.

DELIMITER $$

CREATE FUNCTION existe (v_mes VARCHAR(12))
RETURNS INT(1)
BEGIN
    DECLARE mes_encontrado VARCHAR(12);
    DECLARE existe_flag INT DEFAULT 0;

    -- Handler para NOT FOUND (SQLSTATE '02000')
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET existe_flag = 0; -- Si no se encuentra, existe_flag sigue siendo 0

    -- Intentar seleccionar el mes. Si existe, existe_flag se mantiene en su valor inicial (0) o se podría cambiar aquí.
    -- Sin embargo, el handler es suficiente para detectar la NO existencia.
    SELECT mes INTO mes_encontrado FROM idia WHERE mes = v_mes LIMIT 1;

    -- Si la línea anterior se ejecutó sin activar el handler, significa que el mes existe.
    -- Si el handler se activó, mes_encontrado no se asignó y existe_flag es 0.
    -- Si llegamos aquí sin que el handler se active, el mes existe.
    SET existe_flag = 1; -- Si la SELECT tuvo éxito, el mes existe

    RETURN existe_flag;
END;$$

DELIMITER ;

Alternativa más simple para la función existe usando COUNT:

DELIMITER $$

CREATE FUNCTION existe_alt (v_mes VARCHAR(12))
RETURNS INT(1)
BEGIN
    DECLARE mes_count INT;
    SELECT COUNT(*) INTO mes_count FROM idia WHERE mes = v_mes;
    RETURN mes_count > 0; -- Devuelve 1 si count > 0, 0 en caso contrario
END;$$

DELIMITER ;

3. Creación del trigger t_idia:

Nota: El trigger debe ser AFTER INSERT. La lógica debe usar la función existe (o existe_alt) y actualizar o insertar en idia basándose en el mes del NEW.fecha del movimiento.

-- Eliminar el trigger si ya existe
DROP TRIGGER IF EXISTS t_idia;

DELIMITER $$

CREATE TRIGGER t_idia AFTER INSERT ON movimientos FOR EACH ROW
BEGIN
    DECLARE mes_movimiento VARCHAR(12);
    SET mes_movimiento = MONTHNAME(NEW.fecha);

    -- Usar la función existe para comprobar si el mes ya está en la tabla idia
    IF NOT existe_alt(mes_movimiento) THEN -- Usando la versión alternativa de la función
        -- Si el mes no existe, insertar un nuevo registro
        INSERT INTO idia (mes, cantidad) VALUES (mes_movimiento, NEW.cantidad);
    ELSE
        -- Si el mes ya existe, actualizar la cantidad sumando el nuevo movimiento
        UPDATE idia
        SET cantidad = cantidad + NEW.cantidad
        WHERE mes = mes_movimiento;
    END IF;
END;$$

DELIMITER ;

Auditoría Detallada de Movimientos

Queremos saber quién y a qué hora modificó la tabla movimientos en la base de datos ebanca. Para ello, crear un trigger que registre dichas actualizaciones, incluyendo los datos antiguos y los nuevos para cada registro modificado.

1. Creamos la tabla para recoger la información de auditoría:

CREATE TABLE IF NOT EXISTS auditoria_mov
(
    id_log INT AUTO_INCREMENT PRIMARY KEY,
    id_movimiento INT, -- ID del movimiento modificado
    cod_cuenta_ant INT, -- Usar tipo INT si es un código numérico
    fecha_ant DATETIME, -- Usar DATETIME o TIMESTAMP si la columna original lo es
    cantidad_ant DECIMAL(10, 2), -- Usar DECIMAL o DOUBLE si la columna original lo es
    cod_cuenta_n INT,
    fecha_n DATETIME,
    cantidad_n DECIMAL(10, 2),
    usuario VARCHAR(255), -- Aumentar tamaño para nombres de usuario completos
    fecha_modificacion DATETIME -- Usar DATETIME para fecha y hora de la modificación
) ENGINE=Innodb;$$

2. Creamos el trigger:

Nota: El trigger debe ser AFTER UPDATE para acceder a los valores OLD y NEW después de que la actualización se haya completado con éxito.

-- Eliminar el trigger si ya existe
DROP TRIGGER IF EXISTS t_auditar_mov_update;

DELIMITER $$

CREATE TRIGGER t_auditar_mov_update AFTER UPDATE ON movimientos FOR EACH ROW
BEGIN
    -- Insertar registro en la tabla de auditoría
    INSERT INTO auditoria_mov (
        id_movimiento, cod_cuenta_ant, fecha_ant, cantidad_ant,
        cod_cuenta_n, fecha_n, cantidad_n, usuario, fecha_modificacion
    )
    VALUES (
        OLD.id_movimientos, -- Asumiendo que la tabla movimientos tiene id_movimientos
        OLD.cod_cuenta, OLD.fechahora, OLD.cantidad, -- Usar fechahora si es timestamp/datetime
        NEW.cod_cuenta, NEW.fechahora, NEW.cantidad,
        CURRENT_USER(), NOW()
    );
END;$$

DELIMITER ;

-- Ejemplo de uso (asumiendo que la tabla movimientos existe y tiene datos):
-- UPDATE movimientos SET cantidad = cantidad * 1.1 WHERE id_movimientos = 1;
-- SELECT * FROM auditoria_mov;

Registro de Cuentas en Números Rojos

1. Crear un disparador que cree un registro en la tabla "nrojos" de la base de datos ebanca con los campos cliente, cuenta, fecha y saldo cada vez que algún cliente se quede en números rojos en alguna de sus cuentas.

Creación de la tabla nrojos:

Nota: La clave primaria original (solo cliente) no permite registrar múltiples cuentas o múltiples eventos para el mismo cliente. Usaremos un ID auto-incrementable como clave primaria. Los tipos de datos para saldo y códigos de cuenta deben coincidir con la tabla cuentas.

CREATE TABLE IF NOT EXISTS nrojos
(
    id_registro INT AUTO_INCREMENT PRIMARY KEY,
    codigo_cliente INT(11), -- Usar el nombre de columna correcto de la tabla cuentas
    cuenta INT(11), -- Asumiendo que 'cuenta' es una columna en la tabla cuentas
    fecha DATETIME, -- Registrar fecha y hora exacta
    saldo DECIMAL(10, 2) -- Usar tipo de dato adecuado para saldo
) ENGINE=InnoDB;$$

Creación del trigger num_rojos:

Nota: El trigger debe ser AFTER UPDATE para registrar el estado final del saldo después de la operación.

-- Eliminar el trigger si ya existe
DROP TRIGGER IF EXISTS t_registrar_numeros_rojos;

DELIMITER $$

CREATE TRIGGER t_registrar_numeros_rojos AFTER UPDATE ON cuentas FOR EACH ROW
BEGIN
    -- Comprobar si el nuevo saldo es negativo
    IF NEW.saldo < 0 THEN
        -- Insertar un registro en la tabla nrojos
        INSERT INTO nrojos (codigo_cliente, cuenta, fecha, saldo)
        VALUES (NEW.codigo_cliente, NEW.cuenta, NOW(), NEW.saldo);
    END IF;
END;$$

DELIMITER ;

-- Ejemplo de uso (asumiendo que la tabla cuentas existe y tiene datos):
-- UPDATE cuentas SET saldo = -50 WHERE codigo_cliente = 1 AND cuenta = 101;
-- SELECT * FROM nrojos;

Bonificación por Ingreso Grande y Antigüedad

2. Realizar un trigger para que cada vez que un cliente de ebanca ingrese más de 1000€ se le bonifique con 100€, solo para clientes con cuentas que superen tres años de antigüedad y entre el 1 de febrero de 2012 y el 31 de marzo de 2012.

Nota: El trigger debe ser BEFORE UPDATE para modificar el NEW.saldo antes de que se guarde. La condición de ingreso grande debe basarse en la diferencia entre NEW.saldo y OLD.saldo. La antigüedad se calcula a partir de fecha_creacion.

-- Asumimos una tabla 'cuentas' con columnas 'saldo', 'fecha_creacion', 'codigo_cliente', 'cuenta'
-- CREATE TABLE cuentas (codigo_cliente INT, cuenta INT, saldo DECIMAL(10, 2), fecha_creacion DATE, PRIMARY KEY (codigo_cliente, cuenta));

-- Eliminar el trigger si ya existe
DROP TRIGGER IF EXISTS t_bonificar_ingreso;

DELIMITER $$

CREATE TRIGGER t_bonificar_ingreso BEFORE UPDATE ON cuentas FOR EACH ROW
BEGIN
    -- Calcular el monto del ingreso (solo si el saldo aumenta)
    DECLARE monto_ingreso DECIMAL(10, 2);
    SET monto_ingreso = NEW.saldo - OLD.saldo;

    -- Comprobar las condiciones:
    -- 1. El saldo ha aumentado y el ingreso es >= 1000
    -- 2. La cuenta tiene más de 3 años de antigüedad (calculado desde la fecha actual)
    -- 3. La fecha actual está dentro del rango especificado (01/02/2012 a 31/03/2012)
    IF monto_ingreso >= 1000
       AND (YEAR(CURDATE()) - YEAR(OLD.fecha_creacion)) > 3
       AND CURDATE() BETWEEN '2012-02-01' AND '2012-03-31'
    THEN
        -- Aplicar la bonificación de 100€
        SET NEW.saldo = NEW.saldo + 100;
    END IF;
END;$$

DELIMITER ;

-- Ejemplo de uso (asumiendo que la tabla cuentas existe y tiene datos):
-- Asumir que la fecha actual es 2012-03-15 para probar el rango de fechas.
-- Asumir que hay una cuenta con fecha_creacion anterior a 2009-03-15.
-- UPDATE cuentas SET saldo = saldo + 1500 WHERE codigo_cliente = 1 AND cuenta = 101;
-- (Si las condiciones se cumplen, el saldo se incrementará en 1500 + 100)

Copias de Seguridad con mysqldump

mysqldump es una utilidad de línea de comandos para realizar copias de seguridad lógicas de bases de datos MySQL.

• Acceder a los parámetros del cliente mysqldump y haz una lectura rápida de ellos:

  mysqldump --help

• Realizar una copia de seguridad de una base de datos creada por ti (ej. 'ogame'):

  mysqldump -u root -p ogame --opt > copia_ogame.sql

  Nota: --opt es un atajo para varias opciones recomendadas (--add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, --set-charset).

• Realiza el volcado del fichero de seguridad de nuevo en el servidor:

  mysql -u root -p ogame < copia_ogame.sql

  Nota: Se usa el cliente mysql (no mysqldump) para restaurar, y la redirección de entrada <.

• Realiza una copia de seguridad de todas las bases de datos:

  mysqldump -u root -p --all-databases > copia_todas_bd.sql

  Nota: Corregido el nombre del archivo a .sql.

• Realizar una copia de seguridad de todas las bases de datos guardando las coordenadas del log binario:

  mysqldump -u root -p --all-databases --master-data=2 > copia_con_binlog.sql

  Nota: --master-data=2 añade la posición del log binario como un comentario SQL. --master-data=1 añade la posición como una sentencia CHANGE MASTER TO ejecutable. Es necesario tener el log binario activado en la configuración de MySQL (my.cnf o my.ini). Corregido el nombre del archivo a .sql.

• Realizar una copia de seguridad sin bloqueos, que no moleste a las lecturas y escrituras de las tablas. (consideramos que todas las tablas son InnoDB):

  mysqldump -u root -p --single-transaction --all-databases > copia_innodb_sin_bloqueo.sql

  Nota: --single-transaction es ideal para tablas InnoDB, ya que realiza la copia dentro de una transacción consistente sin necesidad de bloquear las tablas. Corregido el nombre de la opción y del archivo a .sql.