Criação e utilização de triggers no Oracle
Postado por Adauto Santos
As TRIGGERS no Oracle são um excelente recurso para implementar a auditoria a nível de regras de negócio e obter logs de maneira simples e direta. Basicamente, ela consiste em um trecho de código PL/SQL que é executado à partir de uma operação DML em uma determinada tabela. Por exemplo, pode-se desejar que a cada alteração de um salário de um colaborador, as informações da pessoa que realizou a alteração e do percentual aplicado sejam armazenadas em uma tabela em separado para uma consulta futura.
O comando
Para criar uma trigger basta apenas definir o seu nome, o momento e a ação que irá acioná-la, a tabela na qual ela estará atrelada e efetivamente os códigos que irão realizar o processamento. No exemplo a seguir, iremos criar uma TRIGGER simples que para cada alteração salarial da tabela SALARIO_CARGO, a tabela SALARIO_CARGO_HISTORICO será alimentada.
CREATE OR REPLACE TRIGGER GRAVA_HIST_SALARIO_CARGO
AFTER UPDATE ON SALARIO_CARGO
FOR EACH ROW
BEGIN
INSERT INTO SALARIO_CARGO_HISTORICO
(
NR_SEQ_CARGO,
VL_SALARIO_ANTIGO,
VL_SALARIO_NOVO,
PERC_REAJUSTE,
DT_ATUALIZACAO,
NR_SEQ_USUARIO
)
VALUES
(
:OLD.NR_SEQ_CARGO,
:OLD.VL_SALARIO,
:NEW.VL_SALARIO,
(((:NEW.VL_SALARIO - :OLD.VL_SALARIO) / :OLD.VL_SALARIO) * 100),
SYSDATE,
'999'
);
END;
Simples assim! Para cada alteração na tabela SALARIO_CARGO por meio do comando UPDATE um registro novo registro será inserido na tabela SALARIO_CARGO_HISTORICO contendo: o cargo, o salário anterior, o salário atual, o percentual da diferença, a data da alteração e o usuário que alterou.
Os comandos a seguir detalham o processo completo para que você seja capaz de replicar o exemplo em um ambiente de testes.
-- Criação das tabelas SALARIO_CARGO (origem) e SALARIO_CARGO_HISTORICO (destino) CREATE TABLE SALARIO_CARGO ( NR_SEQ_CARGO NUMBER(5), VL_SALARIO NUMBER(12,2) ); -- Inserção dos registros INSERT INTO SALARIO_CARGO (NR_SEQ_CARGO, VL_SALARIO) VALUES (1,'150'); INSERT INTO SALARIO_CARGO (NR_SEQ_CARGO, VL_SALARIO) VALUES (2,'1750'); INSERT INTO SALARIO_CARGO (NR_SEQ_CARGO, VL_SALARIO) VALUES (3,'2000'); COMMIT; CREATE TABLE SALARIO_CARGO_HISTORICO ( NR_SEQ_CARGO NUMBER(5), VL_SALARIO_ANTIGO NUMBER(12,2), VL_SALARIO_NOVO NUMBER(12,2), PERC_REAJUSTE NUMBER(12,2), DT_ATUALIZACAO DATE, NR_SEQ_USUARIO NUMBER(5) ); -- CREATE OR REPLACE TRIGGER GRAVA_HIST_SALARIO_CARGO ... listado no exemplo
A seguir, podemos observar alguns exemplos de alterações salariais e as suas respectivas entradas na tabela SALARIO_CARGO_HISTORICO.
UPDATE SALARIO_CARGO SET VL_SALARIO=2000 WHERE NR_SEQ_CARGO = 1;
UPDATE SALARIO_CARGO SET VL_SALARIO=2000+200 WHERE NR_SEQ_CARGO = 1;
UPDATE SALARIO_CARGO SET VL_SALARIO=2000 WHERE NR_SEQ_CARGO = 3;
UPDATE SALARIO_CARGO SET VL_SALARIO=VL_SALARIO+500 WHERE NR_SEQ_CARGO = 2;
UPDATE SALARIO_CARGO SET VL_SALARIO=2500 WHERE NR_SEQ_CARGO = 2;
UPDATE SALARIO_CARGO SET VL_SALARIO=VL_SALARIO+500 WHERE NR_SEQ_CARGO = 2;
SELECT * FROM SALARIO_CARGO_HISTORICO;
NR_SEQ_CARGO VL_SALARIO_ANTIGO VL_SALARIO_NOVO PERC_REAJUSTE DT_ATUALIZACAO NR_SEQ_USUARIO
------------ ----------------- --------------- ------------- ------------------- --------------
1 150 2000 1233,33 15/07/2016 19:02:12 999
1 2000 2200 10 15/07/2016 19:02:53 999
3 2000 2000 0 15/07/2016 19:03:15 999
2 1750 2250 28,57 15/07/2016 19:03:31 999
2 2250 2500 11,11 15/07/2016 19:04:07 999
2 2500 3000 20 15/07/2016 19:04:16 999
A explicação
No Oracle as triggers são sempre acionadas à partir de uma instrução, seja um DML (INSERT, UPDATE e DELETE) ou DDL (CREATE, DROP, ALTER) e etc. Para isso, ela pode ser acionada antes ou depois da execução do comando (BEFORE/AFTER) e qualquer tabela está suscetível a ter uma trigger. Além disso, a exemplo de uma PROCEDURE ou FUNCTION, não é possível acionar uma trigger manualmente (ex: GRAVA_HIST_SALARIO_CARGO()). Não existe um evento para manipular a instrução SELECT.
Por fim, uma trigger pode ser desabilitada ou habilitada com os comandos a seguir:
SQL> ALTER TRIGGER GRAVA_HIST_SALARIO_CARGO DISABLE; SQL> ALTER TRIGGER GRAVA_HIST_SALARIO_CARGO ENABLE;
Meus testes
No exemplo a seguir, com base nas tabelas já citadas na seção anterior, será criada uma nova trigger que irá atender as três operações DML (Insert, Update e Delete), dessa foram será possível ter um pleno controle sobre as operações executadas na tabela SALARIO_CARGO.
CREATE OR REPLACE TRIGGER GRAVA_HIST_SALARIO_CARGO AFTER INSERT OR UPDATE OR DELETE ON SALARIO_CARGO FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO SALARIO_CARGO_HISTORICO ( OPERACAO, NR_SEQ_CARGO, VL_SALARIO_ANTIGO, VL_SALARIO_NOVO, PERC_REAJUSTE, DT_ATUALIZACAO, NR_SEQ_USUARIO ) VALUES ( 'Inserção', :NEW.NR_SEQ_CARGO, 0, :NEW.VL_SALARIO, NULL, SYSDATE, '999' ); ELSIF UPDATING THEN INSERT INTO SALARIO_CARGO_HISTORICO ( OPERACAO, NR_SEQ_CARGO, VL_SALARIO_ANTIGO, VL_SALARIO_NOVO, PERC_REAJUSTE, DT_ATUALIZACAO, NR_SEQ_USUARIO ) VALUES ( 'Alteração', :OLD.NR_SEQ_CARGO, :OLD.VL_SALARIO, :NEW.VL_SALARIO, (((:NEW.VL_SALARIO - :OLD.VL_SALARIO) / :OLD.VL_SALARIO) * 100), SYSDATE, '999' ); ELSIF DELETING THEN INSERT INTO SALARIO_CARGO_HISTORICO ( OPERACAO, NR_SEQ_CARGO, VL_SALARIO_ANTIGO, VL_SALARIO_NOVO, PERC_REAJUSTE, DT_ATUALIZACAO, NR_SEQ_USUARIO ) VALUES ( 'Remoção', :OLD.NR_SEQ_CARGO, :OLD.VL_SALARIO, null, null, SYSDATE, '999' ); END IF; END;
