En este módulo, aplicaremos lo aprendido en procedimientos almacenados, funciones y triggers mediante casos prácticos. Además, desarrollaremos un proyecto final para consolidar conocimientos.

1. Caso Práctico: Automatización de Procesos con Procedimientos Almacenados

📌 Escenario:
Una empresa quiere automatizar el cálculo de salarios considerando bonificaciones del 10% para empleados con más de 5 años de antigüedad.

Solución con Procedimiento Almacenado

1DELIMITER //
2
3CREATE PROCEDURE CalcularSalario(IN emp_id INT, OUT salario_final DECIMAL(10,2))
4BEGIN
5    DECLARE salario_base DECIMAL(10,2);
6    DECLARE antiguedad INT;
7
8    -- Obtener salario base y antigüedad del empleado
9    SELECT sueldo, TIMESTAMPDIFF(YEAR, fecha_contratacion, NOW())
10    INTO salario_base, antiguedad
11    FROM empleados WHERE id = emp_id;
12
13    -- Aplicar bonificación si corresponde
14    IF antiguedad > 5 THEN
15        SET salario_final = salario_base * 1.10;
16    ELSE
17        SET salario_final = salario_base;
18    END IF;
19END //
20
21DELIMITER ;
22
23-- Uso:
24CALL CalcularSalario(1, @salario);
25SELECT @salario;

2. Caso Práctico: Seguridad y Control con Triggers

📌 Escenario:
Queremos evitar que un empleado con rol de "Admin" pueda ser eliminado accidentalmente.

Solución con Trigger

1CREATE TRIGGER PrevenirEliminacionAdmin
2BEFORE DELETE ON empleados
3FOR EACH ROW
4BEGIN
5    IF OLD.rol = 'Admin' THEN
6        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No puedes eliminar a un administrador';
7    END IF;
8END;

3. Proyecto Final: Implementación de una Lógica de Negocio Completa

Desarrollaremos un sistema para gestionar ventas y stock en una tienda.
El sistema deberá: ✅ Registrar una nueva venta.
✅ Verificar si hay stock disponible antes de realizar la venta.
✅ Descontar el stock del producto vendido.
✅ Guardar un registro de auditoría con cada venta realizada.

📌 Diseño de Tablas

1CREATE TABLE productos (
2    id INT PRIMARY KEY AUTO_INCREMENT,
3    nombre VARCHAR(50),
4    stock INT NOT NULL,
5    precio DECIMAL(10,2) NOT NULL
6);
7
8CREATE TABLE ventas (
9    id INT PRIMARY KEY AUTO_INCREMENT,
10    producto_id INT,
11    cantidad INT NOT NULL,
12    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
13    FOREIGN KEY (producto_id) REFERENCES productos(id)
14);
15
16CREATE TABLE auditoria_ventas (
17    id INT PRIMARY KEY AUTO_INCREMENT,
18    mensaje TEXT,
19    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP
20);

📌 Procedimiento para Registrar Ventas

1DELIMITER //
2
3CREATE PROCEDURE RegistrarVenta(IN prod_id INT, IN cantidad INT)
4BEGIN
5    DECLARE stock_actual INT;
6
7    -- Obtener stock actual
8    SELECT stock INTO stock_actual FROM productos WHERE id = prod_id;
9
10    -- Verificar si hay stock suficiente
11    IF stock_actual >= cantidad THEN
12        -- Registrar la venta
13        INSERT INTO ventas (producto_id, cantidad) VALUES (prod_id, cantidad);
14        -- Actualizar stock
15        UPDATE productos SET stock = stock - cantidad WHERE id = prod_id;
16    ELSE
17        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: Stock insuficiente';
18    END IF;
19END //
20
21DELIMITER ;
22
23-- Uso:
24CALL RegistrarVenta(1, 3);

📌 Trigger para Registrar Auditoría

1CREATE TRIGGER RegistrarAuditoriaVenta
2AFTER INSERT ON ventas
3FOR EACH ROW
4BEGIN
5    INSERT INTO auditoria_ventas (mensaje) VALUES (CONCAT('Venta realizada de ', NEW.cantidad, ' unidades del producto ', NEW.producto_id));
6END;

4. Optimización y Buenas Prácticas

✅ Usar Índices

1CREATE INDEX idx_producto_id ON ventas(producto_id);

✅ Evitar Cursores cuando sea posible

Usar JOIN en lugar de cursores para consultas eficientes.

✅ Validaciones en la Aplicación y en la Base de Datos

Es recomendable validar datos tanto en la aplicación como en la base de datos.

Modelado físico avanzado relacional de bases de datos