O que são Triggers

Os triggers em Oracle PL/SQL são componentes essenciais que aumentam a inteligência e a autonomia dos bancos de dados. Funcionando como “assistentes automáticos”, os triggers são procedimentos armazenados que executam ações específicas em resposta a eventos definidos no banco de dados, como inserções, atualizações ou exclusões de dados. Os triggers desempenham um papel fundamental em manter a integridade dos dados e automatizar tarefas repetitivas.

Ao explorar o uso de triggers, você aprenderá a aproveitar o máximo potencial do Oracle PL/SQL para criar sistemas de banco de dados mais reativos. Esses triggers são acionados automaticamente, possibilitando a implementação de lógica complexa de negócios que garante que o banco de dados responda instantaneamente às mudanças, mantendo-se atualizado sem intervenção manual. Este conhecimento é crucial para qualquer desenvolvedor ou DBA que busca otimizar operações e garantir um gerenciamento eficiente dos dados. Com a implementação adequada de triggers, você pode transformar seu banco de dados em uma plataforma mais inteligente e autônoma.

Definição de Trigger de Banco de Dados

Imagine que nosso departamento de Recursos Humanos (HR) é uma organização altamente eficiente. Os triggers seriam como os assistentes automáticos desse departamento, sempre atentos e prontos para agir.

Um trigger de banco de dados é um bloco de código PL/SQL com nome próprio, armazenado no banco de dados e executado automaticamente em resposta a eventos específicos. É como ter um assistente invisível e incansável, sempre pronto para entrar em ação quando algo importante acontece no nosso banco de dados.

O ato de executar um trigger é chamado de “disparar” o trigger. É como se nosso assistente invisível ganhasse vida de repente para realizar sua tarefa!

Eventos de Disparo

Quais eventos podem despertar nosso assistente invisível? Bem, temos três tipos principais:

Operações DML (Data Manipulation Language): Imagine que você está gerenciando a tabela EMPLOYEES. Toda vez que um novo funcionário é contratado (INSERT), um funcionário é promovido (UPDATE) ou um funcionário deixa a empresa (DELETE), o trigger entra em ação. Ele pode agir antes ou depois desses eventos.
Por exemplo, se você definiu um trigger para disparar antes de um INSERT na tabela EMPLOYEES, ele será ativado cada vez antes de inserir uma nova linha na tabela EMPLOYEES.
Operações DDL (Data Definition Language): É como se o trigger fosse um supervisor que fica de olho em quem está mexendo na estrutura das tabelas (como DEPARTMENTS ou JOBS). Esses triggers são frequentemente usados para fins de auditoria e são especialmente úteis para administradores de banco de dados Oracle.
Eles podem registrar várias alterações no esquema, incluindo quando essas alterações foram feitas e por qual usuário.
Eventos do sistema ou do usuário: Pense no trigger como um porteiro que registra quem entra e sai do sistema (login/logoff) ou um zelador que faz checagens quando o banco de dados “abre” ou “fecha” (startup/shutdown).
Por exemplo, você pode definir um trigger que dispara após um login no banco de dados e registra o nome de usuário e o horário do login.

Sintaxe Básica de um Trigger

Vamos dar uma olhada na receita básica para criar um trigger:

CREATE [OR REPLACE] [EDITIONABLE|NONEDITIONABLE] TRIGGER nome_do_trigger
{BEFORE|AFTER} evento_de_disparo ON nome_da_tabela
[FOR EACH ROW]
[FOLLOWS|PRECEDES outro_trigger] [ENABLE/DISABLE]
[WHEN condição]
DECLARE
   -- Declarações
BEGIN
   -- Comandos executáveis
EXCEPTION
   -- Tratamento de exceções
END;

CREATE [OR REPLACE] [EDITIONABLE|NONEDITIONABLE] TRIGGER nome_do_trigger

{BEFORE|AFTER} evento_de_disparo ON nome_da_tabela

[FOR EACH ROW]

[FOLLOWS|PRECEDES outro_trigger] [ENABLE/DISABLE]

[WHEN condição]

DECLARE

-- Declarações

BEGIN

-- Comandos executáveis

EXCEPTION

-- Tratamento de exceções

