Procedure para criar trigger de log

Home Fóruns SQL e PL/SQL Procedure para criar trigger de log

Visualizando 4 posts - 1 até 4 (de 4 do total)
  • Autor
    Posts
  • #108948
    airoosp
    Participante

    Boa noite,

    Pesquisando na internet sobre criar trigger para gerar log de tabela, encontrei o código abaixo:

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

    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;

    Só que na hora de compilar aparece um erro informando que QUOTEDSTR não existe.

    É uma função do Oracle?

    O código acima esta neste link:

    https://forum.imasters.com.br/topic/261439-resolvido%C2%A0procedure-geradora-de-trigger-de-auditoria/

    Obrigado.

    Airton

    #108950

    Blz ? Então, ** sempre ** que vc tiver uma dúvida se algo é nativo/built-in do RDBMS Oracle, pesquise na documentação online : em http://docs.oracle.com/en/ vc tem a lista de todos os produtos, entrando em Databases e escolhendo releases anteriores vc encontra por exemplo a do 10g em http://www.oracle.com/pls/db102/homepage , e TODAS são pesquisáveis por palavras-chaves : no caso citado vc vai ver que NÃO, em lugar NENHUM do produto ‘database’ existe menção à QUOTEDSTR …
    Pra mim então esse artigo que vc indicou é algo *** GENÉRICO ***, mais um PSEUDO-CÓDIGO do que algo completo e pronto pra execução : inclusive, coisas como a linha :

    SCAMINHO := ‘…’; — DIR UTL

    me fazer pensar isso, pois Não faz Sentido vc ter ‘…’ como um nome de diretório : tá CLARO pra mim que realmente ese cara é um PSEUDOCÓDIGO, um esqueleto pra servir de BASE apenas, cabendo a VOCÊ o complementar….

    Isso respondido, eu indico que :

    a) SE vc quer ter uma Auditoria de Valores (ie, vc quer logar numa tabela/arquivo os valores ANTES e DEPOIS dos DMLs em determinadas tabelas de alguns usuários) , PLEASE pense nas OUTRAS opções de Auditoria, principalmente FGA que é mais customizável : veja no Fórum de database aqui no site mesmo uns links/refs que dei pra isso

    b) outra opção MUITO possível é vc usar as built-ins de captura de mudança de valores, o CDC/Change Data capture : veja https://www.morganslibrary.org/pres/ukoug/ukoug06_cdc.pdf , http://antapex.org/cdc.xls e http://www.nocoug.org/download/2005-08/nocoug_cdc_presentation.pps para refs

    c) APENAS SE e REALMENTE SE nem Audit nem CGC nem leitura de logs via LOGMINER pode ser feita, aí SIM vc vai pensar numa rotina que crie os triggers de INSERT/UPDATE/DELETE pra você : SE chegar a isso a minha RECOMENDAÇÃO , AO INVÉS de tentar adaptar código que sabe deus se é funcional ou não, é um enfoque crescente : começa com um LOOP básico na DBA_TABLES filtrando pelos usuários/tabelas que vc quer, depois pegue o texto do CREATE TRIGGER que vc quer gerar e tente substituir a parte variável dentro de um LOOP…. SIM, SQL dinâmico ** sempre é MAIS COMPLEXO mas se é o que vc quer/precisa blz…

    []s

    Chiappa

    #108983
    Motta
    Participante

    Este procedure é minha vacilei e deixei de publicar uma das functions

    create or replace FUNCTION quotedstr ( pStr IN varchar2)

    RETURN varchar2 IS

    — Por uma string entre aspas simples ==> ‘

    BEGIN
    RETURN ”” || pStr || ””;
    END;

    veja se funciona agora , mas lembro esta é uma opção quando se quer uma auditoria simples com uso de triggers poupando o trabalho de escrever as mesmas

    #108984
    Motta
    Participante

    Remova também as linhas que fazem menção a
    V_OSUSER

Visualizando 4 posts - 1 até 4 (de 4 do total)
  • Você deve fazer login para responder a este tópico.
Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detectado !

Verificamos que está usando alguma extensão para bloquear os anúncios. O GPO (Grupo de Profissionais Oracle) obtém a sua renda através dos anúncios, para assim manter toda a estrutura dedicada a universalização do conhecimento.

Se você gosta de nosso trabalho, pedimos por gentileza que desabilite o ads blocker. Trabalhamos somente com o Google Adsense e tentamos ao máximo exibir apenas o necessário.

Agradecemos de antemão ! :)

Powered By
Best Wordpress Adblock Detecting Plugin | CHP Adblock