Un procedimiento hay que llamarlo. Y basta con que una aplicación, un becario con psql o un proceso de importación escriban directamente en la tabla para saltárselo entero. Un trigger es lo contrario: código que se ejecuta solo, sin que nadie lo invoque, en el momento exacto en que una fila se inserta, se modifica o se borra. Es la única pieza del curso capaz de garantizar que algo pasa siempre, escriba quien escriba.

Aquí se cierra la promesa de 09-02: las reglas de negocio que un CHECK no puede expresar porque necesitan consultar otra tabla. Verás la anatomía completa, los cuatro ejes que definen el comportamiento de un trigger, las variables NEW y OLD con el mecanismo que las hace útiles —devolver NULL cancela la operación—, los cuatro casos canónicos aplicados a TiendaVerde con código completo, y los INSTEAD OF que hacen escribibles las vistas de 10-01. Y sus peligros, con el mismo peso que las ventajas, porque un trigger es lógica invisible: hace que un UPDATE haga cosas que tú no escribiste.

1. Anatomía: función de trigger + CREATE TRIGGER
2. Los cuatro ejes
3. NEW, OLD, TG_OP y el valor de retorno
4. Caso 1: auditoría de cambios de precio
5. Caso 2: mantener un total desnormalizado
6. Caso 3: la regla que un CHECK no puede expresar
7. Caso 4: descuento automático de stock, y si conviene
8. INSTEAD OF sobre vistas
9. Orden de disparo y gestión
10. Los peligros
11. Errores Comunes y Consejos
12. Ejercicios
13. Conclusión

En PostgreSQL un trigger son dos objetos, y esa separación desconcierta al principio:

1. Una función que devuelve el tipo especial TRIGGER y no recibe parámetros declarados.
2. Un CREATE TRIGGER que dice sobre qué tabla, cuándo y con qué granularidad se ejecuta esa función.

La ventaja de separarlos es que una misma función puede servir a varios triggers de varias tablas —de ahí TG_TABLE_NAME—, y es exactamente lo que hace útil una función de auditoría genérica.

(Todos los triggers, funciones y tablas de esta lección son ejemplos: no forman parte del esquema canónico de TiendaVerde de 01-06. La columna pedidos.total del caso 2, en particular, no es canónica.)

La combinación decide casi todo:

BEFORE frente a AFTER en una frase: en BEFORE la fila todavía no existe, así que puedes cambiarla o cancelarla, pero su id autogenerado aún no está disponible en un INSERT; en AFTER ya existe y su id es real, pero devolver algo distinto de NULL no cambia nada. Regla: valida y modifica en BEFORE; reacciona en AFTER.

Y el WHEN no es solo elegancia: WHEN (OLD.precio IS DISTINCT FROM NEW.precio) evita ejecutar la función en los millones de UPDATE que no tocan el precio. Es la optimización más barata que existe en un trigger.

Dentro de una función de trigger FOR EACH ROW hay dos variables de tipo fila, y no siempre están las dos:

Usar NEW en un DELETE o OLD en un INSERT da record "new" is not assigned yet. De ahí que una función que atiende a varias operaciones tenga que preguntar con TG_OP ('INSERT', 'UPDATE', 'DELETE', 'TRUNCATE'). Hay más variables disponibles —TG_TABLE_NAME, TG_WHEN, TG_LEVEL, TG_ARGV[] con los argumentos del CREATE TRIGGER—, pero con esas dos se resuelve casi todo.

Y ahora el mecanismo que hace útiles a los triggers: qué significa lo que devuelve la función.

Léelo despacio, porque son las dos capacidades que ninguna otra herramienta del curso tiene: en un BEFORE ... FOR EACH ROW, devolver NULL cancela la operación —la fila no se inserta, el UPDATE no se aplica, y el INSERT 0 0 es la única pista— y devolver NEW con campos cambiados guarda esos cambios. En un DELETE, lo que se devuelve es OLD. En un AFTER, el valor de retorno se ignora y por convención se escribe RETURN NULL.

Cancelar en silencio es peligroso. Si la fila se rechaza por una regla de negocio, casi siempre es mejor RAISE EXCEPTION con un mensaje claro que RETURN NULL: quien escribió el INSERT merece saber por qué no pasó nada. Reserva el NULL para descartes deliberados y documentados, como filtrar filas basura en una carga masiva.

