Oracle Tutorial de gatilho PL/SQL: em vez de composto [exemplo]

O que é gatilho em PL/SQL?

GATILHOS são programas armazenados que são disparados por Oracle mecanismo automaticamente quando instruções DML como insert, update, delete são executadas na tabela ou alguns eventos ocorrem. O código a ser executado em caso de trigger pode ser definido conforme a necessidade. Você pode escolher o evento no qual o gatilho precisa ser acionado e o tempo de execução. O objetivo do gatilho é manter a integridade das informações no banco de dados.

Benefícios dos gatilhos

A seguir estão os benefícios dos gatilhos.

• Gerando alguns valores de colunas derivadas automaticamente
• Aplicando integridade referencial
• Registro de eventos e armazenamento de informações sobre acesso à tabela
• Auditoria
• Syncreplicação cronológica de tabelas
• Imposição de autorizações de segurança
• Evitando transações inválidas

Tipos de gatilhos em Oracle

Os gatilhos podem ser classificados com base nos seguintes parâmetros.

• Classificação baseada no cronometragem
• BEFORE Trigger: É acionado antes que o evento especificado ocorra.
• AFTER Trigger: É acionado após a ocorrência do evento especificado.
• EM VEZ DE Gatilho: Um tipo especial. Você aprenderá mais sobre os próximos tópicos. (somente para DML)
• Classificação baseada no nível
• Trigger de nível STATEMENT: é acionado uma vez para a instrução de evento especificada.
• Gatilho de nível ROW: é acionado para cada registro que foi afetado no evento especificado. (somente para DML)
• Classificação baseada no Evento
• Gatilho DML: É acionado quando o evento DML é especificado (INSERT/UPDATE/DELETE)
• DDL Trigger: É acionado quando o evento DDL é especificado (CREATE/ALTER)
• DATABASE Trigger: Dispara quando o evento do banco de dados é especificado (LOGON/LOGOFF/STARTUP/SHUTDOWN)

Portanto, cada gatilho é a combinação dos parâmetros acima.

Como criar um gatilho

Abaixo está a sintaxe para criar um gatilho.

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE......]

ON<name of underlying object>

[FOR EACH ROW] 

[WHEN<condition for trigger to get execute> ]

DECLARE
<Declaration part>
BEGIN
<Execution part> 
EXCEPTION
<Exception handling part> 
END;

Explicação da sintaxe:

• A sintaxe acima mostra as diferentes instruções opcionais presentes na criação do gatilho.
• BEFORE/AFTER especificará os horários do evento.
• INSERIR/ATUALIZAR/LOGON/CRIAR/etc. especificará o evento para o qual o gatilho precisa ser acionado.
• A cláusula ON especificará em qual objeto o evento mencionado acima é válido. Por exemplo, este será o nome da tabela na qual o evento DML poderá ocorrer no caso de DML Trigger.
• O comando “FOR EACH ROW” especificará o acionador do nível ROW.
• A cláusula WHEN especificará a condição adicional na qual o gatilho precisa ser acionado.
• A parte de declaração, parte de execução e parte de tratamento de exceções são as mesmas das outras Blocos PL/SQL. A parte da declaração e a parte do tratamento de exceções são opcionais.

:Cláusula NOVA e :ANTIGA

Em um gatilho em nível de linha, o gatilho é acionado para cada linha relacionada. E às vezes é necessário saber o valor antes e depois da instrução DML.

Oracle forneceu duas cláusulas no gatilho de nível RECORD para manter esses valores. Podemos usar essas cláusulas para nos referirmos aos valores antigos e novos dentro do corpo do gatilho.

• :NEW – Mantém um novo valor para as colunas da tabela/view base durante a execução do trigger
• :OLD – Mantém o valor antigo das colunas da tabela/view base durante a execução do trigger

Esta cláusula deve ser usada com base no evento DML. A tabela abaixo especificará qual cláusula é válida para qual instrução DML (INSERT/UPDATE/DELETE).

	INSIRA	ATUALIZAÇÃO	EXCLUIR
:NOVO	VÁLIDO	VÁLIDO	INVÁLIDO. Não há novo valor no caso de exclusão.
:VELHO	INVÁLIDO. Não há valor antigo no caso de inserção	VÁLIDO	VÁLIDO

EM VEZ DE Gatilho

