Fundamentos de la sintaxis de triggers: CREATE TRIGGER, WHEN, EXECUTE FUNCTION

Para crear un trigger en PostgreSQL, tienes que definir los siguientes componentes:

• Nombre del trigger.
• Tipo de evento (INSERT, UPDATE, DELETE).
• Momento de ejecución (BEFORE o AFTER).
• Tabla a la que está asociado.
• Función que se va a ejecutar (en PL/pgSQL u otro lenguaje).

Aquí tienes la estructura general del comando:

CREATE TRIGGER nombre_del_trigger
[BEFORE | AFTER] {INSERT | UPDATE | DELETE}
ON nombre_de_tabla
[FOR EACH ROW | FOR EACH STATEMENT]
WHEN (condición)
EXECUTE FUNCTION nombre_de_funcion();

Ejemplo de un trigger sencillo

Vamos a crear una tabla básica students y añadir un trigger que se activa después de añadir un nuevo registro.

Primero creamos la tabla students

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

¿Qué está pasando aquí? Creamos una tabla con los campos id, name y last_modified. El campo last_modified va a guardar la fecha y hora del último cambio en el registro.

Los triggers siempre están ligados a funciones. Primero vamos a crear una función sencilla que actualiza el campo last_modified cada vez que se añade un registro:

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- Ponemos la fecha y hora actual en el campo last_modified
    NEW.last_modified := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

¿Qué es esta magia?

1. NEW — es una variable especial que guarda los nuevos valores de la fila (para eventos INSERT o UPDATE).
2. CURRENT_TIMESTAMP — función que devuelve la fecha y hora actual.
3. RETURN NEW — devuelve la fila modificada para que se guarde después.

Ahora creamos el trigger en sí:

CREATE TRIGGER set_last_modified
AFTER INSERT
ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

Desglose:

• AFTER INSERT: el trigger se activa después de añadir una nueva fila.
• ON students: el trigger se aplica a la tabla students.
• FOR EACH ROW: el trigger se ejecuta para cada nueva fila.
• EXECUTE FUNCTION: indica qué función se debe llamar.

Comprobando el trigger

Vamos a ver cómo funciona nuestro trigger:

INSERT INTO students (name) VALUES ('Alice');
SELECT * FROM students;

Vas a ver un resultado parecido a este:

El trigger actualizó automáticamente el campo last_modified. ¿Magia? No, simplemente PostgreSQL.

Usando condiciones con WHEN

A veces necesitas que el trigger no se ejecute siempre, sino solo bajo ciertas condiciones. Para eso se usa la palabra clave WHEN.

Vamos a ver un ejemplo donde el trigger solo se ejecuta para ciertos valores.

Por ejemplo, queremos que el trigger solo se active para estudiantes con el nombre "Alice". Cambiamos nuestro trigger:

CREATE OR REPLACE FUNCTION update_last_modified_condition()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_modified := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_last_modified_condition
AFTER INSERT
ON students
FOR EACH ROW
WHEN (NEW.name = 'Alice')
EXECUTE FUNCTION update_last_modified_condition();

Ahora el trigger solo actualizará el campo last_modified para estudiantes con el nombre "Alice".

Probamos:

INSERT INTO students (name) VALUES ('Alice');
INSERT INTO students (name) VALUES ('Bob');
SELECT * FROM students;

Resultado:

Ojo: para el estudiante "Bob" el campo last_modified quedó vacío porque el trigger no se ejecutó.

Relación del trigger con la función: EXECUTE FUNCTION

La función es el corazón de cualquier trigger. Un trigger no puede existir sin una función que defina su lógica. En PostgreSQL puedes escribir funciones en PL/pgSQL o en otros lenguajes soportados, como Python o C.

Vamos a ver un ejemplo usando una función en PL/pgSQL.

Vamos a crear una función que registre los cambios en una tabla aparte llamada audit_log.

Primero creamos la tabla audit_log

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    operation TEXT NOT NULL,
    student_id INTEGER NOT NULL,
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Y ahora – la función:

CREATE OR REPLACE FUNCTION log_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (operation, student_id)
    VALUES ('INSERT', NEW.id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Ahora escribimos el trigger:

CREATE TRIGGER log_student_insert
AFTER INSERT
ON students
FOR EACH ROW
EXECUTE FUNCTION log_insert();

Y comprobamos cómo funciona:

INSERT INTO students (name) VALUES ('Charlie');
SELECT * FROM audit_log;

Vas a ver un resultado parecido a este:

El trigger registró automáticamente el log del nuevo registro.

Errores y particularidades al trabajar con triggers

Error: falta la función. Si intentas crear un trigger sin una función, PostgreSQL te va a dar un error. Siempre crea la función antes de crear el trigger.

Problemas de rendimiento. Tener muchos triggers o funciones muy complejas puede hacer que la base de datos vaya más lenta. Úsalos con cabeza.

Recursión. Si un trigger modifica la misma tabla donde se activa, puede causar un bucle infinito. Usa condiciones WHEN para evitarlo.