Como gerenciar e utilizar acionadores de banco de dados do MySQL no Ubuntu 18.04

O autor selecionou a Apache Software Foundation para receber uma doação como parte do programa Write for DOnations.

Introdução

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.

Pré-requisitos

Antes de começar, certifique-se de ter o seguinte:

• Um servidor Ubuntu 18.04 configurado seguindo a Configuração inicial de servidor com o Ubuntu 18.04, incluindo um usuário sudo não raiz.
• Um banco de dados do MySQL funcionando em seu servidor seguindo: Como instalar o MySQL no Ubuntu 18.04
• Credenciais de conta de usuário raiz para seu banco de dados do MySQL.

Passo 1 — Criando um banco de dados de amostra

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;

Output
Query OK, 1 row affected (0.00 sec)

Em seguida, mude para o test_db com:

Use test_db;

Output
Database 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;

Output
Query 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:

Output
Query 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;

Output
Query 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;

Output
Query 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.

Passo 2 — Criando um acionador antes de inserir

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.

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

Output
ERROR 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:

Output
Query 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.

Passo 3 — Criando um acionador após inserir

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 ;

Output
Query 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');

Output
Query 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.

Passo 4 — Criando um acionador antes de atualizar

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:

Output
ERROR 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';

Output
Rows 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.

Passo 5 — Criando um acionador após atualizar

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

Output
Query 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:

Output
Rows 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.

Passo 6 — Criando um acionador antes de excluir

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:

Output
ERROR 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.

Passo 7 — Criando um acionador após excluir

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';

Output
Query 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:

Output
Empty 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.

Passo 8 — Excluindo acionadores

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;

Output
Query 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.

Conclusão

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:

• Como otimizar o MySQL com o cache de consulta no Ubuntu 18.04
• Como implementar a paginação no MySQL com o PHP no Ubuntu 18.04
• Como solucionar problemas no MySQL