El uso más incontestable de un trigger: registrar qué cambió, quién lo cambió y cuándo. Ninguna aplicación puede olvidarlo, porque no depende de ninguna aplicación.

Una sola fila: el segundo UPDATE no disparó nada gracias al WHEN. Tres decisiones que conviene copiar de este ejemplo: AFTER, porque solo se audita lo que efectivamente ocurrió; IS DISTINCT FROM en lugar de <>, porque con NULL el <> no es verdadero y un cambio de NULL a un valor se perdería (04-03); y TIMESTAMPTZ con now(), no la hora que envíe el cliente.

01-06 explicó por qué pedidos no tiene columna total: es un agregado que habría que mantener sincronizado. Cuando el cálculo al vuelo y la vista de 10-01 ya no bastan —un listado de pedidos que ordena y filtra por total sobre millones de filas—, la salida es guardarlo y mantenerlo con un trigger.

(6 primeros de 20 pedidos; SELECT SUM(total) FROM pedidos da 727.95.) Y si ahora borras una línea del pedido 1, su total se recalcula solo.

Tres detalles que hacen que funcione y que se olvidan siempre: COALESCE(NEW.pedido_id, OLD.pedido_id), porque en un DELETE no hay NEW; el COALESCE(..., 0) exterior, porque al borrar la última línea el SUM devuelve NULL y la columna es NOT NULL; y el relleno inicial, porque un trigger recién creado no sabe nada del pasado — es el error número uno al desnormalizar.

Y la advertencia honesta: esto es lo que se hace cuando no queda más remedio. Has cambiado una lectura barata por una escritura extra en cada INSERT, UPDATE y DELETE de líneas, has creado una vía para que los datos se desincronicen y has movido una regla a un sitio invisible. Antes de llegar aquí, prueba con una vista (10-01), con un índice (módulo 8) y con una vista materializada.

Esta es la promesa de 09-02. La regla es "una reseña solo puede escribirla un cliente que haya comprado ese producto", y un CHECK no puede expresarla: un CHECK solo ve las columnas de su propia fila, y esto exige consultar pedidos y lineas_pedido.

Y las 12 reseñas existentes siguen siendo válidas: todas corresponden a clientes que compraron ese producto, así que el trigger no rompe nada. Compáralo con el ejercicio 3b de 01-06, donde exactamente este INSERT funcionaba porque ninguna restricción lo impedía. Ahora ya no.

Dos advertencias imprescindibles. La primera: un trigger valida lo que pasa a partir de ahora, no lo que ya está. Al añadir una regla a una tabla con datos, comprueba antes cuántas filas la incumplen. La segunda es más sutil: esta comprobación no es inmune a la concurrencia. Entre el SELECT del EXISTS y el INSERT de la reseña, otra transacción podría cancelar el pedido; bajo READ COMMITTED el trigger no lo vería (09-04). Para la mayoría de las reglas de negocio eso es aceptable; para un invariante que deba cumplirse siempre, hacen falta bloqueos explícitos o SERIALIZABLE.

Técnicamente es el más sencillo de los cuatro:

Funciona, y el CHECK (stock >= 0) de productos impide que quede negativo: si alguien intenta vender más de lo que hay, el UPDATE del trigger falla y toda la transacción se deshace, incluida la línea que la provocó.

Pero ¿conviene? Aquí no hay una respuesta única, y merece la pena verla enfrentada al sp_confirmar_pedido de 10-04:

El criterio: si el stock es un invariante sagrado y hay varias vías de escritura, trigger. Si hay una sola vía de entrada controlada y el mensaje al usuario importa, procedimiento. Y si eliges el trigger, añade un BEFORE que valide con un mensaje decente en lugar de dejar que el error lo dé el CHECK.

10-01 dejó pendiente qué hacer con las vistas que no son actualizables automáticamente —las que tienen JOIN, agregados o columnas calculadas—. La respuesta es un trigger INSTEAD OF, que sustituye la operación por lo que tú decidas.

Ahora INSERT INTO v_detalle_ventas (pedido_id, producto_id, cantidad) VALUES (1, 3, 2); funciona: la vista une cuatro tablas, pero el trigger sabe que solo hay que escribir en lineas_pedido y de dónde sacar el precio. Tres reglas: los INSTEAD OF solo existen sobre vistas, siempre son FOR EACH ROW, y hacen falta tres triggers distintos —INSERT, UPDATE, DELETE— si quieres que la vista sea escribible del todo. Existe también el sistema de reglas (CREATE RULE), anterior y hoy desaconsejado: reescribe la consulta antes de ejecutarla, con efectos sorprendentes cuando hay funciones volátiles de por medio.

