Oracle Trigger to write all Insert/Updates to File

Question

I want to place a trigger on a table which writes all inserted / updated Data to an additional log file for processing with an external tool.

Is the a way to accomplish that?

Answer 1

You need to create triggers that execute after the table row has been altered and write to a log file using the UTL_FILE package.

The UTL_FILE package info can be found here: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm

And Trigger documentation can be found here: http://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg13trg.htm

There is a similar answer to what you are looking for here: http://bytes.com/topic/oracle/answers/762007-trigger-output-text-file

More info on writing to a file using PL/SQL here: http://www.devshed.com/c/a/Oracle/Writing-to-Text-Files-in-Oracle-PLSQL/

Hope it helps...

Answer 2

I would avoid writing out to the file system at DML time, but would pull out the data in a batch process each night (or whatever frequency).

From your OP, its not clear if you need the "new" data after the update, or the "old" data before the update. If you just want the latest data, why not just add a modified_date field (date or timestamp type) and update that via a trigger.

create table test
(
id number,
val varchar2(100),
modified_date date  default sysdate not null
)
;

CREATE OR REPLACE TRIGGER TR_TEST_BU
BEFORE UPDATE
ON TEST REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
  :new.modified_date := sysdate;
end;

insert into test(id, val) values (1, 'Insert 1');
commit;

select * from test;

exec dbms_lock.sleep(5);
update test set val = 'Update 1' where id = 1;
commit;

select * from test;

If you need the old data before the update (or you care about deleted data), then you'll modify the trigger to write old or deleted values to a history table, then extract the data from there.

Also note that adding a trigger to a table will slow down associated DML activity. Some shops wish to avoid this by replacing the triggers with business logic rules ("all apps must update modifed_date" edict), which usually leads to inconsistent data (or worse) from what I've seen.

Answer 3

Yes, here you have an example of the update part.

You simply need to do a similar one to the insert part.