Artigo SQL Magazine 46 - Triggers no Oracle

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (1)  (0)

Neste artigo, irei explorar as várias possibilidades de utilização de triggers no Oracle.

Esse artigo faz parte da revista SQL Magazine edição 46. Clique aqui para ler todos os artigos desta edição

 

Triggers no Oracle

Gatilhos disparados no momento certo!

 

Neste artigo, irei explorar as várias possibilidades de utilização de triggers no Oracle. Para isso, farei uso de exemplos práticos que você poderá executar em sua própria estação de trabalho, desde que tenha um banco de dados instalado, ou tenha acesso a um servidor de banco de dados.

 

O ambiente

Neste artigo, utilizei um Oracle Database 10g Enterprise Editon Release 10.2.0.1.0 em um banco de dados criado através do utilitário DBCA (Database Configuration Assistant), onde selecionei a opção de instalação dos esquemas de demonstração. Utilizaremos o velho e bom esquema de demonstração SCOTT cuja senha inicial continua sendo TIGER. O Oracle 10g, por padrão, mantém a conta travada (locked), não se esqueça de conectar-se como SYS e destravá-la. Na primeira conexão feita com o usuário SCOTT, será solicitada a alteração da senha.

Caso já possua um banco de dados criado e o esquema SCOTT não tenha sido instalado, você poderá fazê-lo manualmente executando o script utlsampl.sql, que pode ser encontrado no diretório ORACLE_HOME/rdbms/admin/ (barra invertida - \ - no Windows).

A Listagem 1 apresenta os passos necessários para a criação do ambiente.

 

Listagem 1. Criando o esquema SCOTT ou destravando-o, caso já esteja criado.

SYS@RRDB> -- Criando o esquema SCOTT manualmente.

SYS@RRDB> @?\rdbms\admin\utlsampl

 

SYS@RRDB> -- Destravando o esquema SCOTT, caso o mesmo tenha sido criado juntamente com o BD.

SYS@RRDB> ALTER USER SCOTT ACCOUNT UNLOCK;

User altered.

 

SYS@RRDB> -- Conectando com o usuário SCOTT.

SYS@RRDB> conn scott@rrdb

Enter password: *****

Connected.

 

Apenas um parêntesis, RRDB é o nome da instância que estou utilizando.

 

Afinal, o que são Triggers?

 

Triggers, ou gatilhos, nada mais são que stored procedures em PL/SQL ou Java que são executadas (disparadas) implicitamente, ou seja, sem nenhum “chamamento” explícito, sempre que uma tabela ou view é modificada ou ainda quando acontece alguma determinada ação do usuário ou até mesmo ações do próprio banco de dados.

Triggers são uma parte crítica de qualquer aplicação bem modelada e, através deles, é possível garantir as seguintes funcionalidades:

·         Executar validações de alterações feitas em tabelas ou views: triggers oferecem uma forte garantia na validação de informações, pois esta validação está ligada diretamente ao objeto do banco de dados (tabela ou view).

·         Automatizar manutenções no banco de dados: a partir da versão 8i, é possível associar triggers a eventos do banco de dados, como uma inicialização (startup), permitindo executar tarefas de limpeza na inicialização, por exemplo;

·         Aplicar regras a respeito de atividades de administração de uma maneira extremamente granular: pode-se usar triggers para controlar qual o tipo de alteração que se pode fazer em determinado objeto, como apagar (drop) ou alterar uma tabela.

 

São cinco os tipos de eventos que podem “disparar” um trigger:

·         DML (Data Manipulation Language): sempre que um evento do tipo insert, update ou delete ocorrer na tabela, o trigger será “disparado”;

·         DDL (Data Definition Language): o trigger será disparado sempre que um evento DDL ocorrer, como a criação de uma tabela. É muito útil para o caso de auditorias ou para evitar que certas operações sejam executadas;

·         Eventos do banco de dados: eventos como startup, shutdown, sempre que um usuário conectar-se ou desconectar-se e até mesmo sempre que um erro Oracle ocorrer, o trigger será “disparado”;

·         INSTEAD OF: Instead of (em vez de) triggers são uma ótima alternativa aos triggers de DML, pois eles são “disparados” quando um evento do tipo insert, update ou delete estão para acontecer. O código do trigger define o que deverá acontecer no lugar dos eventos. Este tipo de trigger controla operações em views, não em tabelas;

