Using a trigger with SQL Loader
I am using SQL Loader to load a text file into an Oracle table. This works ok.
I have a trigger set up to insert into an audit table whenever I insert into the other table when I do the load.
However, it is writing an audit record as many times as sql loader commits during the load.
I realise I ccould increase the amount of records loaded before sql loader commits, but I dont want to put a huge strain on the rollback segments.
Are there any suggestions. I am thinking that I may have to do a bit of tricky coding with the trigger!
Here is my trigger code. Pretty simple.
create or replace trigger sales_data_trig
after insert on detail_rep
if inserting then
insert into sales_audit values( sales_audit_seq.nextval, 'LOAD SALES DATA', sysdate, 'N');
just a workaround :
from v$session ses
where sed.audsid = userenv('sessionid')
to verify if the inserting session comes from sqlldr.
If so, do nothing.
If not, do the insert.
Will take some extra coding
Click Here to Expand Forum to Full Width