Triggers no Oracle: Uma abordagem profunda e detalhada

Triggers no Oracle: Uma abordagem profunda e detalhada

Os “triggers” no Oracle Database são um recurso poderoso para automatizar tarefas, controlar o fluxo de eventos e manter a integridade dos dados. Neste artigo, exploraremos os “triggers” em profundidade, com foco no Oracle 19c. Abordaremos os conceitos fundamentais, os tipos de “triggers” (DML triggers, system triggers, SCHEMA triggers, DATABASE triggers e INSTEAD OF CREATE triggers), forneceremos exemplos práticos e explicaremos como habilitar/desabilitar “triggers”. Além disso, discutiremos o desafiador conceito de “mutating trigger” e como resolvê-lo.

O que são “triggers” no Oracle?

Os “triggers” no Oracle são stored procedures que são automaticamente acionados em resposta a eventos específicos no banco de dados. Eles podem ser usados para executar ações antes ou após operações de manipulação de dados (DML) como INSERT, UPDATE e DELETE, bem como em eventos de nível do sistema, eventos de esquema (SCHEMA triggers) e eventos de banco de dados (DATABASE triggers).

Tipos de “triggers” no Oracle

DML Triggers

BEFORE INSERT Trigger: Acionado antes de uma operação de inserção. Pode ser usado para validar dados ou executar ações preliminares.

Exemplo de BEFORE INSERT Trigger:

CREATE OR REPLACE TRIGGER before_insert_example
BEFORE INSERT ON minha_tabela
FOR EACH ROW
BEGIN
   IF :NEW.valor > 1000 THEN
      :NEW.valor := 1000; -- Limita o valor a 1000
   END IF;
END;

AFTER INSERT Trigger: Acionado após uma operação de inserção. Útil para auditoria ou ações de pós-inserção.

Exemplo de AFTER INSERT Trigger:

CREATE OR REPLACE TRIGGER after_insert_example
AFTER INSERT ON minha_tabela
FOR EACH ROW
BEGIN
   INSERT INTO registro_de_auditoria (usuario, acao, data)
   VALUES (USER, 'Inserção', SYSDATE);
END;

BEFORE UPDATE Trigger: Acionado antes de uma operação de atualização. Pode ser usado para validar dados ou executar ações preliminares.

Exemplo de BEFORE UPDATE Trigger:

CREATE OR REPLACE TRIGGER before_update_example
BEFORE UPDATE ON minha_tabela
FOR EACH ROW
BEGIN
   IF :NEW.valor < :OLD.valor THEN
      RAISE_APPLICATION_ERROR(-20001, 'Não é permitido reduzir o valor.');
   END IF;
END;

AFTER UPDATE Trigger: Acionado após uma operação de atualização. Pode ser usado para auditoria ou ações de pós-atualização.

Exemplo de AFTER UPDATE Trigger:

CREATE OR REPLACE TRIGGER after_update_example
AFTER UPDATE ON minha_tabela
FOR EACH ROW
BEGIN
   INSERT INTO registro_de_auditoria (usuario, acao, data)
   VALUES (USER, 'Atualização', SYSDATE);
END;

BEFORE DELETE Trigger: Acionado antes de uma operação de exclusão. Pode ser usado para validar a exclusão ou executar ações preliminares.

Exemplo de BEFORE DELETE Trigger:

CREATE OR REPLACE TRIGGER before_delete_example
BEFORE DELETE ON minha_tabela
FOR EACH ROW
BEGIN
   IF :OLD.status = 'FINALIZADO' THEN
      RAISE_APPLICATION_ERROR(-20001, 'Não é permitido excluir registros finalizados.');
   END IF;
END;

AFTER DELETE Trigger: Acionado após uma operação de exclusão. Pode ser usado para auditoria ou ações de pós-exclusão.

Exemplo de AFTER DELETE Trigger:

CREATE OR REPLACE TRIGGER after_delete_example
AFTER DELETE ON minha_tabela
FOR EACH ROW
BEGIN
   INSERT INTO registro_de_auditoria (usuario, acao, data)
   VALUES (USER, 'Exclusão', SYSDATE);
