Solved

Reading table data from an after insert trigger

Posted on 2011-02-28
7
737 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now