·         Comando suspenso (suspended statement): este conceito foi introduzido no Release 1 do Oracle 9i. Caso um comando sofra um problema de espaço em uma tablespace, por exemplo, o comando ficará “suspenso” até que o problema seja resolvido. Pode-se utilizar este tipo de trigger para enviar um alerta ao DBA ou até mesmo resolver o problema automaticamente, dependendo do caso.

 

Nas próximas seções abordarei cada um dos eventos de maneira prática em nosso banco de testes no esquema SCOTT.

 

Hands On

 

Vamos lá, agora é hora de colocar a “mão na massa” e iremos criar triggers utilizando todos os eventos mencionados na seção anterior. A partir daí, basta usar sua criatividade e necessidade para usufruir ao máximo desta poderosa ferramenta.

 

Triggers de DML

 

Sempre que um evento de insert, update ou delete ocorrer em uma tabela, o trigger será executado. Este é o tipo mais comum de trigger, particularmente para os desenvolvedores; os outros tipos de triggers são mais comumente utilizados pelos DBAs.

Existem várias maneiras de trabalhar com este tipo de trigger. Podemos querer que o trigger seja “disparado” antes ou depois de um comando DML, podemos ainda querer a execução antes ou depois de cada linha afetada pelo comando e, finalmente, podemos utilizar o trigger para cada um dos eventos ou uma combinação deles.

É muito importante que três perguntas sejam respondidas antes de programar o trigger:

·         O trigger deve ser “disparado” uma vez para todo o comando DML (statement level) ou para cada linha afetada por este comando (row level)?

·         O trigger deve ser “disparado” “antes ou depois” de todo o comando DML ou “antes ou depois” de cada linha afetada pelo comando (BEFORE ou AFTER triggers)?

·         O trigger deve ser disparado sempre que houver um insert, um update ou um delete, ou ainda uma combinação deles?

 

Temos ainda outros três componentes que são: os pseudo-registros NEW e OLD e a cláusula WHEN. Vejamos na Tabela 1 o que significa cada um destes componentes.

 

Variável

Descrição

BEFORE trigger

O trigger será executado antes que o evento efetivamente ocorra, por exemplo, BEFORE INSERT.

AFTER trigger

O trigger será executado após o evento efetivamente ter ocorrido, por exemplo, AFTER UPDATE.

Statement level trigger

Este tipo de trigger será executado para o comando como um todo, independente do número de linhas que sejam afetadas por ele. Nestes casos, o trigger, após executado, pode afetar uma ou mais linhas na tabela.

Row level trigger

Neste caso, cada linha afetada pelo comando “disparará” o trigger, em outras palavras, caso um update afete 1000 linhas, o trigger será “disparado” 1000 vezes.

Pseudo-registro NEW

Este pseudo-registro está disponível apenas para triggers de insert e update. Ele armazenará o novo valor da coluna, ou seja, o valor que “entrará” na tabela pelo insert ou update.

Pseudo-registro OLD

Este pseudo-registro está disponível apenas para triggers de update e delete. Ele armazenará o valor antigo da coluna, ou seja, o valor que “sairá” da tabela pelo update ou delete.

Cláusula WHEN

Está cláusula permite que o trigger nem sempre seja executado, pois mesmo que haja o evento (insert, update ou delete), a cláusula WHEN deverá ser satisfeita para que o trigger seja “disparado”.

Tabela 1. Componentes de um trigger de DML.

 

Vejamos agora, na Listagem 2, a sintaxe de criação de um trigger de DML.

Listagem 2. Sintaxe de criação de um trigger de DML.

CREATE [OR REPLACE] TRIGGER nome_do_trigger

  {BEFORE | AFTER}

  {INSERT | DELETE | UPDATE | UPDATE OF lista_de_colunas} ON nome_da_tabela

  [FOR EACH ROW]

  [WHEN (...)]

DECLARE

  Declaracao de variaveis

BEGIN

    ... codigo SQL e/ou PL/SQL ...

  [EXCEPTION ... ]

END [nome_do_trigger];

Row level trigger

