Triggers en PL/pgSQL: auditoria, timestamps y validación

Los triggers de PostgreSQL permiten ejecutar automáticamente una función PL/pgSQL ante eventos de modificación de datos (INSERT, UPDATE, DELETE, TRUNCATE) sobre una tabla o una vista. Bien empleados, desacoplan reglas de integridad, auditoría y campos derivados de la lógica de aplicación, garantizando que se cumplan independientemente del cliente (aplicación Java, script Python, ingesta por COPY). Mal empleados, introducen cascadas invisibles y degradan el rendimiento de cargas masivas. Dominar los triggers requiere entender tanto la sintaxis como los casos de uso recomendados.

Anatomia: función trigger y sentencia CREATE TRIGGER

En PostgreSQL un trigger se compone de dos piezas: una función trigger, que devuelve el tipo especial TRIGGER, y una sentencia CREATE TRIGGER que la vincula a eventos concretos sobre una tabla. Esta separación permite reutilizar la misma función en varias tablas si el caso lo permite.

CREATE OR REPLACE FUNCTION fn_log_cambio()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'Operacion % sobre % (pk=%)', TG_OP, TG_TABLE_NAME, NEW.id;
    RETURN NEW;
END;
$$;

CREATE TRIGGER tg_log_cambio
AFTER INSERT OR UPDATE ON productos
FOR EACH ROW
EXECUTE FUNCTION fn_log_cambio();

La función devuelve NEW en triggers BEFORE (el valor retornado se usa como nueva fila), devuelve NULL para cancelar la operación en un trigger BEFORE ROW, o se ignora el valor de retorno en triggers AFTER (por convención se devuelve NEW u OLD).

Momento: BEFORE, AFTER e INSTEAD OF

Los tres momentos disponibles responden a casos de uso diferentes.

BEFORE se ejecuta antes de aplicar el cambio. Puede modificar NEW para inyectar valores calculados (timestamps, claves derivadas, normalizaciones) o devolver NULL para abortar la operación silenciosamente.

CREATE OR REPLACE FUNCTION fn_normaliza_email()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.email := LOWER(TRIM(NEW.email));
    RETURN NEW;
END;
$$;

CREATE TRIGGER tg_clientes_email_before
BEFORE INSERT OR UPDATE ON clientes
FOR EACH ROW
EXECUTE FUNCTION fn_normaliza_email();

AFTER se ejecuta después de aplicar el cambio. La fila ya está escrita (aunque no visible hasta el COMMIT). Es el momento correcto para auditar, propagar a otras tablas o emitir notificaciones, porque los datos están en su estado final.

INSTEAD OF se aplica solo sobre vistas, no sobre tablas. Permite hacer escribibles vistas que no lo serían de forma automática, redirigiendo cada INSERT/UPDATE/DELETE a las tablas base adecuadas.

CREATE VIEW clientes_activos AS
SELECT id, email, nombre FROM clientes WHERE activo = true;

CREATE OR REPLACE FUNCTION fn_clientes_activos_insert()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO clientes(email, nombre, activo)
    VALUES (NEW.email, NEW.nombre, true)
    RETURNING id INTO NEW.id;
    RETURN NEW;
END;
$$;

CREATE TRIGGER tg_clientes_activos_insert
INSTEAD OF INSERT ON clientes_activos
FOR EACH ROW
EXECUTE FUNCTION fn_clientes_activos_insert();

Granularidad: FOR EACH ROW y FOR EACH STATEMENT

Un trigger ROW se dispara una vez por cada fila afectada. Dentro de la función están disponibles NEW y OLD como la fila individual. Un trigger STATEMENT se dispara una vez por cada sentencia, independientemente de cuántas filas afecte, y no dispone de NEW/OLD por fila, sino de tablas de transición REFERENCING OLD TABLE y NEW TABLE (PostgreSQL 10+).

CREATE OR REPLACE FUNCTION fn_audit_statement()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_total INT;
BEGIN
    SELECT COUNT(*) INTO v_total FROM new_rows;
    INSERT INTO auditoria_lotes(tabla, operacion, filas, usuario, ts)
    VALUES (TG_TABLE_NAME, TG_OP, v_total, current_user, NOW());
    RETURN NULL;
