Trigger Oracle (Básico)

Este artigo relata de forma clara e objetiva as funcionalidades básicas de Triggers utilizadas em banco de dados Oracle.

por Roberto Fernandes Sobrinho

1. Introdução

Em um banco de dados temos 4 ações possíveis (Insert, Update, Delete e Select) , temos 3 dessas ações como possíveis modificadoras do banco de dados (select não modifica o banco de dados). Os bancos de dados prevêem que caso haja uma ação de modificação podemos ter ações complementares vinculadas a mesma, que é o que chamamos de Trigger.

Trigger como a tradução se faz, são gatilhos, o gatilho ocorre toda vez que uma ação ocorre em um banco de dados. O Trigger é conseqüência de uma ação, portanto não é o fim e sim algo que ocorre em função de uma ação.

Os Triggers podem ser do tipo ROW LEVEL (Linha) ou STATEMENT (Tabela). Além disso, os Triggers podem ocorrer (BEFORE) antes ou (AFTER) depois que a ação tenha ocorrido, e por fim pode ocorrer para as ações de INSERT, UPDATE e DELETE em uma tabela.

2. Tipos de TRIGGERS

Trigger ROW LEVEL

Os Triggers do tipo ROW LEVEL podem ser usados sempre que precisarmos que um Trigger trate de valores em uma transação, e por sua vez são disparados a cada ocorrência de uma transação sobre uma tabela. Se um UPDATE atualizar, por exemplo, 1000 linhas em uma tabela que possua um Trigger de Update do tipo row level, serão disparadas 1000 vezes.

Os Triggers do tipo row level são utilizadas para operações como:

• Gravação de LOGS de auditoria de uma aplicação;
• Verificação de dados (Consistência);
• Implementação de integridade referencial;

Trigger STATEMENT

Os Triggers do tipo STATEMENT tem a finalidade de tratar a execução de ações sobre tabelas independentemente de quantas linhas forem afetadas. Através deste tipo de Trigger podemos registrar a execução de comandos INSERT, UPDATE e DELETE contra tabelas que tenham Triggers contemplando essas ações. Caso um comando UPDATE atualize 1000 linhas, um Trigger deste tipo apenas dispararia 1 única vez. Este tipo de Trigger não pode referenciar qualquer valor contido em uma coluna da tabela. Isso ocorre porque se o mesmo dispara uma única vez.

Este tipo de Trigger funciona nos casos de registro de transações ocorridas, independentemente do numero de linhas afetadas.

Exemplo

CREATE OR REPLACE TRIGGER trg_aud_trn
BEFORE INSERT OR DELETE OR UPDATE
ON transportador
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
IF TO_NUMBER (TO_CHAR (SYSDATE, "hh24")) NOT BETWEEN 9 AND 18
THEN
raise_application_error(-20001,"Operação não pode ser executada fora do horário de expediente.");
END IF;
END;
/

COLUMN Trigger

Triggers de Coluna são disparados sempre que a determinada coluna relacionada no Trigger sofrer a ação ligada ao mesmo. Com isso se a ação ocorrer em outras colunas que não aquelas associadas ao Trigger, o Trigger não será disparado. Isso evita de certa forma que um Trigger seja disparado se a ação não ocorra, nada será disparado. Isso pode ser útil em casos de auditoria de mudanças em determinadas colunas, por exemplo:

Toda vez que um salário for alterado na tabela de empregados um Trigger gravará em uma tabela chamada auditoria_empregado um registro contendo o código do empregado e os salários anteriores e atuais.

TABLE Trigger

Trigger de tabela ocorrem independentemente das colunas afetadas pela ação. Isso quer dizer que se uma ação ocorre, não importa qual coluna seja afetada, a ação que o Trigger cobre não está ligado a nenhuma coluna. Estes Triggers podem ser úteis em ações como o exemplo a seguir;

Toda vez que um pedido for alterado (não importa a coluna) gravaremos em uma tabela chamada auditoria_pedido todas as colunas afetadas pelo Trigger.

3. Momento (Antes ou Depois)

Before – (Antes)

Os Triggers do tipo BEFORE como podemos deduzir, disparam antes que a ação ocorra. Isso leva a entender que antes que uma ação de banco de dados ocorra o Trigger será disparado, o que pode fazer com que a ação nem venha a ocorrer. Um Trigger pode impedir que uma ação venha a ocorrer, portanto podemos usar um Trigger deste tipo em situações como, por exemplo:

• Validação de dados;
• Carregamento de dados obrigatórios (datas, usuários, etc..);
• Impedimento de ações em horários não previstos;

Exemplo

CREATE OR REPLACE TRIGGER trg_pedidos_valor
BEFORE INSERT OR DELETE OR UPDATE
ON pedidos
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF :NEW.PEDI_VL_BRUT_CALC < 10000 THEN
THEN
raise_application_error(-20001,"O valor do pedido deve ser inferior a 10.000,00");
END IF;
END;
/

After – (Depois)

Os Triggers do tipo AFTER ocorrem depois que a ação tenha ocorrido, ou seja eles são disparados depois, com isso NÃO podemos com esses tipos de Triggers fazer o que fazemos com Triggers do tipo BEFORE. Aqui a ação já ocorreu então o que podemos fazer com Triggers deste tipo é a auditoria.

Exemplo

CREATE OR REPLACE TRIGGER trg_salario_aud
AFTER UPDATE
ON empregados
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
INSERT INTO log_salario
(codigo, salario_anterior, salario_atual,
data_alteracao, usuario )
VALUES (:NEW.codigo, :OLD.salario_anterior,
:NEW.salario_atual, SYSDATE, USER);
END;
/

4. Modificadores OLD e NEW

Podemos nos casos de Triggers de linha , fazer referência a valores contidos nas colunas e com isso podemos querer saber os valores antes da alteração e depois dos valores efetivamente alterados. Isso vale na ação de UPDATE, nos casos de INSERT e DELETE os valores de OLD (INSERT) e NEW (DELETE) são nulos. Estes modificadores podem ser usados APENAS em TRIGGERS. Não podemos usá-los em procedures, functions ou packages.

Os valores são referenciados da seguinte forma :OLD.nomecoluna e :NEW.nomecoluna.

Não importa se o Trigger for BEFORE ou AFTER os modificadores OLD e NEW não são afetados.

Exemplo

CREATE OR REPLACE TRIGGER trg_salario_aud
AFTER UPDATE
ON empregados
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF :NEW.SALARIO < 550 THEN
:NEW.SALARIO := 550;
END IF;
END;

5. Clausula WHEN

Caso o Trigger tenha alguma condição para ser executado, podemos incluir uma cláusula chamada WHEN. Nesta colocamos as condições que o Trigger irá disparar. Caso precisemos tratar o valor de alguma coluna, usamos os modificadores OLD e NEW, mas nessa clausula não colocaremos os : na frente, pois nesse caso ocorrerá erro.

Exemplo

CREATE OR REPLACE TRIGGER trg_salario_aud
AFTER UPDATE
ON empregados
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (NEW.salario < 550)
BEGIN
:NEW.salario := 550;
END;
/

No exemplo acima o Trigger somente será executado (BEGIN... END) se a condição WHEN vier a ocorrer.

6. Operadores, INSERTING , UPDATING E DELETING

Podemos criar Triggers para serem disparados para várias ações de banco de dados. Dessa forma como podemos diferenciar uma ação de insert de outra de delete ou ainda update. Estes modificadores podem ser feitos APENAS dentro de Triggers, portanto se usarmos esses operadores em procedures, functions, Packages ou mesmo PL/SQL anônimos os mesmos ocasionarão erros de compilação .

Exemplo

CREATE OR REPLACE TRIGGER TRG_SALARIO_AUD
AFTER
INSERT OR DELETE OR UPDATE
ON EMPREGADOS
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO LOG_SALARIO (CODIGO,
SALARIO_ANTERIOR,
SALARIO_ATUAL,
DATA_ALTERACAO,
USUARIO)
VALUES (:NEW.CODIGO,
NULL, :NEW.SALARIO_ATUAL,
SYSDATE, USER);
ELSIF UPDATING THEN
INSERT INTO LOG_SALARIO (CODIGO,
SALARIO_ANTERIOR,
SALARIO_ATUAL,
DATA_ALTERACAO,
USUARIO)
VALUES (:NEW.CODIGO,
:OLD.SALARIO_ATUAL, :NEW.SALARIO_ATUAL,
SYSDATE, USER);
ELSIF DELETING THEN
INSERT INTO LOG_SALARIO (CODIGO,
SALARIO_ANTERIOR,
SALARIO_ATUAL,
DATA_ALTERACAO,
USUARIO)
VALUES (:NEW.CODIGO,
:OLD.SALARIO_ATUAL, NULL,
SYSDATE, USER);
END IF;
END;
/

7. Comando INSTEAD OF

Triggers deste tipo foram implementadas a partir da versão 9i e tem a finalidade de permitir que havendo ações de modificação sobre visões, que os comandos possam ser realizados nas tabelas associadas a essas visões.

Exemplo

CREATE OR REPLACE TRIGGER TRG_NOVO_FUNC
INSTEAD OF INSERT ON EMPREGADOS
FOR EACH ROW
BEGIN
INSERT INTO empregados
(codigo, nome, salario)
VALUES (:NEW.codigo, :NEW.nome, :NEW.salario);
END;

8. Restrições ao uso de Triggers

Não podemos realizar os comandos COMMIT, ROLLBACK e SAVEPOINT em um Trigger, mesmo que seja uma procedure executada em um Trigger.

Não podemos fazer select na mesma tabela que sofre a ação de um Trigger , pois isso pode provocar um erro chamado MUTANT TABLE. Mesmo porque se quisermos saber o valor de uma coluna do registro que está sendo tratado em um Trigger basta colarmos :new.nomecoluna ou :old.nomecoluna para termos respectivamente os valores atuais e anteriores a alteração.

Triggers tornam as operações mais lentas, isso ocorre principalmente em casos de Triggers de linha.