Cuando varios triggers compiten por el mismo momento sobre la misma tabla, PostgreSQL los ejecuta en orden alfabético por nombre.

Que el orden dependa del nombre es tan frágil como suena: renombrar trg_valida a trg_zvalida puede cambiar el comportamiento del sistema sin que nadie toque una línea de lógica. Si dos triggers dependen del orden entre sí, júntalos en uno solo; si no dependen, mejor.

DISABLE TRIGGER es imprescindible para una carga masiva: importar un millón de líneas con el trigger de stock activo son un millón de UPDATE extra. Se desactiva, se carga, se recalculan los agregados de una vez y se reactiva. Y viene con una trampa: mientras está desactivado, cualquier escritura normal también lo esquiva, así que hazlo en una ventana controlada y no lo olvides encendido.

Nombra los triggers con prefijo y patrón: trg_<tabla>_<qué hace>. En una base con doscientos objetos, encontrar por qué un UPDATE hace algo raro empieza por poder listar los triggers de esa tabla y entender sus nombres.

Merecen el mismo espacio que las ventajas, porque un trigger mal puesto es de las cosas más difíciles de depurar que existen.

• Lógica invisible. "El UPDATE hizo algo que yo no escribí" es la frase que define el problema. El SQL de la aplicación no menciona el trigger por ninguna parte; quien depura tiene que sospechar que existe. Es la razón por la que muchos equipos los limitan a auditoría e integridad, que son usos previsibles.
• Coste por fila. FOR EACH ROW significa una ejecución por cada fila. Un UPDATE que toca 500 000 filas ejecuta 500 000 veces la función, con sus consultas dentro. Lo que en una fila tarda 0,2 ms, en medio millón son 100 segundos.
• Cascadas y recursión. Un trigger sobre lineas_pedido que actualiza pedidos puede disparar un trigger sobre pedidos que actualiza lineas_pedido... y así hasta stack depth limit exceeded. Un trigger que modifica su propia tabla es directamente recursivo; se corta con un WHEN que detecte que no hay nada que hacer, o con pg_trigger_depth() = 0.
• Difíciles de probar. No se pueden invocar aisladamente: hay que provocar la operación que los dispara, y las pruebas acaban siendo de integración.
• Transaccionales, para bien y para mal. Un trigger corre dentro de la transacción que lo dispara: si falla, revierte todo, incluida la operación original. Es lo que quieres para integridad; es un desastre si dentro llamas a un servicio externo o envías un correo (10-04, ejercicio 3).
• Interacción con COPY y con las FK. COPY dispara los triggers de fila, así que una importación puede ser mucho más lenta de lo esperado. Y las claves foráneas de PostgreSQL están implementadas como triggers internos, que no aparecen en \d pero sí en pg_trigger con tgisinternal = true.

Nota de dialecto: el concepto es universal, la sintaxis no. PostgreSQL separa función y trigger, y es el único de la lista que lo hace. MySQL 8 pone el cuerpo dentro del CREATE TRIGGER, solo admite FOR EACH ROW y no tiene INSTEAD OF ni triggers de sentencia. SQL Server trabaja por sentencia con las pseudotablas INSERTED y DELETED en lugar de NEW/OLD, y sí tiene INSTEAD OF. Oracle usa :NEW y :OLD con dos puntos y añade los compound triggers. SQLite los tiene, con FOR EACH ROW obligatorio y sin TRUNCATE.

