Ejemplos Prácticos de Triggers en MySQL: Creación y Validación de Datos

Introducción a los Triggers en MySQL

A continuación, se presentan varios ejemplos prácticos para la creación y gestión de triggers (desencadenadores) en una base de datos MySQL, utilizando la base de datos bdempleados.

1. Trigger de Actualización (UPDATE): Límite de Subordinados

Este trigger valida antes de actualizar un registro que un jefe no tenga asignados más de tres empleados.

USE bdempleados;

DROP TRIGGER IF EXISTS no_puede_ser_jefe_de_mas_de_3_empleados_update;

DELIMITER $$

CREATE TRIGGER no_puede_ser_jefe_de_mas_de_3_empleados_update
BEFORE UPDATE ON empleados
FOR EACH ROW 
BEGIN 
    DECLARE subordinados INT;
    
    SELECT COUNT(*)
    INTO subordinados
    FROM empleados
    WHERE jefe = OLD.jefe;
        
    IF (subordinados > 3) THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'No se puede modificar un empleado ya que su jefe ya tiene más de 3 empleados asignados.';
    END IF;
END;
$$

DELIMITER ;

-- Consultas y pruebas de actualización
SELECT * FROM empleados;
UPDATE empleados SET salario = 12 WHERE emp_no = 7698;
UPDATE empleados SET salario = 12 WHERE emp_no = 7499;

2. Trigger de Eliminación (DELETE): Historial de Empleados

Este trigger almacena un registro histórico cada vez que se elimina un empleado de la tabla principal.

DROP TABLE IF EXISTS empleados_historico;

CREATE TABLE IF NOT EXISTS empleados_historico (
    id_empleado INT AUTO_INCREMENT,
    fecha_borrado DATETIME NOT NULL,
    usuario_borrado VARCHAR(65) NOT NULL,
    PRIMARY KEY (id_empleado)
);

USE bdempleados;

DROP TRIGGER IF EXISTS guardar_datos;

DELIMITER $$

CREATE TRIGGER guardar_datos 
AFTER DELETE ON empleados
FOR EACH ROW
BEGIN
    INSERT INTO empleados_historico (id_empleado, fecha_borrado, usuario_borrado) 
    VALUES (OLD.emp_no, NOW(), SYSTEM_USER());
END;
$$

DELIMITER ;

3. Trigger de Inserción (INSERT): Control de Stock en Pedidos

Este trigger verifica la disponibilidad de stock antes de permitir la inserción de un nuevo pedido, actualizando el inventario si la validación es correcta.

USE bdempleados;

DROP TRIGGER IF EXISTS stock;

DELIMITER $$

CREATE TRIGGER stock 
BEFORE INSERT ON pedidos
FOR EACH ROW
BEGIN
    DECLARE v_stock_disponible INT;
    
    SELECT stock_disponible 
    INTO v_stock_disponible 
    FROM productos 
    WHERE producto_no = NEW.producto_no;
    
    IF (v_stock_disponible < NEW.unidades) THEN 
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'No se puede realizar el pedido ya que el número de unidades supera el stock disponible.';
    ELSE 
        UPDATE productos 
        SET stock_disponible = stock_disponible - NEW.unidades 
        WHERE producto_no = NEW.producto_no; 
    END IF;
END;
$$

DELIMITER ;

-- Consultas y pruebas de inserción
SELECT * FROM pedidos;
SELECT * FROM productos;

INSERT INTO pedidos VALUES (2000, 10, 103, 3, NULL, NULL, 'CONTADO');
INSERT INTO pedidos VALUES (2030, 20, 103, 5, NULL, NULL, 'CONTADO');
INSERT INTO pedidos VALUES (2090, 80, 103, 1, NULL, NULL, 'CONTADO');

4. Trigger de Inserción (INSERT): Validación de Nuevos Empleados por Jefe

Similar al trigger de actualización, este valida que al insertar un nuevo empleado, el jefe asignado no supere el límite máximo permitido de tres subordinados.

USE bdempleados;

DROP TRIGGER IF EXISTS no_puede_ser_jefe_de_mas_de_3_empleados_insert;

DELIMITER $$

CREATE TRIGGER no_puede_ser_jefe_de_mas_de_3_empleados_insert
BEFORE INSERT ON empleados
FOR EACH ROW 
BEGIN 
    DECLARE subordinados INT;
    
    SELECT COUNT(*)
    INTO subordinados
    FROM empleados
    WHERE jefe = NEW.jefe;

    IF (subordinados > 3) THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'No se puede insertar un nuevo empleado ya que el jefe tiene más de 3 empleados asignados.';
    END IF;
END;
$$

DELIMITER ;