Artigos Banco de Dados 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.

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

Quer ler esse conteúdo completo? Tenha acesso completo