Q

Resolving ORA-4091

I created a new trigger. This trigger, for each row, started after updating a table. I put a select query on the

same table. I got the famous ORA message: ORA-4091. Table is mutated. I can't rewrite the trigger so it doesn't read that table; I need information out of it. Can you help me?

The ORA-4091 error basically comes because your trigger will be modifying the table's contents. And at the same time, this trigger is trying to look at the table's contents. This is something that you cannot do. As the documentation states, you will have to rewrite your trigger.

Most of the time when I see this, the person writing the trigger is just trying to find out information about the specific row that caused the trigger to fire. In this case, please make use of the ":new" and ":old" operators to get values from the table. For instance, let's assume that I have a column called NAME and I want another column, UPPER_NAME, to be the upper case version of this name. The following trigger will "look up" the name and convert it to upper case:

CREATE OR REPLACE TRIGGER my_tab_trigger
BEFORE INSERT OR UPDATE ON my_tab
BEGIN
   :new.upper_name := UPPER(:new.name);
END;
/

For More Information


This was first published in October 2002

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close