END;

Vamos detalhar cada ingrediente desta receita:

CREATE [OR REPLACE]: Isso especifica que estamos criando um novo trigger ou modificando um existente.
[EDITIONABLE|NONEDITIONABLE]: Especifica se o trigger é editável ou não editável. Isso só se aplica se o versionamento de objetos estiver habilitado.
nome_do_trigger: O nome do nosso assistente invisível.
{BEFORE|AFTER}: Decide se o trigger age antes ou depois do evento.
evento_de_disparo: O que faz o trigger entrar em ação (INSERT, UPDATE, DELETE, etc.).
nome_da_tabela: Em qual tabela o trigger vai atuar (EMPLOYEES, DEPARTMENTS, JOBS, etc.).
[FOR EACH ROW]: Se presente, especifica que o trigger é um trigger de linha e dispara uma vez para cada linha afetada.
[FOLLOWS|PRECEDES outro_trigger]: Permite especificar a ordem em que os triggers devem disparar.
[ENABLE/DISABLE]: Especifica se o trigger é criado no estado habilitado ou desabilitado.
[WHEN condição]: Adiciona uma condição extra para o trigger agir.

Exemplo Prático: Trigger BEFORE

Vamos criar um trigger que atua antes de inserir um novo funcionário na tabela EMPLOYEES:

CREATE OR REPLACE TRIGGER emp_bi
BEFORE INSERT ON EMPLOYEES
FOR EACH ROW
BEGIN
   -- Gera um novo ID de empregado
   :NEW.employee_id := employees_seq.NEXTVAL;
   
   -- Define a data de contratação como a data atual se não for fornecida
   IF :NEW.hire_date IS NULL THEN
      :NEW.hire_date := SYSDATE;
   END IF;
   
   -- Define o salário mínimo baseado no job_id
   IF :NEW.salary IS NULL THEN
      SELECT MIN(min_salary) INTO :NEW.salary
      FROM jobs
      WHERE job_id = :NEW.job_id;
   END IF;
END;

CREATE OR REPLACE TRIGGER emp_bi

BEFORE INSERT ON EMPLOYEES

FOR EACH ROW

BEGIN

-- Gera um novo ID de empregado

:NEW.employee_id := employees_seq.NEXTVAL;

-- Define a data de contratação como a data atual se não for fornecida

IF :NEW.hire_date IS NULL THEN

:NEW.hire_date := SYSDATE;

END IF;

-- Define o salário mínimo baseado no job_id

IF :NEW.salary IS NULL THEN

SELECT MIN(min_salary) INTO :NEW.salary

FROM jobs

WHERE job_id = :NEW.job_id;

END IF;

END;

Este trigger é como um assistente eficiente que preenche informações importantes antes de um novo funcionário ser adicionado. Ele usa :NEW para se referir à nova linha que está sendo inserida.

Após criar este trigger, você não precisa mais fornecer valores para as colunas employee_id e hire_date ao inserir um novo funcionário, e o salário será automaticamente definido para o mínimo do cargo se não for especificado.

Explicação Detalhada da Trigger

Esta trigger, chamada emp_bi, é uma trigger BEFORE INSERT na tabela EMPLOYEES. Ela é executada antes de cada inserção de uma nova linha na tabela. Vamos analisar cada parte:

Definição da Trigger:

CREATE OR REPLACE TRIGGER emp_bi
BEFORE INSERT ON EMPLOYEES
FOR EACH ROW

CREATE OR REPLACE TRIGGER emp_bi

BEFORE INSERT ON EMPLOYEES

FOR EACH ROW

CREATE OR REPLACE: Cria a trigger ou a substitui se já existir.
BEFORE INSERT: Executa antes de uma operação de inserção.
ON EMPLOYEES: Aplica-se à tabela EMPLOYEES.
FOR EACH ROW: Executa para cada linha afetada pela operação de inserção.

Geração de ID de Empregado:

:NEW.employee_id := employees_seq.NEXTVAL;

1	:NEW.employee_id := employees_seq.NEXTVAL;

Atribui um novo valor de sequência ao campo employee_id.
employees_seq é uma sequência presumivelmente criada para gerar IDs únicos.
:NEW refere-se à nova linha sendo inserida.

