Question: How do I work around the "ORA-04091: table APEXDEV.TBL_SEARCH_ITEM is mutating, trigger/function may not see it" in an "AFTER INSERT" trigger?
Update: I have cleaned up this initial post to articulate the problem a little more plainly, so the answer makes sense. As a result, some of the replies below may not completely make sense anymore.
I need to use a newly inserted record, in order to add additional records to this same table, once that insert is complete. The new "child" inserts will be based upon the value enter into the triggering record. My thought was to use an AFTER INSERT trigger.
It is a known issue that executing an update/insert query against the table triggering on an insert throws will throw a "mutating" error if the data being changed is edited. Usually, the case is resolved by converting the trigger to a BEFORE insert, but that did not work for me. Since the record isn't yet in the table, it is not available for all aspects of what I need. I am not editing data that caused the trigger to fire, but adding new records beyond that. In other server-class databases, the AFTER INSERT can be used in this recursive manner. In Oracle, we get the MUTATING TABLE error shown above.
To illustrate, when they enter a search value, if it is comma separated, I want to automatically parse their value and add a new filter with each of the separate values. So, a generic filter value where DESCRIPTION contains ("V1,V2) becomes DESCRIPTION contains ("V1,V2" OR ("V1" AND "V2")) which returns any record that EXACTLY matches the user's original filter plus any record that contains both of the values.
The trigger simply calls a function that contains the logic:
TRIGGER TBL_SEARCH_ITEM_CSV BEFORE INSERT on TBL_SEARCH_ITEM
for each row
begin
BEGIN
--Execute The CSV Parse
TBL_SEARCH.ParseCommaSeparatedValueList(:new.ID);
END;
The procedure (with preparatory and looping code removed for clarity) is basically an INSERT INTO:
PROCEDURE ParseCommaSeparatedValueList (piItemID in NUMBER) AS
BEGIN
--Select Query Removed For Clarity (pSearchItem is the newly inserted record)
--Conditions Check For A Delimiter (Avoid Infinite Loop)
IF INSTR(pSearchItem.VALUE,',')>0 THEN
--Looping Code Removed For Clarity...
--Insert The Parsed Values
INSERT INTO TBL_SEARCH_ITEM (SEARCHID, SEQUENCE, OPENPARENTHESES, CONJUNCTION, SEARCHFIELDID, OPERATOR, VALUE, CLOSEPARENTHESES)
VALUES (pSearchItem.SEARCHID, pSearchItem.SEQUENCE + pSplitValueSequencer, pSplitValueOpenParentheses, pSplitValueConjunction, pSearchItem.SEARCHFIELDID, pSearchItem.OPERATOR, pSearchItem.SPLITVALUE, pSplitValueCloseParentheses);
END IF;
EXCEPTION WHEN OTHERS THEN
pErrorMessage := TO_CHAR(SQLERRM);
INSERT INTO XXEXH_LOG (LOG_EVENT) VALUES (pErrorMessage);
END ParseCommaSeparatedValueList;