END;
$$;

CREATE TRIGGER tg_audit_statement
AFTER UPDATE ON pedidos
REFERENCING NEW TABLE AS new_rows
FOR EACH STATEMENT
EXECUTE FUNCTION fn_audit_statement();

El criterio de elección es claro. Si la lógica depende de cada fila (NEW.columna) se usa FOR EACH ROW. Si se quiere auditar solo el hecho de que alguien ejecutó un UPDATE masivo, sin escribir N filas de auditoría, se usa FOR EACH STATEMENT con tablas de transición.

Variables especiales: NEW, OLD, TG_OP, TG_TABLE_NAME

PL/pgSQL expone varias variables automáticas dentro de una función trigger. Las más habituales son:

• NEW es la nueva fila (disponible en INSERT y UPDATE, no en DELETE).
• OLD es la fila antes del cambio (disponible en UPDATE y DELETE, no en INSERT).
• TG_OP contiene la operación en texto: 'INSERT', 'UPDATE', 'DELETE' o 'TRUNCATE'.
• TG_TABLE_NAME y TG_TABLE_SCHEMA identifican la tabla.
• TG_WHEN vale 'BEFORE', 'AFTER' o 'INSTEAD OF'.
• TG_LEVEL vale 'ROW' o 'STATEMENT'.
• TG_ARGV es un array de argumentos pasados al trigger en CREATE TRIGGER ... EXECUTE FUNCTION fn(arg1, arg2).

CREATE OR REPLACE FUNCTION fn_debug_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'Trigger %.% % % sobre % | args=%',
        TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL, TG_ARGV;
    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$;

Caso 1: auditoria automatica de cambios

El caso canónico. Una tabla auditoria_cambios registra todas las modificaciones sobre tablas críticas. El mismo trigger sirve para INSERT, UPDATE y DELETE si se usa TG_OP para distinguir.