END;

System Triggers

AFTER LOGON Trigger: Acionado após o logon de um usuário. Pode ser usado para rastreamento de logons.

Exemplo de AFTER LOGON Trigger:

CREATE OR REPLACE TRIGGER after_logon_example
AFTER LOGON ON DATABASE
BEGIN
   INSERT INTO registro_logon (usuario, data_logon)
   VALUES (USER, SYSDATE);
END;

AFTER STARTUP Trigger: Acionado após a inicialização do banco de dados. Útil para ações de inicialização personalizadas.

Exemplo de AFTER STARTUP Trigger:

CREATE OR REPLACE TRIGGER after_startup_example
AFTER STARTUP ON DATABASE
BEGIN
   INSERT INTO registro_inicializacao (data_inicializacao)
   VALUES (SYSDATE);
END;

SCHEMA Triggers

AFTER CREATE SCHEMA Trigger: Acionado após a criação de um esquema no banco de dados.

Exemplo de AFTER CREATE SCHEMA Trigger:

CREATE OR REPLACE TRIGGER after_create_schema_example
AFTER CREATE SCHEMA ON DATABASE
BEGIN
   DBMS_OUTPUT.PUT_LINE('Novo esquema criado.');
END;

DATABASE Triggers

AFTER DATABASE START Trigger: Acionado após a inicialização do banco de dados.

Exemplo de AFTER DATABASE START Trigger:

CREATE OR REPLACE TRIGGER after_db_startup_example
AFTER DATABASE START
BEGIN
   INSERT INTO registro_inicializacao (data_inicializacao)
   VALUES (SYSDATE);
END;

INSTEAD OF CREATE Triggers

INSTEAD OF CREATE Trigger: Acionado em vez de uma operação CREATE, como criar uma tabela.

Exemplo de INSTEAD OF CREATE Trigger:

CREATE OR REPLACE TRIGGER instead_of_create_table_example
INSTEAD OF CREATE ON DATABASE
BEGIN
   DBMS_OUTPUT.PUT_LINE('A criação de tabelas não é permitida.');
END;

Habilitando e Desabilitando “Triggers”

Para habilitar um “trigger”, use a seguinte sintaxe:

ALTER TRIGGER nome_do_trigger ENABLE;

Para desabilitar um “trigger”, use a seguinte sintaxe:

ALTER TRIGGER nome_do_trigger DISABLE;

“Mutating Trigger” e Como Resolvê-lo

Um “mutating trigger” ocorre quando um “trigger” tenta ler ou modificar a mesma tabela que acionou o “trigger”. Isso pode levar a erros e comportamentos inesperados. Para resolver esse problema, siga estas diretrizes:

• Evite ler a tabela que acionou o “trigger” usando uma consulta SQL.
• Use variáveis locais ou uma tabela temporária para armazenar valores em vez de ler diretamente da tabela.

Exemplo de resolução de “mutating trigger”:

CREATE OR REPLACE TRIGGER mutating_trigger_resolution
AFTER INSERT ON minha_tabela
FOR EACH ROW
DECLARE
   v_valor_anterior NUMBER;
BEGIN
   -- Use uma variável local para armazenar o valor anterior
   SELECT valor INTO v_valor_anterior
   FROM minha_tabela
   WHERE id = :NEW.id;

   -- Agora você pode usar v_valor_anterior para suas ações
END;

Conclusão

Os “triggers” no Oracle Database são uma ferramenta poderosa para automatizar tarefas, garantir a integridade dos dados e rastrear atividades. Neste artigo, exploramos os conceitos fundamentais, os tipos de “triggers”, fornecemos exemplos práticos e explicamos como habilitar/desabilitar “triggers”. Além disso, discutimos o desafiador conceito de “mutating trigger” e como resolvê-lo.

Agora, você está preparado para aproveitar ao máximo os “triggers” no Oracle Database em diversas situações, desde auditoria até personalização de comportamento de banco de dados.

Abs

Referências

• Oracle Database 19c Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/19/index.html
• Oracle PL/SQL User’s Guide and Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/index.html
• Oracle PL/SQL Language Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/index.html