Automatización y lógica avanzada en la base de datos gracias a funciones y triggers

abr. 15, 2025

En los capítulos anteriores de esta serie, hemos recorrido desde los conceptos fundamentales de las bases de datos hasta ejemplos más avanzados usando PostgreSQL. Hemos creado un sistema para gestionar una red de transporte público y explorado cómo definir relaciones entre entidades, realizar consultas complejas y hasta construir vistas que simplifican el acceso a la información.

Ahora daremos un paso más allá. En este artículo, nos enfocaremos en dos herramientas clave que nos permiten llevar la lógica del negocio directamente al motor de base de datos: las funciones y los triggers (o disparadores). Estas características son especialmente útiles cuando queremos automatizar tareas, validar datos o sincronizar información sin depender de la lógica de aplicación.

¿Qué son las funciones en PostgreSQL?

Una función es un bloque reutilizable de código que se ejecuta en el servidor de base de datos. Puede aceptar parámetros de entrada y devolver un valor (o conjunto de resultados). Las funciones permiten encapsular lógica, validar datos o realizar cálculos de forma eficiente y consistente. De forma similar a la que lo hacen las funciones de un lenguaje de programación cualquiera.

¿Cuándo usar funciones?

• Para centralizar lógica que se repite en múltiples consultas.
• Para validar reglas de negocio.
• Para realizar transformaciones complejas de datos.
• Para ser invocadas desde triggers u otras funciones.

¿Qué es un trigger (disparador)?

Un trigger es una acción automática que se ejecuta como respuesta a un evento en una tabla: inserción (INSERT), actualización (UPDATE) o eliminación (DELETE). El trigger se dispara antes o después del evento y puede utilizar una función asociada para ejecutar lógica adicional.

Usos típicos de los triggers

• Validar o transformar datos antes de insertarlos.
• Registrar logs de auditoría.
• Actualizar datos relacionados.
• Enviar notificaciones o eventos.

¿De dónde vienen las funciones y triggers?

Tanto las funciones como los triggers forman parte del estándar SQL:2003 (y versiones posteriores), aunque su implementación concreta varía ligeramente según el motor de base de datos. Estas herramientas surgieron con el objetivo de permitir que cierta lógica pudiera residir directamente en la base de datos, garantizando reglas de negocio más consistentes, eficientes y reutilizables.

Históricamente, su adopción fue gradual. Al principio, muchos sistemas delegaban toda la lógica de validación y negocio a las aplicaciones cliente. Pero con el crecimiento de los sistemas distribuidos, se volvió cada vez más útil contar con herramientas que garantizaran reglas comunes en un solo lugar: la base de datos.

Hoy en día, la mayoría de los motores relacionales modernos ofrecen soporte para ambas características, aunque con sintaxis y capacidades específicas:

Nota: En el caso de SQLite, las funciones pueden crearse usando extensiones en otros lenguajes como C o Python, pero no de forma nativa como en otros motores.

Funciones vs Triggers: ¿Cuál usar y cuándo?

Aunque pueden parecer similares porque ambas ejecutan lógica en la base de datos, tienen roles y comportamientos distintos. Aquí te dejamos una comparación sencilla para entender cuándo conviene usar cada una:

En resumen:

• Usa funciones cuando necesites encapsular lógica que se pueda reutilizar en diferentes contextos, incluso desde la aplicación.
• Usa triggers cuando necesites ejecutar acciones automáticamente como respuesta a eventos (por ejemplo, cada vez que alguien actualiza una fila).

💡 Muchas veces se usan en conjunto: un trigger puede estar asociado a una función que contiene la lógica real a ejecutar. De esta forma, mantienes el código del cliente más limpio y fácil de mantener.

Ejemplo práctico: Funciones y triggers en el sistema de transporte público

En esta sección, aplicaremos los conceptos aprendidos utilizando como base el sistema de transporte público que hemos venido empezado a desarrollar en otro capítulo de esta serie.

Hasta ahora, hemos diseñado un esquema que incluye entidades como route, stop, vehicle y schedule, todas ellas relacionadas entre sí para representar la lógica de rutas, paradas, horarios y vehículos asignados. Ahora, vamos a incorporar funciones y triggers para extender este modelo y automatizar ciertas tareas repetitivas o propensas a errores cuando se hacen manualmente.

A continuación, realizaremos una serie de mejoras al modelo de datos actual mediante la creación de funciones personalizadas y triggers automáticos. Estas tareas permitirán:

• Registrar modificaciones en horarios:
  • Añadir una columna updated_at en la tabla schedule.
  • Crear una función para actualizar este campo cada vez que se modifica una fila.
  • Asociar la función a un trigger BEFORE UPDATE.
• Validar el límite de vehículos por ruta:
  • Crear una función que cuente cuántos vehículos hay ya asignados a una ruta.
  • Generar un trigger que impida insertar nuevos vehículos si se supera el límite.
• Auditoría básica:
  • Crear una tabla de logs de eventos (event_log).
  • Insertar automáticamente un registro cuando se borre una parada (stop), indicando cuándo y qué parada fue eliminada.

