The Oracle specific problem of not being able to handle access to the current table that a trigger is created for...
(ORA-4091) creates serious problems for us. Maybe you have an idea for a workaround?
We have a table with three columns: low, high and id. The table defines ranges from low to high, in which the id is valid. No ranges may overlap. To insure this, triggers or something similar are necessary. The calling application cannot perform this check. For insert it works because the Oracle specific problem ORA-4091 does not apply for before insert triggers. For an update, I have to do a check if the updated low or high conflicts with an existing range. Therefor I have to access the table. But due to the Oracle limitation ORA-4091, this is not possible.
So how can this be solved? Changing to a DBMS without this limitation is impossible. Would a call from trigger to a stored procedure help? ould we kill a lock manually? Could we raise an exception and perform an exception trigger, or is there still the limitation? Any ideas?
The best thing to do here is to give up the trigger check and have the check be done by a stored function called from the application you're writing. I don't know if you have control of the application code, but in this case it's the preferred solution.
Do the check in a function called f_check_range that returns a boolean true/false checking if the range given is valid. A call to a stored procedure from a trigger will not work because its RPC, and Oracle knows a trigger was fired on the accessed table.
If a trigger is ABSOLUTELY necessary, then create a COPY of that table that is maintained using Insert/Update/Delete triggers. And have the check done on the COPIED table.
For More Information
- Dozens more answers to tough Oracle questions from Eli Leiba are available here.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle error messages
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.