Tutorial do Oracle PL / SQL Trigger: em vez de composto (exemplo)

O que é Trigger no PL / SQL?

TRIGGERS são programas armazenados que são acionados pelo mecanismo Oracle automaticamente quando instruções DML como inserir, atualizar, excluir são executadas na tabela ou alguns eventos ocorrem. O código a ser executado em caso de disparo pode ser definido conforme o requisito. Você pode escolher o evento em que o gatilho deve ser disparado e o momento da execução. O objetivo do gatilho é manter a integridade das informações no banco de dados.

Neste tutorial, você aprenderá-

Benefícios dos gatilhos
Tipos de gatilhos no Oracle
Como criar gatilho
: NOVA e: Cláusula ANTIGA
EM VEZ DE Gatilho
Gatilho Composto

Benefícios dos gatilhos

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

Gerando alguns valores de coluna derivados automaticamente
Impondo integridade referencial
Registro de eventos e armazenamento de informações sobre o acesso à mesa
Auditoria
Replicação síncrona de tabelas
Impondo autorizações de segurança
Prevenir transações inválidas

Tipos de gatilhos no Oracle

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

Classificação com base no tempo
- BEFORE Trigger: dispara antes da ocorrência do evento especificado.
- AFTER Trigger: dispara após a ocorrência do evento especificado.
- EM VEZ DE Trigger: um tipo especial. Você aprenderá mais sobre os outros tópicos. (apenas para DML)
Classificação baseada no nível
- Acionador de nível STATEMENT: dispara uma vez para a instrução de evento especificada.
- Acionador de nível ROW: dispara para cada registro afetado no evento especificado. (apenas para DML)
Classificação com base no evento
- DML Trigger: dispara quando o evento DML é especificado (INSERT / UPDATE / DELETE)
- DDL Trigger: dispara 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 gatilho

Abaixo está a sintaxe para criar um gatilho.

CREATE [ OR REPLACE ] TRIGGER 
[BEFORE | AFTER | INSTEAD OF ][INSERT | UPDATE | DELETE… ]ON[FOR EACH ROW][WHEN ]DECLAREBEGINEXCEPTIONEND;

Explicação da sintaxe:

A sintaxe acima mostra as diferentes instruções opcionais que estão presentes na criação do gatilho.
BEFORE / AFTER irá especificar as temporizações do evento.
INSERT / UPDATE / LOGON / CREATE / etc. irá especificar o evento para o qual o gatilho precisa ser disparado.
A cláusula ON irá especificar em qual objeto o evento acima mencionado é válido. Por exemplo, este será o nome da tabela na qual o evento DML pode ocorrer no caso de DML Trigger.
O comando "PARA CADA LINHA" especificará o acionador do nível ROW.
A cláusula WHEN especificará a condição adicional na qual o gatilho precisa ser disparado.
A parte da declaração, a parte da execução e a parte do tratamento de exceções são as mesmas dos outros blocos PL / SQL. A parte da declaração e a parte do tratamento de exceções são opcionais.

: NOVA e: Cláusula ANTIGA

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

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

: NOVO - contém um novo valor para as colunas da tabela / visão base durante a execução do gatilho
: OLD - Retém o valor antigo das colunas da tabela / visão base durante a execução do gatilho

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

	INSERIR	ATUALIZAR	EXCLUIR
:NOVO	VÁLIDO	VÁLIDO	INVÁLIDO. Não há nenhum novo valor no caso de exclusão.
:VELHO	INVÁLIDO. Não há nenhum valor antigo no caso de inserção	VÁLIDO	VÁLIDO

EM VEZ DE Gatilho

"INSTEAD OF trigger" é o tipo especial de gatilho. Ele é usado apenas em gatilhos DML. É usado quando qualquer evento DML vai ocorrer na visão complexa.

Considere um exemplo em que uma visão é feita a partir de 3 tabelas básicas. Quando qualquer evento DML é emitido sobre esta visualização, ele se torna 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 de base diretamente, em vez de modificar a visualização para o evento fornecido.

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

Tabela_1 é uma tabela emp e

Table_2 é a tabela de departamentos.

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

