Reading table data from an after insert trigger

I'm inserting a row of data in an Oracle 8i table.  After the data is inserted I need to read all the rows of data for the date the data was inserted.  So for instance if I'm inserting a row for 02/28/2011, I need to read all the rows for that date including the one I just inserted.  My problem is that when I do this from an after insert trigger, the trigger doesn't see the data from the row I'm inserting.  I would think that an after insert trigger would fire after the row was inserted and therefore the data should be there.  I am using pragma autonomous_transactions on both the before insert and after insert triggers.  My question is this :  Is this just a normal behavior of after insert triggers or am I doing something wrong.    Below is the scripts of my 2 triggers.



-- HERE'S THE TRIGGER I'M HAVING TROUBLE WITH

create or replace trigger trig_aft_ins_cr_receipts
  after insert on clean_rice_receipts  
  for each row
declare
  -- local variables here
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  -- THIS PROCEDURE READS THE DATA FROM THE CLEAN_RICE_RECEIPTS TABLE BUT
  -- THE DATA IS NOT THERE EVEN THOUGH THIS IS AN AFTER INSERT TRIGGER
  UPDATE_CR_COST_RECEIPT(:NEW.DATE_PURCHASED,FALSE);
  ROLL_CR_COSTING_FORWARD(:NEW.DATE_PURCHASED);
  COMMIT;
end trig_aft_ins_cr_receipts;
/


-- THIS TRIGGER FIRES PRIOR TO INSERT
create or replace trigger trig_insert_crr
  before insert on clean_rice_receipts for each row
declare
  PRAGMA AUTONOMOUS_TRANSACTION;
  my_seq number;
begin
  -- ASSIGN A TRANSACTION ID IF IT IS NULL
  IF :NEW.TRANSACTION_ID IS NULL THEN
    select CLEAN_RICE_RECEIPTS_SEQ.nextval into my_seq from dual;
    :new.TRANSACTION_ID := my_seq;
  END IF;
  -- IF THE CWTS <> 0 THEN POST THE INVENTORY DETAIL
  POST_INITIAL_RECEIPT_ENTRY(:NEW.TRANSACTION_ID,
                             :NEW.DATE_PURCHASED,
                             (NVL(:NEW.CWTS, 0) *
                             NVL(:NEW.PRICE_PER_CWT, 0)) +
                             (NVL(:NEW.CWTS, 0) *
                             NVL(:NEW.FREIGHT_PER_CWT, 0)));

  -- SET THE INITIAL VALUES
  -- RICE IS POSTED TO FALSE
  :NEW.RICE_POSTED := 'FALSE';
  -- FREIGHT IS POSTED TO FALSE
  :NEW.FREIGHT_POSTED := 'FALSE';
  -- DATES, NSEQ AND CSEQ TO NULL
  :NEW.DATE_RICE_POSTED    := NULL;
  :NEW.DATE_FREIGHT_POSTED := NULL;
  :NEW.RICE_NSEQ           := NULL;
  :NEW.FREIGHT_NSEQ        := NULL;
  :NEW.RICE_CSEQ           := NULL;
  :NEW.FREIGHT_CSEQ        := NULL;
  -- COMMIT THE ENTRY
  COMMIT;
end trig_insert_crr;
/



Randy RichPresidentAsked:
Who is Participating?
 
MilleniumaireConnect With a Mentor Commented:
When you use an autonomous transaction this will create a different session and this session will only see data that has been committed.

Because the trigger is part of the original transaction, the commit isn't performed until after the trigger, therefore the trigger code itself won't see the new data in the table due to it being autonomous.

Why do you need the trigger to be autonomous?
0
 
Randy RichPresidentAuthor Commented:
Thanks for your quick response.  

Because if I don't make the autonomous transaction, I get an ora-04091 table is mutating error.  
ERROR-MESSAGE.JPG
0
 
MilleniumaireCommented:
I suspected that would be the case!

Unfortunately, you're caught between a rock and a hard place: You are using the autonomous transaction to get around the mutating trigger, but this is preventing your code from seeing the data due to the fact it hasn't been committed.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Randy RichPresidentAuthor Commented:
Sounds like I'm going to have to figure out another way to do this.  That did answer my question however.  Thanks for your help.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Did you try with the BEFORE INSERT trigger ( remove autonomous ) and AFTER INSERT trigger ( with autonomous ) to see if that works ?

Thanks,
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
it is not always a good idea to use autonomous transaction in triggers along with commits and sometimes it so happens that the code in the autonomous triggers gets executed and gets committed but the actual insert may fail due to some reason and all those cases should be tested/taken care accordingly.
0
 
MilleniumaireCommented:
There are other ways to work around the mutating trigger, which avoid the use of autonomous transactions.  Have a look at the following article by Tom Kyte:

http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:2212445691154

If you avoid using autonomous transactions, your trigger code may then start to work as expected.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.