Triggers em MySQL – Definição, Sintaxe e Criação – 44
Triggers em MySQL
Neste artigo vamos introduzir o conceito de triggers em MySQL, com exemplos de código e aplicações.
O que é um Trigger SQL?
Um trigger (“gatilho”) é um objeto programável do banco de dados associado a uma tabela. Trata-se de um procedimento que é invocado automaticamente quando um comando DML é executado na tabela, sendo executado para cada linha afetada. Desta forma, as operações que podem disparar um trigger são:
- INSERT
- UPDATE
- DELETE
Geralmente, os triggers são empregados para verificar integridade dos dados, fazer validação dos dados e outras operações relacionadas.
Diferença entre Trigger e Procedimento Armazenado
Tanto os triggers quanto as stored procedures são objetos programáveis de um banco de dados. Porém, eles possuem diferenças importantes entre si, que afetam o modo como são aplicados. Algumas das principais diferenças entre trigger e procedimentos armazenados são:
- Um Trigger é associado a uma tabela.
- Os triggers são armazenados no próprio banco de dados como arquivos separados.
- Triggers não são chamados diretamente, sendo invocados automaticamente, ao contrário dos procedimentos armazenados.
- Procedimentos armazenados podem trabalhar com parâmetros; Não passamos parâmetros aos triggers.
- Os triggers não retornam um conjunto de resultados (resultset) ao cliente chamador.
Aplicações dos triggers
As principais aplicações dos triggers em bancos de dados são:
- Validação de Dados (tipos de dados, faixas de valores, etc).
- Rastreamento e registro de logs de atividades em tabelas.
- Verificação de integridade de dados e consistência
- Arquivamento de registros excluídos.
Modos de Disparo de um Trigger
Um Trigger em MySQL pode ser disparado de dois modos diferentes:
- BEFORE – O trigger é disparado e seu código executado ANTES da execução de cada evento – por exemplo, antes de cada inserção de registros na tabela.
- AFTER – O código presente no trigger é executado após todas as ações terem sido completadas na tabela especificada.
Sintaxe para criação de um trigger em MySQL
Para criar um trigger em MySQL usamos a seguinte sintaxe:
CREATE TRIGGER nome timing operação ON tabela FOR EACH ROW declarações
Onde:
- timing pode ser BEFORE ou AFTER
- operação pode ser INSERT / UPDATE / DELETE
Exemplo
Neste exemplo criaremos uma tabela chamada Produto, que conterá os seguintes dados:
- Nome do produto
- Identificação do produto (chave primária)
- Preço normal
- Preço com desconto a ser aplicado
Logo após criaremos um trigger de nome tr_desconto, cuja função será aplicar um valor de desconto de 10% à coluna Preco_Desconto quando for disparado. Ou seja, todos os produtos terão seu preço reduzido em 10% nesta coluna. O trigger será disparado ao inserir um novo registro na tabela, o que faremos no passo 3. Veja o código completo do exercício a seguir:
-- 1. Criar a tabela de exemplo: CREATE TABLE Produto ( idProduto INT NOT NULL AUTO_INCREMENT, Nome_Produto VARCHAR(45) NULL, Preco_Normal DECIMAL(10,2) NULL, Preco_Desconto DECIMAL(10,2) NULL, PRIMARY KEY (idProduto)); -- 2. Criar o Trigger: CREATE TRIGGER tr_desconto BEFORE INSERT ON Produto FOR EACH ROW SET NEW.Preco_Desconto = (NEW.Preco_Normal * 0.90); -- 3. Executar uma inserção que irá disparar o Trigger: INSERT INTO Produto (Nome_Produto, Preco_Normal) VALUES ("DVD", 1.00), ("Pendrive", 18.00); -- 4. Verificar se trigger foi disparado observando o preço com desconto: SELECT * FROM Produto;
Como excluir um trigger
Para excluir um trigger em MySQL usamos a declaração DROP TRIGGER, seguida do nome do trigger, como no exemplo:
DROP TRIGGER tr_desconto;
Saiba mais sobre triggers em MySQL assistindo ao vídeo a seguir:
Prezado Fábio, quero parabenizá-lo pelos excelentes treinamentos que você disponibiliza, os quais venho acompanhando a bastante tempo e tem me ajudado bastante.
Estou aprofundando meus conhecimentos em Banco de Dados, em particular o MySQL, e seguindo o seu curso do mesmo, escrevi duas triggers (uma para BEFORE INSERT e outra para BEFORE UPDATE), e uma STORED PROCEDURE para validar uma entrada de dados antes de gravar em uma tabela.
A criação da tabela, da STORED PROCEDURE e das TRIGGERS estão funcionando corretamente, porém quando executo o INSERT, é apresentado um erro e não consigo inserir os dados na tabela.
Poderia dar uma ajuda? Onde estou errando?
Segue abaixo todo o script que criei, para sua compreensão:
—
— Script para criação e exibição da estrutura da tabela tbl_teste
— Nome do script: cria_tbl_teste.sql
—
— Chamada para execução do script:
—
— mysql> SOURCE C:/cria_tbl_teste.sql
—
DELIMITER $$
DROP TABLE IF EXISTS tbl_teste;
CREATE TABLE tbl_teste (
id INT(11) PRIMARY KEY AUTO_INCREMENT,
dia_abreviado VARCHAR(3));
DESCRIBE tbl_teste;
$$
DELIMITER ;
—
— Cria a PROCEDURE para ser utilizada pela TRIGGER
—
DELIMITER $$
DROP PROCEDURE IF EXISTS validarDia;
CREATE PROCEDURE validarDia (IN dia_abreviado VARCHAR(3))
BEGIN
IF (NEW.dia_abreviado ‘DOM’) AND (NEW.dia_abreviado ‘SEG’) AND (NEW.dia_abreviado ‘TER’) AND (NEW.dia_abreviado ‘QUA’) AND (NEW.dia_abreviado ‘QUI’) AND (NEW.dia_abreviado ‘SEX’) AND (NEW.dia_abreviado ‘SAB’) THEN
SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘Abreviacao do dia incorreta’;
END IF;
END
$$
DELIMITER ;
—
— TRIGGER que testa o dia quando é feito um INSERT na tabela tbl_teste:
—
DELIMITER $$
DROP TRIGGER IF EXISTS insertDia;
CREATE TRIGGER insertDia BEFORE INSERT ON tbl_teste FOR EACH ROW
CALL validarDia(dia_abreviado);
— END
$$
DELIMITER ;
—
— TRIGGER que testa o dia quando é feito um UPDATE na tabela tbl_teste:
—
DELIMITER $$
DROP TRIGGER IF EXISTS updateDia;
CREATE TRIGGER updateDia BEFORE UPDATE ON tbl_teste FOR EACH ROW
CALL validarDia(dia_abreviado);
— END
$$
DELIMITER ;
—
— Testa a inserção de dados na tabela tbl_teste:
—
INSERT INTO tbl_teste (dia_abreviado) VALUES (‘DOM’);
INSERT INTO tbl_teste (dia_abreviado) VALUES (‘ABC’);
SELECT * FROM tbl_teste;
As mensagens decorrentes da execução do script são as seguintes:
mysql> SOURCE C:/create_trigger_01.sql
Query OK, 0 rows affected (0.17 sec)
Query OK, 0 rows affected (0.33 sec)
+—————+————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+—————+————+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| dia_abreviado | varchar(3) | YES | | NULL | |
+—————+————+——+—–+———+—————-+
2 rows in set (0.41 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
ERROR 1054 (42S22): Unknown column ‘dia_abreviado’ in ‘field list’
ERROR 1054 (42S22): Unknown column ‘dia_abreviado’ in ‘field list’
Empty set (0.00 sec)
Agradeceria imensamente qualquer ajuda, pois já pesquisei bastante na Internet, sem sucesso…
Um grande abraço e no aguardo de um breve contato,
atenciosamente
Augusto Cesar Nunes