“INSTEAD OF trigger” é o tipo especial de trigger. É usado apenas em gatilhos DML. É usado quando qualquer evento DML ocorrerá na visualização complexa.

Considere um exemplo em que uma visualização é feita a partir de 3 tabelas base. Quando qualquer evento DML for emitido nesta visualização, isso se tornará inválido porque os dados são obtidos de 3 tabelas diferentes. Portanto, neste gatilho INSTEAD OF é usado. O gatilho INSTEAD OF é usado para modificar as tabelas base diretamente, em vez de modificar a visualização de um determinado evento.

Exemplo 1: Neste exemplo, vamos criar uma visão complexa a partir de duas tabelas base.

• Tabela_1 é tabela emp e

• Tabela_2 é tabela de departamento.

Em seguida, veremos como o gatilho INSTEAD OF é usado para emitir UPDATE a instrução de detalhes de localização nesta visualização complexa. Também veremos como :NEW e :OLD são úteis em gatilhos.

• Etapa 1: Criando tabelas 'emp' e 'dept' com colunas apropriadas
• Etapa 2: preencher a tabela com valores de amostra
• Etapa 3: Criando visualização para a tabela criada acima
• Etapa 4: atualização da visualização antes do gatilho em vez de
• Etapa 5: Criação do gatilho em vez de
• Etapa 6: atualização da visualização após o acionamento, em vez do acionador

Passo 1) Criando tabela 'emp' e 'dept' com colunas apropriadas

CREATE TABLE emp(
emp_no NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
manager VARCHAR2(50),
dept_no NUMBER);
/

CREATE TABLE dept( 
Dept_no NUMBER, 
Dept_name VARCHAR2(50),
LOCATION VARCHAR2(50));
/

Explicação do código

• Linha de código 1-7: Criação da tabela 'emp'.
• Linha de código 8-12: Criação da tabela 'dept'.

saída

Tabela criada

Passo 2) Agora que criamos a tabela, iremos preenchê-la com valores de amostra e criação de visualizações para as tabelas acima.

BEGIN
INSERT INTO DEPT VALUES(10,‘HR’,‘USA’);
INSERT INTO DEPT VALUES(20,'SALES','UK’);
INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN'); 
COMMIT;
END;
/

BEGIN
INSERT INTO EMP VALUES(1000,'XXX5,15000,'AAA',30);
INSERT INTO EMP VALUES(1001,‘YYY5,18000,‘AAA’,20) ;
INSERT INTO EMP VALUES(1002,‘ZZZ5,20000,‘AAA',10); 
COMMIT;
END;
/

Explicação do código

• Linha de código 13-19: Inserindo dados na tabela 'dept'.
• Linha de código 20-26: Inserindo dados na tabela 'emp'.

saída

Procedimento PL/SQL concluído

Passo 3) Criando uma visualização para a tabela criada acima.

CREATE VIEW guru99_emp_view(
Employee_name:dept_name,location) AS
SELECT emp.emp_name,dept.dept_name,dept.location
FROM emp,dept
WHERE emp.dept_no=dept.dept_no;
/

SELECT * FROM guru99_emp_view;

Explicação do código

• Linha de código 27-32: Criação da visualização 'guru99_emp_view'.

• Linha de código 33: Consultando guru99_emp_view.

saída

Visualização criada

NOME DO EMPREGADO	DEPT_NAME	LOCALIZAÇÃO
ZZZ	HR	USA
AAAA	VENDAS	UK
XXX	FINANCEIRO	JAPÃO

Passo 4) Atualização da visualização antes do gatilho.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name=:'XXX’;
COMMIT;
END;
/

Explicação do código

• Linha de código 34-38: Atualize a localização de “XXX” para 'FRANÇA'. Abriu a excepção porque o Declarações DML não são permitidos na visualização complexa.

saída

ORA-01779: não é possível modificar uma coluna que mapeia para uma tabela sem preservação de chave

ORA-06512: na linha 2

Passo 5)Para evitar o erro durante a atualização da visualização na etapa anterior, nesta etapa usaremos “em vez de gatilho”.

CREATE TRIGGER guru99_view_modify_trg
INSTEAD OF UPDATE
ON guru99_emp_view
FOR EACH ROW
BEGIN
UPDATE dept
SET location=:new.location
WHERE dept_name=:old.dept_name;
END;
/

Explicação do código

