DEV Community: Elisa

Trabalhando com Triggers na Oracle: Exemplos Práticos

Elisa — Thu, 07 Dec 2023 23:46:42 +0000

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 registrar
REFERENCING:
É 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

Trabalhando com Triggers no Oracle

Elisa — Mon, 13 Nov 2023 19:51:22 +0000

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 que desempenham um papel crucial no gerenciamento de banco de dados e automação de tabelas.

Definição
Uma trigger são mecanismos do tipo procedimento armazenado, que são executados assim que os dados sofrem mudanças num banco de dados. Estes, que são por meio do DML (Data Manipulation Language) utilizando INSERT, UPDATE ou DELETE numa tabela ou view. São engatilhadas em qualquer situação válida dentro do sistema do banco. Também podem ser acionadas por meio do DDL (Data Definition Language), usando CREATE, ALTER e DROP.

Sintaxe de uma Trigger
Para que possamos criar uma trigger, precisamos utilizar a sua declaração, que é:

CREATE TRIGGER

Sua declaração pode variar, de acordo com o tipo de Trigger que você deseja criar, podendo ser um Dml Trigger, Schema Trigger ou Database Trigger.

As Triggers que normalmente são mais usadas são as Triggers de DML, então, vamos ver alguns exemplos com esses casos.

CREATE [OR REPLACE] TRIGGER TRIGGER_NAME 
{BEFORE | AFTER} dml_event ON TABLE_NAME
[FOR EACH ROW] 
  [DECLARE variable] 
  BEGIN 
  pl_sql_code 
  [EXCEPTION exception_code] 
  END;

Vamos ver em detalhes o que cada item dentro da nossa declaração faz:

OR REPLACE: Usada de forma opcional. Caso você use-a, qualquer outra trigger que conter o mesmo nome, será substituída por essa Trigger que você está criando;
nome_trigger: Nome dado a trigger dentro do banco de dados (database);
BEFORE | AFTER: Definide se você deseja que a trigger seja disparada antes ou depois de alguma declaração ou evento;
evento_dml: Esse é o responsável pelo disparo da nossa trigger, ele pode ser usado juntamente com INSERT, UPDATE ou DELETE;
nome_tabela: Esse é o nome da tabela que disparará o evento para nossa trigger;
FOR EACH ROW: Usado para definir de o gatilho será disparado para cada linha de comando, caso não use essa declaração, a trigger só será disparada uma vez;
DECLARE: Caso você necessite de variáveis dentro da sua trigger, você pode usar o declare. Lembrando que elas só podem e serão usadas dentro da trigger;
codigo_pl_sql: Aqui é onde escreveremos o comportamento da nossa trigger e tudo o que ela irá fazer;
EXCEPTION: Nossa exception é o nosso retorno caso haja algum erro, esperado ou não.

Quando escrevemos triggers para dml, a sua finalidade é ser para uma instrução, com isso, precisamos fazer a referenciação aos dados dentro da instrução. Como por exemplo:

Dados que estão sendo inseridos em um INSERT;
Dados sendo atualizados em um UPDATE (podendo ser os dados antigos ou novos);
Dados sendo excluídos em um DELETE.

Podemos referenciá-los com duas variáveis.

:NEW
:OLD

Vamos a um exemplo:

Supondo que você deseja pegar o valor de um salário antes que um UPDATE seja inserido, você pode usar:
:OLD.salario
Caso você deseje pegar o valor de um novo salário após um INSERT, você pode usar:
:NEW.salario

Há diversos modo de utilizarmos Triggers, abaixo, temos uma tabela onde podemos ver as situações na qual podemos utilizá-las com :NEW e :OLD.

Vamos ver agora alguns exemplos de situações em que aplicamos as Triggers.

SINTAXE BEFORE INSERT TRIGGER

CREATE [OR REPLACE] TRIGGER trigger_name 
BEFORE INSERT ON table_name 
[FOR EACH ROW] 
  [DECLARE variables] 
  BEGIN 
  pl_sql_code 
  [EXCEPTION exception_code] 
  END;

Algumas observações:

Você não pode criar uma Trigger BEFORE INSERT em uma VIEW, a menos que seja uma VIEW de edição;
Se você executar uma Trigger de instrução e omitir a cláusula FOR EACH ROW, não poderá ler as variáveis :NEW e :OLD, pois eles não existem para um Trigger INSERT.

SINTAXE AFTER INSERT TRIGGER

CREATE [OR REPLACE] TRIGGER trigger_name 
AFTER INSERT ON table_name 
[FOR EACH ROW] 
[DECLARE variables] 
BEGIN 
  pl_sql_code 
[EXCEPTION exception_code] 
END;

Algumas observações:

Você não pode criar um gatilho AFTER INSERT em uma visualização, a menos que seja uma visualização de edição;
Se você estiver executando um gatilho de instrução (omitindo a cláusula FOR EACH ROW), não poderá ler as variáveis :NEW ou :OLD;
Se você estiver executando um gatilho de linha (usando FOR EACH ROW):
Você pode ler, mas não atualizar os :NEW valores;
Você não pode ler ou atualizar os valores :OLD, pois eles não existem para um gatilho INSERT.

SINTAXE BEFORE UPDATE TRIGGER

