Trabalhando com Triggers DML no Oracle

Neste artigo vamos apresentar o conceito de Triggers no banco de dados Oracle 11g, a fim de vermos como e quando utilizá-las.

Uma trigger é basicamente uma unidade PL/SQL que é armazenada na base de dados e pode ser chamada várias vezes no decorrer de uma operação, de igual forma a uma stored procedure. A diferença entre as duas operações é que, diferentemente das stored procedures, as triggers podem ser habilitadas ou desabilitadas, e no momento em que está habilitada pode ser utilizada a qualquer momento.

Para criarmos uma trigger utilizamos a instrução CREATE TRIGGER, e esta, ao ser escrita, será executada em resposta a algum evento que tenha feito a solicitação com relação ao item que irá utilizá-la. Estes itens aos quais nos referimos podem ser Views, tables, schemas ou mesmo bases de dados as quais os eventos estejam associados. Além disso, podemos também definir o momento no qual as triggers serão disparadas, como por exemplo, se será antes ou depois da declaração ser executada. Por padrão, temos que a trigger ao ser criada, já encontra-se habilitada. Normalmente uma trigger é utilizada com base em três eventos, sendo estes, em instruções DDL, instruções DML e operações de banco de dados, vejamos então um pouco sobre cada uma delas.

• Instruções DML – estas como bem sabemos, são utilizadas para manipulação de dados através das instruções de Delete, Insert e Update.
• Instruções DDL – estas são mais utilizadas em momentos que são necessárias auditoria de alteração em objetos de um schema, como é o caso da utilização de operações como Create, Drop e Alter.
• Operações de banco de dados – neste caso, as triggers são utilizadas nas operações de startup, Logon, Logof, dentre outras operações.

No decorrer do nosso artigo, estaremos interessados em tratar sobre pontos referentes as triggers DML, pois estas são mais utilizadas nos projetos.

Por quê devemos usar triggers?

Veremos aqui algumas das razões pelas quais devemos usar as triggers, pois elas permitem que possamos personalizar o nosso sistema de gerenciamento de banco de dados. Alguns dos exemplos que podemos citar são geração automática de valores em colunas virtuais, log de eventos, mudança de dados da tabela quando DML’s que são disparados em views, imposição da integridade referencial, prevenção de transações inválidas, dentre outras. Para desenvolvermos nossas triggers, precisamos ter alguns cuidados e seguir algumas diretrizes, como podemos citar a seguir:

• Utilização de triggers para garantir que quando uma operação específica seja realizada, as ações relacionadas a ela também o sejam;
• Não definir triggers que dupliquem funções do banco de dados;

Mesmo que possamos utilizar triggers e restrições de integridade (integrity constraint) para definir e assegurar qualquer tipo de regra de integridade, a Oracle não recomenda que utilizemos triggers para restringir a entrada de dados em alguns casos, como descritos a seguir:

• Em casos onde são utilizados para impor a integridade referencial no momento em que as tabelas dependentes estão em diferentes nós de um banco de dados distribuído;
• E quando uma regra de integridade referencial necessária não pode ser executada utilizando restrições como o NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DELETE CASCADE ou DELETE SET NULL.

O que podemos fazer e não fazer com triggers?

Ao trabalharmos com triggers, precisamos tomar alguns cuidados, como é o caso do tamanho da lógica que será utilizada, pois esta não pode passar de 32k. Não devemos também criar triggers recursivas. Devemos, no entanto, utilizar as triggers de forma criteriosa na base de dados, pois estas são executadas para cada usuário para o qual a trigger foi criada. Em casos que utilizamos triggers de Login para fazer o monitoramento do logon dos usuários, estes incluem manipulações de exceção nas triggers, pois caso tenhamos alguma exceção que não tenha sido tratada, isso poderá bloquear todas as conexões do banco de dados. Já se utilizamos as triggers de logon apenas para a execução de pacotes, podemos colocar a manipulação de exceção diretamente no pacote, ao invés de ser na trigger. A utilização das triggers é de extrema importância para qualquer aplicação, pois com ela podemos garantir algumas funcionalidades, como por exemplo:

