Solved

Reading table data from an after insert trigger

Posted on 2011-02-28
7
747 Views
Last Modified: 2012-06-27
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;
/



0
Comment
Question by:Randy Rich
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 500 total points
ID: 34998904
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
 

Author Comment

by:Randy Rich
ID: 34998989
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
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 34999031
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:Randy Rich
ID: 34999093
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35004149
Did you try with the BEFORE INSERT trigger ( remove autonomous ) and AFTER INSERT trigger ( with autonomous ) to see if that works ?

Thanks,
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35004155
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
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 35005309
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question