Ask the Expert

What is a mutating row?

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

    Requires Free Membership to View

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

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: