[Resolvido] Procedure geradora de trigger de auditoria

[Motta 645]

Crie uma tabela assim

 

CREATE TABLE TABELA_LOG (
  TABELA   VARCHAR2(20)   NULL,
  CHAVE	VARCHAR2(20)   NULL,
  USUARIO  VARCHAR2(20)   NULL,
  DATA	 DATE		   NULL,
  CONTEUDO VARCHAR2(4000) NULL
)

Crie esta SP

 

CREATE OR REPLACE PROCEDURE CRIA_TRIGGER_AUDIT (PTABELA IN CHAR)
IS
  /*
  FONTE GERADOR DE TRIGGER DE LOG, RECEBE A TABELA COMO PARAMETRO E GERA UM ARQUIVO
  DE SCRIPT PARA A GERAÇÃO DA TRIGGER NO DIR UTL
  */
  AARQUIVO	SYS.UTL_FILE.FILE_TYPE;
  SARQ		VARCHAR2(100) := '';
  SCAMINHO	VARCHAR2(100);
  SFASE	   CHAR(2);
  CR		  CHAR(1) := CHR(13); 
  VS_CHV VARCHAR2(200);		 
BEGIN
  SCAMINHO := '...'; -- DIR UTL
  SARQ := 'TRG_AUD_'||TRIM(PTABELA)||'.TXT';
  AARQUIVO := SYS.UTL_FILE.FOPEN(SCAMINHO,SARQ,'W'); 
  --
  FOR R IN (SELECT COLUMN_NAME
				  FROM   USER_CONSTRAINTS , USER_CONS_COLUMNS
				  WHERE  CONSTRAINT_TYPE = 'P'
				  AND	USER_CONS_COLUMNS.CONSTRAINT_NAME = USER_CONSTRAINTS.CONSTRAINT_NAME
				AND	USER_CONSTRAINTS.TABLE_NAME = PTABELA
																   ORDER BY POSITION ) 
  LOOP
	VS_CHV := VS_CHV ||	':NEW.' || R.COLUMN_NAME || '||';
  END LOOP;
  VS_CHV := SUBSTR(VS_CHV,1,(LENGTH(VS_CHV)-2)); 
  --
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'CREATE OR REPLACE TRIGGER TRG_'||PTABELA||'_A_U_LOG'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'AFTER UPDATE ON ' ||PTABELA||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'REFERENCING OLD AS OLD NEW AS NEW'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'FOR EACH ROW'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  -----------------------------------------------------------------------------'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  -- TRIGGER PARA LOG DE ALTERACOES DA TABELA '||PTABELA||' --------------------------'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  -- CODIGO GERADO AUTOMATICAMENTE PELO PROGRAMA CRIA_TRIGGER_AUDIT -----------'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  -- CRIADO EM '|| TO_CHAR(SYSDATE,'DD/MM/YY') || ' -------------------------------------------------------'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  -----------------------------------------------------------------------------'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'DECLARE'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  ------------------'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  ---  VARIAVEIS ---'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  ------------------'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  V_OSUSER	VARCHAR2(50);'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  V_DATA	  DATE := SYSDATE;'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  V_TABELA	VARCHAR2(20) := '||QUOTEDSTR(PTABELA)||';'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  V_CHAVE	 VARCHAR2(20);'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  V_CONTEUDO  VARCHAR2(4000);'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  -----------------------------------------------------------------------------'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'BEGIN'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  -----------------------------------------------------------------------------'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  ---------------------------- OBTER USUARIO REDE -----------------------------'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  -----------------------------------------------------------------------------'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  SELECT OSUSER INTO V_OSUSER FROM DUAL;'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  -----------------------------------------------------------------------------'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  --------------- TESTE DE MODIFICAÇÃO E INSERÇÃO NA TABELA DE LOG ------------'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  -----------------------------------------------------------------------------'||CR);
  --
  FOR R IN (SELECT COLUMN_NAME COLUNA,
				   '  IF (NVL(:OLD.'||COLUMN_NAME||','||
					   DECODE(DATA_TYPE,'VARCHAR2',QUOTEDSTR('#'),
										'CHAR',QUOTEDSTR('#'),
										'NUMBER','-1',
										'DATE','(SYSDATE-36500)')||') <> ' ||
					   'NVL(:NEW.'||COLUMN_NAME||','||
					   DECODE(DATA_TYPE,'VARCHAR2',QUOTEDSTR('#'),
										'CHAR',QUOTEDSTR('#'),
										'NUMBER','-1',
									  'DATE','(SYSDATE-36500)')||')) THEN' LINHA1,
				   (CASE WHEN DATA_TYPE LIKE '%CHAR%' THEN QUOTEDSTR(COLUMN_NAME||' DE : ') || '|| :OLD.'||COLUMN_NAME || ' || ' ||
														   QUOTEDSTR(' PARA : ') || '|| :NEW.'||COLUMN_NAME
													  ELSE QUOTEDSTR(COLUMN_NAME||' DE : ') || '|| TO_CHAR(' || ':OLD.'||COLUMN_NAME || ') ||'  ||
														   QUOTEDSTR(' PARA : ') || '|| TO_CHAR(' || ':NEW.'||COLUMN_NAME || ')' END) LINHA2
		   FROM ALL_TAB_COLUMNS
		   WHERE OWNER = '...'
		   AND   TABLE_NAME = PTABELA
		   ORDER BY COLUMN_ID)
  LOOP
	SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  -------> '||R.COLUNA||' '||CR);
	SYS.UTL_FILE.PUT_LINE(AARQUIVO,R.LINHA1||CR);
	SYS.UTL_FILE.PUT_LINE(AARQUIVO,'	V_CHAVE	:= '||VS_CHV||';'||CR);
	SYS.UTL_FILE.PUT_LINE(AARQUIVO,'	V_CONTEUDO := '||R.LINHA2||';'||CR);
	SYS.UTL_FILE.PUT_LINE(AARQUIVO,'	INSERT INTO TABELA_LOG VALUES (V_TABELA,V_CHAVE,V_OSUSER,V_DATA,V_CONTEUDO);'||CR);
	SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  END IF;'||CR);
  END LOOP;
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'  -----------------------------------------------------------------------------'||CR);
  SYS.UTL_FILE.PUT_LINE(AARQUIVO,'END;'||CR);
  SYS.UTL_FILE.FCLOSE(AARQUIVO);
END;
/

Rode a procedure passando a tabela como parametro e será gerado um script para geração da trigger, a trigger só loga alterações mas pode ser adaptada para insert e updates.

 

A melhor solução é se usar o padrão do Oracle mas pode ser querer algo mais simples e creio que esta SP facilita o trabalho braçal.

 

A variável CR CHAR(1) := CHR(13);

só é necessária se o ambiente for Unix.