Using a trigger with SQL Loader
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Using a trigger with SQL Loader

  1. #1
    Join Date
    Sep 2002
    Posts
    15

    Question 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
    begin
    if inserting then
    insert into sales_audit values( sales_audit_seq.nextval, 'LOAD SALES DATA', sysdate, 'N');
    end if;
    end;

  2. #2
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    just a workaround :

    use

    select ses.program
    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

    HTH
    Gert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width