Mutating Table

[jgsalins]

Hi,
I have a UPDATE trigger on a table.
The value that I am updating to FIELD1, I need to check whether the value exists in FIELD2 of the same table. If we go by normal method of coding, it results in mutating table error.

Could anybody pls help me out on, how to handle this situation.
Thanks

James

[sambavan]

When does the mutating table error occour?

This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.


Sam

[kmesser]

Here's one way:

create a package with 3 procedures and a PL/SQL table. Procedures must include:

1. A procedure to initialize/clear out the PL/SQL table;
2. A procedure to populate the PL/SQL table with rows to check;
3. A procedure to process the rows identified in #2 above as necessary.

You could also check before updating rather than use a trigger, such as:

update mytable A set field1 = mynewvalue where field2 not in (select distinct field2 from mytable B);