Banco de Dados 2

1. Banco de Dados 2 Prof. Dr. Sylvio Barbon Junior prof.sylvio@fatecriopreto.edu.br

2. 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;

3. 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.

4. 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);

5. 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 inválidas;

6. 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;

7. Banco de Dados 2 – Aula 7 TRIGGER 3) Privilégios para o uso dos Triggers • É necessário o privilégio CREATE TRIGGER; • É necessário ter privilégios de acesso a tabela em questão. GRANT CREATE TRIGGER TO USUARIO;

8. Banco de Dados 2 – Aula 7 TRIGGER 4) Criando Triggers CREATE OR REPLACE TRIGGER Imprimir_Diferenca_Trigger BEFOREDELETE 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;

9. 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;

10. 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;

11. 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;

12. 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;

13. 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");

14. 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;

15. 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_triggers.htm