Menu de navegação

[airoospParticipante]

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

[José Laurindo ChiappaModerador]

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

[MottaParticipante]

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

[MottaParticipante]

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

[Autor]

Posts