Expert: Suchitra Joshi - 4/29/2009

Question
QUESTION: Hi,

I have a table in which i need to insert a row. before
inserting into that table i need to check a value for a
specific reference and if it is false then i need to
insert.

we are getting mutating errors. when try locating a
solution for the same we found that even though we can
avoid using autonomous transaction, we might land in some
other problem.

In this case we are in a position where we could nt find
any other solution.

Can you help me how to solve it

Sethu

ANSWER: Hi Sethu,

Can you please send the table structure, which column to check, with what, and what action to take after that? Please send the details so that I can help further.

Regards

Suchitra

---------- FOLLOW-UP ----------

QUESTION: Hi Suchitra,

Thanks for your concern.

To start with let me explain the functionality what am
trying to achieve.  

I have a transaction table in which I will be having some
requisitions and issues.  In my application, there are some
issue transactions are happening twice at the same time
with different document number.  if it inserts with the
same document number I will be able to restrict the same
using primary key constraint.  since it is happening twice
with different number I do not know for what kind of issue
transaction the problem occurs since it is happening once
in a while.  

to solve or locate the problem where and when it is
happening i thought i will have an trigger which will alert
me when the issued quantity is more than the requested
quantity.  

to achieve the above solution when i tried creating a
trigger it throws up MUTATING error since I am trying to
insert and check in the same table.  the table structure
can be as it found in the attached image.

in that you can see there are two request and three issues.  
out of the three issues, the third issue is a duplicate
one.  i would like to have a trigger which will alert when
the application try to insert a issue accidently [ due to
bug in the application ].

Hope am clear what I would like to archive. [ is it
possible to include my email id phone in this forum ?  
normally that wont be acceptable ]

Sethu  

Answer
Hi Sethu,

Thanks for explaining in detail.

Here is logic you can apply -

1) Create a package and declare a variable for total issue qty in the specification, and compile it.
2) Create a stmt level trigger - before insert on table
  - set pkg variable to 0.
3) Create the row level trigger - before insert on table for each row
  - set the pkg variable to pkg variable + current qty.
  - if current qty > pkg variable, then display error

You can debug this (i.e store row data, pkg variable value etc in temp table) and check what exactly the problem is.

I hope this helps. If you get any errors, please let me know.

Regards

Suchitra