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.
O que é trigger
Quando dispara e o que enxerga
| Evento | NEW | OLD |
|---|---|---|
| INSERT | linha que vai entrar | — |
| UPDATE | versão nova | versã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.
Trigger no PostgreSQL
-- 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.
O mesmo no MySQL
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();Validação com BEFORE
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 UPDATERevisão rápida
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.