• Olvidar el RETURN en un BEFORE ... FOR EACH ROW. Caer al final de la función devuelve NULL, y eso cancela la operación en silencio: filas que no se insertan sin ningún error.
• Usar NEW en un DELETE u OLD en un INSERT. record "new" is not assigned yet. Pregunta por TG_OP o usa COALESCE(NEW.x, OLD.x).
• Crear un trigger de validación y no revisar los datos existentes. Solo se aplica al futuro; las filas que ya incumplen la regla se quedan.
• Desnormalizar sin el relleno inicial. El trigger mantiene la columna a partir de ahora, pero las 20 filas anteriores se quedan a 0.
• Comparar con <> en vez de IS DISTINCT FROM en el WHEN. Con NULL implicado, <> no es verdadero y el cambio no se audita.
• Poner un FOR EACH ROW donde bastaba un FOR EACH STATEMENT, o meter una consulta cara dentro de un trigger de fila. Multiplica por el número de filas.
• Hacer llamadas externas desde un trigger. Corre dentro de la transacción: si esta se deshace, el correo ya se envió y no se puede desenviar.
• Depender del orden alfabético entre dos triggers. Es frágil. Si el orden importa, unifícalos.
• Dejar DISABLE TRIGGER ALL puesto. La carga masiva termina y nadie reactiva; a partir de ahí las reglas no se aplican y nadie se entera hasta meses después.
• Consejo: prefijo trg_ y patrón trg_<tabla>_<acción>. Y documenta en el CREATE TABLE, con COMMENT ON TRIGGER, qué hace y por qué.
• Consejo: RAISE EXCEPTION con mensaje claro, no RETURN NULL. El silencio es el peor mensaje de error posible.
• Consejo: ante un comportamiento inexplicable, \d tabla es el primer comando. Los triggers salen listados ahí, y muchas veces la explicación está en esa línea.

Diseña la auditoría completa de productos: una tabla auditoria_productos que registre cualquier INSERT, UPDATE o DELETE con la operación, la fila antigua y la nueva en formato texto, el usuario y el momento. (1) ¿BEFORE o AFTER? ¿ROW o STATEMENT? (2) Escribe la función y el trigger usando TG_OP. (3) ¿Cómo la harías genérica para servir también a clientes y pedidos sin duplicar código?

Sobre el trigger de reseñas del apartado 6, responde razonando y comprueba: (1) ¿Qué pasa si intentas insertar la reseña de Hugo (cliente 14) sobre el producto 1? ¿Y si el cliente 1 reseña el producto 1? (2) ¿Por qué el trigger es BEFORE y no AFTER? ¿Funcionaría igual siendo AFTER? (3) El pedido 6 está cancelado: si su cliente intentara reseñar un producto que solo compró en ese pedido, ¿pasaría la validación? Localiza en la consulta la línea responsable.

Un compañero ha puesto este trigger sobre productos y ahora ningún UPDATE termina:

(1) ¿Qué error da y por qué? (2) Reescríbelo bien, sin UPDATE interno. (3) ¿En qué otro caso, además de este, un trigger puede entrar en bucle?

Solución 1

1. AFTER ... FOR EACH ROW. AFTER porque solo se audita lo que ocurrió de verdad —en BEFORE, la operación aún podría fallar por un CHECK o una FK y quedaría un registro de algo que nunca pasó— y FOR EACH ROW porque se audita cada fila, no cada sentencia. 2:

3. Ya lo es. La función no menciona productos por ninguna parte: usa TG_TABLE_NAME para saber de dónde viene y NEW::TEXT / OLD::TEXT para volcar la fila entera sea cual sea su estructura. Basta con crear otro CREATE TRIGGER idéntico sobre clientes y pedidos. Los dos CASE son necesarios porque OLD no existe en un INSERT ni NEW en un DELETE. En un sistema real se guardaría to_jsonb(NEW) en una columna JSONB en lugar de texto, y así la auditoría sería consultable clave a clave — que es 10-06.

Solución 2

1. La de Hugo falla con ERROR: El cliente 14 no ha comprado el producto 1: no tiene ningún pedido. La del cliente 1 sobre el producto 1 pasa, porque el aceite está en la línea 1 de su pedido 1. De hecho esa reseña ya existe: es la número 1 de las doce.

2. Es BEFORE porque valida, y validar es rechazar antes de escribir. Siendo AFTER también funcionaría —el RAISE EXCEPTION abortaría la transacción y la fila insertada se desharía—, pero se habría hecho trabajo inútil: escribir la fila, actualizar sus índices y deshacerlo todo. BEFORE para validar, AFTER para reaccionar, y aquí además BEFORE deja abierta la puerta a corregir NEW en lugar de rechazarlo.

3. No pasaría, por la línea AND pe.estado <> 'cancelado'. Es una decisión de negocio deliberada: un pedido cancelado no es una compra, así que no da derecho a reseñar. Y es exactamente el tipo de matiz que solo cabe en un trigger: un CHECK no puede consultar pedidos.estado, y una clave foránea tampoco.

