Creando un trigger sencillo para actualizar datos: AFTER INSERT

Imagina que tenemos una tabla donde guardamos datos sobre estudiantes. En esta tabla necesitamos actualizar automáticamente el campo last_modified (fecha del último cambio del registro) cada vez que se añade un nuevo estudiante. Este campo es importante para rastrear cambios y gestionar los datos.

El escenario es el siguiente:

1. Cuando se añade un nuevo registro a la tabla, el campo last_modified asigna automáticamente la fecha y hora actual.
2. Vamos a usar un trigger AFTER INSERT, que se ejecuta después de insertar los datos con éxito.

Creando la función para el trigger

Primero hay que crear una función en PL/pgSQL. Esta función va a actualizar el campo last_modified en nuestra tabla. La función es obligatoria para que funcione el trigger, porque el trigger solo indica qué hacer, pero toda la lógica la ejecuta la función.

Así que, empezamos creando la tabla:

-- Creamos la tabla students
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL,
    last_modified TIMESTAMP
);

Ahora creamos la función para actualizar el campo last_modified:

-- Función para actualizar last_modified
CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- Ponemos la hora actual en el campo last_modified
    NEW.last_modified := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Vamos a ver qué hace esta función:

• CREATE OR REPLACE FUNCTION update_last_modified() — creamos una función llamada update_last_modified. Si ya existe, se reemplaza.
• RETURNS TRIGGER — indicamos que la función es para usar con un trigger.
• NEW.last_modified := NOW(); — actualizamos el campo last_modified usando la función NOW(), que devuelve la fecha y hora actual.
• RETURN NEW; — devolvemos el registro actualizado. Esto es obligatorio para el trigger AFTER.

Creando el trigger

Después de crear la función, podemos crear el trigger y vincularlo a la tabla students. Así se hace:

-- Creamos el trigger después de insertar un registro
CREATE TRIGGER set_last_modified
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

Esto es lo que pasa aquí:

• CREATE TRIGGER set_last_modified — creamos un trigger llamado set_last_modified.
• AFTER INSERT — el trigger se ejecuta después de insertar una fila en la tabla.
• ON students — el trigger está vinculado a la tabla students.
• FOR EACH ROW — el trigger se ejecuta para cada nueva fila añadida a la tabla.
• EXECUTE FUNCTION update_last_modified(); — llama a la función que creamos antes.

Nota: el nombre del trigger (set_last_modified) y de la función (update_last_modified) puede ser el que quieras, pero es importante seguir estándares de nombres para que el código sea entendible.

Probando el trigger

Vamos a comprobar cómo funciona nuestro trigger. Primero añadimos algunos registros a la tabla students:

-- Insertamos datos en la tabla
INSERT INTO students (name, age) VALUES ('Ivan Ivanov', 20);
INSERT INTO students (name, age) VALUES ('Anna Petrova', 22);

Ahora miramos qué hay en la tabla:

-- Vemos los datos en la tabla
SELECT * FROM students;

El resultado esperado puede ser algo así:

Fíjate que el campo last_modified se rellenó automáticamente con la fecha y hora actual para cada registro.

Errores que pueden aparecer

1. Error: "relation does not exist" al crear el trigger. Este error sale si la tabla students no está creada. Asegúrate de crear la tabla antes de crear el trigger.
2. Error de permisos. Si el usuario de la base de datos no tiene permisos para crear funciones o triggers, el trigger no se creará. Revisa los privilegios del usuario.
3. Falta llamar a la función en el trigger. Si olvidas poner EXECUTE FUNCTION update_last_modified(), el trigger no podrá hacer lo que toca.

Mejorando el trigger: añadiendo condiciones

En tareas reales, a veces es útil limitar la ejecución del trigger con ciertas condiciones. Por ejemplo, si el campo age es menor de 18, no queremos actualizar last_modified. Esto se puede hacer con la condición WHEN:

-- Creamos un trigger con condición
CREATE TRIGGER set_last_modified
AFTER INSERT ON students
FOR EACH ROW
WHEN (NEW.age >= 18)
EXECUTE FUNCTION update_last_modified();

Ahora el campo last_modified solo se actualizará para estudiantes con edad >= 18.

Aplicación práctica

Triggers como este se usan mucho en proyectos reales. Aquí tienes algunos ejemplos:

• Actualizar automáticamente la fecha del último cambio de un registro (como hicimos).
• Rastrear cambios en la base de datos y guardar esos cambios en una tabla de logs.
• Asegurar la integridad de los datos, por ejemplo, comprobando tablas relacionadas antes de hacer una operación.
• Auditoría de datos para cumplir normas legales o corporativas.

Estas habilidades son especialmente útiles si trabajas con sistemas donde la precisión y la fiabilidad de los datos son importantes, como sistemas bancarios, gestión de inventarios o CRM.