Mutating table error on AFTER UPDATE trigger

Question

According to Burleson we can avoid a mutating table/trigger error by using AFTER UPDATE:

If you must use a trigger, it's best to avoid the mutating table error by using an "after" trigger, to avoid the currency issues associated with a mutating table. For example, using a trigger ":after update on xxx", the original update has completed and the table will not be mutating.

However, this poster to SO was using AFTER UPDATE and still received the mutating table error.

I've spent quite a bit of time reading through the various posts and have seen various solutions and opinions. I've seen this error a few times before in my own code using an AFTER UPDATE trigger - so how have I misunderstood the syntax Burleson is referring to?

CREATE OR REPLACE TRIGGER transcript_after_update
AFTER UPDATE on blatChildren
FOR EACH ROW
BEGIN
    update BLATranscript SET (enrollmentStatus, completionDate) = (select 'C',sysdate from dual)
    where id in (select blat.id from BLATranscript blat 
    inner join BlendedActivity bla on blat.blendedactivityid=bla.id
    where blat.id=:new.blaTranscriptId and minCompletion<=(
    select count(countForCompletion) as total from blatChildren blac
    inner join BlendedActivityMembership bam on blac.childActivityId=bam.childActivityId
    where completionDate>=sysdate-acceptPrevWork
    and blat.id=:new.blaTranscriptId));
END;

I've read the various posts and opinions here on SO and asktom about poor programming, normalization, making them autonomous (or why you shouldn't) and why you shouldn't use triggers for this, etc. and I'm more interested in how I've incorrectly interpreted Burleson or if his statement is incorrect. It seems to me that the state of the data should be stable after the update has completed.

Your misunderstanding is: Burleson is not clear on that he is talking about AFTER UPDATE STATEMENT TRIGGER. Which is different from Your AFTER UPDATE ROW TRIGGER. The statement-trigger is fired once, after all rows have been updated. As opposed to the row-trigger, which is fired n times, one time for each row that is affected by the update. In the Statement-trigger you don't specify 'For each row', and you have no access to ':old' and ':new'. — Njal, Sep 23 '15 at 15:07
@Njal - Thank you for your very clear expanation. That makes sense now. So, I guess that means that there's no way to use a basic trigger mechanism that queries the table your trigger is attached to at the row level short of making it autonomous or some other mechanism like procedures. Thank you for the clarification. — Trebor, Sep 23 '15 at 15:17
@Njal While the accepted answer is similar, your comment came first and is a better answer, at least to me. — Fering, Aug 02 '19 at 13:26

score 1 · Accepted Answer · answered Sep 23 '15 at 15:21

Statement level trigger (not for each row) will allow you query base table without Mutating table error.
But in such case you will not have ability to use :old. and :new. variables so it will not help you much.
Possible way out is to use 2 triggers:
- row level trigger to store ID-s to temporary table,
- statement level trigger to run your update based on this temporary table.

Mutating table error on AFTER UPDATE trigger

1 Answers1