Solución 3

1. Da ERROR: stack depth limit exceeded. El trigger se dispara AFTER UPDATE sobre productos y hace un UPDATE sobre productos, que vuelve a dispararlo, que vuelve a actualizar… hasta agotar la pila. Es la recursión directa del apartado 10, y no hay ninguna protección automática contra ella.

2. La forma correcta es no actualizar nada: modificar la fila que se está escribiendo se hace en un BEFORE, cambiando NEW y devolviéndolo.

Este es el patrón canónico de la columna actualizado_en que llevan casi todas las tablas de producción, y la razón por la que se implementa con BEFORE y no con AFTER.

3. Con cascadas entre tablas: A escribe en B, un trigger de B escribe en A, y el ciclo se cierra sin que ninguno se modifique a sí mismo. También con ON DELETE CASCADE combinado con triggers de borrado, y con dos triggers que se reactivan mutuamente en la misma tabla. Las defensas: WHEN que corte cuando ya no hay nada que cambiar (WHEN (OLD.total IS DISTINCT FROM NEW.total)), la comprobación IF pg_trigger_depth() > 1 THEN RETURN NULL; END IF;, o rediseñar para que el ciclo no exista.

Código que se ejecuta solo, con todo lo que eso implica:

• Un trigger son dos objetos: una función que devuelve TRIGGER y un CREATE TRIGGER que dice sobre qué tabla, cuándo y con qué granularidad se ejecuta. La separación permite que una función genérica sirva a varias tablas, con TG_TABLE_NAME y TG_OP.
• Los cuatro ejes: cuándo (BEFORE valida y modifica, AFTER reacciona, INSTEAD OF sustituye), qué (INSERT/UPDATE/DELETE/TRUNCATE), granularidad (FOR EACH ROW frente a FOR EACH STATEMENT) y WHEN (condición), que evita disparar en balde.
• NEW y OLD no siempre están las dos, y el valor de retorno es el mecanismo clave: en un BEFORE ... FOR EACH ROW, RETURN NULL cancela la operación y RETURN NEW modificado la altera. En AFTER se ignora.
• Los cuatro casos de TiendaVerde: auditoría de cambios de precio con WHEN (OLD.precio IS DISTINCT FROM NEW.precio) —el uso más incontestable—; pedidos.total desnormalizado que suma 727,95 € y exige relleno inicial; la regla de la reseña que un CHECK no puede expresar porque consulta otra tabla, cerrando 09-02; y el descuento de stock, con la comparación honesta frente al sp_confirmar_pedido de 10-04.
• Los INSTEAD OF hacen escribibles las vistas que 10-01 no podía actualizar automáticamente: solo sobre vistas, siempre FOR EACH ROW, y uno por operación.
• El orden de disparo es alfabético por nombre, lo cual es frágil: si dos triggers dependen del orden, unifícalos. Se listan con \d y con pg_trigger, se apagan con ALTER TABLE ... DISABLE TRIGGER para las cargas masivas y se nombran trg_<tabla>_<acción>.
• Y los peligros, con el mismo peso: lógica invisible que sorprende a quien depura, coste por fila en cargas masivas, cascadas y recursión hasta stack depth limit exceeded, dificultad para probar, y el hecho de que corren dentro de la transacción que los dispara. Un trigger es la respuesta correcta cuando la regla debe cumplirse escriba quien escriba; es un parche cuando sustituye a un CHECK o esconde lógica de negocio que debería verse.

Con vistas, CTE, funciones de ventana, procedimientos y triggers, la caja de herramientas de SQL relacional está casi completa. Falta un caso que el modelo relacional lleva mal por diseño: los datos cuya forma no es fija. Un aceite tiene acidez y variedad; una crema, ingredientes y tipo de piel; el año que viene alguien querrá guardar la huella de carbono de cada producto. Añadir una columna por atributo es inviable, y montar una tabla de pares clave-valor es el remedio clásico y doloroso. En la última lección del módulo, JSON y datos semiestructurados, verás el tipo JSONB de PostgreSQL: cómo construir documentos, cómo acceder a ellos con ->, ->> y JSONPath, cómo indexarlos con GIN —la promesa pendiente de 08-03—, cómo convertirlos de nuevo en filas para seguir usando todo el SQL del curso, y —lo más importante— qué no debe ir nunca en un JSON.