Apenas um detalhe, a cláusula FOR EACH ROW é o que define que o trigger é do tipo Row level, sua omissão faz com que o trigger seja do tipo Statement level.

Para iniciar nossos exemplos, faremos algumas alterações nas tabelas do esquema SCOTT. Estas alterações serão mostradas no decorrer do artigo.

Iniciarei a construção de alguns triggers de DML e explicarei cada exemplo.

Neste primeiro exemplo, criaremos um trigger de DML do tipo Row level que será disparado antes (BEFORE) da ação. A ação poderá ser uma inserção (INSERT), alteração (UPDATE) ou exclusão (DELETE) e será “disparada” para cada linha afetada pelo comando (FOR EACH ROW).

A Listagem 3 mostra os passos que serão executados.

 

Listagem 3. Trigger de DML, BEFORE INSERT ou, UPDATE.

SCOTT@RRDB> -- Criando o trigger.

SCOTT@RRDB> CREATE OR REPLACE TRIGGER TR_EMP_RBIU

  2    BEFORE INSERT OR UPDATE ON EMP

  3    FOR EACH ROW

  4  DECLARE

  5    V_MAN NUMBER(2);

  6  BEGIN

  7    IF INSERTING THEN

  8      IF :NEW.DEPTNO = 10 AND :NEW.SAL > 5000 THEN

  9        RAISE_APPLICATION_ERROR(-20000,'Admissão não permitida no departamento ACCOUNTING. Salário maior que $5000,00');

 10      ELSIF :NEW.DEPTNO = 20 AND :NEW.SAL > 3000 THEN

 11        RAISE_APPLICATION_ERROR(-20001,'Admissão não permitida no departamento RESEARCH. Salário maior que $3000,00');

 12      ELSIF :NEW.DEPTNO = 30 AND :NEW.SAL > 2850 THEN

 13        RAISE_APPLICATION_ERROR(-20002,'Admissão não permitida no departamento SALES. Salário maior que $2850,00');

 14      ELSIF :NEW.DEPTNO = 40 AND :NEW.SAL > 2000 THEN

 15        RAISE_APPLICATION_ERROR(-20003,'Admissão não permitida no departamento OPERATIONS. Salário maior que $2000,00');

 16      END IF;

 17    END IF;

 18    IF UPDATING THEN

 19      IF :NEW.DEPTNO = 10 AND :NEW.SAL > 5000 THEN

 20        :NEW.SAL := :OLD.SAL;

 21        DBMS_OUTPUT.PUT_LINE('Salário definido para o funcionário '||:NEW.EMPNO||' maior que o permitido para o departamento ACCOUNTING. Salário atual mantido.');

 22      ELSIF :NEW.DEPTNO = 20 AND :NEW.SAL > 3000 THEN

 23        :NEW.SAL := :OLD.SAL;

 24        DBMS_OUTPUT.PUT_LINE('Salário definido para o funcionário '||:NEW.EMPNO||' maior que o permitido para o departamento RESEARCH. Salário atual mantido.');

 25      ELSIF :NEW.DEPTNO = 30 AND :NEW.SAL > 2850 THEN

 26        :NEW.SAL := :OLD.SAL;

 27        DBMS_OUTPUT.PUT_LINE('Salário definido para o funcionário '||:NEW.EMPNO||' maior que o permitido para o departamento SALES. Salário atual mantido.');

 28      ELSIF :NEW.DEPTNO = 40 AND :NEW.SAL > 2000 THEN

 29        :NEW.SAL := :OLD.SAL;

 30        DBMS_OUTPUT.PUT_LINE('Salário definido para o funcionário '||:NEW.EMPNO||' maior que o permitido para o departamento OPERATIONS. Salário atual mantido.');

 31      END IF;

 32    END IF;

 33  END TR_EMP_RBIU;

 34  /

 

Trigger created.

 

Elapsed: 00:00:00.15

 

SCOTT@RRDB> -- Verificando os valores da tabela EMP para o departamento 10.

SCOTT@RRDB> SELECT EMPNO, ENAME, JOB, SAL

  2    FROM EMP

  3    WHERE DEPTNO=10;

 

     EMPNO ENAME      JOB              "

A exibição deste artigo foi interrompida :(
Este post está disponível para assinantes MVP

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?