Trigger dinamica e genérica de LOG de tabelas

[NJM 0]

Olá pessoal, imagino que muitos tenham criado uma tabela de log para monitorar as alterações efetuadas na base de dados. Estou tentando fazer uma rotina genérica, pois ter que ficar criando e fazendo manutenção nas trigger manualmente uma a uma é muito trabalhoso, e toda vez que se cria ou exclui alguma coluna tem que ir alterar, e se esquecer para tudo. Criei uma rotina para gerar o "insert" automaticamente, porém na hora de executar com o "execute immediate" ele retorna o erro ORA-01008 que é relacionado as variáveis BIND ":new" e ":old" que é gerado dentro do insert. Neste artigo "http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_dynamic_sql.htm#BJEBCCBD " ele mostra um pouco sobre como usa-las, mas está colocando o "using" com as variáveis fixas para interpreta-las, sendo assim não resolve o problema, pois a melhor parte que seria a "dinâmica" de gerar as colunas fica perdida. Segue o meu codigo abaixo. Qualquer duvida ou sugestão é só postar.

 

-- Criacao da tabela de log baseado em uma chamado teste

create table teste_log as (select t.*, sysdate as log_timestamp, user as log_usuario, '1234567890' as log_tipo

from teste t where t.coluna_id=0)

 

-- procedure para gerar o insert

CREATE OR REPLACE PROCEDURE PR_GERA_SCRIPT_LOG (

P_TABELA IN VARCHAR2,

P_TP_LOG IN CHAR,

P_SCRIPT OUT VARCHAR2) -- U-UPDATE / D-DELETE

IS

V_TABELA VARCHAR2(60);

V_DATA DATE;

V_COLUNA VARCHAR2(60);

V_TIPO VARCHAR2(60);

V_TAMANHO INTEGER;

V_EH_NULO VARCHAR2(60);

V_ERRM VARCHAR2(64);

V_SCRIPT_COLUNA VARCHAR2(3000);

V_SCRIPT_DADOS VARCHAR2(3000);

V_DADOS VARCHAR2(100);

V_COLUNA_DATAHORA VARCHAR2(100) := 'LOG_TIMESTAMP';

V_COLUNA_USUARIO VARCHAR2(100) := 'LOG_USUARIO';

V_COLUNA_TIPO VARCHAR2(100) := 'LOG_TIPO';

/*-----------------------------------------------------------

Descrição .: Gera script de insert para tabela de log se existir

Data .: 28/04/2011

 

Histórico de Modificações

Nr Programador Data Comentarios

-- ----------- -------- -------------------------------------------

-----------------------------------------------------------*/

begin -- Inicio

 

if ((P_TABELA IS NULL) or (P_TP_LOG IS NULL)) then

return;

end if;

P_SCRIPT := '';

V_TABELA := TRIM(P_TABELA) || '_LOG';

FOR C1 IN (SELECT

C.COLUMN_NAME AS COLUNA,

C.DATA_TYPE AS TIPO,

DECODE(C.DATA_PRECISION, NULL, C.CHAR_COL_DECL_LENGTH, C.DATA_PRECISION) AS TAMANHO,

C.NULLABLE AS EH_NULO

FROM ALL_TABLES T, ALL_TAB_COLUMNS C

WHERE T.OWNER = C.OWNER

AND T.TABLE_NAME = C.TABLE_NAME

AND T.OWNER = 'EFICAZ'

AND UPPER(T.TABLE_NAME) = UPPER(V_TABELA))

LOOP

begin

if UPPER(c1.coluna) = V_COLUNA_DATAHORA THEN

V_COLUNA := V_COLUNA_DATAHORA;

V_DADOS := 'SYSDATE';

ELSE

if UPPER(c1.coluna) = V_COLUNA_USUARIO THEN

V_COLUNA := V_COLUNA_USUARIO;

V_DADOS := 'USER';

ELSE

if UPPER(c1.coluna) = V_COLUNA_TIPO THEN

