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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- 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.