| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Urgent: ORA-02067 transaction or savepoint rollback required (Distributed transaction)
Hi Arup,
Thanks for the reply.
I have checked the trigger code thoroughly and surely it's not a mutating trigger. Nor is there any cascade effect or the trace files would have shown the statements within that trigger.
There is some other error occurring, but we are not able to trap it because the front-end application just doesn't do error trapping.
Is there any way to see the error occurred within another session? We are checking trace logs but they don't show any error.
Thanks & regards,
Charu
-----Original Message-----
Sent: Thursday, November 21, 2002 3:32 PM
To: Multiple recipients of list ORACLE-L
required (Distributed transaction)
Charu,
I guess the error you are getting is "Table or Trigger is mutating". and perhaps you are using an after insert row trigger. This is a common and expected problem. There are ways to get around it. You have not mentioned the version of Oracle; I would assume 8.1.7 or later.
Inside an after insert row trigger, you can not operate on the table on which trigger is defined. for instance in your case the trigger (say, TRA_LT) on the table LT can't insert into LT inside it. However if you define the same in a BEFORE INSERT row trigger, it's allowed.
Caution, though, the insert through the trigger will fire the BEFORE INSERT trigger too, which in turn will insert a row in LT and then the trigger fires again..a vicious cycle. Fortuanately Oracle breaks the cascading trigger after 17 atetmpts. So, you need to have a limiting condition while defining the trigger WHEN (NEW.STATUS='OLD') or similar to make sure you can differentiate between the trigger inserted and the user inserted rows.
Finally, if you insert into the table LT using a construct like INSERT INTO LT VALUES (...) it works; but if you use INSERT INTO LT SELECT .. FROM ANOTHER_TABLE, it does not. this is not documented anywhere; I found this by accident.
Hope this helps.
Arup Nanda
www.proligence.com
> Gurus,
>
> Desperately need some ideas to solve this one.
>
> Following is the sequence of events as it happens:
>
> 1. Front-end application (VC++) queries from a view V. V fetches data from
a
> remote database RD via a database link.
> 2. Front-end inserts a row in a local table LT.
> 3. The Insert trigger on this table queries view V to take a final stock.
> 4a. If conditions match, the trigger inserts in remote table RT on RD. NO
> PROBLEM in condition.
> 4b. If conditions don't match, the front-end application tries to insert
> another row in LT. The trigger starts again and tries to insert row in RT.
> At this point we SEEM to get the error.
>
> We can't get anybody to debug the front-end application. We tried setting
> SQL_TRACE TRUE, and the only thing made clear was that the trigger didn't
go
> beyond trying to insert into RT.
>
> We tried replicating the whole scenario by executing each and every
> statement that the front-end fires through SQL*Plus as a script, and it
> doesn't give any error.
>
> I realize that I have given only a fraction of the whole information, but
> what is the best way to debug this situation?
>
> Are there any do's don'ts in distributed transactions which our code may
not
> be following?
>
> Thanks in advance,
> Charu
>
This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charu Joshi INET: joshic_at_mahindrabt.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Nov 21 2002 - 11:19:39 CST
 |
 |