Triggers Registrar cambios y auditoria

En esta píldora aprenderás los conceptos fundamentales de triggers y cómo aplicarlos en bases de datos.

📌 ¿Qué es un Trigger?

Un Trigger es un procedimiento almacenado en una base de datos que se ejecuta automáticamente cuando ocurre un evento en una tabla (INSERT, UPDATE, DELETE).

Tipos de Triggers

BEFORE → Se ejecuta antes de que ocurra la acción.
AFTER → Se ejecuta después de que ocurra la acción.

Aplicaciones Comunes

✔️ Registro de auditoría de cambios en los datos.
✔️ Validación automática antes de una operación.
✔️ Automatización de cálculos o actualizaciones.
✔️ Protección de datos para evitar errores.

Ejemplo Práctico: Registrar Cambios en Nombres de Pacientes

Vamos a crear un trigger en la base de datos gestion_hospitalaria que registrará automáticamente los cambios en los nombres de los pacientes en una tabla de historial.

🔹 1. Crear la tabla historial

CREATE TABLE historial_pacientes (
    id_historial INT AUTO_INCREMENT PRIMARY KEY,
    id_paciente INT,
    nombre_anterior VARCHAR(100),
    nombre_nuevo VARCHAR(100),
    fecha_cambio TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Importante: `TIMESTAMP DEFAULT CURRENT_TIMESTAMP`

Esta columna se usa para registrar automáticamente la fecha y hora de cada inserción en la tabla sin necesidad de introducirla manualmente.

📌 ¿Cómo funciona?

fecha_cambio TIMESTAMP DEFAULT CURRENT_TIMESTAMP

📍 TIMESTAMP: Tipo de dato que almacena fecha y hora (YYYY-MM-DD HH:MI:SS).
📍 DEFAULT CURRENT_TIMESTAMP: MySQL asigna la fecha y hora actual al insertar un nuevo registro.

✅ Ejemplo Práctico

Si insertamos un nuevo registro SIN especificar la fecha:

INSERT INTO historial_pacientes (id_paciente, nombre_anterior, nombre_nuevo)
VALUES (1, 'Juan Pérez', 'Juan Gómez');

✅ **Salida esperada:** La columna fecha_cambio se llena automáticamente:

SELECT * FROM historial_pacientes;

id_historial	id_paciente	nombre_anterior	nombre_nuevo	fecha_cambio
1	1	Juan Pérez	Juan Gómez	2025-02-20 14:35:20

📌 ¿Cómo hacer que la fecha se actualice automáticamente en cada `UPDATE`?

fecha_cambio TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

✅ Esto permite que la fecha se actualice automáticamente cada vez que se edite el registro.

📢 Conclusión

✅ TIMESTAMP DEFAULT CURRENT_TIMESTAMP permite automatizar el registro de fechas en bases de datos.
✅ Ideal para historiales de cambios y auditorías.
✅ Evita que los desarrolladores tengan que ingresar la fecha manualmente en cada inserción o actualización.

🔹 2. Crear el Trigger

DELIMITER //
CREATE TRIGGER before_update_paciente
BEFORE UPDATE ON pacientes
FOR EACH ROW
BEGIN
    INSERT INTO historial_pacientes (id_paciente, nombre_anterior, nombre_nuevo)
    VALUES (OLD.id_paciente, OLD.nombre, NEW.nombre);
END //
DELIMITER ;

📘 ¿Cómo Funciona ?

El Trigger before_update_paciente se activa antes de actualizar un registro en la tabla pacientes, permitiendo guardar un historial de los cambios en los nombres de los pacientes.

📌 Desglose del Código

DELIMITER //

🔹 Cambia el delimitador de MySQL de ; a // para que el motor no interprete erróneamente los ; dentro del trigger.

CREATE TRIGGER before_update_paciente

🔹 Crea un nuevo trigger llamado before_update_paciente.

BEFORE UPDATE ON pacientes

🔹 Se ejecuta antes de que ocurra un UPDATE en la tabla pacientes.

FOR EACH ROW

🔹 Se activa para cada fila que se actualice en la consulta.

⚠️ Nota Importante: `FOR EACH ROW`

📌 ¿Qué significa y cómo se aplica?

🔹 Traducción: «Para cada fila».
🔹 Significado: Indica que el trigger se ejecutará una vez por cada tupla afectada en la consulta (INSERT, UPDATE, DELETE).
🔹 No es un bucle, pero actúa como tal en el sentido de que se ejecuta de forma individual para cada tupla modificada.

📌 Aplicaciones y ejemplos

1️⃣ Auditoría de Cambios

Si actualizamos múltiples filas, el trigger se ejecutará una vez por cada fila afectada:

UPDATE pacientes SET ciudad = 'Barcelona' WHERE ciudad = 'Madrid';

✅ Si hay 5 pacientes en Madrid, el trigger se ejecutará 5 veces, registrando cada cambio.

2️⃣ Validaciones Previas a un `DELETE`

Evitar la eliminación de pacientes si tienen consultas registradas:

DELIMITER //
CREATE TRIGGER before_delete_paciente
BEFORE DELETE ON pacientes
FOR EACH ROW
BEGIN
    IF (SELECT COUNT(*) FROM consultas WHERE id_paciente = OLD.id_paciente) > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'No se puede eliminar un paciente con consultas registradas';
    END IF;
END //
DELIMITER ;

✅ Evita la eliminación de pacientes con consultas activas.

3️⃣ Automatización de Cálculos

Reducir automáticamente el stock de productos después de una venta:

DELIMITER //
CREATE TRIGGER after_insert_ventas
AFTER INSERT ON ventas
FOR EACH ROW
BEGIN
    UPDATE productos 
    SET stock = stock - NEW.cantidad 
    WHERE id_producto = NEW.id_producto;
END //
DELIMITER ;

✅ Actualiza el stock de productos automáticamente después de cada venta.

📌 Conclusión

🔹 FOR EACH ROW NO es un bucle explícito, pero actúa fila por fila en cada evento INSERT, UPDATE o DELETE.
🔹 Se usa para auditoría, validaciones y automatización de cálculos en la base de datos.

BEGIN

🔹 Inicio del bloque de código del trigger.

INSERT INTO historial_pacientes (id_paciente, nombre_anterior, nombre_nuevo)
VALUES (OLD.id_paciente, OLD.nombre, NEW.nombre);

📍 INSERT INTO historial_pacientes: Guarda los datos en la tabla historial_pacientes.
📍 OLD.id_paciente: Obtiene el ID del paciente antes de la actualización.
📍 OLD.nombre: Guarda el nombre del paciente antes del cambio.
📍 NEW.nombre: Guarda el nuevo nombre después del cambio.

END //

🔹 Indica el final del bloque del trigger.

DELIMITER ;

🔹 Restaura el delimitador predeterminado ; en MySQL.

✅ Ejemplo Práctico

Si un paciente tenía registrado su nombre como Juan Pérez y lo actualizamos a Juan Gómez:

UPDATE pacientes
SET nombre = 'Juan Gómez'
WHERE id_paciente = 1;

✅ Se almacenará automáticamente en historial_pacientes:

SELECT * FROM historial_pacientes;

id_historial	id_paciente	nombre_anterior	nombre_nuevo	fecha_cambio
1	1	Juan Pérez	Juan Gómez	2025-02-20 14:35:20

📢 Conclusión

✅ BEFORE UPDATE permite registrar cambios antes de la actualización.
✅ FOR EACH ROW garantiza que el trigger se ejecuta por cada paciente afectado.
✅ Se usa para auditoría y control de cambios en bases de datos.

📌 Ejercicios sobre Triggers en gestion_hospitalaria

Estos ejercicios te ayudarán a reforzar el uso de triggers en bases de datos con distintos escenarios prácticos.

1️⃣ Ejercicio 1: Registro de Cambios en la Edad de los Pacientes

📍 Objetivo: Registrar cada vez que la edad de un paciente se actualiza.

🔹 Pasos a seguir:

Crea una tabla historial_edades para almacenar los cambios de edad.
Crea un trigger BEFORE UPDATE en la tabla pacientes que registre la edad antes de la actualización.
Ejecuta un UPDATE para cambiar la edad de un paciente y verifica el historial.

2️⃣Ejercicio 2 : Evitar la Eliminación de Doctores con Consultas Activas

📍 Objetivo: No permitir que se elimine un doctor si todavía tiene consultas asignadas.

🔹 Pasos a seguir:

Crea un trigger BEFORE DELETE en la tabla doctores para evitar eliminaciones si el doctor tiene consultas registradas.
Intenta eliminar un doctor con consultas activas y verifica que MySQL impide la acción.

3️⃣ Ejercicio 3: Registro de Altas de Nuevos Pacientes

📍 Objetivo: Cada vez que se registre un nuevo paciente, guardar un historial con la fecha de ingreso.

🔹 Pasos a seguir:

Crea una tabla historial_altas para registrar las altas de pacientes.
Crea un trigger AFTER INSERT en la tabla pacientes para registrar cada nuevo ingreso.
Ejecuta un INSERT en pacientes y revisa el historial.

Currículum

Triggers en Bases de Datos

Triggers Registrar cambios y auditoria

📌 ¿Qué es un Trigger?

Tipos de Triggers

Aplicaciones Comunes

Ejemplo Práctico: Registrar Cambios en Nombres de Pacientes

🔹 1. Crear la tabla historial

Importante: TIMESTAMP DEFAULT CURRENT_TIMESTAMP

📌 ¿Cómo funciona?

✅ Ejemplo Práctico

📌 ¿Cómo hacer que la fecha se actualice automáticamente en cada UPDATE?

📢 Conclusión

🔹 2. Crear el Trigger

📘 ¿Cómo Funciona ?

📌 Desglose del Código

⚠️ Nota Importante: FOR EACH ROW

📌 ¿Qué significa y cómo se aplica?

📌 Aplicaciones y ejemplos

1️⃣ Auditoría de Cambios

2️⃣ Validaciones Previas a un DELETE

3️⃣ Automatización de Cálculos

📌 Conclusión

✅ Ejemplo Práctico

📢 Conclusión

📌 Ejercicios sobre Triggers en gestion_hospitalaria

1️⃣ Ejercicio 1: Registro de Cambios en la Edad de los Pacientes

2️⃣Ejercicio 2 : Evitar la Eliminación de Doctores con Consultas Activas

3️⃣ Ejercicio 3: Registro de Altas de Nuevos Pacientes

Modal title

Importante: `TIMESTAMP DEFAULT CURRENT_TIMESTAMP`

📌 ¿Cómo hacer que la fecha se actualice automáticamente en cada `UPDATE`?

⚠️ Nota Importante: `FOR EACH ROW`

2️⃣ Validaciones Previas a un `DELETE`