Definição da Data de Contratação:

IF :NEW.hire_date IS NULL THEN
   :NEW.hire_date := SYSDATE;
END IF;

IF :NEW.hire_date IS NULL THEN

:NEW.hire_date := SYSDATE;

END IF;

Se a data de contratação não for fornecida (NULL), define-a como a data atual (SYSDATE).

Definição do Salário Mínimo:

IF :NEW.salary IS NULL THEN
   SELECT MIN(min_salary) INTO :NEW.salary
   FROM jobs
   WHERE job_id = :NEW.job_id;
END IF;

IF :NEW.salary IS NULL THEN

SELECT MIN(min_salary) INTO :NEW.salary

FROM jobs

WHERE job_id = :NEW.job_id;

END IF;

Se o salário não for fornecido, busca o salário mínimo correspondente ao job_id na tabela jobs.
Atribui esse valor mínimo ao campo salary do novo empregado.

Exemplo para Validar o Funcionamento

Vamos criar um exemplo para testar esta trigger:

Primeiro, vamos assumir que temos as seguintes tabelas:

CREATE TABLE EMPLOYEES (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    hire_date DATE,
    job_id VARCHAR2(10),
    salary NUMBER(8,2)
);

CREATE TABLE JOBS (
    job_id VARCHAR2(10) PRIMARY KEY,
    job_title VARCHAR2(35),
    min_salary NUMBER(8,2),
    max_salary NUMBER(8,2)
);

CREATE SEQUENCE employees_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE EMPLOYEES (

employee_id NUMBER PRIMARY KEY,

first_name VARCHAR2(50),

last_name VARCHAR2(50),

email VARCHAR2(100),

hire_date DATE,

job_id VARCHAR2(10),

salary NUMBER(8,2)

);

CREATE TABLE JOBS (

job_id VARCHAR2(10) PRIMARY KEY,

job_title VARCHAR2(35),

min_salary NUMBER(8,2),

max_salary NUMBER(8,2)

);

CREATE SEQUENCE employees_seq START WITH 1 INCREMENT BY 1;

Inserimos alguns dados na tabela JOBS:

INSERT INTO JOBS (job_id, job_title, min_salary, max_salary)
VALUES ('IT_PROG', 'Programmer', 4000, 10000);

1 2	INSERT INTO JOBS (job_id, job_title, min_salary, max_salary) VALUES ('IT_PROG', 'Programmer', 4000, 10000);

Agora, vamos inserir um novo empregado sem especificar o ID, a data de contratação e o salário:

INSERT INTO EMPLOYEES (first_name, last_name, email, job_id)
VALUES ('John', 'Doe', 'john.doe@example.com', 'IT_PROG');

1 2	INSERT INTO EMPLOYEES (first_name, last_name, email, job_id) VALUES ('John', 'Doe', 'john.doe@example.com', 'IT_PROG');

Verificamos o resultado:

SELECT * FROM EMPLOYEES;

1	SELECT * FROM EMPLOYEES;

O resultado esperado seria algo como:

EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL                 | HIRE_DATE  | JOB_ID  | SALARY
1           | John       | Doe       | john.doe@example.com  | 2024-11-20 | IT_PROG | 4000

1 | John | Doe | john.doe@example.com | 2024-11-20 | IT_PROG | 4000

Neste resultado:

O EMPLOYEE_ID foi gerado automaticamente (1, assumindo que é o primeiro registro).
A HIRE_DATE foi definida como a data atual (2024-11-20, considerando a data fornecida nas instruções).
O SALARY foi definido como 4000, que é o salário mínimo para o cargo ‘IT_PROG'.

Este exemplo demonstra que a trigger funcionou corretamente:

Gerou um ID de empregado único.
Definiu a data de contratação automaticamente.
Atribuiu o salário mínimo baseado no job_id.

Para validar completamente, você pode tentar inserções adicionais com diferentes cenários (por exemplo, fornecendo uma data de contratação ou um salário) para ver como a trigger se comporta em cada caso.

Exemplo Prático: Trigger AFTER

Agora, vamos criar um trigger que atua após uma atualização ou exclusão na tabela EMPLOYEES. Para isso, primeiro vamos criar uma tabela de auditoria:

CREATE TABLE emp_audit_trail (
   audit_id NUMBER PRIMARY KEY,
   employee_id NUMBER,
   transaction_name VARCHAR2(10),
   transaction_user VARCHAR2(30),
   transaction_date DATE
);

CREATE SEQUENCE emp_audit_seq START WITH 1;

CREATE TABLE emp_audit_trail (

audit_id NUMBER PRIMARY KEY,

employee_id NUMBER,

transaction_name VARCHAR2(10),

transaction_user VARCHAR2(30),

transaction_date DATE

);

CREATE SEQUENCE emp_audit_seq START WITH 1;

Agora, vamos criar o trigger:

CREATE OR REPLACE TRIGGER emp_aud
AFTER UPDATE OR DELETE ON EMPLOYEES
FOR EACH ROW
DECLARE
   v_trans_type VARCHAR2(10);
BEGIN
   v_trans_type := CASE
                      WHEN UPDATING THEN 'UPDATE'
                      WHEN DELETING THEN 'DELETE'
                   END;
   
   INSERT INTO emp_audit_trail
      (audit_id, employee_id, transaction_name, transaction_user, transaction_date)
   VALUES
      (emp_audit_seq.NEXTVAL, :OLD.employee_id, v_trans_type, USER, SYSDATE);
END;

CREATE OR REPLACE TRIGGER emp_aud

AFTER UPDATE OR DELETE ON EMPLOYEES

FOR EACH ROW

DECLARE

v_trans_type VARCHAR2(10);

BEGIN

v_trans_type := CASE

WHEN UPDATING THEN 'UPDATE'

WHEN DELETING THEN 'DELETE'

END;

INSERT INTO emp_audit_trail

(audit_id, employee_id, transaction_name, transaction_user, transaction_date)

VALUES

(emp_audit_seq.NEXTVAL, :OLD.employee_id, v_trans_type, USER, SYSDATE);

END;

Este trigger é como um secretário que anota num livro de registros (tabela emp_audit_trail) toda vez que um funcionário é atualizado ou removido do sistema.

Demonstração Prática: UPDATE e DELETE com Trigger

Vamos usar o trigger emp_aud que criamos anteriormente e ver como ele funciona na prática. Vamos fazer algumas operações e ver o resultado:

Demonstração de UPDATE

Primeiro, vamos atualizar o salário de um funcionário:

UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 103;

UPDATE employees

SET salary = salary * 1.1

WHERE employee_id = 103;

Agora, vamos verificar o que foi registrado na nossa tabela de auditoria:

SELECT * FROM emp_audit_trail
WHERE employee_id = 103
ORDER BY transaction_date DESC;

SELECT * FROM emp_audit_trail

WHERE employee_id = 103

ORDER BY transaction_date DESC;

Você deverá ver uma entrada como esta:

AUDIT_ID   EMPLOYEE_ID   TRANSACTION_NAME   TRANSACTION_USER   TRANSACTION_DATE
--------   -----------   ----------------   -----------------   -----------------
1          103           UPDATE             HR                  2024-11-20 14:30:00

AUDIT_ID EMPLOYEE_ID TRANSACTION_NAME TRANSACTION_USER TRANSACTION_DATE

-------- ----------- ---------------- ----------------- -----------------

1 103 UPDATE HR 2024-11-20 14:30:00

Isso mostra que o trigger capturou a operação de UPDATE, registrando quem fez a alteração e quando.

Demonstração de DELETE

Agora, vamos deletar um funcionário:

DELETE FROM employees
WHERE employee_id = 107;

1 2	DELETE FROM employees WHERE employee_id = 107;

Vamos verificar novamente nossa tabela de auditoria:

SELECT * FROM emp_audit_trail
WHERE employee_id = 107
ORDER BY transaction_date DESC;

SELECT * FROM emp_audit_trail

WHERE employee_id = 107

ORDER BY transaction_date DESC;

Você deverá ver uma entrada como esta:

AUDIT_ID   EMPLOYEE_ID   TRANSACTION_NAME   TRANSACTION_USER   TRANSACTION_DATE
--------   -----------   ----------------   -----------------   -----------------
2          107           DELETE             HR                  2024-11-20 14:35:00

