Ejemplos Prácticos de SQL Avanzado: Procedimientos, Triggers, Eventos y Manejo de Errores

Contar Empleados con Procedimiento Almacenado

Crear un procedimiento almacenado que cuente los empleados registrados en la tabla PERSONAL y muestre el total en pantalla.

CREATE PROCEDURE contarPersonal()
BEGIN
    DECLARE n INT DEFAULT 0;
    DECLARE done INT DEFAULT 0;
    DECLARE dni_empleado INT; -- Renombrado para claridad
    DECLARE cursor1 CURSOR FOR SELECT DNI FROM PERSONAL;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cursor1;

    read_loop: REPEAT
        FETCH cursor1 INTO dni_empleado;
        IF done = 0 THEN
            SET n = n + 1;
        END IF;
    UNTIL done END REPEAT read_loop;

    CLOSE cursor1;

    SELECT n AS 'Nº de empleados';
END;

Actualizar Fechas y Crear Vista de Cuentas

Modificar la fecha de apertura de las cuentas creadas en 2011 para que sea 1 de enero de 2014. Posteriormente, crear una vista que muestre las cuentas abiertas durante el año 2014.

-- Actualizar fechas de 2011 a 2014
UPDATE cuentas 
SET fecha_creacion = '2014-01-01'
WHERE fecha_creacion BETWEEN '2011-01-01' AND '2011-12-31';

-- Crear vista para cuentas de 2014
CREATE VIEW vista_cuentas_2014 AS
SELECT cod_cuenta, fecha_creacion -- Añadido cod_cuenta para más utilidad
FROM cuentas
WHERE fecha_creacion BETWEEN '2014-01-01' AND '2014-12-31';

Identificar Mes y Año con Mejor Saldo Total (Evento y Procedimiento)

Implementar una solución para determinar el mes y año con el mejor saldo total acumulado en la base de datos ebanca, utilizando un evento para registrar saldos mensuales y un procedimiento para consultar el máximo.

-- Evento para registrar saldo total mensual
CREATE EVENT registrar_saldo_mensual
ON SCHEDULE EVERY 1 MONTH
STARTS CURRENT_TIMESTAMP -- O una fecha de inicio específica
DO
BEGIN
    DECLARE nsaldo DECIMAL(15, 2); -- Ajustar precisión si es necesario
    DECLARE nmes INT;
    DECLARE nanio INT;

    SELECT SUM(saldo), MONTH(NOW()), YEAR(NOW())
    FROM cuentas
    INTO nsaldo, nmes, nanio;

    -- Asumiendo que existe la tabla saldo_mes(saldototal DECIMAL, mes INT, anio INT)
    INSERT INTO saldo_mes (saldototal, mes, anio) 
    VALUES (nsaldo, nmes, nanio);
END;

-- Procedimiento para obtener el mes/año con máximo saldo registrado
CREATE PROCEDURE obtener_mejor_saldo_mes()
BEGIN
    SELECT saldototal, mes, anio 
    FROM saldo_mes
    WHERE saldototal = (SELECT MAX(saldototal) FROM saldo_mes);
END;

Auditar Modificaciones en Tabla Movimientos con Trigger

Crear un trigger para auditar quién y cuándo modifica la tabla movimientos en la base de datos ebanca. El trigger debe registrar los datos antiguos y nuevos de cada fila actualizada en una tabla de auditoría (auditoria_mov).

CREATE TRIGGER auditar_movimientos_update
AFTER UPDATE ON movimientos
FOR EACH ROW
BEGIN
    -- Asumiendo tabla auditoria_mov con columnas adecuadas
    INSERT INTO auditoria_mov (
        cod_cuenta_ant, fecha_ant, cantidad_ant, 
        cod_cuenta_n, fecha_n, cantidad_n, 
        usuario, fecha_mod
    )
    VALUES (
        OLD.cod_cuenta, OLD.fecha, OLD.cantidad, 
        NEW.cod_cuenta, NEW.fecha, NEW.cantidad, 
        CURRENT_USER(), NOW()
    );
END;

Programar Bonificación con Evento

Crear un evento en la base de datos ebanca que se ejecute una vez, un mes a partir de ahora, y otorgue una bonificación de 100€ a las cuentas que fueron creadas hoy.

