Triggers PL/SQL: saiba quando e por que usar

As triggers são programas armazenados que podem ser executados automaticamente ou podem ser disparados quando houver a ocorrência de algum evento associado a elas. Elas são, de fato, escritas para serem executadas em resposta a algum dos eventos expostos a seguir:

• Com instruções DML - Manipulação da base de dados com a utilização das declarações DELETE, INSERT e UPDATE.
• Com instruções DDL - Disponível a partir da versão 8i do Oracle Database. É mais utilizado em casos que envolvam auditoria de alterações nos objetos de um schema, assim como é o caso das declarações CREATE, DROP e ALTER.
• Com operações da base de dados como LOGON, LOGOFF, STARTUP, SERVERERROR, dentre outros.

As triggers podem ser definidas em tabelas, views, schemas ou em alguma base de dados com a qual o evento seja associado.

Quais são os benefícios na utilização de triggers?

As triggers podem ser escritas para diferentes propósitos, dos quais podemos destacar a geração de valores derivados de colunas da base de dados automaticamente, auditoria, replicação sincronizada de tabelas, prevenção de transações inválidas, aplicação de integridade referencial, imposição de autorização de segurança, registro de eventos e armazenamento de acesso as tabelas da base de dados.

Como podemos criar uma trigger?

A sintaxe para a estrutura básica de criação de uma trigger é apresentada na Listagem 1.

Listagem 1. Sintaxe básica de criação da trigger PL/SQL.

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;

Onde temos:

• CREATE [OR REPLACE] TRIGGER trigger_name: cria uma nova trigger ou sobrescreve uma já existente com o nome informado, que no caso é apresentado como trigger_name.
• {BEFORE | AFTER | INSTEAD OF }: aqui definimos quando a trigger deve ser executada. A cláusula INSTEAD OF é utilizada quando uma trigger é criada em uma view.
• {INSERT [OR] | UPDATE [OR] | DELETE}: especifica a operação DML.
• [OF col_name]: especificamos aqui a coluna que deve ser atualizada.
• ON table_name: aqui especificamos o nome da tabela que será associada a trigger.
• [REFERENCING OLD AS o NEW AS n]: neste ponto, permitimos a referência a valores novos e valores antigos para várias declarações DML, assim como seria o caso para o insert e o delete.
• [FOR EACH ROW]: esta instrução especifica uma trigger no nível de linha. Neste caso, a trigger deveria ser escutada para cada linha afetada.
• WHEN (condição): este trecho da instrução provê a condição para que a trigger seja disparada nas linhas. Esta cláusula só é válida para triggers em nível de linha.

Vamos criar um exemplo simples de utilização para melhorar nossas habilidades, onde o preço de um produto muda constantemente. Então existe aí a importância de manter um histórico dos preços desses produtos. Então começaremos com a criação das tabelas de nossa base, conforme a Listagem 2.

Listagem 2. Criação das tabelas de ‘produtos’ e ‘historico_preco_produto’.

CREATE TABLE historico_preco_produto (produto_id number(5), produto_nome varchar2(50), produto_descricao varchar2(150), produto_preco_unitario number(7,2) ); CREATE TABLE produtos (produto_id number(5), produto_nome varchar2(32), produto_descricao varchar2(150), produto_preco_unitario number(7,2) );

Em seguida, vamos popular estas tabelas com informações pertinentes, conforme a Listagem 3.

Listagem 3. Populando as tabelas

INSERT INTO produtos VALUES (1, 'leite', 'bebida nutritiva', '2.50'); INSERT INTO produtos VALUES (2, 'carne bovina', 'musculo', '10.00'); INSERT INTO produtos VALUES (3, 'ovos', 'item vendido por unidade', '2.50'); INSERT INTO produtos VALUES (4, 'pão', '10 unidades', '3.50'); INSERT INTO produtos VALUES (5, 'laranja', 'fruta rica em vitamina C, quantidade minima 12 unidades', '5.00'); INSERT INTO produtos VALUES (6, 'feijão', 'Alimento rico em ferro', '4.50');

O próximo ponto agora é criarmos a nossa trigger, conforme a Listagem 4.

Listagem 4. Criação da trigger

CREATE or REPLACE TRIGGER historico_preco_trigger BEFORE UPDATE OF prd_preco_unitario ON produtos FOR EACH ROW BEGIN INSERT INTO historico_preco_produto VALUES (:old.produto_id, :old.produto_nome, :old.produto_descricao, :old.produto_preco_unitario); END; /

