Trigger para gerar trigger no Oracle

[André Melim 0]

Olá pessoal,

 

Vou criar triggers em algumas tabelas do banco de dados que vai gravar dados em uma tabela de auditoria. O problema é que, quando eu criar um novo campo em alguma dessas tabelas teoricamente eu teria que recriar também a trigger dessa tabela para colocar esse novo campo para ir para a auditoria. Então eu gostaria de colocar um recurso no banco de dados de tal forma que, seu eu vier a criar algum campo novo nessas tabelas, dispare automaticamente um envento que irá recriar essas triggers automaticamente acrescentando esse novo campo para ser gerado na auditoria. A princípio eu pensei em colocar uma trigger em 'after alter on schema' assim:

 

create or replace trigger audit_ddl_trg after alter on schema

begin

gera_trigger(ora_dict_obj_name);

end;

 

Então, dessa forma, sempre que ocorrer um evento de alteração/inclusão de algum campo nas tabelas, eu disparo o procediemnto 'gera_trigger' que ira recriar a trigger associada a tabela. Também dei permissão 'create any trigger' ao usuario do banco de dados que estou trabalhando.

Até aí beleza, o problema é quando eu altero ou crio algum campo em alguma tabela e entra nesse procedimento 'gera_trigger', dá mensagem de erro 'ORA-00604: Ocorreu um erro no nível 1 SQL recursivo'. Vejam o codigo desse procedimento que comecei a montar:

 

CREATE OR REPLACE PROCEDURE GERA_TRIGGER (P_NOME_TABELA IN VARCHAR2)

IS

V_TEXTO VARCHAR2(32767);

V_NOME_TABELA VARCHAR2(24);

V_AUX_CAMPOS VARCHAR2(32767);

BEGIN

V_NOME_TABELA := SUBSTR(P_NOME_TABELA,1,24);

V_AUX_CAMPOS := '';

FOR REC IN (SELECT T.COLUMN_NAME FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME=P_NOME_TABELA) LOOP

V_AUX_CAMPOS := V_AUX_CAMPOS || REC.COLUMN_NAME || ',';

END LOOP;

/* GERA A TRIGGER DE INSERT */

V_TEXTO :=

'CREATE OR REPLACE TRIGGER A_INS_' || V_NOME_TABELA || CHR(10) ||

'AFTER INSERT ON ' || P_NOME_TABELA || CHR(10) ||

' FOR EACH ROW' || CHR(10) ||

'BEGIN' || CHR(10) ||

'' || CHR(10) ||

' INSERT INTO AUDITORIA (' || CHR(10) ||

' etc ... etc... etc...' || CHR(10) ||

'' || CHR(10) ||

'END A_INS_' || P_NOME_TABELA || ';';

EXECUTE IMMEDIATE V_TEXTO;

END;

 

A intenção desse loop é pegar a lista de campos da tabela para gerar a auditoria dos mesmos, ou seja, isso vai ter que ter dentro do procedimento, e pelo que pude perceber é isso que causa esse problema da msg de erro de sql recursivo. Se eu tirar esse trecho de codigo (somente para teste), não dá essa msg de erro, porém fico sem alternativa para obter a lista de campos. Não sei se estou fazendo da maneira correta isso, não sou expert em Oracle, mas acredito que deve haver algum recurso para implementar isso que desejo, afinal de contas, se não tiver isso de maneira automática, toda vez que criar um novo campo na tabela vou ter que ficar alterando tambem a trigger para gerar auditoria dessa tabela.

 

Grato pela atenção!

[Motta 645]

Fiz algo parecido, uma SP que gera outra SP de auditoria, mas se a tabela for alterada requer realmente manutenção.

 

Mas talvez como as tabelas estão em manutenção a query não possa ser chamada pois seria uma leitura em um objeto que está sendo alterado.

 

Minha ideia é somente realmente gerar a trigger uma vez as alterações seguintes seriam a manutenção normal de um objeto (alterar manualmente).

 

_________________________________________________________________________________________________________________________________________

Se o motivo for realmente a chamada do objeto em uso uma solução seria :

 

Gravar nome da tabela editada em outra tabela.

 

Um job le este registro, gera a nova trigger e deleta o registro da tabela em questão, se não registros nada faz.