O autor selecionou a Apache Software Foundation para receber uma doação como parte do programa Write for DOnations.
No MySQL, um acionador é um comando SQL definido por usuário que é chamado automaticamente durante uma operação INSERT
, DELETE
, ou UPDATE
. O código de acionador está associado a uma tabela e é destruído assim que uma tabela for descartada. Você pode especificar um momento de ação do acionador e definir se ele será ativado antes ou após o evento de banco de dados definido.
Os acionadores possuem várias vantagens. Por exemplo, você pode usá-los para gerar o valor de uma coluna derivada durante uma instrução de INSERT
. Outro caso de uso é a aplicação da integridade referencial, onde é possível utilizar um acionador para salvar um registro em várias tabelas relacionadas. Outros benefícios dos acionadores incluem o registro de ações do usuário para auditar tabelas, bem como cópias de dados em tempo real em diferentes esquemas de bancos de dados para fins de redundância, com o objetivo de evitar um único ponto de falha.
Você também pode utilizar acionadores para manter as regras de validação no nível do banco de dados. Isso ajuda no compartilhamento da fonte de dados em vários aplicativos, sem quebrar a lógica de negócios. Isso também reduz bastante os testes de viagem de ida e volta para o servidor de banco de dados, o que, portanto, melhora o tempo de resposta dos seus aplicativos. Como o servidor de banco de dados executa os acionadores, eles podem aproveitar-se de recursos de servidor melhorados, como RAM e CPU.
Neste tutorial, você criará, utilizará e excluirá vários tipos de acionadores no seu banco de dados do MySQL.
Antes de começar, certifique-se de ter o seguinte:
Neste passo, você criará um banco de dados de clientes de amostra com várias tabelas para demonstrar como os acionadores do MySQL funcionam.
Para entender melhor sobre as consultas do MySQL, leia nossa Introdução às consultas no MySQL.
Primeiro, faça login no seu servidor do MySQL como raiz:
- mysql -u root -p
Digite sua senha raiz do MySQL quando solicitado e clique em ENTER
para continuar. Quando você vir o prompt do mysql>
, execute o seguinte comando para criar um banco de dados test_db
:
- Create database test_db;
OutputQuery OK, 1 row affected (0.00 sec)
Em seguida, mude para o test_db
com:
- Use test_db;
OutputDatabase changed
Você começará criando uma tabela de customers
. Essa tabela terá os registros dos clientes, incluindo a customer_id
, o customer_name
e o level
. Haverá dois níveis de clientes: BASIC
e VIP
.
- Create table customers(customer_id BIGINT PRIMARY KEY, customer_name VARCHAR(50), level VARCHAR(50) ) ENGINE=INNODB;
OutputQuery OK, 0 rows affected (0.01 sec)
Agora, adicione alguns registros à tabela customers
. Para fazer isso, execute os comandos um a um:
- Insert into customers (customer_id, customer_name, level )values('1','JOHN DOE','BASIC');
- Insert into customers (customer_id, customer_name, level )values('2','MARY ROE','BASIC');
- Insert into customers (customer_id, customer_name, level )values('3','JOHN DOE','VIP');
Você verá o seguinte resultado após executar cada um dos comandos INSERT
:
OutputQuery OK, 1 row affected (0.01 sec)
Para garantir que os registros de amostra foram inseridos com sucesso, execute o comando SELECT
:
- Select * from customers;
Output+-------------+---------------+-------+
| customer_id | customer_name | level |
+-------------+---------------+-------+
| 1 | JOHN DOE | BASIC |
| 2 | MARY ROE | BASIC |
| 3 | JOHN DOE | VIP |
+-------------+---------------+-------+
3 rows in set (0.00 sec)
Você criará também outra tabela para reter informações relacionadas sobre a conta customers
. A tabela terá os campos customer_id
e status_notes
.
Execute o seguinte comando:
- Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;
Em seguida, você criará uma tabela de sales
(vendas). Esta tabela terá os dados de vendas relacionados a diferentes clientes através da coluna customer_id
:
- Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;
OutputQuery OK, 0 rows affected (0.01 sec)
Você adicionará os dados da amostra aos dados de sales
nos passos próximos, enquanto testa os acionadores. Em seguida, crie uma tabela audit_log
para registrar as atualizações feitas para a tabela de sales
quando você implementar o acionador AFTER UPDATE
no Passo 5:
- Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;
OutputQuery OK, 0 rows affected (0.02 sec)
Com o banco de dados test_db
e as quatro tabelas em funcionamento, siga em frente para trabalhar com os vários acionadores do MySQL no seu banco de dados.
Neste passo, você examinará a sintaxe de um acionador do MySQL, antes da aplicação dessa lógica para criar um acionador BEFORE INSERT
, que valida o campo sales_amount
quando os dados são inseridos na tabela de sales
.
A sintaxe geral para a criação de um acionador do MySQL é mostrada no exemplo a seguir:
DELIMITER //
CREATE TRIGGER [TRIGGER_NAME]
[TRIGGER TIME] [TRIGGER EVENT]
ON [TABLE]
FOR EACH ROW
[TRIGGER BODY]//
DELIMITER ;
A estrutura do acionador inclui:
DELIMITER //
: o delimitador padrão do MySQL é o ;
— ele é necessário para modificá-lo para outra coisa, de modo a permitir que o MySQL trate as linhas seguintes como um comando até que ele atinja seu delimitador personalizado. Neste exemplo, o delimitador é alterado para //
e, em seguida, o delimitador ;
é redefinido no final.
[TRIGGER_NAME]
: um acionador deve ter um nome e é aqui que você inclui o valor.
[TRIGGER TIME]
: um acionador pode ser chamado durante diferentes intervalos de tempo. O MySQL permite que você defina se o acionador iniciará antes ou após uma operação de banco de dados.
[TRIGGER EVENT]
: os acionadores são chamados apenas por operações de INSERT
, UPDATE
e DELETE
. Você pode utilizar qualquer valor aqui, dependendo do que quiser fazer.
[TABLE]
: qualquer acionador que você criar no seu banco de dados do MySQL deve estar associado a uma tabela.
FOR EACH ROW
: essa instrução diz ao MySQL para executar o código do acionador para cada linha que o acionador afeta.
[TRIGGER BODY]
: o código que é executado quando o acionador é chamado tem o nome de trigger body. Pode ser uma única instrução do SQL ou vários comandos. Note que, se estiver executando várias instruções do SQL no corpo do acionador, você deve envolvê-las entre um bloco BEGIN...
END.
Nota: ao criar o corpo do acionador, você pode utilizar as palavras-chave OLD
e NEW
para acessar os valores de coluna antigos e novos inseridos durante uma operação INSERT
, UPDATE
e DELETE
. Em um acionador de DELETE
, apenas a palavra-chave OLD
pode ser usada (que você usará no Passo 4).
Agora, você criará seu primeiro acionador BEFORE INSERT
. Este acionador estará associado à tabela de sales
e será chamado antes de um registro ser inserido para validar o sales_amount
. A função do acionador é a de verificar se o sales_amount
que está sendo inserido no quadro de vendas é maior do que 10000
e de gerar um erro, caso isso seja verdadeiro.
Certifique-se de estar logado no servidor do MySQL. Em seguida, digite os comandos do MySQL seguintes um de cada vez:
- DELIMITER //
- CREATE TRIGGER validate_sales_amount
- BEFORE INSERT
- ON sales
- FOR EACH ROW
- IF NEW.sales_amount>10000 THEN
- SIGNAL SQLSTATE '45000'
- SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';
- END IF//
- DELIMITER ;
Você está usando a instrução IF... THEN... END IF
para avaliar se a quantidade que está sendo fornecida durante a instrução INSERT
está dentro da sua faixa. O acionador consegue extrair o novo valor de sales_amount
que está sendo fornecido, usando a palavra-chave NEW
.
Para gerar uma mensagem de erro genérica, use as linhas seguintes para informar o usuário sobre o erro:
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';
Em seguida, insira um registro com um sales_amount
de 11000
para a tabela de sales
para verificar se o acionador interromperá a operação:
- Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000');
OutputERROR 1644 (45000): Sale has exceeded the allowed amount of 10000.
Este erro mostra que o código do acionador está funcionando como esperado.
Agora, teste um novo registro com um valor de 7500
para verificar se o comando será bem-sucedido:
- Insert into sales(sales_id, customer_id, sales_amount) values('1','1','7500');
Como o valor está dentro da faixa recomendada, você verá o seguinte resultado:
OutputQuery OK, 1 row affected (0.01 sec)
Para confirmar que os dados foram inseridos, execute o seguinte comando:
- Select * from sales;
O resultado confirma que os dados estão na tabela:
Output+----------+-------------+--------------+
| sales_id | customer_id | sales_amount |
+----------+-------------+--------------+
| 1 | 1 | 7500 |
+----------+-------------+--------------+
1 row in set (0.00 sec)
Neste passo, você testou os acionadores para validar dados antes da inserção deles em um banco de dados.
Em seguida, você trabalhará com o acionador AFTER INSERT
para salvar informações relacionadas em tabelas diferentes.
Os acionadores AFTER INSERT
são executados quando os registros são inseridos com sucesso em uma tabela. Essa funcionalidade pode ser usada para executar outras lógicas relacionados a negócios de maneira automática. Por exemplo, em um aplicativo bancário, um acionador AFTER INSERT
pode fechar uma conta de empréstimo quando um cliente terminar de pagar o empréstimo. O acionador pode monitorar todos os pagamentos inseridos em uma tabela de transação e fechar o empréstimo automaticamente assim que o saldo do empréstimo se tornar zero.
Neste passo, você trabalhará com sua tabela de customer_status
usando um acionador AFTER INSERT
para digitar registros de clientes relacionados.
Para criar o acionador AFTER INSERT
, digite os comandos a seguir:
- DELIMITER //
- CREATE TRIGGER customer_status_records
- AFTER INSERT
- ON customers
- FOR EACH ROW
- Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'ACCOUNT OPENED SUCCESSFULLY')//
- DELIMITER ;
OutputQuery OK, 0 rows affected (0.00 sec)
Aqui, você instruirá o MySQL para salvar outro registro para a tabela customer_status
assim que um novo registro de cliente for inserido na tabela customers
.
Agora, insira um novo registro na tabela customers
para confirmar que seu código do acionador será chamado:
- Insert into customers (customer_id, customer_name, level )values('4','DAVID DOE','VIP');
OutputQuery OK, 1 row affected (0.01 sec)
Como o registro foi inserido com sucesso, verifique se um novo registro de status foi inserido na tabela customer_status
:
- Select * from customer_status;
Output+-------------+-----------------------------+
| customer_id | status_notes |
+-------------+-----------------------------+
| 4 | ACCOUNT OPENED SUCCESSFULLY |
+-------------+-----------------------------+
1 row in set (0.00 sec)
O resultado confirma que o acionador foi executado com sucesso.
O acionador AFTER INSERT
é útil no monitoramento do ciclo de vida de um cliente. Em um ambiente de produção, as contas dos clientes podem ser submetidas a diferentes etapas, como a abertura da conta, a suspensão da conta e o fechamento da conta.
Nos passos a seguir, você trabalhará com os acionadores de UPDATE
.
Um acionador BEFORE UPDATE
é semelhante ao acionador BEFORE INSERT
—a diferença é o momento onde eles são chamados. Você pode utilizar o acionador BEFORE UPDATE
para verificar uma lógica de negócios antes de um registro ser atualizado. Para testar isso, você utilizará a tabela customers
, na qual você já inseriu alguns dados.
Você tem dois níveis para seus clientes no banco de dados. Neste exemplo, assim que uma conta de cliente for atualizada para o nível VIP
, a conta não poderá ser rebaixada para o nível BASIC
. Para aplicar essa regra, você criará um acionador BEFORE UPDATE
que executará antes da instrução UPDATE
, como mostrado a seguir. Se um usuário de banco de dados tentar rebaixar um cliente do nível VIP
para o nível BASIC
, será acionada uma exceção definida pelo usuário.
Digite os comandos SQL seguintes um de cada vez para criar o acionador BEFORE UPDATE
:
- DELIMITER //
- CREATE TRIGGER validate_customer_level
- BEFORE UPDATE
- ON customers
- FOR EACH ROW
- IF OLD.level='VIP' THEN
- SIGNAL SQLSTATE '45000'
- SET MESSAGE_TEXT = 'A VIP customer can not be downgraded.';
- END IF //
- DELIMITER ;
Use a palavra-chave OLD
para capturar o nível que o usuário está fornecendo ao executar o comando UPDATE
. Novamente, use a instrução IF...
THEN… END IF para sinalizar uma instrução de erro genérica para o usuário.
Em seguida, execute o comando SQL a seguir, que tenta rebaixar uma conta de cliente associada ao customer_id
de 3
:
- Update customers set level='BASIC' where customer_id='3';
Você verá o seguinte resultado, fornecendo o SET MESSAGE_TEXT
:
OutputERROR 1644 (45000): A VIP customer can not be downgraded.
Se executar o mesmo comando para um cliente de nível BASIC
e tentar atualizar a conta para o nível VIP
, o comando será executado com sucesso:
- Update customers set level='VIP' where customer_id='1';
OutputRows matched: 1 Changed: 1 Warnings: 0
Você usou o acionador BEFORE UPDATE
para aplicar uma regra de negócios. Agora, siga em frente para usar um acionador AFTER UPDATE
para log de auditoria.
Um acionador AFTER UPDATE
é chamado assim que um registro de banco de dados for atualizado com sucesso. Este comportamento torna o acionador adequado para o log de auditoria. Em um ambiente de muitos usuários, o administrador pode querer exibir um histórico de usuários atualizando os registros em uma tabela específica para fins de auditoria.
Você criará um acionador que registra a atividade de atualização da tabela sales
. Nossa tabela audit_log
terá as informações sobre os usuários do MySQL atualizando a tabela de sales
, a date
da atualização e os valores old
e new
da sales_amount
.
Para criar o acionador, execute os comandos SQL seguintes:
- DELIMITER //
- CREATE TRIGGER log_sales_updates
- AFTER UPDATE
- ON sales
- FOR EACH ROW
- Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
- DELIMITER ;
Insira um novo registro para a tabela audit_log
. Use a palavra-chave NEW
para recuperar o valor do sales_id
e o novo sales_amount
. Além disso, use a palavra-chave OLD
para recuperar o sales_amount
anterior, já que deseja registrar ambos os montantes para fins de auditoria.
O comando SELECT USER()
recupera o usuário atual que executa a operação e a instrução NOW()
recupera o valor da data e hora atual do servidor MySQL.
Agora, caso um usuário tente atualizar o valor de qualquer registro na tabela sales
, o acionador log_sales_updates
inserirá um novo registro para a tabela audit_log
.
Vamos criar um novo registro de vendas com uma sales_id
aleatória de 5
e tentar atualizá-lo. Primeiro, insira o registro de vendas com:
- Insert into sales(sales_id, customer_id, sales_amount) values('5', '2','8000');
OutputQuery OK, 1 row affected (0.00 sec)
Em seguida, atualize o registro:
- Update sales set sales_amount='9000' where sales_id='5';
Você verá o seguinte resultado:
OutputRows matched: 1 Changed: 1 Warnings: 0
Agora, execute o comando a seguir para verificar se o acionador AFTER UPDATE
conseguiu registrar um novo registro na tabela audit_log
:
- Select * from audit_log;
O acionador registrou a atualização Seu resultado mostra o sales_amount
e o new amount
previamente registrados com o usuário que atualizou os registros:
Output+--------+----------+-----------------+------------+----------------+---------------------+
| log_id | sales_id | previous_amount | new_amount | updated_by | updated_on |
+--------+----------+-----------------+------------+----------------+---------------------+
| 1 | 5 | 8000 | 9000 | root@localhost | 2019-11-07 09:28:36 |
+--------+----------+-----------------+------------+----------------+---------------------+
1 row in set (0.00 sec)
Você também tem a data e hora que a atualização foi realizada, que são valiosos para fins de auditoria.
Em seguida, você usará o acionador DELETE
para aplicar a integridade da referenciação no nível de banco de dados.
Acionadores do BEFORE DELETE
são chamados antes que uma instrução DELETE
seja executada em uma tabela. Normalmente, esses tipos de acionadores são usados para aplicar a integridade referencial em diferentes tabelas relacionadas. Por exemplo, cada registro da tabela de sales
se relaciona a um customer_id
da tabela customers.
Se um usuário excluísse um registro da tabela customers
que tem um registro relacionado na tabela sales
, você não teria como saber qual era o cliente associado àquele registro.
Para evitar isso, é possível criar um acionador BEFORE DELETE
para aplicar sua lógica. Execute os comandos SQL a seguir, um de cada vez:
- DELIMITER //
- CREATE TRIGGER validate_related_records
- BEFORE DELETE
- ON customers
- FOR EACH ROW
- IF OLD.customer_id in (select customer_id from sales) THEN
- SIGNAL SQLSTATE '45000'
- SET MESSAGE_TEXT = 'The customer has a related sales record.';
- END IF//
- DELIMITER ;
Agora, tente excluir um cliente que tenha um registro de vendas relacionado a ele:
- Delete from customers where customer_id='2';
Como resultado, você verá a seguinte saída:
OutputERROR 1644 (45000): The customer has a related sales record.
O acionador BEFORE DELETE
pode evitar a exclusão acidental de informações relacionadas em um banco de dados.
No entanto, em algumas situações, você pode querer excluir todos os registros associados a um registro em específico de diferentes tabelas relacionadas. Nesta situação, você usaria o acionador AFTER DELETE
, que será testado no próximo passo.
Os acionadores AFTER DELETE
são ativados assim que um registro tenha sido excluído com sucesso. Um exemplo de como se pode utilizar um acionador AFTER DELETE
é uma situação em que o nível de desconto que um determinado cliente recebe é determinado pelo número de vendas realizadas durante um período definido. Se alguns dos registros do cliente forem excluídos da tabela de sales
, o nível de desconto de cliente terá que ser rebaixado.
Outro uso do acionador AFTER DELETE
é o de excluir as informações relacionadas de outra tabela assim que um registro de uma tabela base for excluído. Por exemplo, você definirá um acionador que exclui o registro de clientes se os registros de vendas com o customer_id
relacionado forem excluídos da tabela de sales
. Execute o comando a seguir para criar seu acionador:
- DELIMITER //
- CREATE TRIGGER delete_related_info
- AFTER DELETE
- ON sales
- FOR EACH ROW
- Delete from customers where customer_id=OLD.customer_id;//
- DELIMITER ;
Em seguida, execute o seguinte comando para excluir todos os registros de vendas associados a um customer_id
de 2
:
- Delete from sales where customer_id='2';
OutputQuery OK, 1 row affected (0.00 sec)
Agora, verifique se há registros para o cliente na tabela de sales
:
- Select * from customers where customer_id='2';
Você receberá um resultado em um Empty Set
, já que o registro de cliente associado ao customer_id
de 2
foi excluído pelo acionador:
OutputEmpty set (0.00 sec)
Agora, você usou cada uma das diferentes formas de acionadores para realizar funções específicas. Em seguida, você verá como remover um acionador do banco de dados caso não precise mais dele.
De maneira similar a qualquer outro objeto, você pode excluir acionadores utilizando o comando DROP
. A seguir temos a sintaxe para excluir um acionador:
Drop trigger [TRIGGER NAME];
Por exemplo, para excluir o último acionador AFTER DELETE
que você criou, execute o comando a seguir:
- Drop trigger delete_related_info;
OutputQuery OK, 0 rows affected (0.00 sec)
A necessidade de excluir acionadores surge quando você quiser recriar a estrutura deles. Em tal caso, você pode remover o acionador e redefinir um novo com os diferentes comandos de acionador.
Neste tutorial, você criou, utilizou e excluiu diferentes tipos de acionadores de um banco de dados do MySQL. Utilizando um exemplo de banco de dados relacionados a clientes, você implementou acionadores para diferentes casos de uso, como validação de dados, aplicação de lógica de negócios, log de auditoria e aplicação da integridade referencial.
Para obter mais informações sobre como utilizar seu banco de dados do MySQL, confira o seguinte:
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Sign up for Infrastructure as a Newsletter.
Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.