Agora que temos nossa tabela criada, populada com alguns registros e também com a nossa trigger pronta, vamos então realizar uma atualização em um dos valores de produtos que temos, conforme a Listagem 5.

Listagem 5. Atualização de valores

UPDATE PRODUTOS SET produto_preco_unitario = 800 WHERE produto_id = 5;

Como tivemos esta atualização em um dos itens de produtos na tabela produtos, a nossa trigger foi disparada fazendo assim a atualização da tabela ‘historico_preco_produto’.

Tipos de Triggers PL/SQL

Existem dois tipos de triggers baseados de acordo com o nível em que são disparadas:

• Trigger em nível de linha: Esta é a mais comum e é executada uma vez para cada linha afetada pela SQL que a disparou. Apenas neste nível é que as triggers tem acesso aos valores de registros afetados, sendo estes novos ou antigos.
• Trigger em nível de declaração: É executada para cada declaração SQL que é executada. Este tipo de trigger não é muito utilizado.

Quando disparamos uma trigger, esta segue uma hierarquia de ações da seguinte forma:

1. [BEFORE] - Antes da declaração da trigger ser disparada pela primeira vez.
2. [BEFORE] - Após isso, antes da trigger disparar em nível de linha, uma vez para cada linha afetada.
3. [AFTER] - Então, após a trigger ser disparada em nível de linha para cada linha afetada. Estes eventos alternarão entre antes e depois da trigger ser disparada.
4. [AFTER] - E por último, depois a declaração da trigger em nível dispara.

Para facilitar o entendimento destas informações, vamos criar uma nova tabela chamada ‘produto_mensagem’, onde nós poderemos usá-la para armazenar as mensagens quando as triggers forem disparadas.Observe a Listagem 6.

Listagem 6. Tabela Produto_mensagem

CREATE TABLE produto_mensagem (Mensagem varchar2(50), Data_corrente number(32) );

Agora criaremos as declarações de BEFORE UPDATE para os níveis de declaração e de linha.

A declaração BEFORE UPDATE, nível de declaração é uma trigger que irá inserir um registro na tabela ‘produto_mensagem’ antes da instrução SQL para atualização ser executada, em nível de declaração. Observe a Listagem 7.

Listagem 7. BEFORE UPDATE

CREATE or REPLACE TRIGGER Before_Update_Stat_produto BEFORE UPDATE ON produtos Begin INSERT INTO produto_mensagem Values('Before update, statement level',sysdate); END; /

Agora a BEFORE UPDATE, nível de linha é uma trigger que vai inserir um registro na tabela ‘produto_mensagem’ antes de cada linha ser atualizada. Observe a Listagem 8.

Listagem 8. BEFORE UPDATE em nível de linha

CREATE or REPLACE TRIGGER Before_Upddate_Row_produto BEFORE UPDATE ON produtos FOR EACH ROW BEGIN INSERT INTO produto_mensagem Values('Before update row level',sysdate); END; /

A AFTER UPDATE em nível de declaração é a trigger que irá inserir um novo registro na tabela ‘checagem_produto’ depois que a declaração SQL de atualização for executada. Observe a Listagem 9.

Listagem 9. AFTER UPDATE em nível de declaração

CREATE or REPLACE TRIGGER After_Update_Stat_produto AFTER UPDATE ON produtos BEGIN INSERT INTO produto_mensagem Values('After update, statement level', sysdate); End; /

A AFTER UPDATE em nível de linha é a trigger que irá inserir um registro na tabela ‘produto_mensagem’ depois que cada linha for atualizada. Observe a Listagem 10.

Listagem 10. AFTER UPDATE em nível de linha

CREATE or REPLACE TRIGGER After_Update_Row_produto AFTER insert On produtos FOR EACH ROW BEGIN INSERT INTO produto_mensagem Values('After update, Row level',sysdate); END; /

Agora que temos tudo criado, vamos executar uma declaração de atualização na nossa tabela de produtos, conforme a Listagem 11.

Listagem 11. Atualização da tabela produtos

UPDATE PRODUTOS SET produto_preco_unitario = 1200 WHERE produto_id in (3,5);

Algo importante a se saber também com relação as triggers é que caso estas tenham sido definidas de forma idêntica, elas serão executadas sem uma ordem específica (podendo ser de forma aleatória). Caso isto seja realmente necessário, devemos ter cuidado com a integridade do banco de dados para que não haja dependência com relação à ordem de execução das triggers.