Ask the Expert

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: