Saturday, August 05, 2006

Writing an external audit file

Awhile back I answered a question from somebody I believe on Dizwell on how to write a custom trigger that would write a record to an external datafile with every insert. I stumbled across the script here and thought I would post it.




CREATE OR REPLACE TRIGGER audtest_Bi
 BEFORE
  INSERT
 ON audtest
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
DECLARE

JOBOUT NUMBER;

BEGIN

dbms_job.submit( JOBOUT, 'BEGIN AUDWRITE('||:NEW.PK||'); END;',SYSDATE );
:NEW.JOBNUM := JOBOUT;

END;
/

CREATE OR REPLACE PROCEDURE AUDWRITE(I_PK NUMBER) AS

L_OUTPUT        UTL_FILE.FILE_TYPE;

BEGIN
    L_OUTPUT := UTL_FILE.FOPEN( 'AUDTEST_DIR', 'AUDTEST_AUDIT.CSV', 'A' );

FOR LINE IN (SELECT * FROM AUDTEST WHERE PK=I_PK) LOOP
    UTL_FILE.PUT( L_OUTPUT, LINE.PK||','||LINE.SOMEDATA);
    UTL_FILE.NEW_LINE( L_OUTPUT );
    END LOOP;

    UTL_FILE.FCLOSE( L_OUTPUT );

END;
/


INSERT INTO AUDTEST (PK,SOMEDATA) VALUES (AUDTEST_SEQ.NEXTVAL,DBMS_RANDOM.string('A',ROUND(DBMS_RANDOM.VALUE(1,45))));
INSERT INTO AUDTEST (PK,SOMEDATA) VALUES (AUDTEST_SEQ.NEXTVAL,DBMS_RANDOM.string('A',ROUND(DBMS_RANDOM.VALUE(1,45))));
INSERT INTO AUDTEST (PK,SOMEDATA) VALUES (AUDTEST_SEQ.NEXTVAL,DBMS_RANDOM.string('A',ROUND(DBMS_RANDOM.VALUE(1,45))));
INSERT INTO AUDTEST (PK,SOMEDATA) VALUES (AUDTEST_SEQ.NEXTVAL,DBMS_RANDOM.string('A',ROUND(DBMS_RANDOM.VALUE(1,45))));
INSERT INTO AUDTEST (PK,SOMEDATA) VALUES (AUDTEST_SEQ.NEXTVAL,DBMS_RANDOM.string('A',ROUND(DBMS_RANDOM.VALUE(1,45))));


--SHOULD BE 5 JOBS
SELECT * FROM USER_JOBS;

--SHOULD BE 5 ROWS WITH JOBNUM FILLED IN
SELECT * FROM AUDTEST;


--WON'T BE A FILE IN C:\TEMP
COMMIT;
--NOW THERE WILL BE A FILE IN C:\TEMP




Some work and a lot of testing will need to be done if this goes into production. But might have some use to somebody.

1 comment:

Adi said...

Oes Tsetnoc one of the ways in which we can learn seo besides Mengembalikan Jati Diri Bangsa. By participating in the Oes Tsetnoc or Mengembalikan Jati Diri Bangsa we can improve our seo skills. To find more information about Oest Tsetnoc please visit my Oes Tsetnoc pages. And to find more information about Mengembalikan Jati Diri Bangsa please visit my Mengembalikan Jati Diri Bangsa pages. Thank you So much.
Oes Tsetnoc | Semangat Mengembalikan Jati Diri Bangsa