A medida que avancemos, iremos explicando cada paso en detalle con código SQL comentado, para que puedas reproducirlo en tu propio entorno o adaptarlo a tus necesidades.

💡 Como en artículos anteriores, trabajaremos sobre una base PostgreSQL que puedes ejecutar fácilmente en local usando Docker. Si no lo tienes configurado aún, te recomendamos repasar el artículo anterior sobre cómo montar PostgreSQL en contenedor.

Vamos con la primera tarea: registrar la fecha de última modificación en los horarios del sistema. 👇

1. Control automático de actualización de horarios (updated_at en schedule)

Una de las tareas más comunes en bases de datos relacionales es llevar un control de cuándo se actualizó por última vez un registro. Esto es útil tanto para auditoría como para sincronización o monitoreo de cambios. En esta primera tarea, implementaremos esa funcionalidad en la tabla schedule de nuestro sistema de transporte.

Aunque en la actualidad casi todos los ORM y frameworks de desarrollo manejan esto automáticamente, está bien saber cómo hacerlo por si nuestro caso de uso requisiera una solución más personalizada.

Paso 1: Añadir la columna updated_at

Primero, añadiremos una nueva columna en la tabla schedule para registrar la fecha y hora de la última actualización:

   ALTER TABLE schedule
   ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
    

Esta columna se inicializa automáticamente con la fecha y hora actuales al momento de insertar el registro, pero no se actualiza automáticamente cuando se modifica la fila. Para lograr eso, necesitamos usar una función y un trigger.

Paso 2: Crear la función para actualizar updated_at

Vamos a crear una función en PostgreSQL utilizando el lenguaje plpgsql que se encargue de actualizar el campo updated_at cada vez que se modifique una fila en la tabla schedule.

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

¿Qué hace esta función?

• Toma el nuevo valor del registro (NEW) antes de que se escriba en la base de datos.
• Le asigna el valor de la fecha y hora actual (CURRENT_TIMESTAMP) al campo updated_at.
• Retorna el registro modificado para que sea almacenado.

Paso 3: Crear el trigger asociado

Ahora conectamos la función con la tabla schedule usando un trigger. El trigger se ejecutará antes de cada actualización (BEFORE UPDATE), de manera que updated_at se actualice correctamente sin intervención manual.

   CREATE TRIGGER trg_update_schedule_timestamp
   BEFORE UPDATE ON schedule
   FOR EACH ROW
   EXECUTE FUNCTION update_schedule_timestamp();
    

Nota técnica:

• FOR EACH ROW indica que el trigger se ejecutará una vez por cada fila afectada por la operación.
• También podríamos hacer un trigger a nivel de sentencia (FOR EACH STATEMENT), pero no funcionaría para este caso porque necesitamos modificar datos fila por fila.

Comprobación de la funcionalidad

Para probar que todo funciona correctamente, realizamos una actualización sobre cualquier fila de schedule y luego consultamos el campo updated_at.

   -- Ver registro actual
   SELECT id, scheduled_time, updated_at FROM schedule WHERE id = 1;
   
   -- Actualizar el horario
   UPDATE schedule SET scheduled_time = scheduled_time + INTERVAL '15 minutes' WHERE id = 1;
   
   -- Verificar que se actualizó el campo
   SELECT id, scheduled_time, updated_at FROM schedule WHERE id = 1;
    

Deberías ver que el valor de updated_at ha cambiado a la fecha y hora en la que realizaste la actualización.

2. Validar el número máximo de vehículos por ruta

En muchos sistemas de gestión, necesitamos imponer ciertas restricciones lógicas que no siempre se pueden definir solo con constraints tradicionales. Por ejemplo, podríamos querer limitar la cantidad de vehículos asignados a una misma ruta para evitar sobrecargas operativas o simplemente por requisitos de negocio.

En esta segunda tarea, implementaremos una validación que impida asignar más de 10 vehículos a una misma ruta.

Paso 1: Crear una función de validación

Esta función contará cuántos vehículos hay ya registrados para la ruta indicada en el nuevo vehículo (NEW.route_id). Si ya hay 10 o más, lanzará una excepción y detendrá la inserción.

   CREATE OR REPLACE FUNCTION validate_vehicle_limit()
   RETURNS TRIGGER AS $$
   DECLARE
       vehicle_count INT;
   BEGIN
       -- Contar vehículos ya asignados a la ruta
       SELECT COUNT(*) INTO vehicle_count FROM vehicle WHERE route_id = NEW.route_id;
   
       -- Si se supera el límite, lanzar error
       IF vehicle_count >= 10 THEN
           RAISE EXCEPTION 'Route % already has the maximum number of vehicles allowed (10)', NEW.route_id;
       END IF;
   
       RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;
    

¿Qué hace esta función?

• Consulta cuántos vehículos están asociados actualmente a la ruta del nuevo registro.
• Si el conteo es mayor o igual a 10, lanza una excepción personalizada.
• En caso contrario, permite que el registro se inserte normalmente.

Paso 2: Crear el trigger asociado

