Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Reading table data from an after insert trigger

Posted on 2011-02-28
7
Medium Priority
?
755 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 2000 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

604 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