• A execução de validações quanto a alteração realizada em tables ou Views – isso acontece pelo fato de que triggers oferecem uma maior garantia quanto a validação de informações, já que as validações estão ligadas diretamente ao objeto do banco de dados.
• Automatiza a manutenção no banco de dados – neste ponto, é possível a associação de triggers a eventos do banco de dados.

Criando uma Trigger

Para criarmos uma trigger em estado desativado, devemos utilizar a cláusula DISABLE da instrução CREATE TRIGGER. Para obtermos mais informações sobre os estados das triggers, podemos ver na documentação oficial da Oracle através do seu site. Para a criação de uma trigger, precisamos ter noção quanto a sintaxe básica de sua estrutura, a qual apresentamos de acordo com a Listagem 1.

Listagem 1. Sintaxe básica de criação de uma trigger.

CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END;

Como podemos observar pela Listagem 1, definiremos a seguir o que significa cada um dos trechos presentes na sintaxe, onde:

• CREATE [OR REPLACE] TRIGGER trigger_name – é definido para a criação de uma trigger ou a sobrescrita de uma trigger já existente com o nome informado.
• {BEFORE | AFTER | INSTEAD OF } – neste ponto, definimos quando a trigger deverá ser executada. No caso da cláusula INSTEAD OF, esta é utilizada quando uma trigger é criada em uma View.
• {INSERT [OR] | UPDATE [OR] | DELETE} – neste ponto especificamos uma operação DML.
• [OF col_name] - especificamos aqui a coluna que será atualizada.
• ON table_name - aqui especificamos o nome da tabela que será associada a trigger.
• [REFERENCING OLD AS o NEW AS n] – aqui permitimos referências a valores novos e valores antigos para várias declarações do tipo DML.
• [FOR EACH ROW] - esta instrução especifica uma trigger no nível de linha.
• WHEN (condição) – neste momento, definimos a condição para que a trigger seja disparada nas linhas.

Dependendo da ferramenta que estejamos utilizando, como por exemplo o SQL*Plus, ao final da criação de nossa trigger, precisaremos adicionar uma “/” (barra) na última linha do código. Vejamos então um simples exemplo de criação de uma trigger, como a apresentada de acordo com a Listagem 2.

Listagem 2. Criando uma trigger simples.

CREATE OR REPLACE TRIGGER Atualiza_salario_func BEFORE DELETE OR INSERT OR UPDATE ON funcionario FOR EACH ROW WHEN (NEW.CODFUNC > 0) DECLARE diferenca_salario number; BEGIN diferenca_salario := :NEW.SALARIOFUNC - :OLD.SALARIOFUNC; dbms_output.put('Salário antigo:' || :OLD.salarioFunc); dbms_output.put('Salário novo:' || :NEW.salarioFunc); dbms_output.put_line(' A diferença de salário foi de: ' || diferenca_salario); END; /

Como podemos observar no código apresentado pela Listagem 2, temos uma trigger que é disparada quando as operações DML são executadas na tabela funcionario. Devido ao fato de estarmos declarando a palavra-chave “BEFORE” na nossa trigger, podemos acessar os novos valores antes que estes sejam enviados para a tabela, e dessa forma podemos alterar os valores. Caso ocorra alguma falha na execução, o erro poderá ser facilmente corrigido, atribuindo a cláusula NEW.column_name. Além disso, podemos utilizar a palavra-chave AFTER, caso tenhamos interesse em consultar ou mesmo alterar a mesma tabela. No momento em que utilizamos a cláusula FOR EACH ROW da trigger, podemos executar a instrução várias vezes, o que pode ocorrer em momentos onde precisamos realizar atualizações ou exclusões de várias linhas simultaneamente.

Após a criação da trigger, a instrução SQL dispara a trigger para cada linha de código que é atualizada, onde temos no fim, a apresentação dos valores dos salários antigo, novo e a diferença entre eles. Para a atualização dos valores, podemos definir então da seguinte forma:

UPDATE funcionario SET salarioFunc = salarioFunc + 750.00 WHERE codDep = 6;

Uma coisa que devemos ter em mente é que o nome das triggers deve ser exclusivo dentro de um mesmo schema, o que não ocorre com relação aos objetos de um schema, como é o caso de tabelas ou Views. Na Listagem 2, temos que a instrução ATUALIZA_SALARIO_FUNC é disparada após qualquer instrução de DELETE, INSERT ou UPDATE na tabela funcionários, onde podemos ver que qualquer uma das seguintes afirmações poderá acionar a trigger ATUALIZA_SALARIO_FUNC, que são:

DELETE FROM funcionario; INSERT INTO funcionario VALUES ( ... ); INSERT INTO funcionario SELECT ... FROM ... ; UPDATE funcionario SET ... ;

Outra maneira de criarmos nossas triggers é utilizando a cláusula INSTEAD_OF, a qual utilizaremos neste momento, onde primeiramente criaremos duas novas tabelas, as quais serão chamadas de cliente_juridico e cliente_fisico e em seguida, veremos como esta cláusula deve ser aplicada de acordo com os exemplos presentes na Listagem 3, 4, 5 e 6.

Listagem 3. Criando as tabelas cliente_físico e cliente_jurídico.

// Criando a tabela cliente_fisico CREATE TABLE cliente_fisico ( codigo NUMBER(6), nomeCli VARCHAR2(150), creditoDisp NUMBER(10,2) ); // Criando a tabela cliente_juridico CREATE TABLE cliente_juridico ( codigo NUMBER(6), nomeCli VARCHAR2(150), creditoDisp NUMBER(10,2) );

Ao criarmos nossas tabelas, preenchamos com alguns dados e em seguida, criaremos um novo tipo OBJECT, o qual, chamaremos de clienteObjeto. Nele teremos os mesmos dados presentes nas tabelas de clientes e um campo adicional que será o estado, como apresentado de acordo com a Listagem 4. Além disso, criaremos uma nova View que será responsável por apresentar todos os dados presentes nas tabelas de cliente_fisico e cliente_juridico, como podemos ver de acordo com a Listagem 5.

Listagem 4. Criação do tipo clienteObjeto e da View todosclientes.

CREATE TYPE clienteObjeto AS OBJECT ( codigo NUMBER(6), nomeCli VARCHAR2(50), creditoDisp NUMBER(9,2), estado VARCHAR2(2) );

Listagem 5. Criando a View todosClientes.

CREATE VIEW todosClientes (codigo) AS SELECT clienteObjeto (codigo, nomeCli, creditoDisp, 'PE') FROM cliente_fisico UNION ALL SELECT clienteObjeto (codigo, nomeCli, creditoDisp, 'BH') FROM cliente_juridico;

Agora que estamos com a nossa View e o nosso objeto clienteObjeto, iremos criar a nossa trigger, a qual chamaremos de triggerUtilizandoIsteadOf, como podemos ver de acordo com a Listagem 6.

Listagem 6. Criação da trigger triggerUtilizandoIsteadOf.

CREATE TRIGGER triggerUtilizandoIsteadOf INSTEAD OF INSERT ON todosClientes FOR EACH ROW BEGIN IF (:new.codigo.estado = 'PE') THEN INSERT INTO cliente_fisico VALUES (:new.codigo.codigo, :new.codigo.nomeCli,:new.codigo.creditoDisp); ELSE INSERT INTO cliente_juridico VALUES (:new.codigo.codigo, :new.codigo.nomeCli, :new.codigo.creditoDisp); END IF; END;

Como podemos ver de acordo com a Listagem 6, temos uma trigger que faz uma verificação com o Instead of de inserções e passa as informações por um FOR EACH ROW, por onde faz uma verificação com base no estado especificado para realizar as inserções nas tabelas de clientes especificadas.

Triggers compostas

