– Audit Generator
– By Helio Dias
– http://heliodias.com
– 20/08/2004, Audit Update and Delete
SET SERVEROUTPUT ON
SET FEEDBACK OFF
PROMPT Name of Target Table
DEFINE tbpai = &tabela_pai
PROMPT Name of Log Table
DEFINE tblog = &tabela_log
SPOOL audit_gen.SQL
DECLARE
v_TABELA_PAI VARCHAR2( 30) := ‘&tbpai’;
V_TABELA_LOG VARCHAR2( 30) := ‘&tblog’;
CURSOR CUR_COLUNAS IS
SELECT COLUMN_NAME COLUNA,DATA_TYPE TIPO,
DECODE(DATA_TYPE,
’NUMBER’ ,’(‘||NVL(DATA_PRECISION,DATA_LENGTH)||’,'||NVL(DATA_SCALE,0)||’)',
’VARCHAR2′,’(‘||DATA_LENGTH||’)',
’CHAR’,'(‘||DATA_LENGTH||’)') COMPLEMENTO
FROM user_tab_columns WHERE table_name=V_TABELA_PAI
AND DATA_TYPE NOT IN (‘BLOB’,'CLOB’,'LONG’,'LONGRAW’)
ORDER BY COLUMN_ID;
V_COLUNA VARCHAR2(30);
V_TIPO VARCHAR2(30);
V_COMPLEMENTO VARCHAR2(30):=”;
BEGIN
dbms_output.enable( 200000);
v_tabela_PAI := UPPER( v_tabela_PAI );
v_tabela_LOG := UPPER( v_tabela_LOG );
OPEN CUR_COLUNAS;
FETCH CUR_COLUNAS INTO V_COLUNA,V_TIPO,V_COMPLEMENTO;
DBMS_OUTPUT.PUT_LINE(‘CREATE TABLE ‘||V_TABELA_LOG||’(‘);
WHILE CUR_COLUNAS%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(V_COLUNA||’ ‘||V_TIPO||V_COMPLEMENTO||’,');
V_COMPLEMENTO:=”;
FETCH CUR_COLUNAS INTO V_COLUNA,V_TIPO,V_COMPLEMENTO;
END LOOP;
CLOSE CUR_COLUNAS;
DBMS_OUTPUT.PUT_LINE( ‘LOG_USUARIO VARCHAR2(30),LOG_DATA DATE,LOG_OPERACAO CHAR(1));’ );
DBMS_OUTPUT.PUT_LINE(‘CREATE OR REPLACE TRIGGER ‘||V_TABELA_LOG||’_TRX’);
DBMS_OUTPUT.PUT_LINE(‘AFTER UPDATE OR DELETE’);
DBMS_OUTPUT.PUT_LINE(‘ON ‘||V_TABELA_PAI||’ FOR EACH ROW’);
DBMS_OUTPUT.PUT_LINE(‘DECLARE V_OPERACAO CHAR(1);’);
DBMS_OUTPUT.PUT_LINE(‘BEGIN’);
DBMS_OUTPUT.PUT_LINE(‘V_OPERACAO:=’||CHR(39)||’U'||CHR(39)||’;');
DBMS_OUTPUT.PUT_LINE(‘IF DELETING THEN’);
DBMS_OUTPUT.PUT_LINE(‘V_OPERACAO:=’||CHR(39)||’D'||CHR(39)||’;');
DBMS_OUTPUT.PUT_LINE(‘END IF;’);
DBMS_OUTPUT.PUT_LINE(‘INSERT INTO ‘||V_TABELA_LOG||’(‘ );
OPEN CUR_COLUNAS;
FETCH CUR_COLUNAS INTO V_COLUNA,V_TIPO,V_COMPLEMENTO;
WHILE CUR_COLUNAS%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(V_COLUNA||’,');
FETCH CUR_COLUNAS INTO V_COLUNA,V_TIPO,V_COMPLEMENTO;
END LOOP;
CLOSE CUR_COLUNAS;
DBMS_OUTPUT.PUT_LINE(‘LOG_USUARIO,LOG_DATA,LOG_OPERACAO)’);
DBMS_OUTPUT.PUT_LINE(‘VALUES (‘);
OPEN CUR_COLUNAS;
FETCH CUR_COLUNAS INTO V_COLUNA,V_TIPO,V_COMPLEMENTO;
WHILE CUR_COLUNAS%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(‘:OLD.’||V_COLUNA||’,');
FETCH CUR_COLUNAS INTO V_COLUNA,V_TIPO,V_COMPLEMENTO;
END LOOP;
CLOSE CUR_COLUNAS;
DBMS_OUTPUT.PUT_LINE (‘USER,SYSDATE,V_OPERACAO);’);
DBMS_OUTPUT.PUT_LINE(‘END;’);
DBMS_OUTPUT.PUT_LINE(‘/’);
end;
/
SPOOL OFF
SET FEEDBACK ON
