Artigo SQL Magazine 46 - 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."

[...] continue lendo...