Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Reading table data from an after insert trigger

Posted on 2011-02-28
7
Medium Priority
?
758 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

577 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