AUDIT_ID EMPLOYEE_ID TRANSACTION_NAME TRANSACTION_USER TRANSACTION_DATE

-------- ----------- ---------------- ----------------- -----------------

2 107 DELETE HR 2024-11-20 14:35:00

Isso mostra que o trigger também capturou a operação de DELETE, registrando os detalhes da transação.

Explicação

Quando fazemos um UPDATE, o trigger é acionado uma vez para cada linha afetada. Ele captura o ID do funcionário que está sendo atualizado (usando :OLD.employee_id) e registra a ação como ‘UPDATE'.
Quando fazemos um DELETE, o trigger é acionado uma vez para cada linha deletada. Ele captura o ID do funcionário que está sendo deletado (novamente usando :OLD.employee_id) e registra a ação como ‘DELETE'.
Em ambos os casos, o trigger também registra o usuário que realizou a ação (usando a função USER) e o momento exato da transação (usando SYSDATE).

Observações Importantes

O trigger é acionado automaticamente, sem necessidade de nenhuma ação adicional do usuário além da própria operação de UPDATE ou DELETE.
Como este é um trigger AFTER, as operações de UPDATE e DELETE já foram concluídas quando o trigger é acionado. Isso significa que se houver algum erro no trigger, a operação principal (UPDATE ou DELETE) não será afetada.
Se você fizer um UPDATE que afeta múltiplas linhas (por exemplo, aumentar o salário de todos os funcionários de um departamento), o trigger será acionado uma vez para cada linha afetada.
Este trigger usa :OLD.employee_id para capturar o ID do funcionário. Em um trigger AFTER UPDATE, você também poderia usar :NEW.employee_id, que teria o mesmo valor, já que o employee_id normalmente não é alterado em uma operação de UPDATE.

Esta demonstração prática mostra como os triggers podem ser usados para criar um sistema de auditoria robusto, capturando automaticamente informações importantes sobre mudanças nos dados. Isso é especialmente útil em ambientes onde a rastreabilidade das alterações é crucial.

Usos Comuns de Triggers

Triggers são usados para diferentes propósitos no schema HR, como:

Impor regras de negócios complexas, como garantir que o salário de um funcionário não seja menor que o mínimo para seu cargo.
Manter regras de segurança complexas, como impedir alterações em salários fora do horário comercial.
Gerar automaticamente valores para colunas derivadas, como calcular a comissão baseada no departamento.
Coletar informações estatísticas sobre acessos às tabelas de RH.
Prevenir transações inválidas, como impedir a exclusão de um departamento que ainda tem funcionários.
Fornecer auditoria de valores, como registrar todas as alterações de salário.

Restrições em Triggers

Existem algumas restrições importantes ao criar triggers:

Um trigger não pode emitir comandos de controle de transação como COMMIT, SAVEPOINT ou ROLLBACK (com exceção de triggers que contêm transações autônomas, que veremos em outra aula).
Qualquer função ou procedimento chamado por um trigger também não pode emitir comandos de controle de transação.
Não é permitido declarar variáveis LONG ou LONG RAW no corpo de um trigger.

Conclusão

Triggers são ferramentas incrivelmente poderosas que tornam nosso banco de dados HR mais inteligente e autônomo. Eles podem validar dados, preencher informações automaticamente, manter registros de auditoria e muito mais.

Lembre-se:

Use triggers BEFORE para validações e preenchimentos automáticos.
Use triggers AFTER para auditorias e sincronizações.
Sempre considere o impacto dos triggers no desempenho do banco de dados.

Definição de Trigger de Banco de Dados

Eventos de Disparo

Sintaxe Básica de um Trigger

Exemplo Prático: Trigger BEFORE

Explicação Detalhada da Trigger

Exemplo para Validar o Funcionamento

Exemplo Prático: Trigger AFTER

Demonstração Prática: UPDATE e DELETE com Trigger

Demonstração de UPDATE

Demonstração de DELETE

Explicação

Observações Importantes

Usos Comuns de Triggers

Restrições em Triggers

Conclusão

Related Posts