CREATE TABLE auditoria_cambios (
    id BIGSERIAL PRIMARY KEY,
    tabla TEXT NOT NULL,
    operacion TEXT NOT NULL,
    fila_id BIGINT,
    datos_antes JSONB,
    datos_despues JSONB,
    usuario TEXT NOT NULL DEFAULT current_user,
    ts TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION fn_audit_generico()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_fila_id BIGINT;
    v_antes JSONB;
    v_despues JSONB;
BEGIN
    IF TG_OP = 'INSERT' THEN
        v_fila_id := NEW.id;
        v_antes := NULL;
        v_despues := to_jsonb(NEW);
    ELSIF TG_OP = 'UPDATE' THEN
        v_fila_id := NEW.id;
        v_antes := to_jsonb(OLD);
        v_despues := to_jsonb(NEW);
    ELSIF TG_OP = 'DELETE' THEN
        v_fila_id := OLD.id;
        v_antes := to_jsonb(OLD);
        v_despues := NULL;
    END IF;

    INSERT INTO auditoria_cambios(tabla, operacion, fila_id, datos_antes, datos_despues)
    VALUES (TG_TABLE_NAME, TG_OP, v_fila_id, v_antes, v_despues);

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$;

CREATE TRIGGER tg_audit_empleados
AFTER INSERT OR UPDATE OR DELETE ON empleados
FOR EACH ROW EXECUTE FUNCTION fn_audit_generico();

CREATE TRIGGER tg_audit_salarios
AFTER INSERT OR UPDATE OR DELETE ON salarios
FOR EACH ROW EXECUTE FUNCTION fn_audit_generico();

Guardar en JSONB todo el estado antes y después permite reconstruir la historia completa sin un esquema rígido por tabla. La misma función se reutiliza en cualquier tabla con columna id.

Una consulta típica sobre el histórico de auditoría recupera el último cambio de una fila:

SELECT operacion, datos_antes, datos_despues, usuario, ts
FROM auditoria_cambios
WHERE tabla = 'empleados'
  AND fila_id = 42
ORDER BY ts DESC
LIMIT 5;

Para reportes agregados se puede consultar cuántos cambios hizo cada usuario en un periodo:

SELECT usuario, operacion, COUNT(*) AS cambios
FROM auditoria_cambios
WHERE ts >= NOW() - INTERVAL '7 days'
GROUP BY usuario, operacion
ORDER BY cambios DESC;

Caso 2: updated_at automatico

Uno de los usos más frecuentes. Cada UPDATE debe refrescar la columna updated_at. Un trigger BEFORE UPDATE lo resuelve de forma estándar.

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

CREATE TRIGGER tg_productos_updated_at
BEFORE UPDATE ON productos
FOR EACH ROW EXECUTE FUNCTION fn_touch_updated_at();

CREATE TRIGGER tg_clientes_updated_at
BEFORE UPDATE ON clientes
FOR EACH ROW EXECUTE FUNCTION fn_touch_updated_at();

Una mejora es disparar el trigger solo cuando algo realmente cambia, usando la cláusula WHEN de CREATE TRIGGER, evitando reescribir la fila si la aplicación hace un UPDATE idéntico:

CREATE TRIGGER tg_productos_updated_at
BEFORE UPDATE ON productos
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION fn_touch_updated_at();

Caso 3: soft delete

Convertir DELETE en un marcado lógico es un patrón habitual en aplicaciones con requisitos de retención. Un trigger BEFORE DELETE cancela el borrado físico y aplica un UPDATE sobre deleted_at.

ALTER TABLE clientes ADD COLUMN deleted_at TIMESTAMPTZ;

CREATE OR REPLACE FUNCTION fn_soft_delete()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE clientes SET deleted_at = NOW() WHERE id = OLD.id;
    RETURN NULL;
END;
$$;

CREATE TRIGGER tg_clientes_soft_delete
BEFORE DELETE ON clientes
FOR EACH ROW EXECUTE FUNCTION fn_soft_delete();

Devolver NULL en un BEFORE DELETE cancela la operación. El UPDATE explícito marca la fila como borrada. Combinado con una vista filtrada se obtiene el comportamiento esperado:

CREATE VIEW clientes_activos AS
SELECT id, email, nombre
FROM clientes
WHERE deleted_at IS NULL;

Para una restauración rápida basta con limpiar la columna:

UPDATE clientes SET deleted_at = NULL WHERE id = 42;

Caso 4: validación cross-row

Las restricciones CHECK solo pueden consultar la fila actual. Cuando una regla depende del conjunto de filas (presupuesto mensual total, slots de reserva, suma por categoría), un trigger con consulta agregada es la solución.

CREATE TABLE gastos (
    id BIGSERIAL PRIMARY KEY,
    departamento_id INT NOT NULL,
    mes DATE NOT NULL,
    importe NUMERIC(12,2) NOT NULL CHECK (importe > 0)
);

CREATE TABLE presupuestos (
    departamento_id INT,
    mes DATE,
    limite NUMERIC(12,2) NOT NULL,
    PRIMARY KEY (departamento_id, mes)
);

CREATE OR REPLACE FUNCTION fn_valida_presupuesto()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_total NUMERIC;
    v_limite NUMERIC;
BEGIN
    SELECT COALESCE(SUM(importe), 0)
      INTO v_total
    FROM gastos
    WHERE departamento_id = NEW.departamento_id
      AND mes = NEW.mes;

    SELECT limite INTO v_limite
    FROM presupuestos
    WHERE departamento_id = NEW.departamento_id
      AND mes = NEW.mes;

    IF v_total + NEW.importe > v_limite THEN
        RAISE EXCEPTION 'Presupuesto agotado para dept % mes % (limite=%, usado=%, nuevo=%)',
            NEW.departamento_id, NEW.mes, v_limite, v_total, NEW.importe;
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER tg_gastos_valida
BEFORE INSERT ON gastos
FOR EACH ROW EXECUTE FUNCTION fn_valida_presupuesto();

CONSTRAINT TRIGGER y DEFERRABLE

Un CONSTRAINT TRIGGER es un trigger AFTER ROW que se comporta como una restricción: puede diferirse hasta el final de la transacción con DEFERRABLE INITIALLY DEFERRED. Es la herramienta adecuada para validaciones que necesitan "ver" el estado completo tras varios INSERT/UPDATE relacionados.

CREATE CONSTRAINT TRIGGER tg_consistencia_factura
AFTER INSERT OR UPDATE ON lineas_factura
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION fn_valida_total_factura();

Dentro de la transacción, el cliente puede llamar SET CONSTRAINTS ALL DEFERRED para posponer todas las comprobaciones diferibles hasta el COMMIT, o SET CONSTRAINTS ALL IMMEDIATE para forzar la verificación en ese instante.

BEGIN;
    SET CONSTRAINTS tg_consistencia_factura DEFERRED;
    INSERT INTO lineas_factura(factura_id, importe) VALUES (1, 100);
    INSERT INTO lineas_factura(factura_id, importe) VALUES (1, 50);
    UPDATE facturas SET total = 150 WHERE id = 1;
COMMIT;

Sin DEFERRABLE, el primer INSERT dispararía la comprobación con un total incompleto y fallaría.

Antipatrones y consideraciones de rendimiento

Los triggers son potencialmente invisibles para quien lee una aplicación y pueden producir efectos sorprendentes. Hay tres antipatrones que conviene conocer.

Trigger que hace demasiado. Una función trigger con 200 líneas que escribe en seis tablas, llama a una API externa con dblink y recalcula agregados es muy difícil de mantener. Si el trigger necesita ese volumen de lógica, suele ser mejor exponer un PROCEDURE explícito que la aplicación invoque con CALL.

Cascadas recursivas. Un trigger AFTER UPDATE ON a que hace UPDATE b que a su vez tiene un trigger que hace UPDATE a puede entrar en bucle. La variable pg_trigger_depth() ayuda a detectar recursión: muchos equipos añaden IF pg_trigger_depth() > 1 THEN RETURN NEW; END IF; al inicio de la función para evitarla.

CREATE OR REPLACE FUNCTION fn_sync_cache()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF pg_trigger_depth() > 1 THEN
        RETURN NEW;
    END IF;
    UPDATE productos_cache
    SET total = NEW.total
    WHERE producto_id = NEW.id;
    RETURN NEW;
END;
$$;

Performance en cargas masivas. Un trigger ROW sobre una tabla que recibe un COPY de 10 millones de filas multiplica el tiempo de carga por un factor grande. Para cargas en batch existen dos estrategias: desactivar temporalmente el trigger con ALTER TABLE tabla DISABLE TRIGGER nombre; ... ENABLE TRIGGER nombre; o migrarlo a FOR EACH STATEMENT con tablas de transición si la lógica se puede reformular en un INSERT ... SELECT agregado.

Inventario y diagnostico de triggers

El catálogo pg_trigger lista los triggers existentes. information_schema.triggers es una vista portable equivalente.

SELECT event_object_schema AS schema,
       event_object_table AS tabla,
       trigger_name,
       action_timing AS cuando,
       event_manipulation AS evento,
       action_orientation AS nivel
FROM information_schema.triggers
ORDER BY tabla, trigger_name;

Para deshabilitar temporalmente un trigger en producción sin eliminarlo:

ALTER TABLE empleados DISABLE TRIGGER tg_audit_empleados;
-- Operaciones masivas sin auditoria
ALTER TABLE empleados ENABLE TRIGGER tg_audit_empleados;

Para eliminar un trigger cuando ya no se necesita:

DROP TRIGGER IF EXISTS tg_audit_empleados ON empleados;
DROP FUNCTION IF EXISTS fn_audit_generico();

Los triggers son una herramienta de la base de datos que aporta garantías reales: se ejecutan siempre, sin importar qué cliente hace la modificación. Usados con mesura (un patrón por trigger, funciones cortas, ausencia de recursión, evaluación de impacto en cargas masivas) son una de las formas más efectivas de mantener invariantes de negocio críticos en PostgreSQL.