Acervo.estudos
Trilhas Enviar material Entrar
mapa · Avançado

2.28 SQL: Triggers

Triggers no PostgreSQL e no MySQL: eventos, BEFORE e AFTER, FOR EACH ROW e STATEMENT, variáveis NEW e OLD, função de trigger e exemplos de backup, log e validação.

por @julio · rev. 23 set · 6 telas

Código que dispara sozinho

O que é trigger

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
BEFORE × AFTER, ROW × STATEMENT, NEW × OLD

Quando dispara e o que enxerga

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
EventoNEWOLD
INSERTlinha que vai entrar—
UPDATEversão novaversão antiga
DELETE—linha que sai

Acesso: NEW.nome_da_coluna, OLD.nome_da_coluna. NEW e OLD só existem em triggers FOR EACH ROW.

⚠ O vídeo diz que em NEW.<nome do campo> o campo "corresponde ao nome da tabela". É o nome da coluna.

Função RETURNS TRIGGER + CREATE TRIGGER

Trigger no PostgreSQL

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
-- PostgreSQL: guarda cópia de cada usuário apagado
CREATE FUNCTION backup_usuario_excluido() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO bkp_usuarios (id_usuario, nome_usuario)
    VALUES (OLD.id_usuario, OLD.nome_usuario);
    RETURN NULL;   -- AFTER: retorno ignorado
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER executa_backup_usuarios
AFTER DELETE ON tb_usuarios
FOR EACH ROW EXECUTE FUNCTION backup_usuario_excluido();

⚠ O vídeo usa EXECUTE PROCEDURE. Desde o PostgreSQL 11 o certo é EXECUTE FUNCTION; a forma antiga ainda funciona, mas está obsoleta. Também cita a variável "TG_NAME_SCHEMA", que não existe: o nome é TG_TABLE_SCHEMA.

⚠ No exemplo do backup, a função é criada como "backup_usuario_exluido" e o trigger chama "backup_usuario_excluido()": com nomes diferentes, o CREATE TRIGGER falha. No exemplo do backup de administradores falta o EXECUTE em "FOR EACH ROW PROCEDURE", e na validação "IF.IDADE < 0" deveria ser IF NEW.idade < 0.

Corpo dentro do próprio trigger

O mesmo no MySQL

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom

PostgreSQL

CREATE FUNCTION log_edicao_usuario()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO tb_log_edicao_usuarios
        (id_usuario, nome_usuario_antigo)
    VALUES (OLD.id_usuario, OLD.nome_usuario);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER executa_log_edicao
AFTER UPDATE ON tb_usuarios
FOR EACH ROW
EXECUTE FUNCTION log_edicao_usuario();

MySQL

CREATE TRIGGER executa_log_edicao
AFTER UPDATE ON tb_usuarios
FOR EACH ROW
INSERT INTO tb_log_edicao_usuarios
    (id_usuario, nome_usuario_antigo)
VALUES (OLD.id_usuario, OLD.nome_usuario);
-- MySQL: BEFORE pode ajustar a linha antes de gravar
CREATE TRIGGER marca_atualizacao
BEFORE UPDATE ON tb_usuarios
FOR EACH ROW
SET NEW.atualizado_em = NOW();
Barrar dados inválidos

Validação com BEFORE

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom

PostgreSQL

CREATE FUNCTION valida_funcionario()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.nome IS NULL THEN
        RAISE EXCEPTION 'Informe o nome';
    END IF;
    IF NEW.idade < 0 THEN
        RAISE EXCEPTION
          'Idade inválida: % anos', NEW.idade;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validacao
BEFORE INSERT OR UPDATE ON funcionarios
FOR EACH ROW
EXECUTE FUNCTION valida_funcionario();

MySQL

DELIMITER //
CREATE TRIGGER validacao_ins
BEFORE INSERT ON funcionarios
FOR EACH ROW
BEGIN
    IF NEW.nome IS NULL THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Informe o nome';
    END IF;
    IF NEW.idade < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Idade inválida';
    END IF;
END //
DELIMITER ;
-- para UPDATE: outro trigger igual,
-- BEFORE UPDATE
Teste antes de seguir

Revisão rápida

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
Num trigger de DELETE, dá para usar NEW?

Não. No DELETE só existe OLD, a linha que está saindo.

Quero preencher automaticamente a coluna atualizado_em. BEFORE ou AFTER?

BEFORE: só nele dá para alterar NEW antes de gravar.

Um DELETE apaga 3 linhas. Quantas vezes roda um trigger FOR EACH ROW? E FOR EACH STATEMENT?

3 vezes e 1 vez.

No PostgreSQL, o que acontece se um trigger BEFORE INSERT retornar NULL?

A linha é descartada em silêncio: não é inserida e não há erro.

← Voltar ao curso