V_COLUNA := V_COLUNA_TIPO;

V_DADOS := CHR(39)||UPPER(P_TP_LOG)||CHR(39);

ELSE

SELECT

C.COLUMN_NAME AS COLUNA,

C.DATA_TYPE AS TIPO,

DECODE(C.DATA_PRECISION, NULL, C.CHAR_COL_DECL_LENGTH, C.DATA_PRECISION) AS TAMANHO,

C.NULLABLE AS EH_NULO

INTO

V_COLUNA, V_TIPO, V_TAMANHO, V_EH_NULO

FROM ALL_TABLES T, ALL_TAB_COLUMNS C

WHERE T.OWNER = C.OWNER

AND T.TABLE_NAME = C.TABLE_NAME

AND lower(C.COLUMN_NAME) = lower(C1.COLUNA)

AND T.OWNER = 'EFICAZ'

AND UPPER(T.TABLE_NAME) = UPPER(P_TABELA);

if sqlcode = 100 then

V_COLUNA:='';

else

-- IF V_TIPO <> 'NUMBER' AND V_TIPO <> 'DECIMAL' AND V_TIPO <> 'INTEGER' THEN

-- V_DADOS := CHR(39)||':old.'||V_COLUNA||CHR(39);

-- ELSE

V_DADOS := ':old.'||V_COLUNA;

-- end if;

end if;

end if;

end if;

end if;

IF V_COLUNA IS NOT NULL THEN

if V_SCRIPT_COLUNA IS NOT NULL then

V_SCRIPT_COLUNA := V_SCRIPT_COLUNA || ',';

V_SCRIPT_DADOS := V_SCRIPT_DADOS || ',';

END IF;

V_SCRIPT_COLUNA := V_SCRIPT_COLUNA || V_COLUNA;

V_SCRIPT_DADOS := V_SCRIPT_DADOS || V_DADOS;

END if;

exception

WHEN OTHERS THEN

v_errm := SUBSTR(SQLERRM, 1 , 64);

end;

 

END LOOP;

 

IF V_SCRIPT_COLUNA IS NOT NULL THEN

P_SCRIPT := 'INSERT INTO ' || V_TABELA || ' (' || V_SCRIPT_COLUNA || ') VALUES (' || V_SCRIPT_DADOS || ')';

END IF;

 

END PR_GERA_SCRIPT_LOG;

 

-- trigger

create or replace trigger TG_BIUD_TESTE

before INSERT OR UPDATE OR DELETE on teste

for each row

declare

-- local variables here

ins_log varchar2(500);

tipo varchar2(20);

V_ERRM VARCHAR2(64);

begin

if updating then

tipo := 'UPDATE';

else

if deleting then

tipo := 'DELETE';

else

return;

end if;

end if;

 

-- ins_log := 'insert into teste_log (log_usuario, log_timestamp, log_tipo, coluna_id, descricao, outro_id) values (user, sysdate, '''||tipo||''','|| :old.coluna_id||', '''||:old.descricao||''', '||:old.outro_id||')';

pr_gera_script_log('teste',tipo,ins_log);

if ins_log is not null then

begin

execute immediate(ins_log);

-- insert into teste_log (log_usuario, log_timestamp, log_tipo, coluna_id, descricao, outro_id) values (user, sysdate, tipo, :old.coluna_id, :old.descricao, :old.outro_id);

exception

WHEN OTHERS THEN

v_errm := SUBSTR(SQLERRM, 1 , 64);

raise_application_error(-20005,'Erro de processamento - '|| ins_log ||' - '|| SQLCODE ||' - '|| V_ERRM);

END;

end if;

end TG_BIUD_TESTE;

[Motta 645]

Já fiz algo parecido ,veja se te ajuda.

 

http://forum.imasters.com.br/topic/261439-procedure-geradora-de-trigger-de-auditoria/

[NJM 0]

Obrigado Motta, estarei verificando e retorno ..