• Linha de código 39: Criação do gatilho INSTEAD OF para o evento 'UPDATE' na view 'guru99_emp_view' no nível ROW. Ele contém a instrução update para atualizar o local na tabela base 'dept'.
• Linha de código 44: A instrução de atualização usa ':NEW' e ':OLD' para encontrar o valor das colunas antes e depois da atualização.

saída

Gatilho criado

Passo 6) Atualização da visualização após vez do gatilho. Agora o erro não ocorrerá, pois o “em vez de gatilho” cuidará da operação de atualização desta visualização complexa. E quando o código for executado, a localização do funcionário XXX será atualizada para “França” de “Japão”.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX'; 
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

Explicação do código:

• Linha de código 49-53: Atualização da localização de “XXX” para 'FRANÇA'. Foi bem-sucedido porque o gatilho 'INSTEAD OF' interrompeu a instrução de atualização real na visualização e executou a atualização da tabela base.
• Linha de código 55: Verificando o registro atualizado.

Saída:

Procedimento PL/SQL concluído com sucesso

NOME DO EMPREGADO	DEPT_NAME	LOCALIZAÇÃO
ZZZ	HR	USA
AAAA	VENDAS	UK
XXX	FINANCEIRO	FRANÇA

Gatilho Composto

O gatilho composto é um gatilho que permite especificar ações para cada um dos quatro pontos de tempo no corpo do gatilho único. Os quatro pontos de tempo diferentes que ele suporta são os seguintes.

• ANTES DA DECLARAÇÃO – nível
• ANTES DA LINHA – nível
• DEPOIS DA LINHA – nível
• APÓS DECLARAÇÃO – nível

Ele fornece a facilidade de combinar ações para diferentes tempos no mesmo gatilho.

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 
FOR
[INSERT | UPDATE | DELET.......]
ON <name of underlying object>
<Declarative part>‭	‬
BEFORE STATEMENT IS
BEGIN
<Execution part>;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
<Execution part>;
END EACH ROW;

AFTER EACH ROW IS
BEGIN
<Execution part>;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
<Execution part>;
END AFTER STATEMENT;
END;

Explicação da sintaxe:

• A sintaxe acima mostra a criação do gatilho 'COMPOUND'.
• A seção declarativa é comum para todos os blocos de execução no corpo do gatilho.
• Esses 4 blocos de temporização podem estar em qualquer sequência. Não é obrigatório ter todos estes 4 blocos de temporização. Podemos criar um gatilho COMPOUND apenas para os tempos necessários.

Exemplo 1: Neste exemplo, criaremos um gatilho para preencher automaticamente a coluna salário com o valor padrão 5000.

CREATE TRIGGER emp_trig 
FOR INSERT 
ON emp
COMPOUND TRIGGER 
BEFORE EACH ROW IS 
BEGIN
:new.salary:=5000;
END BEFORE EACH ROW;
END emp_trig;
/

BEGIN
INSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30); 
COMMIT;
END;
/

SELECT * FROM emp WHERE emp_no=1004;

Explicação do código:

• Linha de código 2-10: Criação de gatilho composto. Ele é criado para cronometrar o nível BEFORE ROW para preencher o salário com o valor padrão 5000. Isso alterará o salário para o valor padrão '5000' antes de inserir o registro na tabela.
• Linha de código 11-14: Insira o registro na tabela 'emp'.
• Linha de código 16: Verificando o registro inserido.

Saída:

Gatilho criado

Procedimento PL / SQL concluído com sucesso.

EMP_NAME	EMP_NO	SALÁRIO	MANAGER	DEPT_NO
CCC	1004	5000	AAA	30

Habilitando e desabilitando gatilhos

Os gatilhos podem ser ativados ou desativados. Para habilitar ou desabilitar o gatilho, uma instrução ALTER (DDL) precisa ser fornecida para o gatilho que o desabilita ou habilita.

Abaixo está a sintaxe para ativar/desativar os gatilhos.

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

Explicação da sintaxe:

• A primeira sintaxe mostra como ativar/desativar o gatilho único.
• A segunda instrução mostra como ativar/desativar todos os gatilhos em uma tabela específica.

Resumo

Neste capítulo, aprendemos sobre os gatilhos PL/SQL e suas vantagens. Também aprendemos as diferentes classificações e discutimos o gatilho INSTEAD OF e o gatilho COMPOUND.