Uma trigger composta permite que o código para um ou mais pontos de tempo para um objeto específico possam ser combinados em uma única trigger. Os pontos de sincronização individuais podem compartilhar uma única seção de declaração global, cujo estado é mantido por toda a vida útil da declaração. Nas versões anteriores do Oracle, este tipo de funcionalidade só foi possível através da definição de várias triggers cujo código e variáveis globais foram definidas em um pacote separado.

As ações da trigger são definidas da mesma forma que uma trigger DML, com a adição da cláusula COMPOUND TRIGGER. O corpo principal da trigger é formada por uma seção opcional de declaração global e com uma ou mais seções de ponto temporais, onde cada uma das quais podem conter uma seção de declarações locais cujo estado não é mantido. A sintaxe básica utilizada é de acordo com a Listagem 7.

Listagem 7. Sintaxe básica de uma trigger composta.

CREATE OR REPLACE TRIGGER <trigger-name> FOR <trigger-action> ON <table-name> COMPOUND TRIGGER -- Declaração global. g_global_variable VARCHAR2(10); BEFORE STATEMENT IS BEGIN NULL; -- as instruções são postas aqui. -- END BEFORE STATEMENT; BEFORE EACH ROW IS BEGIN NULL; -- as instruções são postas aqui. -- END BEFORE EACH ROW; AFTER EACH ROW IS BEGIN NULL; -- as instruções são postas aqui. -- END AFTER EACH ROW; AFTER STATEMENT IS BEGIN NULL; -- as instruções são postas aqui. -- END AFTER STATEMENT; END <trigger-name>; /

A partir de agora, criaremos um exemplo simples, onde criaremos uma tabela de testes e uma trigger composta que será disparada para cada ponto de tempo associado com instruções insert, update e delete. As ações que forem disparadas serão armazenadas em uma tabela PL/SQL definida na seção de declaração global. O ponto de sincronização final para cada declaração mostra o conteúdo da tabela para mostrar que o estado variável foi mantido durante todo o tempo de vida da declaração, como podemos ver de acordo com a Listagem 8.

Listagem 8. Utilizando trigger composta.

-- criação da tabela -- CREATE TABLE testeTriggerComposta ( id NUMBER, descricao VARCHAR2(255) ); -- Criação da trigger -- CREATE OR REPLACE TRIGGER testeTriggerComposta_trigger FOR INSERT OR UPDATE OR DELETE ON testeTriggerComposta COMPOUND TRIGGER -- Global declaration. TYPE tipo_tabela IS TABLE OF VARCHAR2(50); tabelaTeste tipo_tabela := tipo_tabela(); BEFORE STATEMENT IS BEGIN tabelaTeste.extend; CASE WHEN INSERE THEN tabelaTeste(tabelaTeste.last) := 'ANTES DO MÉTODO - INSERT'; WHEN ATUALIZA THEN tabelaTeste(tabelaTeste.last) := 'ANTES DO MÉTODO - UPDATE'; WHEN EXCLUE THEN tabelaTeste(tabelaTeste.last) := 'ANTES DO MÉTODO - DELETE'; END CASE; END BEFORE STATEMENT; BEFORE EACH ROW IS BEGIN tabelaTeste.extend; CASE WHEN INSERE THEN tabelaTeste(tabelaTeste.last) := 'antes da cláusula INSERT (new.id=' || :new.id || ')'; WHEN ATUALIZA THEN tabelaTeste(tabelaTeste.last) := 'antes da cláusula UPDATE (new.id=' || :new.id || ' old.id=' || :old.id || ')'; WHEN EXCLUE THEN tabelaTeste(tabelaTeste.last) := ' antes da cláusula DELETE (old.id=' || :old.id || ')'; END CASE; END BEFORE EACH ROW; AFTER EACH ROW IS BEGIN tabelaTeste.extend; CASE WHEN INSERE THEN tabelaTeste(tabelaTeste.last) := 'Após cada INSERT (new.id=' || :new.id || ')'; WHEN ATUALIZA THEN tabelaTeste(tabelaTeste.last) := ' Após cada UPDATE (new.id=' || :new.id || ' old.id=' || :old.id || ')'; WHEN EXCLUE THEN tabelaTeste(tabelaTeste.last) := ' Após cada DELETE (old.id=' || :old.id || ')'; END CASE; END AFTER EACH ROW; AFTER STATEMENT IS BEGIN tabelaTeste.extend; CASE WHEN INSERE THEN tabelaTeste(tabelaTeste.last) := 'Após o método - INSERT'; WHEN ATUALIZA THEN tabelaTeste(tabelaTeste.last) := 'Após o método - UPDATE'; WHEN EXCLUE THEN tabelaTeste(tabelaTeste.last) := 'Após o método - DELETE'; END CASE; FOR i IN tabelaTeste.first .. tabelaTeste.last LOOP DBMS_OUTPUT.put_line(tabelaTeste(i)); END LOOP; tabelaTeste.delete; END AFTER STATEMENT; END testeTriggerComposta_trigger; /

