Q

What is a mutating row?

What is mutating row? Why does it occur? How can we avoid it?

You only run into the mutating row problem when you have a trigger on a table. Let's assume that I have a trigger on a table that gets fired whenever an update to the row occurs. That trigger will update the same row. So you issue a UPDATE statement against the table, which updates a row of data. The trigger fires, updating the same row of data. This causes the trigger to fire again (since the row was updated by the trigger). This causes the trigger to fire again..... It never ends!

This is the mutating row problem. A trigger is fired based on the action on a row in a table. That action can be INSERT, UPDATE, or DELETE. That trigger forces the trigger to be fired again.

The solution is to modify the trigger so that it does not force the trigger to fire again. How that is done is fully dependent on the code you have in the trigger and the job you want the trigger to do. One common mistake people make is to use the ":new.column" variable in their trigger when they mean ":old.column". This can cause a mutating row to occur.


This was first published in October 2004

Dig deeper on Oracle database backup and recovery

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