Este trigger se ejecutará antes de insertar un nuevo vehículo, para asegurarse de que la validación ocurra justo antes de que los datos lleguen a la base de datos.

   CREATE TRIGGER trg_validate_vehicle_limit
   BEFORE INSERT ON vehicle
   FOR EACH ROW
   EXECUTE FUNCTION validate_vehicle_limit();
    

Comprobación de la funcionalidad

Puedes probar esta funcionalidad insertando vehículos asociados a una ruta específica. Supongamos que la ruta con ID 3 ya tiene 10 vehículos registrados. Al intentar agregar uno más, deberías recibir un error como este:

  -- Insertar vehículos en una misma ruta
  INSERT INTO vehicle (plate_number, capacity, route_id) VALUES
   ('TTT-0001', 40, 3),
   ('TTT-0002', 40, 3),
   ('TTT-0003', 40, 3),
   ('TTT-0004', 40, 3),
   ('TTT-0005', 40, 3),
   ('TTT-0006', 40, 3),
   ('TTT-0007', 40, 3);
   ('TTT-0008', 40, 3);
   ('TTT-0009', 40, 3);
   ('TTT-0010', 40, 3); -- Este será el décimo vehículo
  
  -- Esta insert dará error por superar el límite
  INSERT INTO vehicle (plate_number, capacity, route_id) VALUES
  ('OVER-9999', 40, 3);   
     
  -- Resultado esperado:
  -- ERROR: Route 3 already has the maximum number of vehicles allowed (10)
  
  -- Si intentas con una ruta diferente o con menos de 10 vehículos, debería funcionar:
  
  INSERT INTO vehicle (plate_number, capacity, route_id) VALUES ('XYZ789Y', 50, 4);
    

Gracias a esta lógica implementada en la base de datos, protegemos la consistencia del sistema independientemente del cliente o aplicación que interactúe con los datos.

3. Registro automático de eventos: auditoría básica al eliminar paradas

En algunos sistemas, especialmente en entornos regulados o críticos, es importante mantener un registro de los cambios que ocurren en la base de datos. Aunque PostgreSQL cuenta con soluciones avanzadas como pgAudit, muchas veces una implementación simple con triggers es suficiente para auditar acciones específicas.

En esta tarea, crearemos una tabla de auditoría para registrar cada vez que se elimine una parada (stop) del sistema de transporte.

Paso 1: Crear la tabla de auditoría

Creamos una nueva tabla que almacenará los eventos de eliminación, con información básica como el ID de la parada, su nombre y la fecha de eliminación.

    
  CREATE TABLE event_log (
      id SERIAL PRIMARY KEY,
      event_type TEXT NOT NULL,
      table_name TEXT NOT NULL,
      record_id INTEGER,
      description TEXT,
      deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );
    

Paso 2: Crear la función de auditoría

Esta función se activará automáticamente cada vez que se elimine una parada (stop). Registrará la acción en la tabla event_log.

  CREATE OR REPLACE FUNCTION log_stop_deletion()
  RETURNS TRIGGER AS $$
  BEGIN
      INSERT INTO event_log (event_type, table_name, record_id, description)
      VALUES (
          'DELETE',
          'stop',
          OLD.id,
          CONCAT('Stop "', OLD.name, '" was deleted.')
      );
  
      RETURN OLD;
  END;
  $$ LANGUAGE plpgsql;
    

¿Por qué usamos OLD?

• En triggers AFTER DELETE, los datos del registro eliminado están disponibles a través de la variable OLD. No existe NEW, ya que no hay ningún nuevo registro al que hacer referencia.

Paso 3: Crear el trigger asociado

Este trigger se ejecutará después de eliminar una parada (AFTER DELETE), para asegurarse de que la acción de auditoría se registre correctamente.

  CREATE TRIGGER trg_log_stop_deletion
  AFTER DELETE ON stop
  FOR EACH ROW
  EXECUTE FUNCTION log_stop_deletion();
    

Comprobación de la funcionalidad

Para probar la funcionalidad de auditoría, eliminamos una parada y luego consultamos la tabla event_log para asegurarnos que se ha registrado correctamente.

  -- Eliminar una parada
  DELETE FROM stop WHERE id = 2;
  
  -- Consultar los logs
  SELECT * FROM event_log ORDER BY deleted_at DESC;
    

Deberías ver un nuevo registro en la tabla event_log que indica que la parada con ID 2 fue eliminada, junto con la fecha y hora de la acción.

Resumen y conclusiones

En este artículo hemos explorado el poder de las funciones y los triggers en PostgreSQL aplicados a un sistema real. Estas herramientas permiten automatizar tareas, validar reglas de negocio y mantener la integridad de los datos directamente desde el motor de base de datos.

Mejoras que aportan valor sin necesidad de depender completamente del código de la aplicación, y son perfectamente escalables a escenarios más complejos.

¿Tienes alguna idea de qué podrías automatizar tú en tus propias bases de datos? ¡Experimenta con estos ejemplos y adáptalos a tus necesidades!

Pero sobre todo… ¡Happy Coding!