Triggers no SQL: BEFORE/AFTER, NEW/OLD e o padrao updated_at

Um trigger e uma funcao que o banco executa automaticamente em um INSERT, UPDATE ou DELETE sobre uma tabela. Ele permite anexar logica diretamente a gravacao: preencher uma coluna de servico, registrar o historico de mudancas ou recusar uma operacao invalida, nao importa qual cliente rodou a consulta.

BEFORE/AFTER e ROW/STATEMENT

Um trigger tem dois eixos independentes. O momento: BEFORE dispara antes da gravacao e pode alterar ou cancelar a linha, AFTER dispara quando a linha ja esta confirmada na tabela. A granularidade: FOR EACH ROW roda por linha afetada, FOR EACH STATEMENT roda uma unica vez para a consulta inteira.

• BEFORE ... FOR EACH ROW para editar valores antes de gravar (normalizacao, colunas automaticas).
• AFTER ... FOR EACH ROW para efeitos colaterais: auditoria, atualizar tabelas relacionadas.
• AFTER ... FOR EACH STATEMENT para agregados ou checagens sobre todo o comando.

No PostgreSQL um trigger sao sempre duas pecas: uma funcao plpgsql que retorna o tipo trigger, e o objeto CREATE TRIGGER que a liga a um evento.

NEW, OLD e o que retornar

Dentro de uma funcao em nivel de linha ha dois registros especiais: NEW e o estado novo da linha (INSERT/UPDATE), OLD e o estado anterior (UPDATE/DELETE). O valor retornado importa em um trigger BEFORE:

• retornar NEW para que a gravacao siga, talvez com suas edicoes;
• retornar OLD (no DELETE) para que a exclusao siga;
• retornar NULL para pular em silencio a operacao naquela linha.

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();

Em um trigger AFTER o valor retornado e ignorado, embora por habito ainda se escreva RETURN NEW;. Mutar NEW so tem efeito em BEFORE: depois da gravacao, as edicoes nao se aplicam mais.

O padrao auto-updated_at

O uso mais comum e inofensivo: atualizar automaticamente um carimbo de tempo a cada mudanca de linha. Uma unica funcao serve para qualquer tabela com uma coluna 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();

Tem que ser BEFORE: o valor precisa ser definido antes de gravar a linha. A aplicacao nao precisa mais lembrar do updated_at, entao a coluna e sempre confiavel.

Log de auditoria

Um trigger AFTER e o encaixe natural para um log de mudancas: a operacao original ja passou pelas verificacoes e apenas anexamos uma linha a uma tabela de historico. Guardamos o estado antigo e o novo inteiros 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();

A variavel TG_OP guarda o tipo de operacao (INSERT, UPDATE, DELETE), entao uma unica funcao cobre os tres casos. Retornar NULL e seguro em um trigger AFTER.

Quando triggers atrapalham

Triggers sao poderosos, mas sao logica oculta: um desenvolvedor le um UPDATE e nunca ve que tres insercoes a mais rodaram por tras. Dai algumas regras:

• Comportamento oculto. A logica do trigger e facil de ignorar ao depurar e revisar. Mantenha-a pequena e previsivel.
• Desempenho. Um trigger em nivel de linha roda por linha: um UPDATE em massa sobre um milhao de linhas sao um milhao de chamadas da funcao.
• Cascatas e loops. Um trigger que escreve em outra tabela pode acordar os triggers daquela tabela. Recursao e deadlocks surgem com facilidade.

Pegadinha: quando varios triggers de linha disparam no mesmo evento no PostgreSQL, eles rodam em ordem alfabetica do nome. Nao confie em uma ordem "logica", nomeie-os com intencao.

Diferencas entre engines:

• MySQL nao usa uma funcao a parte: o corpo vai direto em CREATE TRIGGER ... FOR EACH ROW BEGIN ... END, e em vez de NEW.col := voce atribui com SET NEW.col = .... O MySQL nao tem triggers em nivel de statement.
• ClickHouse nao tem triggers DML classicos; necessidades parecidas sao atendidas com visoes materializadas que reagem a insercoes.