Automatización de tareas con triggers

Imagínate que eres el admin de una base de datos enorme. De repente, alguien borra información importante de una tabla y todo el mundo grita: "¿Quién lo hizo?!". Para evitar estos marrones, las bases de datos permiten registrar los cambios y ver qué ha pasado con los datos. Todo esto se hace con logging y auditoría.

• Registro de cambios te permite guardar el historial de lo que ha pasado: qué registro cambió, cómo y cuándo.
• Auditoría de datos se usa para un control más profundo, incluyendo no solo los cambios sino también info sobre el usuario que los inició.

Ahora que tienes claro el "por qué", vamos a aprender el "cómo".

Creando una tabla para logs

Antes de empezar a configurar triggers, necesitamos una tabla para guardar los logs de los cambios. Aquí tienes un ejemplo:

-- Creamos una tabla para registrar los cambios
CREATE TABLE change_logs (
    log_id SERIAL PRIMARY KEY,       -- Identificador único del registro
    table_name TEXT NOT NULL,        -- Nombre de la tabla donde hubo cambios
    operation TEXT NOT NULL,         -- Tipo de operación: INSERT, UPDATE, DELETE
    change_time TIMESTAMP DEFAULT NOW(), -- Momento del cambio
    old_data JSONB,                  -- Datos antes del cambio (para UPDATE/DELETE)
    new_data JSONB                   -- Datos después del cambio (para INSERT/UPDATE)
);

¿Qué está pasando aquí?

1. log_id — identificador único para cada registro en el log.
2. table_name — vamos a guardar qué tabla fue modificada.
3. operation — tipo de operación: INSERT, UPDATE o DELETE.
4. change_time — guarda el momento exacto del cambio.
5. old_data y new_data — datos antes y después del cambio en formato JSON.

Registrando cambios con un trigger

Ahora que tenemos la tabla de logs, vamos a crear un trigger para una de las tablas, por ejemplo, students. Va a registrar todos los cambios: añadir nuevos estudiantes, actualizarlos o borrarlos. Esto es lo que vamos a hacer:

1. Escribimos una función en PL/pgSQL que añade registros a la tabla de logs.
2. Creamos un trigger en la tabla students.

La función recibirá info sobre la operación (INSERT, UPDATE, DELETE), y también los datos que cambiaron (OLD y NEW).

-- Función para registrar cambios en la tabla de logs
CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Registro de operación INSERT
    IF TG_OP = 'INSERT' THEN
        INSERT INTO change_logs (table_name, operation, new_data)
        VALUES ('students', 'INSERT', row_to_json(NEW));

    -- Registro de operación DELETE
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO change_logs (table_name, operation, old_data)
        VALUES ('students', 'DELETE', row_to_json(OLD));

    -- Registro de operación UPDATE
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO change_logs (table_name, operation, old_data, new_data)
        VALUES ('students', 'UPDATE', row_to_json(OLD), row_to_json(NEW));
    END IF;

    RETURN NULL; -- Devolvemos NULL porque es un trigger AFTER
END;
$$ LANGUAGE plpgsql;

Aquí:

• TG_OP — variable especial que contiene la operación actual: INSERT, UPDATE, DELETE.
• row_to_json(OLD) y row_to_json(NEW) — convierten los datos de la fila a formato JSON para guardarlos fácilmente.
• RETURN NULL — como es un trigger AFTER, no debe devolver datos modificados.

Ahora conectamos nuestra función con la tabla students.

-- Creamos el trigger para registrar cambios
CREATE TRIGGER students_log_trigger
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_changes();

¿Qué pasa aquí?

• AFTER INSERT OR UPDATE OR DELETE — el trigger se dispara después de ejecutar las operaciones indicadas en la tabla students.
• FOR EACH ROW — el trigger se ejecuta para cada fila modificada.
• EXECUTE FUNCTION log_student_changes() — llama a nuestra función para registrar el cambio.

Probando el trigger

Es hora de comprobar cómo funciona nuestro trigger.

1. Insertar un nuevo registro

INSERT INTO students (name, age, grade)
VALUES ('Otto Lin', 20, 'A');

Vamos a ver qué se ha guardado en la tabla de logs:

SELECT * FROM change_logs;

Ejemplo de resultado:

2. Actualizar un registro

UPDATE students
SET grade = 'B'
WHERE name = 'Otto Lin';

Otra vez, revisamos la tabla de logs:

SELECT * FROM change_logs ORDER BY change_time DESC;

Resultado:

3. Borrar un registro

DELETE FROM students
WHERE name = 'Otto Lin';

Y otra vez, revisamos el log:

Ejemplos de uso real

1. Registro de operaciones en tablas críticas: por ejemplo, una tabla con cuentas bancarias necesita guardar todos los cambios para evitar fraudes.
2. Auditoría del sistema: puedes guardar registros para cumplir normativas o analizar la actividad de los usuarios.
3. Recuperación de datos: si alguien borra datos por accidente, puedes restaurarlos desde la tabla de logs.

Detalles y trampas

Cuando implementas logging con triggers, es importante tener en cuenta el rendimiento. Si el trigger se dispara muy a menudo, puede aumentar la carga en la base de datos. Así que:

• Usa logging solo en tablas realmente importantes.
• Si el volumen de logs crece demasiado, piensa en estrategias de archivado.

Los triggers son como las cuerdas de una guitarra bien tensadas: necesitan ajuste fino, pero suenan genial, automatizando tareas aburridas y dándote control sobre los datos.