Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2004
    Posts
    14

    Unanswered: Trigger To Insert Then Delete

    I am importing a csv file into an oracle database table. (table1)

    I want to place a trigger on the table1 that will insert all records into table2. Then when that is finsihed i eant to delete all records from table1.

    When I try to do this I get table mutation errors.

    Any ideas how I can do this.

    Thanks in advance

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Try an insert trigger on table1 that calls a places a stored procedure in the
    job queue to be executed in say 2 seconds.

    The stored procedure can insert into table2 then delete table1

    HTH
    Gregg

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by sgarvan
    I am importing a csv file into an oracle database table. (table1)

    I want to place a trigger on the table1 that will insert all records into table2. Then when that is finsihed i eant to delete all records from table1.

    When I try to do this I get table mutation errors.

    Any ideas how I can do this.

    Thanks in advance
    What are you doing in the trigger that you can't do in the sql-loader control file. If it is just a matter or the field order, you can skip fields, do calculations and store in different tables using the control file. Let us see the code for your trigger and we can give much better advice.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Nov 2004
    Posts
    14
    Quote Originally Posted by beilstwh
    What are you doing in the trigger that you can't do in the sql-loader control file. If it is just a matter or the field order, you can skip fields, do calculations and store in different tables using the control file. Let us see the code for your trigger and we can give much better advice.
    hi bill thanks for the reply.

    I have the control file inserting into the database without any problems. when it is all in the database i want to take out all the records that have been entered and place them into another table.

    Here is the trigger that i had.

    Code:
    create or replace trigger tab1_trigger
    after insert
    on table1
    for each row
    begin
    
      insert into table2(col1,col2,col3) values (:new.col1, :new.col2, :new.col3);
      
      delete from table1;
    
    end;
    /

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What is the point of doing this? Why not just insert the data into table2 in the first place?

  6. #6
    Join Date
    Nov 2004
    Posts
    14
    Quote Originally Posted by andrewst
    What is the point of doing this? Why not just insert the data into table2 in the first place?
    i know what you are saying but i want all the info to go into table1 and then i will select certain info from table1 to go into table 2

  7. #7
    Join Date
    Nov 2004
    Posts
    14
    basically in psudeo code what i am tring to do is

    Code:
    create trigger tab1_trigger on table1
    
    insert all records into table2.
    
    when finished inserting all records into table2
    
    delete all records from table1

  8. #8
    Join Date
    Nov 2004
    Posts
    57
    Table mutation errors are common when you're trying to do 2 tasks at the same time to the same table. You'r looking to read a record and at the same time delete it. That makes Oracle go a little crazy. It's like asking yourself which came first the chicken or the egg?

    I agree with Tony on this why don't you just import the data directly into the destination table. It will be a little simpler.

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If you replace table1 by a view (view1 say) then you could use an INSTEAD OF trigger:
    Code:
    create trigger view1_ioi
    instead of insert on view1
    for each row
    begin
      insert into table2(col1,col2,col3) values (:new.col1, :new.col2, :new.col3);
    end;

  10. #10
    Join Date
    Nov 2004
    Posts
    14
    thanks for all your replies.

    i got it working the way i wanted to but in a totally different way.

    thanks again

    Seán

Posting Permissions

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