Etapa 1: Criação da tabela '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 visã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 em vez do acionador

Etapa 1) Criação da 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 de 'departamento' da tabela.

Resultado

Mesa Criada

Etapa 2) Agora, uma vez que criamos a tabela, iremos preencher esta tabela com valores de amostra e Criação de Visualizações para as tabelas acima.

BEGININSERT INTO DEPT VALUES(10,‘HR’,‘USA’);INSERT INTO DEPT VALUES(20,'SALES','UK’);INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN');COMMIT;END;/BEGININSERT 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'.

Resultado

Procedimento PL / SQL concluído

Etapa 3) Criação de uma visão para a tabela criada acima.

CREATE VIEW guru99_emp_view(Employee_name:dept_name,location) ASSELECT emp.emp_name,dept.dept_name,dept.locationFROM emp,deptWHERE 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.

Resultado

Ver criado

NOME DO EMPREGADO	DEPT_NAME	LOCALIZAÇÃO
ZZZ	RH	EUA
AAA	VENDAS	Reino Unido
Xxx	FINANCEIRO	JAPÃO

Etapa 4) Atualização da visão antes, em vez de disparar

BEGINUPDATE 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'. Ele levantou a exceção porque as instruções DML não são permitidas na visão complexa.

Resultado

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

ORA-06512: na linha 2

Etapa 5) Para evitar o erro encontrado durante a atualização da visualização na etapa anterior, nesta etapa usaremos "em vez de disparar".

CREATE TRIGGER guru99_view_modify_trgINSTEAD OF UPDATEON guru99_emp_viewFOR EACH ROWBEGINUPDATE deptSET location=:new.locationWHERE 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 visualização 'guru99_emp_view' no nível ROW. Ele contém a instrução de atualização para atualizar a localização 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.

Resultado

Gatilho criado

Etapa 6) Atualização da visão após o gatilho. Agora, o erro não virá, pois o "em vez do gatilho" tratará da operação de atualização dessa visão complexa. E quando o código for executado, a localização do funcionário XXX será atualizada para "França" de "Japão".

BEGINUPDATE 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'. É bem-sucedido porque o gatilho 'INSTEAD OF' interrompeu a instrução de atualização real na exibição e executou a atualização da tabela base.
Linha de código 55: Verificando o registro atualizado.

Resultado:

Procedimento PL / SQL concluído com sucesso

NOME DO EMPREGADO	DEPT_NAME	LOCALIZAÇÃO
ZZZ	RH	EUA
AAA	VENDAS	Reino Unido
Xxx	FINANCEIRO	FRANÇA

Gatilho Composto

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

ANTES DE DECLARAÇÃO - nível
ANTES DA LINHA - nível
APÓS LINHA - nível
APÓS DECLARAÇÃO - nível

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

CREATE [ OR REPLACE ] TRIGGER 
FOR[INSERT | UPDATE | DELET… .]ON ‭ ‬BEFORE STATEMENT ISBEGIN;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN;END EACH ROW;AFTER EACH ROW ISBEGIN;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN;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 esses 4 blocos de temporização. Podemos criar um acionador COMPOSTO apenas para os tempos que forem necessários.

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

CREATE TRIGGER emp_trigFOR INSERTON empCOMPOUND TRIGGERBEFORE EACH ROW ISBEGIN:new.salary:=5000;END BEFORE EACH ROW;END emp_trig;/

BEGININSERT 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 BEFORE ROW- nível para preencher o salário com o valor padrão 5000. Isso mudará 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.

Resultado:

Gatilho criado

Procedimento PL / SQL concluído com sucesso.

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

Ativando e desativando 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 desativa ou habilita.

Abaixo está a sintaxe para habilitar / desabilitar os gatilhos.

ALTER TRIGGER 
 [ENABLE|DISABLE];ALTER TABLE 
 [ENABLE|DISABLE] ALL TRIGGERS;

Explicação da sintaxe:

A primeira sintaxe mostra como habilitar / desabilitar o gatilho único.
A segunda instrução mostra como habilitar / desabilitar todos os gatilhos em uma determinada tabela.

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.