Automação de Triggers no PostgreSQL

Introdução

Neste laboratório, você aprenderá como automatizar tarefas no PostgreSQL usando triggers (gatilhos). O laboratório se concentra na criação de uma função de trigger usando PL/pgSQL para registrar automaticamente as alterações salariais em uma tabela employees em uma tabela separada salary_changes.

Você será guiado através da escrita da função de trigger, vinculando-a a um evento de tabela (especificamente, uma atualização na tabela employees), testando o comportamento do trigger e, finalmente, desabilitando e removendo o trigger.

Criar Tabelas e a Função de Trigger

Nesta etapa, você criará as tabelas employees e salary_changes e, em seguida, definirá a função log_salary_change usando PL/pgSQL.

Primeiro, conecte-se ao banco de dados PostgreSQL como o usuário postgres:

sudo -u postgres psql

Agora, crie a tabela employees:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

Este comando cria uma tabela com uma coluna id (chave primária), name e salary.

Em seguida, crie a tabela salary_changes para armazenar o histórico de alterações salariais:

CREATE TABLE salary_changes (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    change_date TIMESTAMP
);

Agora, vamos escrever a função de trigger usando PL/pgSQL. Esta função será executada antes de uma operação de atualização na tabela employees. Ela registrará os valores de salário antigo e novo na tabela salary_changes.

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)
        VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Este código define uma função chamada log_salary_change. Ele verifica se o salário foi alterado. Se foi, ele insere uma nova linha na tabela salary_changes com o ID do funcionário, salário antigo, salário novo e o timestamp atual.

Criar o Trigger

Nesta etapa, você vinculará a função log_salary_change à tabela employees para que ela seja executada automaticamente quando uma atualização salarial ocorrer.

Crie o trigger usando o seguinte comando SQL:

CREATE TRIGGER salary_change_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

Este comando cria um trigger chamado salary_change_trigger. Ele especifica que o trigger deve ser ativado antes de uma operação UPDATE na tabela employees. A cláusula FOR EACH ROW indica que a função de trigger deve ser executada para cada linha que for atualizada.

Testar o Trigger

Nesta etapa, você testará o comportamento do trigger. Você inserirá dados na tabela employees e, em seguida, atualizará o salário para ver se o trigger registra corretamente as alterações na tabela salary_changes.

Primeiro, insira alguns dados iniciais na tabela employees:

INSERT INTO employees (name, salary) VALUES ('Alice', 50000.00);
INSERT INTO employees (name, salary) VALUES ('Bob', 60000.00);

Agora, atualize o salário de Alice:

UPDATE employees SET salary = 55000.00 WHERE name = 'Alice';

Para verificar se o trigger funcionou corretamente, consulte a tabela salary_changes:

SELECT * FROM salary_changes;

Você deve ver uma linha na tabela salary_changes com as informações sobre a alteração salarial de Alice.

Em seguida, atualize o salário de Bob para o mesmo valor que ele já possui:

UPDATE employees SET salary = 60000.00 WHERE name = 'Bob';

Como o salário não está realmente mudando, o trigger não deve inserir uma nova linha na tabela salary_changes. Vamos verificar isso:

SELECT * FROM salary_changes;

Você ainda deve ver apenas a linha relacionada à alteração salarial de Alice.

Finalmente, insira um novo funcionário:

INSERT INTO employees (name, salary) VALUES ('Charlie', 70000.00);

Inserir um novo funcionário não deve acionar o trigger log_salary_change, pois ele só está configurado para disparar em eventos UPDATE. Vamos confirmar isso verificando a tabela salary_changes novamente:

SELECT * FROM salary_changes;

Você ainda deve ver apenas a linha relacionada à alteração salarial de Alice.

Desativar e Remover o Trigger

Nesta etapa, você aprenderá como desativar e remover um trigger no PostgreSQL.

Primeiro, desative o salary_change_trigger:

ALTER TABLE employees DISABLE TRIGGER salary_change_trigger;

Agora, atualize o salário de Alice novamente:

UPDATE employees SET salary = 60000.00 WHERE name = 'Alice';

Verifique a tabela salary_changes:

SELECT * FROM salary_changes;

Você ainda deve ver apenas a linha da atualização anterior. Isso confirma que o trigger está desativado.

Para reativar o trigger, você pode usar o seguinte comando:

ALTER TABLE employees ENABLE TRIGGER salary_change_trigger;

Finalmente, para remover permanentemente o trigger, remova-o usando o seguinte comando:

DROP TRIGGER salary_change_trigger ON employees;

Resumo

Neste laboratório, você aprendeu como automatizar tarefas no PostgreSQL usando triggers. Você criou uma função de trigger usando PL/pgSQL para registrar as alterações salariais em uma tabela employees em uma tabela salary_changes. Você escreveu a função, vinculou-a a um evento de tabela, testou-a e a desativou/removeu.