Скачать или смотреть Fixing the table is mutating Exception in Oracle Triggers

Learn how to resolve the `table is mutating` exception when using triggers in Oracle. Discover effective solutions to update your tables without triggering errors.
---
This video is based on the question https://stackoverflow.com/q/77493346/ asked by the user 'Turing' ( https://stackoverflow.com/u/8094913/ ) and on the answer https://stackoverflow.com/a/77493390/ provided by the user 'Littlefoot' ( https://stackoverflow.com/u/9097906/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, comments, revision history etc. For example, the original title of the Question was: How to fix table is mutating exception while updating the table after insert using trigger in oracle?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Fixing the table is mutating Exception in Oracle Triggers: A Simple Guide

When working with Oracle databases, one common issue developers encounter is the dreaded mutating table exception. This problem arises when you attempt to update a table that is currently being modified by a trigger. In this post, we will examine what causes this error and, more importantly, how to solve it effectively.

Understanding the Issue

The specific error message looks something like this:

[[See Video to Reveal this Text or Code Snippet]]

This error indicates that you cannot directly update a table while inserting rows into it. In this case, you are inserting multiple rows into the SchemeComp table and trying to update it at the same time, which leads to this mutating table exception.

The Original Trigger

Here's a breakdown of the original trigger that caused the error:

[[See Video to Reveal this Text or Code Snippet]]

Problem with this Trigger

The trigger is set to run AFTER INSERT, which is when the mutating table error occurs. It attempts to select from and update the same table that is currently being modified.

The Solution

To fix this issue, you can change the trigger to run BEFORE INSERT instead of AFTER. This approach allows you to select the parent ID from the SchemeComp table and assign it to the new row being inserted without triggering the mutating error:

Revised Trigger Code

[[See Video to Reveal this Text or Code Snippet]]

What Makes This Work?

BEFORE INSERT: The trigger executes before a new row is inserted. This means we can safely read values from the table without causing a mutation.

Using a Local Variable: We use a local variable l_parentid to store the result of our SELECT statement. This avoids direct table manipulation during the insert.

Conclusion

By changing the trigger from AFTER to BEFORE INSERT, you can effectively work around the table is mutating exception in Oracle, allowing for smooth data manipulation and avoiding errors. Remember, understanding how triggers function and the timing of their execution is critical to maintaining a robust and error-free database.

If you have any questions or run into issues implementing this solution, feel free to reach out in the comments below!