Criação e utilização de triggers no Oracle

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;

Adauto Santos

Analista de Sistemas Senior na Unimed Regional Maringá. Formado em Informática pela Universidade Estadual de Maringá. Pós-Graduado em Administração de Banco de Dados pela Metrocamp e Business Intelligence pela Unicesumar.

WebSite: http://www.adautosantos.com.br/
LinkedIn: http://www.linkedin.com/in/adauto-santos-91278029
Facebook: https://www.facebook.com/adauto.santos.946

Envie seu comentário

Mensagem
Nome
E-mail

Comentários

Não há comentários no artigo. Seja o primeiro a comentar!