Oracle PL/SQL Trigger Tutorial: em Vez de, Composto [Exemplo]

neste tutorial, você vai aprender

• Benefícios de Gatilhos
• Tipos de Gatilhos em Oracle
• Como Create Trigger
• :NOVO e :VELHO Cláusula
• em VEZ DE Disparar
• Composto de Trigger

Benefícios de Gatilhos

estes são os benefícios de gatilhos.

• Gerar algum derivado valores da coluna automaticamente
• a Impor a integridade referencial
• o log de Eventos e armazenar informações sobre a tabela de acesso
• Auditoria
• replicação Síncrona de tabelas
• a Imposição de autorizações de segurança
• Impedindo inválido transações

Tipos de Gatilhos em Oracle

Triggers podem ser classificadas com base nos seguintes parâmetros.

• classificação baseada na cronometragem
  • antes do desencadeamento: dispara antes da ocorrência especificada.
  • Após o desencadeamento: dispara após o acontecimento especificado.
  • em vez de gatilho: um tipo especial. Você vai aprender mais sobre os outros tópicos. (apenas para DML )
• classificação baseada no nível de desencadeamento do nível de declaração: dispara uma vez para a declaração de eventos especificada.
• gatilho de nível da linha: dispara para cada registo que foi afectado no evento especificado. (apenas para DML)

classificação baseada na ocorrência

• DML Trigger: dispara quando o evento DML é especificado (inserir / actualizar / apagar)
• DDL Trigger: Ele dispara quando o evento DDL é especificado (criar/alterar)
• banco de dados gatilho: ele dispara quando o evento da base de dados é especificado (LOGON/LOGOFF/STARTUP/SHUTDOWN)

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

como criar Trigger

abaixo está a sintaxe para criar um trigger.

CREATE TRIGGER <trigger_name> ON<name of underlying object> DECLARE<Declaration part>BEGIN<Execution part> EXCEPTION<Exception handling part> END;

Sintaxe Explicação:

• A sintaxe acima mostra as diferentes opcional instruções que estão presentes no gatilho criação.
• Antes/ Depois irá especificar os horários do evento.
• INSERT/UPDATE/LOGON/CREATE / etc. especificará o evento para o qual o gatilho precisa ser disparado.
• na cláusula irá especificar em que objecto o evento acima mencionado é válido. Por exemplo, Este será o nome da tabela em que o evento DML pode ocorrer no caso de DML Trigger.o comando
• “para cada linha” irá indicar a margem de variação do nível da linha.
• Quando a cláusula especificar a condição adicional em que o gatilho precisa disparar.
• a parte da declaração, a parte de execução, a parte de tratamento de excepções é igual à dos outros blocos PL/SQL. A parte da declaração e a parte do tratamento das excepções são opcionais.

: nova e antiga Cláusula

numa linha de gatilho, os disparos de cada linha relacionada. E às vezes é necessário saber o valor antes e depois da declaração DML.

Oracle forneceu duas cláusulas no dispositivo de desencadeamento do nível de registo para manter estes valores. Podemos usar estas Cláusulas para nos referirmos aos valores antigos e novos dentro do corpo do gatilho.

• :NOVO – Ele tem um novo valor para as colunas da tabela base/vista durante a execução do disparador
• :IDADE – Ele tem valor antigo das colunas da tabela base/vista durante a execução do disparador

Esta cláusula deve ser utilizado com base na DML de evento. A tabela abaixo irá especificar qual cláusula é válida para qual declaração DML (inserir/actualizar/apagar).

	INSERIR	ATUALIZAR	APAGAR
:NOVO	VÁLIDO	VÁLIDO	INVÁLIDA. Não há nenhum novo valor no caso de exclusão.
:Antigo	inválido. Não existe um valor antigo em caso de inserção	válido	válido

em vez de gatilho

“em vez de gatilho” é o tipo especial de gatilho. É usado apenas em gatilhos DML. É usado quando qualquer evento DML vai ocorrer na vista complexa.

considere um exemplo no qual uma vista é feita a partir de 3 tabelas de base. Quando qualquer evento DML é emitido sobre esta vista, que se tornará inválido porque os dados são retirados de 3 tabelas diferentes. Então, neste lugar do gatilho é usado. O em vez de trigger é usado para modificar as tabelas de base diretamente em vez de modificar a janela para o evento dado.

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

• Table_1 é a tabela emp e

• Table_2 é a tabela departamental.

então vamos ver como o em vez de trigger é usado para emitir atualizar a declaração de detalhes de localização nesta área complexa. Nós também vamos ver como o: novo e: velho é útil em gatilhos.

• Passo 1: Criando a tabela ‘emp’ e ‘departamento’ com as devidas colunas
• Passo 2: Preencher a tabela com os valores de exemplo
• Passo 3: Criando vista para o acima criada a tabela
• Passo 4: Atualização de vista antes em vez de disparar
• Passo 5: Criação de em vez de disparar
• Passo 6: Atualização de vista, depois de, em vez de disparar