CREATE [OR REPLACE] TRIGGER trigger_name 
BEFORE UPDATE ON table_name 
[FOR EACH ROW] 
[DECLARE variables] 
BEGIN 
  pl_sql_code 
[EXCEPTION exception_code] 
END;

Algumas observações:

Um TRIGGER BEFORE UPDATE é um TRIGGER executado antes de uma instrução UPDATE ser executada;
Se você executar uma Trigger de instrução e omitir a cláusula FOR EACH ROW, não poderá ler as variáveis :NEW e :OLD, pois eles não existem para um Trigger INSERT;
Você não pode criar um gatilho BEFORE UPDATE em uma visualização, a menos que seja uma visualização de edição;
Se você estiver executando um gatilho de instrução (omitindo a cláusula FOR EACH ROW), não poderá ler as variáveis :NEW ou :OLD;
Se você estiver executando um gatilho de linha (usando FOR EACH ROW), poderá ler e atualizar os valores :NEW e :OLD.

SINTAXE AFTER UPDATE TRIGGER

CREATE [OR REPLACE] TRIGGER trigger_name 
AFTER UPDATE ON table_name 
[FOR EACH ROW] 
[DECLARE variables] 
BEGIN 
  pl_sql_code 
[EXCEPTION exception_code] 
END;

Algumas observações sobre o AFTER UPDATE:

Você não pode criar um gatilho AFTER UPDATE em uma visualização, a menos que seja uma visualização de edição;
Se você estiver executando um gatilho de instrução (omitindo a cláusula FOR EACH ROW), não poderá ler as variáveis :NEW ou :OLD;
Se você estiver executando um gatilho de linha (usando FOR EACH ROW), poderá ler, mas não atualizar, os valores :NEW e :OLD.

SINTAXE BEFORE DELETE TRIGGER

CREATE [OR REPLACE] TRIGGER trigger_name 
BEFORE DELETE ON table_name 
[FOR EACH ROW] 
[DECLARE variables] 
BEGIN 
  pl_sql_code 
[EXCEPTION exception_code] 
END;

Algumas observações:

Você não pode criar um gatilho BEFORE DELETE em uma visualização, a menos que seja uma visualização de edição;
Se você estiver executando um gatilho de instrução (omitindo a cláusula FOR EACH ROW), não poderá ler os valores :OLD;
Se você estiver executando um gatilho de linha (usando FOR EACH ROW), poderá ler e atualizar os valores :OLD.

SINTAXE AFTER DELETE TRIGGER

CREATE [OR REPLACE] TRIGGER trigger_name 
AFTER DELETE ON table_name 
[FOR EACH ROW] 
[DECLARE variables] 
BEGIN 
  pl_sql_code 
[EXCEPTION exception_code] 
END;

Algumas observações:

Você não pode criar um gatilho AFTER DELETE em uma visualização, a menos que seja uma visualização de edição;
Se você estiver executando um gatilho de instrução (omitindo a cláusula FOR EACH ROW), não poderá ler os valores :OLD;
Se você estiver executando um gatilho de linha (usando FOR EACH ROW), poderá ler, mas não atualizar os valores :OLD.

Quadro de vantagens e desvantagens

Vantagens

Mantém todas as alterações sob controle – UPDATE, DELETE, INSERT- nas tabelas criadas, quando ocorre a inserção/alteração de dados nas tabelas de auditorias;
Permitem uma replicação síncrona nas tabelas;
Operam com autorizações/padrões de segurança nas tabelas;
Fornecem alternativas para executar tarefas agendadas, já que, os triggers automaticamente auxiliam antes ou depois de uma alteração ser feita nos dados de uma tabela.

Desvantagens

Podem impactar no desempenho do sistema, como aumentar a sobrecarga do banco de dados;
Só utilizam troubleshoots estendidas, o que invalida outras validações;
Por usar triggers, caso tenha um uso excessivo e/ou seja um banco de dados grande, implica numa dificuldade de refatorar ou na manutenbilidade do sistema.

Usando Trigger SQL no Mercado
Por seguinte, exemplos de empresas que podem utilizar essa ferramenta, para regras, segurança e velocidade:

Entradas e atualizações de dados
A trigger, pode entrar em uso nesse caso, quando uma empresa de vendas (mercado livre), o qual tem muitas atualizações por causa das compras e mudanças nos valores.
Mudanças de valores
A trigger, pode entrar em uso no caso de uma empresa de investimentos, pois a cada segundo os valores das bolsas são atualizados, sendo as funcionalidades do trigger são os eventos, possibilitando de configurar esses eventos para a cada segundo e verificar os campos dos valores.
Verificar entradas inválidas
Um exemplo simples é uma empresa de finanças que tem entradas de valores constantes, onde é importante utilizar o trigger e as ferramentas dele, para verificação de campos inválidos e evitar não quebrar ou parar o banco de dados.
Geração de valores entres colunas
Um exemplo é uma empresa de cobrança, onde tem que pegar o valor e adicionar os juros, essa é uma utilização do trigger nesse caso.
Sincronização entre tabelas
Exemplo de utilização é em uma empresa que tem o RH, e quando atualizar as informações dos usuários possam refletir em todos o sistema, como alteração de senha ou quando o usuário atualizar a senha, todos os sistemas da empresa devem atualizar essa atualização.

Referências bibliográficas