Introdução
As triggers são componentes de código PL/SQL, relacionados aos comandos de inserção, atualizar e excluir (INSERT, UPDATE e DELETE), que servem como um tipo de armazenamento, auxiliando, principalmente, na manutenção da integridade dos dados do banco de dados. Com isso em mente, iremos ver mais um pouco sobre esse mecanismo, porém agora, iremos apresentar exemplos práticos focando em cenários de uso e estudos de caso, explicação de termos técnicos, exemplos visuais e como este mecanismo pode ser utilizado numa rotina de trabalho.
Exemplos práticos das Triggers
AFTER DELETE Trigger
Definição
Em síntese, é acionado (engatilhado) automaticamente quando acontece uma exclusão – DELETE - de uma ou mais linhas de uma tabela.
Parâmetros
Nesse tipo de trigger, usando o DELETE, são utilizados os parâmetros de ‘:OLD’ e/ou ‘:NEW’, cujos, representam os antigos (‘OLD’) e novos (‘NEW’) valores em uma linha afetada por uma instrução de manipulação de dados (INSERT, UPDATE, DELETE).
Outro ponto sobre esses dois, é que o ‘:OLD’ na Trigger AFTER DELETE tem como uso de ver os valores da linha afetada antes de sua exclusão. Como o exemplo abaixo:
CREATE OR REPLACE TRIGGER after_delete_example
AFTER DELETE ON sua_tabela
FOR EACH ROW
DECLARE
v_antigo_valor VARCHAR2(50);
BEGIN
v_antigo_valor := :OLD.nome_coluna;
END;
-- O restante do código
/
Com isso, o :OLDfoi utilizado no :OLD.nome_colunaque representa o nome_coluna, porém antes da exclusão (DELETE).
Agora sobre o :NEW, utilizamos quando é referente à valores novos, não é tão utilizada em Triggers de AFTER DELETE, mas quando é, os seus valores são nulos.
Vejamos o seguinte exemplo:
CREATE OR REPLACE TRIGGER after_delete_example
AFTER DELETE ON sua_tabela
FOR EACH ROW
DECLARE
v_novo_valor VARCHAR2(50);
BEGIN
-- O uso de :NEW é permitido, mas seus valores serão nulos após uma DELETE.
v_novo_valor := :NEW.nome_coluna;
END;
-- O restante do código
/
Como podemos observar, o ‘:NEW’ apenas adicionou um novo valor ao ‘nome_coluna’, sendo este, nulo. Além disso, ele não é tanto utilizado no DELETE, e sim, o ‘:OLD’.
Cenários de uso e Estudo de caso
As Triggers podem estar presentes em vários cenários de uso, então, segue abaixo alguns desses cenários, junto de sua implementação e estudo de caso:
1. Auditoria e registro de alterações
- Cenário de uso: Tem que manter a tabela com os dados do histórico de alterações para auxiliar a auditoria.
- Implementação de uso: A trigger AFTER DELETE vai ser usado depois de uma exclusão de um registro, colocando os detalhes dessa ação na tabela de ou de auditoria.
-- Criação da Tabela Principal
CREATE TABLE Produtos (
ProdutoID NUMBER PRIMARY KEY,
Nome VARCHAR2(255),
Preco NUMBER(10, 2)
);
-- Criação da Tabela de Auditoria
CREATE TABLE Produtos_Historico (
HistoricoID NUMBER PRIMARY KEY,
ProdutoID NUMBER,
NomeAnterior VARCHAR2(255),
PrecoAnterior NUMBER(10, 2),
DataExclusao TIMESTAMP
);
-- Trigger AFTER DELETE para Auditoria em Oracle
CREATE OR REPLACE TRIGGER ExclusaoProdutoTrigger
AFTER DELETE ON Produtos
FOR EACH ROW
BEGIN
INSERT INTO Produtos_Historico (ProdutoID, NomeAnterior, PrecoAnterior, DataExclusao)
VALUES (:OLD.ProdutoID, :OLD.Nome, :OLD.Preco, SYSTIMESTAMP);
END;
/
Vamos ver em detalhes o que cada item dentro da nossa declaração faz:
- CREATE OR REPLACE TRIGGER: Cria ou substitui uma trigger.
- CREATE TABLE: Cria uma tabela.
- AFTER DELETE ON Produtos: Indica que o trigger será acionado após a execução de uma instrução DELETE na tabela Produtos.
- FOR EACH ROW: Usado para definir que o gatilho será disparado para cada linha que foi usado o AFTER DELETE.
- :OLD: Usado para representar onde os valores antigos das colunas foram afetados pela instrução DELETE. Visto que, no Oracle este pseudo-registro tem como utilidade referenciar os valores antigos nas triggers AFTER DELETE.
- INSERT INTO: Insere valores dentro das tabelas.
- VALUES: Representa os valores/dados inseridos dentro das tabelas.
2. Desativação lógica em vez de exclusão física
- Cenário de uso: Declarar uma desativação lógica ao invés de implementar uma exclusão física do registro/valor.
- Implementação de uso: A trigger AFTER DELETE será utilizada para deixar o registro dado como “inativo” ou "excluído" por meio de uma alteração de estado.
-- Criação da Tabela Funcionarios
CREATE TABLE Funcionarios (
FuncionarioID NUMBER PRIMARY KEY,
Nome VARCHAR2(255),
Ativo BOOLEAN
);
-- Trigger AFTER DELETE para Exclusão Lógica em Oracle
CREATE OR REPLACE TRIGGER ExclusaoLogicaFuncionarioTrigger
AFTER DELETE ON Funcionarios
FOR EACH ROW
BEGIN
UPDATE Funcionarios SET Ativo = 0 WHERE FuncionarioID = :OLD.FuncionarioID;
END;
/
Vamos ver em detalhes o que cada item dentro da nossa declaração faz:
- CREATE OR REPLACE TRIGGER: Cria ou substitui uma trigger.
- CREATE TABLE: Cria uma tabela.
- Ativo BOOLEAN: Coluna que representa o estado ativo/inativo do funcionário.
- SET Ativo = 0: Desativa o funcionário (exclusão lógica) atualizando o campo Ativo.
3. Integridade referencial e exclusões em cascata:
- Cenário de uso: Declarar que a exclusão (DELETE) de um registro/valor de uma tabela principal reflita nas outras tabelas que estiverem relacionadas.
- Implementação de uso: A trigger AFTER DELETE irá excluir os valores/registros que estiverem associados em outras tabelas, mantendo a integridade referencial.
-- Criação da Tabela Clientes
CREATE TABLE Clientes (
ClienteID NUMBER PRIMARY KEY,
Nome VARCHAR2(255)
);
-- Criação da Tabela Pedidos
CREATE TABLE Pedidos (
PedidoID NUMBER PRIMARY KEY,
ClienteID NUMBER REFERENCES Clientes(ClienteID),
ValorTotal NUMBER(10, 2)
);
-- Trigger AFTER DELETE para Integridade Referencial em Oracle
CREATE OR REPLACE TRIGGER ExclusaoClienteTrigger
AFTER DELETE ON Clientes
FOR EACH ROW
BEGIN
DELETE FROM Pedidos WHERE ClienteID = :OLD.ClienteID;
END;
/
Vamos ver em detalhes o que cada item dentro da nossa declaração faz:
- CREATE OR REPLACE TRIGGER: Cria ou substitui uma trigger.
- CREATE TABLE: Cria uma tabela.
- REFERENCES Clientes(ClienteID): Declara uma chave estrangeira na tabela Pedidos, referenciando a coluna ClienteID na tabela Clientes.
- DELETE FROM Pedidos WHERE ClienteID = :OLD.ClienteID;: Excluí os valores datados na tabela Pedidos, os quais, pertencem ao Cliente excluído.
4. Limpeza de dados relacionados:
- Cenário de uso: Os dados que estiverem relacionados/dependentes com registros principais, serão excluídos automaticamente assim que os valores principais forem deletados.
- Implementação de uso: Utilizar trigger AFTER DELETE para remover valores de uma vez em tabelas relacionadas, evitando dados órfãos.
-- Criação da Tabela Departamentos
CREATE TABLE Departamentos (
DepartamentoID NUMBER PRIMARY KEY,
Nome VARCHAR2(255)
);
-- Criação da Tabela Funcionarios
CREATE TABLE Funcionarios (
FuncionarioID NUMBER PRIMARY KEY,
Nome VARCHAR2(255),
DepartamentoID NUMBER REFERENCES Departamentos(DepartamentoID)
);
-- Trigger AFTER DELETE para Limpeza de Dados Relacionados em Oracle
CREATE OR REPLACE TRIGGER LimpezaFuncionariosTrigger
AFTER DELETE ON Departamentos
FOR EACH ROW
BEGIN
DELETE FROM Funcionarios WHERE DepartamentoID = :OLD.DepartamentoID;
END;
/
Vamos ver em detalhes o que cada item dentro da nossa declaração faz:
- CREATE OR REPLACE TRIGGER: Cria ou substitui uma trigger.
- CREATE TABLE: Cria uma tabela.
- REFERENCES Departamentos(DepartamentoID): Declara uma chave estrangeira na tabela Funcionarios, referenciando a coluna DepartamentoID na tabela Departamentos.
- DELETE FROM Funcionarios WHERE DepartamentoID = :OLD.DepartamentoID;: Exclusão automática dos valores que estiverem na tabela Funcionarios, os quais, já pertencem ao Departamento, anteriormente, excluído.
Explicação de termos técnicos
Até que, é bem claro que existem vários termos técnicos associados a triggers AFTER DELETE, portanto, segue abaixo alguns termos mais básicos e comuns:
Logging (Registro):
Ele desempenha o papel de registrar as informações importantes, já que falamos do AFTER DELETE, temos o detalhamento sobre as exclusões de registros/valores que foram feitas nas tabelas. registro (logging) é uma prática comum em triggers para registrarREFERENCING:
É um auxiliador na hora de associar nomes alternativos (aliases), acompanhando de um OLD ou NEW, um exemplo seria no caso de OLD, você pode usar o REFERECING para referenciar um estado anterior de uma linha do banco de dados do sistema.PL/SQL:
Se trata de uma linguagem da Oracle, que é utilizada para triggers e outros tipos de blocos de códigos dentro dos banco de dados. As triggers são comumentes escritas para automatizar alguma tarefa específica.
Exemplos práticos
Como primeiro exemplo, vamos supor que você queira criar uma tabela “Clientes” e a outra “Pedidos”, sendo que ambas estão relacionadas. A partir disso, você percebe que caso um cliente for excluído, todo e qualquer pedido relacionado a ele tem que ser deletado igualmente. E é aí que o AFTER DELETE pode te auxiliar nessa tarefa:
-- Criação da tabela clientes
CREATE TABLE clientes (
cliente_id NUMBER PRIMARY KEY,
nome VARCHAR2(50),
email VARCHAR2(100)
);
-- Criação da tabela pedidos
CREATE TABLE pedidos (
pedido_id NUMBER PRIMARY KEY,
cliente_id NUMBER REFERENCES clientes(cliente_id),
valor NUMBER
);
-- Criação da trigger AFTER DELETE
CREATE OR REPLACE TRIGGER after_delete_cliente
AFTER DELETE ON clientes
FOR EACH ROW
BEGIN
-- Excluir todos os pedidos associados ao cliente excluído
DELETE FROM pedidos WHERE cliente_id = :OLD.cliente_id;
DBMS_OUTPUT.PUT_LINE('Trigger AFTER DELETE executada com sucesso.');
END;
/
Acima temos a nossa trigger chamada after_delete_cliente que vai ser engatilhada assim que ocorrer uma exclusão em alguma linha da tabela Clientes. Além disso, podemos observar que temos um bloco do PL/SQL que vai acionar um DELETE dentro da trigger na tabela de Pedidos, ou seja, ali vai acontecer o que o usuário queria: excluir todo e qualquer pedido relacionado/associado ao cliente que foi excluído. Um detalhe que expõe que está funcionando e que sua função está voltada a informar ao usuário é quando mostra a mensagem "Trigger AFTER DELETE executada com sucesso." a qual, é exibida usando DBMS_OUTPUT.PUT_LINE.
Ainda tem algumas outras dúvidas sobre outras situações que o AFTER DELETE pode ser útil? Então vamos para um segundo exemplo. Imagina que você tem um banco de dados com duas tabelas, uma chamada Funcionarios e a outra historico_salarios. Só que, você quer que quando ocorrer uma exclusão na tabela Funcionarios, um registro no histórico de salários ocorra antes mesmo de remover esse funcionário. Tendo isso em vista, vamos ver uma forma de escrever isso em bloco de código:
-- Criação da tabela funcionarios
CREATE TABLE funcionarios (
funcionario_id NUMBER PRIMARY KEY,
nome VARCHAR2(50),
salario NUMBER
);
-- Criação da tabela historico_salarios
CREATE TABLE historico_salarios (
historico_id NUMBER PRIMARY KEY,
funcionario_id NUMBER REFERENCES funcionarios(funcionario_id),
salario_anterior NUMBER,
data_modificacao DATE
);
-- Criação da trigger AFTER DELETE
CREATE OR REPLACE TRIGGER after_delete_funcionario
AFTER DELETE ON funcionarios
FOR EACH ROW
DECLARE
salario_anterior NUMBER;
BEGIN
-- Armazenar o salário anterior no histórico_salarios
salario_anterior := :OLD.salario;
INSERT INTO historico_salarios (funcionario_id, salario_anterior, data_modificacao)
VALUES (:OLD.funcionario_id, salario_anterior, SYSDATE);
DBMS_OUTPUT.PUT_LINE('Trigger AFTER DELETE executada com sucesso.');
END;
/
Aqui podemos ver que a trigger after_delete_funcionario é a que desempenha a função que você queria, pois assim que ela é engatilhada, ocorre a exclusão de uma linha na tabela Funcionários. Enquanto o salário anterior do funcionário vai ficar armazenado dentro da trigger.
BEFORE DELETE Trigger
Definição
É uma função automática que verificar eventos de exclusão e executa a exclusão dos dados ou registros.
Parâmetros
No trigger BEFORE DELETE, são usados alguns parâmetros como o :OLD e o NEW que são pseudo-registros referidos para acessar dados em nível de linha ao usar o gatilho em nível de linha.
- :old - refere-se ao valor antigo
- :new - refere-se ao novo valor
Exemplo:
CREATE OR REPLACE TRIGGER Print_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab
FOR EACH ROW
WHEN (new.Empno > 0)
DECLARE sal_diff number;
BEGIN
sal_diff := :new.sal - :old.sal;
dbms_output.put('Old salary: ' || :old.sal);
dbms_output.put(' New salary: ' || :new.sal);
dbms_output.put_line(' Difference ' || sal_diff);
END;
Cenários de uso e Estudo de caso
- Registro de Logs: Registar detalhes sobre as exclusões feitas, exemplo um histórico de navegação.
Exemplo de uso:
CREATE OR REPLACE TRIGGER log_before_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_log (action, table_name, record_id, timestamp) VALUES ('DELETE', 'employees', :OLD.employee_id, SYSTIMESTAMP);
END;
No código acima toda vez que a função audit_log é chamada, depois de uma exclusão na tabela de “employees”.
Explicação de termos técnicos
Os termos técnicos do Trigger BEFORE DELETE, segue as seguintes técnicas para utilização nos projetos:
- FOR EACH ROW: É uma função propria que u, BEFORE DELETE TRIGGER, será executado por cada linha afetada pela função ou operação “DELETE”.
- BEGIN E END: Essa função é responsável por delimitar um certo bloco de código de um trigger. Quando entra no BEGIN e END, é responsável por toda a lógica de quando um trigger BEFORE DELETE, for executado.
- OLD: Essa OLD, ele é uma referência de valores antigos, antes das operações SQL, que ajuda a olhar valores já existentes nas colunas que serão deletadas, o OLD, também é útil para comparar valores antigos com certas condições.
Exemplos práticos
CREATE OR REPLACE TRIGGER before_delete_cliente
BEFORE DELETE ON clientes
FOR EACH ROW
BEGIN
-- Registra a exclusão na tabela de auditoria
INSERT INTO auditoria_clientes (cliente_id, acao, data)
VALUES (:OLD.cliente_id, 'Exclusão', SYSDATE);
END;
Nesse exemplo acima, esse BEFORE DELETE, é aplicado para inserir um registro na tabela auditoria sempre que cliente for excluído.
AFTER UPDATE Trigger
Definição
É um gatilho que é disparado APÓS o UPDATE de uma tabela que foi anteriormente especificada na declaração da TRIGGER.
Parâmetros
Nesse tipo de trigger, usando o UPDATE, são utilizados os parâmetros de :OLD e/ou :NEW, cujos, representam os antigos (OLD) e novos (NEW) valores em uma linha afetada por uma instrução de manipulação de dados (INSERT, UPDATE, DELETE).
Outro ponto sobre esses dois, é que o :OLD na Trigger AFTER UPDATE nos dá a possibilidade de fazer uso dos valores da linha afetada antes de sua atualização. Como o exemplo abaixo:
Cenários de uso e Estudo de caso
Vamos supor que tenhamos um sistema de gerenciamento de funcionários. Nesse sistema, precisaremos fazer alterações no salário dos mesmos, porém, temos a necessidade de que caso o novo salário inserido seja maior que o antigo, que tenhamos um log(mensagem) dessa alteração.
Explicação de termos técnicos
- CREATE OR REPLACE TRIGGER: Cria ou substitui uma trigger.
- AFTER UPDATE ON: Executará a trigger somente após uma atualização (update) na tabela aplicada.
- FOR EACH ROW: Executará o código do escopo para cada linha.
- BEGIN: Define o ponto inicial do código da trigger. -IF (:NEW.salario > :OLD.salario): Realizará uma condição ontem só será executado o código do escopo caso o :NEW.salario seja maior que o :OLD.salario.
- THEN: Realiza código caso a condição do IF seja veradeira.
- END IF: Encerra o escopo da condição.
Exemplos práticos
Registros de Logs:
CREATE OR REPLACE TRIGGER log_aumento_salario
AFTER UPDATE ON funcionarios
FOR EACH ROW
BEGIN
IF (:NEW.salario > :OLD.salario) THEN
Registrar_aumento_salario(:NEW.id, :NEW.salario - :OLD.salario);
END IF;
Neste cenário, temos uma trigger que cria logs quando acontece um update de salário na tabela funcionários. Porém, a trigger só registra um aumento quando o :NEW.salario é maior que o :OLD. Salario.
BEFORE UPDATE Trigger
Definição
Basicamente o BEFORE UPDATE Trigger é um mecanismo que mudar os registros/dados antes mesmo de acontecer uma atualização - UPDATE - em alguma linha na tabela de um banco de dados.
Parâmetros
Os parâmetros do BEFORE UPDATE, são OLD e o NEW, são usados para acessar valores novos e antigos durante a execução da trigger, e podendo ser manipulados.
Cenários de uso e Estudo de caso
Vamos imaginar que temos um sistema gerenciador de estoque de produtos, como todo sistema de estoque, temos que fazer atualizações frequentes no número de produtos para manter o estoque sempre atualizado, porém, temos uma regra de negócio, nosso estoque só pode ser atualizado caso o número de produtos seja maior que o mínimo definido pelo usuário. Vejamos na prática!
Exemplos práticos
Agora vamos ilustrar alguns conceitos de como seria o BEFORE UPDATE Trigger num banco de dados. Suponhamos que exista uma tabela chamada produtos, a qual precisa verificar automaticamente a situação do estoque, já que caso algum produto fique abaixo da do mínimo permitido. Para resolver isso, precisamos criar um trigger que vai quando for acionado, irá impedir que ocorra essa atualização:
-- Criar a tabela de produtos
CREATE TABLE produtos (
id INT PRIMARY KEY,
nome VARCHAR(255),
estoque INT,
estoque_minimo INT
);
-- Criar um BEFORE UPDATE Trigger
DELIMITER //
CREATE TRIGGER antes_de_atualizar_produto
BEFORE UPDATE ON produtos
FOR EACH ROW
BEGIN
-- Verificar se a atualização reduz o estoque abaixo do mínimo permitido
IF NEW.estoque < NEW.estoque_minimo THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Atualização não permitida. Estoque abaixo do mínimo.';
END IF;
END;
//
DELIMITER ;
Vamos ver em detalhes o que cada item dentro da nossa declaração faz:
- CREATE TRIGGER: Cria um novo gatilho (trigger).
- antes_de_atualizar_produto: O nome da trigger que foi escolhido para o exemplo, pode ser personalizado.
- BEFORE UPDATE ON produtos: A trigger é engatilhada antes de uma atualização na tabela produtos.
- FOR EACH ROW: Define que o gatilho será executado para cada linha afetada pela operação de atualização.
- BEGIN...END: Indica o limite do bloco de código que será executado pela trigger.
- IF...THEN...END IF: Um condicional que analisa se a condição for verdadeira ou não.
- SIGNAL SQLSTATE '45000': Declara uma exceção personalizada se a condição IF for verdadeira.
AFTER INSERT Trigger
Definição
Se trata de uma ferramenta que depois que acontece uma operação de inserção - INSERT - de novos registros/dados numa tabela, a trigger será engatilhada.
Parâmetros
No AFTER INSERT, é muito usado o parâmetro NEW, onde o NEW representa valores novos que são inseridos nas colunas das tabelas depois da execução de inserção de valores.
Cenários de uso e Estudo de caso
Um cenário ideal para explicar de forma simples e intuitiva o AFTER INSERT é uma funcionalidade de Notificação, onde os novos dados são inseridos, e o AFTER INSERT, entra nesse exemplo quando é inserido um novo dado, pois ele chama e aciona um processo de envio de e-mail.
Exemplos práticos
Ainda tem algumas outras dúvidas sobre outras situações que o AFTER INSERT Trigger pode ser útil? Então vamos para um segundo exemplo. Neste próximo exemplo, vamos supor que exista uma tabela chamada pedidos e você precisa que sempre que um novo pedido é inserido na tabela pedidos, um gatilho - AFTER INSERT Trigger - seja engatilhado, ou seja, que ocorra uma atualização simultânea no status de envio para 'Aguardando Envio'. Com isso, vejamos como seria feito:
-- Criar um AFTER INSERT Trigger
DELIMITER //
CREATE TRIGGER apos_inserir_pedido
AFTER INSERT ON pedidos
FOR EACH ROW
BEGIN
-- Atualizar automaticamente o status de envio para 'Aguardando Envio'
UPDATE pedidos
SET status_envio = 'Aguardando Envio'
WHERE id = NEW.id;
END;
//
DELIMITER ;
Vamos ver em detalhes o que cada item dentro da nossa declaração faz:
- CREATE TRIGGER: Cria um novo gatilho.
- apos_inserir_pedido: Nome do gatilho desse banco de dados.
- AFTER INSERT ON pedidos: Indica que o gatilho é acionado após uma inserção na tabela "pedidos".
- FOR EACH ROW: Usado para definir que o gatilho será disparado para cada linha que foi usado o BEFORE UPDATE.
- BEGIN...END: São comandos para limitar o começo e final do código que será executado pela trigger.
- UPDATE pedidos:Atualiza a tabela "pedidos".
- SET status_envio = 'Aguardando Envio': Um novo valor a coluna status_envio para Aguardando Envio.
- WHERE id = NEW.id: Restringe a atualização apenas ao registro recém-inserido (referenciado por NEW).
- DELIMITER: Define um limite para instruções SQL mais complexas, permitindo que o ponto e vírgula (;) seja utilizado dentro do bloco de código do gatilho sem ser interpretado como o final da instrução.
Considerações finais
Em síntese, as triggers são um componente fundamental e de grande importância no desempenho, automatização, gerenciamento e construção de banco de dados. Portanto, se trata de uma ferramenta muito útil ao construir e gerenciar um sistema, especialmente, quando se trata de controle. Todas as Triggers apresentadas aqui acima desempenham papéis cruciais nos bancos de dados, proporcionando um maior controle sobre as operações realizadas nas tabelas.
Referências bibliográficas
Top comments (0)