Passo 1) Criando a tabela ‘emp’ e ‘departamento’ com as devidas colunas

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 de Código

• linha de Código 1-7: Criação da tabela ‘emp’.
• linha de código 8-12: quadro “dept” criação.

saída

tabela criada

Passo 2) Agora que criamos a tabela, vamos povoar esta tabela com valores de amostra e criação de vistas 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 de Código

• linha de Código 13-19: Inserir dados no ‘departamento’ tabela.
• linha de código 20-26: inserção de dados na tabela “emp”.

Saída

pl/SQL procedimento concluído

Passo 3) criação de uma vista para a tabela acima criada.

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 de Código

• linha de Código 27-32: a Criação de “guru99_emp_view’ modo de exibição.

• linha de código 33: Querying guru99_emp_view.

Output

View created

EMPLOYEE_NAME	DEPT_NAME	LOCATION
ZZZ	HR	USA
YYY	SALES	UK
XXX	FINANCIAL	JAPAN

Step 4) Update of view before instead-of trigger.

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

Code Explanation

• Code line 34-38: Update the location of “XXX” to ‘FRANCE’. Ele levantou a exceção porque as declarações DML não são permitidas na vista complexa.

Saída

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

ORA-06512: em linha 2

Etapa 5)Para evitar o erro ocorrer durante a actualização ver na etapa anterior, neste passo, vamos usar “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 de Código

• linha de Código 39: Criação em vez de activar para o evento ‘UPDATE’ na janela ‘guru99_emp_view’ ao nível da linha. Contém a declaração de actualização para actualizar a localização na tabela de base “dept”.
• linha de código 44: a instrução de actualização usa ‘: novo’ e’: antigo ‘ para encontrar o valor das colunas antes e depois da actualização.

saída

gatilho criado

Passo 6) actualização da vista após em vez da activação. Agora o erro não virá como o “em vez de trigger” irá lidar com a operação de atualização desta área complexa. E quando o código tiver executado a localização do empregado XXX será atualizado para “França” do 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 do “XXX” para “a FRANÇA”. Ele é bem sucedido porque o’ em vez de ‘ trigger parou a atual declaração de atualização na view e realizou a atualização da tabela base.linha de código 55: verificação do registo actualizado.

resultado:

PL/SQL procedimento concluído com sucesso

EMPLOYEE_NAME	DEPT_NAME	LOCALIZAÇÃO
ZZZ	HR	EUA
Y	VENDAS	PORTUGAL
XXX	FINANCEIRO	FRANÇA

Composto de Trigger

O Composto de trigger é um gatilho que permite especificar ações para cada um dos quatro pontos de horário no único trigger corpo. Os quatro pontos de tempo diferentes que suporta é como abaixo.

• antes da declaração – nível
• antes da linha – nível
• Após linha – nível
• após declaração – nível

proporciona a possibilidade de combinar as acções para um calendário diferente no mesmo gatilho.

CREATE TRIGGER <trigger_name> FORON <name of underlying object><Declarative part>‭ ‬BEFORE STATEMENT ISBEGIN<Execution part>;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN<Execution part>;END EACH ROW;AFTER EACH ROW ISBEGIN<Execution part>;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN<Execution part>;END AFTER STATEMENT;END;

Sintaxe Explicação:

• A sintaxe acima mostra a criação de ‘COMPOSTO’ gatilho.
• a seção declarativa é comum para todos os blocos de execução no corpo de gatilho.estes 4 blocos podem estar em qualquer sequência. Não é obrigatório ter todos estes quatro blocos de tempo. Podemos criar um detonador composto apenas para os horários que são necessários.

exemplo 1: Neste exemplo, vamos criar um gatilho para auto-povoar a coluna salarial com o valor padrão 5000.

CREATE TRIGGER emp_trig FOR INSERT ON empCOMPOUND TRIGGER BEFORE EACH ROW IS BEGIN: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 composto gatilho. Ele é criado para timing antes do nível de linha para povoar o salário com valor padrão 5000. Isto irá mudar o salário para o valor padrão ‘5000’ antes de inserir o registro na tabela.linha de código 11-14: inserir o registo na tabela “emp”.linha de código 16: verificação do registo inserido.

saída:

gatilho criado

PL / SQL procedimento concluído com sucesso.

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

Activar e Desactivar Dispara

Gatilhos pode ser habilitado ou desabilitado. Para activar ou desactivar o gatilho, é necessário indicar uma instrução ALTER (DDL) para o gatilho que o desactive ou Active.

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

ALTER TRIGGER <trigger_name> ;ALTER TABLE <table_name> ALL TRIGGERS;

explicação de sintaxe:

• a primeira sintaxe mostra como activar/desactivar a activação única.
• a segunda instrução mostra como activar/desactivar todos os gatilhos numa tabela em particular.

resumo

neste capítulo, aprendemos sobre gatilhos PL / SQL e suas vantagens. Também aprendemos as diferentes classificações e discutimos em vez de trigger e trigger composto.