CREATE EVENT bonificacion_nuevas_cuentas
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MONTH
DO
    UPDATE ebanca.cuentas -- Asegúrate que la tabla es 'cuentas'
    SET saldo = saldo + 100 
    WHERE DATE(fecha_creacion) = DATE(CURRENT_DATE() - INTERVAL 1 MONTH); -- Bonifica cuentas creadas hace exactamente un mes respecto a la ejecución del evento.
    -- Si la intención era bonificar las creadas el día que se crea el evento:
    -- WHERE DATE(fecha_creacion) = DATE(CURRENT_DATE()); 

Registrar Ingresos Mensuales con Función y Trigger

Crear una tabla idia para almacenar la cantidad total de ingresos por mes. Desarrollar una función existe que verifique si ya existe un registro para un mes específico en idia. Implementar un trigger que, tras cada inserción en movimientos, actualice o inserte el total de ingresos del mes correspondiente en la tabla idia.

Tabla de Registro Mensual

CREATE TABLE idia (
    mes VARCHAR(20), -- Almacena el nombre del mes (e.g., 'January')
    cantidad DOUBLE, -- Almacena el total de ingresos del mes
    PRIMARY KEY (mes) -- Clave primaria para evitar duplicados
) ENGINE=InnoDB;

Función para Verificar Existencia del Mes

CREATE FUNCTION existe (pas_mes VARCHAR(20))
RETURNS INT
DETERMINISTIC -- Añadido ya que depende solo de la entrada y estado de la tabla
BEGIN
    DECLARE v_return INT DEFAULT 0;
    DECLARE v_mes VARCHAR(20) DEFAULT NULL;

    SELECT mes INTO v_mes 
    FROM idia 
    WHERE mes = pas_mes;

    IF v_mes IS NOT NULL THEN
        SET v_return = 1;
    END IF;
    
    RETURN v_return;
END;

Trigger para Actualizar/Insertar Ingresos Mensuales

CREATE TRIGGER registrar_ingreso_mensual
AFTER INSERT ON movimientos
FOR EACH ROW
BEGIN
    DECLARE nombre_mes VARCHAR(20);
    SET nombre_mes = MONTHNAME(NEW.fecha);

    IF existe(nombre_mes) = 0 THEN
        -- Si no existe registro para el mes, lo inserta
        INSERT INTO idia (mes, cantidad) 
        VALUES (nombre_mes, NEW.cantidad);
    ELSE
        -- Si ya existe, actualiza la cantidad sumando el nuevo ingreso
        UPDATE idia 
        SET cantidad = cantidad + NEW.cantidad 
        WHERE mes = nombre_mes;
    END IF;
END;

Auditoría Detallada de Actualizaciones en Movimientos (Trigger)

Implementar un trigger para registrar detalladamente quién y cuándo modifica la tabla movimientos en la base de datos ebanca. Se deben almacenar los valores antiguos y nuevos de cada columna modificada en la tabla auditoria_mov.

-- Nota: Este es un ejemplo similar al anterior, asegurando la correcta sintaxis.
CREATE TRIGGER auditar_movimientos_update_detalle -- Nombre ligeramente diferente si coexiste
AFTER UPDATE ON movimientos
FOR EACH ROW
BEGIN
    INSERT INTO auditoria_mov (
        cod_cuenta_ant, fecha_ant, cantidad_ant, 
        cod_cuenta_n, fecha_n, cantidad_n, 
        usuario, fecha_mod
    )
    VALUES (
        OLD.cod_cuenta, OLD.fecha, OLD.cantidad, 
        NEW.cod_cuenta, NEW.fecha, NEW.cantidad, 
        CURRENT_USER(), NOW()
    );
END;

Manejo de Errores de Integridad Referencial con Handler

Ejemplo de control de errores de integridad referencial (clave ajena, error 1452) durante una inserción en una tabla (t3), utilizando un HANDLER para registrar el error en una tabla error_log.

CREATE PROCEDURE procedimientoConHandler (parametro1 INT)
BEGIN
    -- Handler para el error específico de violación de clave ajena (1452)
    DECLARE EXIT HANDLER FOR 1452
    BEGIN
        -- Asumiendo que error_log tiene una columna 'mensaje' de tipo TEXT o VARCHAR
        INSERT INTO error_log (mensaje) 
        VALUES (CONCAT('Time: ', CURRENT_TIMESTAMP, '. Error de clave ajena (1452) al intentar insertar valor: ', parametro1));
    END;

    -- Intento de inserción que podría fallar por la clave ajena
    -- Asumiendo que t3 tiene una columna 'columna_fk' que es clave ajena
    INSERT INTO t3 (columna_fk) VALUES (parametro1);
    
    -- Si la inserción fue exitosa, se puede añadir lógica adicional aquí.
END;