Automação de Gatilhos SQLite

Introdução

Neste laboratório, você explorará a automação de gatilhos (triggers) SQLite. Aprenderá a criar, validar e gerenciar gatilhos, que executam automaticamente instruções SQL em resposta a eventos específicos em uma tabela. Este laboratório irá guiá-lo na criação de logs de auditoria e no gerenciamento eficaz de gatilhos.

Criar Tabelas: employees e employee_audit

Nesta etapa, você criará duas tabelas: employees para armazenar dados de funcionários e employee_audit para registrar atualizações salariais.

Primeiro, abra o shell SQLite em seu terminal.

sqlite3 /home/labex/project/test.db

Este comando abre o shell SQLite e conecta-se a um arquivo de banco de dados chamado test.db. Se o arquivo não existir, o SQLite o criará.

Agora, crie a tabela employees com a seguinte estrutura:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    salary REAL
);

Este comando SQL cria uma tabela chamada employees com três colunas:

• id: Um inteiro que serve como chave primária (primary key) para cada funcionário.
• name: Um campo de texto para armazenar o nome do funcionário.
• salary: Um número real para armazenar o salário do funcionário.

Em seguida, crie a tabela employee_audit:

CREATE TABLE employee_audit (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER,
    old_salary REAL,
    new_salary REAL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Esta tabela armazenará um log das alterações salariais. As colunas são:

• id: Um identificador único para cada entrada de auditoria, incrementando automaticamente.
• employee_id: O ID do funcionário cujo salário foi alterado.
• old_salary: O salário antes da atualização.
• new_salary: O salário após a atualização.
• updated_at: Um timestamp indicando quando a atualização ocorreu.

Inserir Dados de Exemplo na Tabela employees

Nesta etapa, você inserirá alguns dados de exemplo na tabela employees. Esses dados serão usados para testar o gatilho (trigger) que você criará na próxima etapa.

Execute os seguintes comandos SQL para inserir três registros de funcionários:

INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 50000.00);
INSERT INTO employees (id, name, salary) VALUES (2, 'Bob', 60000.00);
INSERT INTO employees (id, name, salary) VALUES (3, 'Charlie', 70000.00);

Esses comandos adicionam três linhas à tabela employees, com os valores especificados de id, name e salary.

Para confirmar que os dados foram adicionados corretamente, execute este comando para visualizar todos os registros na tabela:

SELECT * FROM employees;

Saída Esperada:

1|Alice|50000.0
2|Bob|60000.0
3|Charlie|70000.0

Esta saída mostra o id, name e salary para cada registro.

Criar um Trigger: salary_update_trigger

Agora, você criará um gatilho que registra automaticamente as atualizações salariais na tabela employee_audit.

Execute o seguinte comando SQL para criar o gatilho:

CREATE TRIGGER salary_update_trigger
AFTER UPDATE OF salary ON employees
BEGIN
    INSERT INTO employee_audit (employee_id, old_salary, new_salary)
    VALUES (OLD.id, OLD.salary, NEW.salary);
END;

Este comando cria um gatilho chamado salary_update_trigger. Este gatilho será executado automaticamente após qualquer atualização na coluna salary da tabela employees.

• AFTER UPDATE OF salary ON employees: Especifica que o gatilho será ativado após uma atualização na coluna salary na tabela employees.
• BEGIN ... END: Encerra as instruções SQL a serem executadas quando o gatilho é ativado.
• INSERT INTO employee_audit ...: Insere um novo registro na tabela employee_audit com o ID do funcionário, o salário antigo e o novo salário.
  • OLD.id: Refere-se ao id da linha antes da atualização.
  • OLD.salary: Refere-se ao salary antes da atualização.
  • NEW.salary: Refere-se ao salary após a atualização.

Testar o Trigger

Nesta etapa, você testará o gatilho atualizando o salário de um funcionário e, em seguida, verificando se um novo registro é adicionado à tabela employee_audit.

Vamos atualizar o salário de Bob:

UPDATE employees SET salary = 65000.00 WHERE id = 2;

Este comando atualiza a coluna salary para o funcionário com id 2 (Bob) para 65000.00.

Agora, consulte a tabela employee_audit para ver se o gatilho funcionou:

SELECT * FROM employee_audit;

Saída Esperada:

1|2|60000.0|65000.0|...

Você deve ver uma linha na tabela employee_audit com o ID do funcionário de Bob, o salário antigo e o novo salário. A coluna updated_at mostrará o carimbo de data/hora da atualização. O carimbo de data/hora exato variará dependendo de quando você executou a atualização.

Remover o Trigger

Nesta etapa, você aprenderá como remover ou excluir um gatilho do seu banco de dados SQLite.

Para remover o salary_update_trigger, execute o seguinte comando:

DROP TRIGGER salary_update_trigger;

Este comando remove o salary_update_trigger do banco de dados.

Para verificar se o gatilho foi removido, você pode consultar a tabela sqlite_master:

SELECT name FROM sqlite_master WHERE type='trigger' AND name='salary_update_trigger';

Esta consulta deve retornar um conjunto de resultados vazio, indicando que o gatilho não existe mais.

Agora, vamos tentar atualizar o salário de Alice para confirmar que o gatilho foi realmente removido:

UPDATE employees SET salary = 55000.00 WHERE id = 1;

Em seguida, verifique a tabela employee_audit:

SELECT * FROM employee_audit;

Você deve ver que nenhuma nova entrada foi adicionada à tabela employee_audit, confirmando que o gatilho foi removido com sucesso.

Finalmente, saia do shell SQLite:

.exit

Resumo

Neste laboratório, você aprendeu como criar gatilhos (triggers) SQLite que executam automaticamente instruções SQL em resposta a eventos de atualização em uma tabela específica. Você criou uma tabela employees e uma tabela employee_audit. Em seguida, você criou um gatilho chamado salary_update_trigger que registra as atualizações de salário na tabela employee_audit. Finalmente, você aprendeu como remover um gatilho.