Creación y Uso de Triggers en MySQL utilizando la Base de Datos «Comisaria»

Los triggers en MySQL son una herramienta poderosa que permite la ejecución automática de acciones en respuesta a eventos específicos en la base de datos. En este artículo, exploraremos cómo crear y utilizar varios triggers utilizando la base de datos de una comisaría para asegurar la integridad y la automatización de tareas dentro de la base de datos.

Creación de la Base de Datos y Tablas

Para empezar, creamos la base de datos y las tablas necesarias:

CREATE DATABASE Comisaria;
USE Comisaria;

-- Tabla de agentes
CREATE TABLE Agentes (
    id_agente INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    rango VARCHAR(30),
    salario DECIMAL(10, 2) NOT NULL,
    fecha_ingreso DATE
);

-- Tabla de ciudadanos
CREATE TABLE Ciudadanos (
    id_ciudadano INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    direccion VARCHAR(100),
    telefono VARCHAR(15)
);

-- Tabla de casos
CREATE TABLE Casos (
    id_caso INT AUTO_INCREMENT PRIMARY KEY,
    descripcion TEXT,
    fecha_inicio DATE,
    estado VARCHAR(20),
    id_agente INT,
    FOREIGN KEY (id_agente) REFERENCES Agentes(id_agente)
);

-- Tabla de reportes
CREATE TABLE Reportes (
    id_reporte INT AUTO_INCREMENT PRIMARY KEY,
    id_caso INT,
    id_ciudadano INT,
    descripcion TEXT,
    fecha_reporte DATE,
    FOREIGN KEY (id_caso) REFERENCES Casos(id_caso),
    FOREIGN KEY (id_ciudadano) REFERENCES Ciudadanos(id_ciudadano)
);

-- Insertar datos en la tabla Agentes
INSERT INTO Agentes (nombre, rango, salario, fecha_ingreso) VALUES
('Juan Pérez', 'Sargento', 45000.00, '2015-06-23'),
('Ana Gómez', 'Teniente', 50000.00, '2018-09-15'),
('Carlos López', 'Cabo', 40000.00, '2020-01-10');

-- Insertar datos en la tabla Ciudadanos
INSERT INTO Ciudadanos (nombre, direccion, telefono) VALUES
('Pedro Martínez', 'Calle Falsa 123', '555-1234'),
('María Fernández', 'Avenida Siempre Viva 742', '555-5678'),
('Luis García', 'Boulevard Central 456', '555-8765');

-- Insertar datos en la tabla Casos
INSERT INTO Casos (descripcion, fecha_inicio, estado, id_agente) VALUES
('Robo en tienda', '2022-03-10', 'Abierto', 1),
('Asalto a mano armada', '2022-04-22', 'Cerrado', 2),
('Vandalismo en parque', '2022-05-15', 'En Proceso', 3);

-- Insertar datos en la tabla Reportes
INSERT INTO Reportes (id_caso, id_ciudadano, descripcion, fecha_reporte) VALUES
(1, 1, 'Robo de mercancía en tienda', '2022-03-10'),
(2, 2, 'Asalto a mano armada en banco', '2022-04-22'),
(3, 3, 'Daños en parque público', '2022-05-15');

Creación de Triggers en MySQL

1. Trigger para Mantener un Registro de Auditoría

Este trigger crea un registro en una tabla de auditoría cada vez que se actualiza la información de un agente.

CREATE TABLE AuditoriaAgentes (
    id_auditoria INT AUTO_INCREMENT PRIMARY KEY,
    id_agente INT,
    accion VARCHAR(50),
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    detalles TEXT
);

DELIMITER //

CREATE TRIGGER after_update_agente
AFTER UPDATE ON Agentes
FOR EACH ROW
BEGIN
    INSERT INTO AuditoriaAgentes (id_agente, accion, detalles)
    VALUES (NEW.id_agente, 'UPDATE', CONCAT('Nombre: ', OLD.nombre, ' -> ', NEW.nombre, ', Rango: ', OLD.rango, ' -> ', NEW.rango));
END //

DELIMITER ;

2. Trigger para Evitar la Eliminación de Agentes con Casos Asignados

Este trigger evita la eliminación de un agente si este está asignado a algún caso.

DELIMITER //

CREATE TRIGGER before_delete_agente
BEFORE DELETE ON Agentes
FOR EACH ROW
BEGIN
    DECLARE caso_count INT;
    SELECT COUNT(*) INTO caso_count FROM Casos WHERE id_agente = OLD.id_agente;

    IF caso_count > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No se puede eliminar el agente porque está asignado a casos.';
    END IF;
END //

DELIMITER ;

3. Trigger para Actualizar el Estado de un Caso al Agregar un Reporte

Este trigger actualiza el estado de un caso a «En Proceso» automáticamente cuando se agrega un nuevo reporte a un caso que está «Abierto».

DELIMITER //

CREATE TRIGGER after_insert_reporte
AFTER INSERT ON Reportes
FOR EACH ROW
BEGIN
    IF (SELECT estado FROM Casos WHERE id_caso = NEW.id_caso) = 'Abierto' THEN
        UPDATE Casos
        SET estado = 'En Proceso'
        WHERE id_caso = NEW.id_caso;
    END IF;
END //

DELIMITER ;

4. Trigger para Evitar la Inserción de Reportes en Casos Cerrados

Este trigger evita que se inserten nuevos reportes en casos que ya están cerrados.

DELIMITER //

CREATE TRIGGER before_insert_reporte
BEFORE INSERT ON Reportes
FOR EACH ROW
BEGIN
    IF (SELECT estado FROM Casos WHERE id_caso = NEW.id_caso) = 'Cerrado' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No se pueden agregar reportes a casos cerrados.';
    END IF;
END //

DELIMITER ;

Conclusión

Los triggers en MySQL permiten la automatización y el mantenimiento de la integridad de los datos dentro de una base de datos. En este artículo, hemos creado varios triggers utilizando la base de datos de una comisaría para registrar auditorías, evitar eliminaciones indebidas, actualizar automáticamente el estado de los casos y prevenir la inserción de reportes en casos cerrados. Con el uso adecuado de los triggers, se puede mejorar la seguridad y la consistencia de los datos en una base de datos MySQL.