Un trigger hace que PostgreSQL llame a una función automáticamente cuando cambia una tabla. Escribe primero la función de trigger, declarada RETURNS trigger, y después asóciala: CREATE TRIGGER name BEFORE UPDATE ON table FOR EACH ROW EXECUTE FUNCTION fn();. El uso clásico es mantener al día una columna updated_at:
Solo se actualizó el post 1, así que solo él tiene marca de tiempo. NEW es la fila que está a punto de escribirse; un trigger BEFORE puede cambiarla y tiene que devolverla. Puedes asociar la misma función a todas las tablas que tengan una columna updated_at.
BEFORE, AFTER e INSTEAD OF
| Momento | Se ejecuta | Uso típico |
|---|---|---|
BEFORE | antes de escribir la fila | cambiar o validar NEW, u omitir la fila |
AFTER | después de escribir la fila | escribir en otras tablas: registros de auditoría, contadores |
INSTEAD OF | en lugar del cambio, solo en vistas | hacer que una vista admita escrituras |
FOR EACH ROW ejecuta la función una vez por cada fila afectada. FOR EACH STATEMENT (el valor por defecto) la ejecuta una vez por sentencia, aunque no cambie ninguna fila. En un trigger de fila, NEW contiene la fila nueva (INSERT, UPDATE) y OLD la anterior (UPDATE, DELETE). TG_OP indica la operación que lo disparó y TG_TABLE_NAME la tabla.
El valor que devuelve un trigger BEFORE de fila decide qué pasa: devuelve NEW (modificado o no) para continuar, o NULL para omitir la fila sin error. El valor que devuelve un trigger AFTER se ignora, así que por convención devuelve NULL.
Escribir un registro de auditoría con un trigger AFTER
La tabla de auditoría registra dos INSERT, un UPDATE de 10 a 12 y un DELETE del producto que costaba 20. OLD es NULL en un insert y NEW es NULL en un delete, de ahí el coalesce. Para guardar filas completas, pon to_jsonb(OLD) y to_jsonb(NEW) en una columna jsonb.
Bloquear u omitir un cambio
Un trigger BEFORE puede rechazar un cambio con RAISE EXCEPTION, que aborta la sentencia, u omitir una fila devolviendo NULL:
El DELETE borró el borrador y conservó sin avisar la factura pagada. Un trigger BEFORE DELETE devuelve OLD para que el borrado siga adelante. Para que falle toda la sentencia, cambia RETURN NULL por RAISE EXCEPTION 'invoice % is paid', OLD.id;. Un error es más fácil de depurar que una fila que se queda sin hacer ruido.
Dispararse solo cuando cambia una columna
UPDATE OF column limita un trigger a los updates que asignan esa columna. Una condición WHEN evita la llamada a la función cuando es falsa. IS DISTINCT FROM trata NULL como un valor, así que también detecta cambios hacia o desde NULL:
El primer update pone el mismo precio, así que el WHEN es falso y el contador se queda en 0. El segundo lo cambia, y price_changed termina en 1.
Listar, deshabilitar y eliminar triggers
-- triggers you created (excludes the ones behind foreign keys)
SELECT tgrelid::regclass AS table_name, tgname
FROM pg_trigger WHERE NOT tgisinternal;
ALTER TABLE products DISABLE TRIGGER products_price;
ALTER TABLE products ENABLE TRIGGER products_price;
DROP TRIGGER products_price ON products;
En psql, \d products lista los triggers de una tabla. CREATE OR REPLACE TRIGGER existe desde PostgreSQL 14, y EXECUTE FUNCTION desde PostgreSQL 11; el código antiguo escribe EXECUTE PROCEDURE, que sigue funcionando. Un trigger que escribe en su propia tabla puede volver a dispararse, así que protégete de los bucles (pg_trigger_depth() devuelve el nivel de anidamiento). El capítulo de funciones de trigger lista todas las variables TG_, y funciones y procedimientos explica PL/pgSQL.
Preguntas frecuentes
¿Un trigger de PostgreSQL puede ejecutarse sin una función?
No. CREATE TRIGGER siempre llama a una función declarada RETURNS trigger. Escribe primero la función, normalmente en PL/pgSQL, y después asóciala.
¿En qué orden se disparan los triggers de una misma tabla?
Los triggers del mismo tipo sobre el mismo evento se disparan en orden alfabético de sus nombres. Pon números o letras delante de los nombres si el orden importa.
¿Un error en un trigger deshace la sentencia?
Sí. Un error lanzado en un trigger, por ejemplo con RAISE EXCEPTION, aborta la sentencia que lo disparó, y toda la transacción salvo que lo captures con un savepoint.
¿Los triggers se disparan con TRUNCATE?
Los triggers DELETE de fila no. Crea un trigger de sentencia con BEFORE TRUNCATE o AFTER TRUNCATE para reaccionar a él.