Banco de Dados 2
Prof. Dr. Sylvio Barbon Junior
[email protected]
Banco de Dados 2 – Aula 7
TRIGGER
Sumário:
1) Visão Geral dos Triggers;
2) Boas Práticas do uso dos Triggers;
3) Privilégios necessários para o uso dos Triggers;
4) Criando Triggers;
5) Codificando o corpo do Trigger;
6) Obtendo informações sobre o Trigger;
7) Ativando e Desativando o Trigger;
8) Exemplos;
Banco de Dados 2 – Aula 7
TRIGGER
1) Visão Geral
- É um programa armazenado no banco de dados que é
disparado (executado) em resposta a um evento;
- O evento que dispara o trigger pode estar associado a uma
tabela, view ou database, exemplo:
- DML: DELETE, INSERT ou UPDATE;
- DDL: CREATE, ALTER ou DROP;
- Operações: SERVERERROR, LOGON, LOGOFF ETC.
Banco de Dados 2 – Aula 7
TRIGGER
1) Visão Geral
–
–
–
–
–
DML Trigger: Disparado por comando DML (DELETE,
INSERT ou UPDATE);
DDL Trigger: Disparado por comando DDL (CREATE,
ALTER ou DROP);
INSTEAD OF Trigger é um DML Trigger definido em
uma VIEW;
SYSTEM Trigger, associados a um usuários e a
diversas operações que podem ser realizadas;
As triggers pode ser disparadas nos seguintes pontos:
•
Antes da execução de uma operação (BEFORE);
•
Após a execução de uma operação (AFTER);
•
Antes de cada linha afetada por uma operação
(BEFORE each ROW);
•
Depois da execuação da operação, linha a linha
(AFTER each ROW);
Banco de Dados 2 – Aula 7
TRIGGER
1) Visão Geral
–
–
Os Triggers podem ter 2 estados:
•
Ativado (Enabled)
•
Desativado (Disabled)
Usos comuns para os Triggers:
•
Automaticamente gerar valores à elementos
derivados;
•
Reforçar a integridade referencial em nós de bases
distribuídas;
•
Reforçar regras de negócios complexas;
•
Criar sistemas de log transparentes;
•
Promover auditoria;
•
Manter a sincronização de tabelas replicadas;
•
Adquirir estatísticas do uso de tabelas;
•
Ajustar valores antes da persistência;
•
Restringir operações considerando horário ou outra
informação;
•
Reforçar a segurança e previnir transações
Banco de Dados 2 – Aula 7
TRIGGER
2) Boas Práticas no uso dos Triggers
–
–
–
–
Use os triggers para garantir que certa operação será
realizada automaticamente;
Não crie um trigger que pode ser utilizado com uma
solução mais simples:
•
Não defina um trigger para rejeitar algum dado
incorreto, utilize para isso as constraints;
•
A Oracle recomenda o uso dos triggers para
restringir data nas seguintes situações:
»
Bases Distribuídas;
»
Regras Complexas de Validação;
»
Quando a restrição não se aplica a uma
constraint: NOT NULL, UNIQUE, PK, FK,
CHECK, DELETE CASCADE ou DELETE
SET NULL;
O comprimento de um código de Trigger não pode
passar de 32K
Não crie Triggers recursivos;
Banco de Dados 2 – Aula 7
TRIGGER
3) Privilégios para o uso dos Triggers
– É necessário o privilégio CREATE
TRIGGER;
GRANT CREATE TRIGGER TO USUARIO;
–
É necessário ter privilégios de acesso a
tabela em questão.
Banco de Dados 2 – Aula 7
TRIGGER
4) Criando Triggers
CREATE OR REPLACE TRIGGER Imprimir_Diferenca_Trigger
BEFORE DELETE OR INSERT OR UPDATE ON Empregado
FOR EACH ROW
WHEN (NEW.EMPNO > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.SAL - :OLD.SAL;
dbms_output.put('Salário Antigo: ' || :OLD.sal);
dbms_output.put('Novo Salário: ' || :NEW.sal);
dbms_output.put_line('Diferença:' || sal_diff);
END;
Banco de Dados 2 – Aula 7
TRIGGER
4) Criando Triggers
–
–
–
–
OR REPLACE: garante que se o trigger existir, será
substituído;
BEFORE: especifica que o trigger será disparado
antes da execução da operação;
•
Não se pode especificar o BEFORE em uma
VIEW ou objeto complexo;
•
Você pode modificar o :NEW e não o :OLD;
AFTER: especifica que o trigger será disparado após
a execução da operação;
•
Não se pode especificar o AFTER em uma VIEW
ou objeto complexo;
•
Não se pode modificar o :NEW nem o :OLD;
FOR EACH ROW: especifica que o trigger executará
seu algoritmo para cada linha afetada;
Banco de Dados 2 – Aula 7
TRIGGER
4) Criando Triggers
–
–
–
WHEN: especifica um condição que deve ser
satisfeita para o disparo do trigger;
DECLARE: escopo para criação de variáveis;
BEGIN e END: determinam o escopo do algoritmo
que será executado pelo trigger;
Banco de Dados 2 – Aula 7
TRIGGER
5) Codificando o corpo do Trigger
–
Linguagem PL/SQL;
DECLARE
--instruções
BEGIN
-- instruções
END;
–
Variáveis devem ser declaradas no bloco DECLARE:
v_produto_id INTEGER;
v_nome
VARCHAR(40);
v_preco
NUMBER(5,2);
–
Lógica Condicional
IF (condição) THEN
ELSEIF (condição) THEN
END IF;
Banco de Dados 2 – Aula 7
TRIGGER
5) Codificando o corpo do Trigger
–
Estrutura de Laço
LOOP
-- instrução
END LOOP;
Exemplo:
v_contador := 0;
LOOP
v_contador := v_contador + 1;
EXIT WHEN v_contador = 5;
END LOOP;
WHILE v_contador < 6 LOOP
v_contador := v_contador +1;
END LOOP;
Banco de Dados 2 – Aula 7
TRIGGER
5) Codificando o corpo do Trigger
–
Estrutura Loop (continuação)
FOR v_contador IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(v_contador);
END LOOP;
–
Encerrando o Fluxo
raise_application_error(-20001,"mensagem");
Banco de Dados 2 – Aula 7
TRIGGER
5) Codificando o Corpo do Trigger
– Para disparar as saídas do trigger, você
precisa executar o comando:
SET SERVEROUTUP ON;
6) Obtendo informações sobre o Trigger
– Visão: user_triggers, exemplo:
SELECT * FROM user_triggers
WHERE trigger_name = 'MINHATRIGGER';
7) Ativando e Desativando Triggers
– ALTER TRIGGER 'NOME' ENABLE;
– ALTER TRIGGER 'NOME' DISABLE;
Banco de Dados 2 – Aula 7
TRIGGER
Referências Bibliográficas:
Sistemas de Gerenciamento de Bancos de Dados - 3ªed. R.Ramakrishnan, J.Gehrke – McGraw-Hill
http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_t
riggers.htm