Com base no código visto na Listagem 8, temos uma a criação de uma tabela de testes e uma trigger composta que dispara para cada ponto de sincronização associado com os métodos insert, update e delete. As ações que são disparadas passam a ser registradas numa tabela que foi definida pela seção de declaração global. Para cada ponto de sincronização final de cada declaração apresenta o conteúdo presente na tabela para mostrar que a variável de estado permanece ativo durante todo o ciclo de vida da declaração. Para comprovarmos isso, basta que criemos algumas declarações de insert, updates e deletes, para isso, basta que façamos essas declarações na tabela de testes, como podemos ver de acordo com a Listagem 9.

Listagem 9. Inserindo dados na tabela de testes.

INSERT INTO testeTriggerComposta VALUES (1, 'teste de inserção'); UPDATE testeTriggerComposta SET id = id; DELETE FROM testeTriggerComposta;

Antes da versão 11g da Oracle, tínhamos a problemática com relação a ativação e desativação de uma trigger, o que foi solucionado a partir desta versão, onde de forma explicita podemos realizar as operações tanto de habilitar quanto de desabilitar uma trigger. Podemos ver isso através de um exemplo simples, onde criaremos primeiro uma nova tabela chamada de controleTrigger e em seguida, criaremos duas triggers, as quais contaram com as cláusulas de Enable e Disable, como apresentado pelas Listagens 10, 11 e 12.

Listagem 10. Criação da tabela controleTrigger.

CREATE TABLE controleTrigger ( id NUMBER, descricaoOperacao VARCHAR2(150) );

Listagem 11. Criação da trigger com a cláusula Enable (ativa).

CREATE OR REPLACE TRIGGER controleTrigger_trigger BEFORE INSERT ON controleTrigger FOR EACH ROW ENABLE BEGIN DBMS_OUTPUT.put_line('A trigger que criamos foi utilizada!! =) '); END; /

Para que possamos testar a nossa trigger, basta que realizemos a inserção de um registro na tabela controleTrigger, como podemos ver a seguir:

INSERT INTO trigger_control_test VALUES (3, 'Nossa trigger');

Ao executarmos a instrução, veremos que o registro foi inserido e que a mensagem foi apresentada. Vejamos agora o que ocorre com relação a nossa Listagem 11.

Listagem 12. Criação da trigger com a cláusula Disabled (desativado).

CREATE OR REPLACE TRIGGER controleTrigger_trigger BEFORE INSERT ON controleTrigger FOR EACH ROW DISABLE BEGIN DBMS_OUTPUT.put_line('A trigger que criamos não foi utilizada!! =( '); END; /

De igual forma, realizaremos a inserção de um novo registro na tabela, mas a diferença é que neste caso não tivemos a apresentação da mensagem que criamos anteriormente.

Com isso finalizamos o nosso artigo, onde vimos um pouco com relação ao mundo das triggers no Oracle 11g, pois como ainda é o mais usado no mercado, precisamos estar cientes de seu funcionamento. Vimos então como podemos criar as triggers, como habilitá-las e desabilitá-las, além de aprendermos de forma prática com relação a utilização de triggers compostas. Esperamos que tenham gostado! =)