Trigger para actualizar automáticamente el campo last_modified al modificar un registro

Imagina que estás desarrollando una app para gestionar estudiantes y cursos, y tienes una tabla students. En esa tabla hay un campo last_modified que debería actualizarse automáticamente cada vez que se cambian los datos de un registro (por ejemplo, cuando cambias el nombre del estudiante o su edad).

En vez de escribir manualmente la actualización de last_modified en cada consulta SQL, vamos a crear un trigger que lo haga por nosotros.

Estructura de la tabla students

Para empezar, vamos a crear la tabla students que usaremos en el ejemplo. Esta tabla tiene info básica sobre los estudiantes:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY, -- Identificador único del estudiante
    name VARCHAR(100) NOT NULL,   -- Nombre del estudiante
    age INT,                      -- Edad del estudiante
    last_modified TIMESTAMP NOT NULL DEFAULT NOW() -- Momento de la última modificación
);

• El campo last_modified se rellena inicialmente con la hora actual (NOW()) al crear el registro.
• Este campo se actualizará automáticamente cuando cambien los datos del estudiante.

Vamos a meter algunos datos de prueba en la tabla:

INSERT INTO students (name, age)
VALUES 
    ('Otto Lin', 20),
    ('Maria Chi', 22),
    ('Alex Song', 19);

Ahora los datos en la tabla se ven así:

Creando la función para actualizar last_modified

La función en PL/pgSQL la va a usar el trigger para actualizar el valor del campo last_modified. Se va a ejecutar automáticamente antes de hacer el cambio en el registro.

Vamos a crear la función update_last_modified:

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- Actualizamos el campo last_modified con la hora actual
    NEW.last_modified := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

• NEW — es una variable especial que contiene los nuevos datos del registro (después del cambio).
• Le ponemos a NEW.last_modified el valor de NOW() (fecha y hora actual).
• La función devuelve la variable NEW actualizada, que es necesario para que el trigger funcione bien.

Creando el trigger

Ahora vamos a crear el trigger que va a llamar automáticamente a la función update_last_modified cada vez que se actualice un registro en la tabla students.

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

¿Qué pasa aquí?

• BEFORE UPDATE indica que el trigger se ejecuta antes de hacer la operación de actualización.
• FOR EACH ROW significa que el trigger se ejecuta para cada fila que se cambia.
• EXECUTE FUNCTION update_last_modified() indica que hay que llamar a la función update_last_modified.

Probando el trigger

Ahora vamos a ver cómo funciona nuestro trigger. Vamos a seleccionar los datos actuales de la tabla students:

SELECT * FROM students;

Resultado:

Ahora vamos a actualizar la edad del estudiante con student_id = 1:

UPDATE students
SET age = 21
WHERE student_id = 1;

Volvemos a seleccionar los datos de la tabla:

SELECT * FROM students;

Resultado esperado:

Fíjate: el campo last_modified para el registro con student_id = 1 se ha actualizado a la hora actual, mientras que los otros registros no han cambiado.

Ampliando la lógica del trigger

Supón que ahora quieres que el campo last_modified se actualice solo si cambian ciertas columnas. Por ejemplo, si solo cambia el nombre del estudiante o su edad, el trigger debe ejecutarse, pero si cambian otras cosas, no.

Para eso puedes añadir una condición con el operador WHEN en la definición del trigger.

Vamos a crear un nuevo trigger con condición:

DROP TRIGGER IF EXISTS set_last_modified ON students;

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
WHEN (OLD.name IS DISTINCT FROM NEW.name OR OLD.age IS DISTINCT FROM NEW.age)
EXECUTE FUNCTION update_last_modified();

Aquí:

• La condición WHEN comprueba si los valores antiguos (OLD) son distintos de los nuevos (NEW) para las columnas name y age.
• Si ninguna de esas columnas ha cambiado, el trigger no se ejecuta.

Vamos a volver a actualizar datos en la tabla y probar la nueva lógica.

Recomendaciones para usar triggers

1. No abuses de los triggers. Son útiles, pero pueden complicar la lógica de la base de datos y hacer la depuración más difícil.
2. Documenta siempre lo que hace el trigger y para qué casos se usa.
3. Usa condiciones WHEN para minimizar llamadas innecesarias al trigger.
4. Recuerda que los triggers pueden afectar al rendimiento de la base de datos, sobre todo si la tabla tiene muchos registros.

Errores típicos al trabajar con triggers

Cambios incorrectos en los datos. Por ejemplo, te olvidas de poner el valor para NEW y devuelves los datos originales sin cambios.

Condiciones incorrectas. Por ejemplo, te olvidas de añadir la condición WHEN y el trigger se ejecuta incluso cuando no hace falta cambiar nada.

Recursión. Si el trigger llama a una función que vuelve a llamar al trigger, puedes crear un bucle infinito sin querer. PostgreSQL tiene protección contra recursión, pero mejor evitar estas situaciones.

Este ejemplo muestra cómo usar triggers puede simplificar tareas de actualización automática de datos. En proyectos reales, esta técnica se usa mucho para llevar un registro de cambios, mantener la integridad de los datos y automatizar tareas rutinarias.