Triggers en SQL: BEFORE/AFTER, NEW/OLD y el patron updated_at

Un trigger es una funcion que la base de datos ejecuta de forma automatica ante un INSERT, UPDATE o DELETE sobre una tabla. Permite enganchar logica directamente a la escritura: rellenar una columna de servicio, registrar el historial de cambios o rechazar una operacion invalida, sin importar que cliente lanzo la consulta.

BEFORE/AFTER y ROW/STATEMENT

Un trigger tiene dos ejes independientes. El momento: BEFORE se dispara antes de escribir y puede alterar o cancelar la fila, AFTER se dispara cuando la fila ya esta confirmada en la tabla. La granularidad: FOR EACH ROW corre por cada fila afectada, FOR EACH STATEMENT corre una sola vez para toda la consulta.

• BEFORE ... FOR EACH ROW para editar valores antes de grabarlos (normalizacion, columnas automaticas).
• AFTER ... FOR EACH ROW para efectos secundarios: auditoria, actualizar tablas relacionadas.
• AFTER ... FOR EACH STATEMENT para agregados o comprobaciones sobre toda la sentencia.

En PostgreSQL un trigger son siempre dos piezas: una funcion plpgsql que devuelve el tipo trigger, y el objeto CREATE TRIGGER que la conecta a un evento.

NEW, OLD y que devolver

Dentro de una funcion a nivel de fila hay dos registros especiales: NEW es el estado entrante de la fila (INSERT/UPDATE), OLD es el estado previo (UPDATE/DELETE). El valor devuelto importa en un trigger BEFORE:

• devolver NEW para que la escritura siga, quizas con tus ediciones;
• devolver OLD (en DELETE) para que el borrado siga;
• devolver NULL para saltar en silencio la operacion en esa fila.

CREATE FUNCTION normalize_user() RETURNS trigger AS $$
BEGIN
  NEW.email := lower(trim(NEW.email));
  IF NEW.country IS NULL THEN
    NEW.country := 'US';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_normalize
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION normalize_user();

En un trigger AFTER el valor devuelto se ignora, aunque por costumbre se sigue escribiendo RETURN NEW;. Mutar NEW solo tiene efecto en BEFORE: tras la escritura, las ediciones ya no se aplican.

El patron auto-updated_at

El uso mas comun e inofensivo: refrescar de forma automatica una marca de tiempo en cada cambio de fila. Una sola funcion sirve para cualquier tabla con una columna updated_at.

ALTER TABLE notes ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();

CREATE FUNCTION touch_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER touch BEFORE UPDATE ON notes
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

Tiene que ser BEFORE: el valor debe fijarse antes de grabar la fila. La aplicacion ya no necesita acordarse de updated_at, asi que la columna siempre es fiable.

Log de auditoria

Un trigger AFTER es el encaje natural para un registro de cambios: la operacion original ya paso sus comprobaciones y solo agregamos una fila a una tabla de historial. Guardamos el estado anterior y el nuevo completos como jsonb.

CREATE TABLE orders_audit (
  id         bigserial PRIMARY KEY,
  order_id   bigint,
  action     text,
  old_row    jsonb,
  new_row    jsonb,
  changed_at timestamptz NOT NULL DEFAULT now()
);

CREATE FUNCTION log_order_change() RETURNS trigger AS $$
BEGIN
  INSERT INTO orders_audit (order_id, action, old_row, new_row)
  VALUES (
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    to_jsonb(OLD),
    to_jsonb(NEW)
  );
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_audit_trg
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION log_order_change();

La variable TG_OP guarda el tipo de operacion (INSERT, UPDATE, DELETE), de modo que una sola funcion cubre los tres casos. Devolver NULL es seguro en un trigger AFTER.

Cuando los triggers estorban

Los triggers son potentes, pero son logica oculta: un desarrollador lee un UPDATE y no ve que detras corrieron tres inserciones mas. De ahi unas reglas:

• Comportamiento oculto. La logica del trigger es facil de pasar por alto al depurar o revisar. Mantenla pequena y predecible.
• Rendimiento. Un trigger a nivel de fila corre por cada fila: un UPDATE masivo sobre un millon de filas son un millon de llamadas a la funcion.
• Cascadas y bucles. Un trigger que escribe en otra tabla puede despertar los triggers de esa tabla. La recursion y los interbloqueos surgen con facilidad.

Gotcha: cuando varios triggers de fila se disparan en el mismo evento en PostgreSQL, corren en orden alfabetico del nombre. No confies en un orden "logico", nombralos con intencion.

Diferencias entre motores:

• MySQL no usa una funcion aparte: el cuerpo va directo en CREATE TRIGGER ... FOR EACH ROW BEGIN ... END, y en lugar de NEW.col := se asigna con SET NEW.col = .... MySQL no tiene triggers a nivel de sentencia.
• ClickHouse no tiene triggers DML clasicos; necesidades parecidas se cubren con vistas materializadas que reaccionan a las inserciones.