CODEWITHSUNDEEP
oracle-database
Trebor
Trebor

Reputation: 813

Mutating table error on AFTER UPDATE trigger

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.

Upvotes: 0

Views: 855

Answers (1)

Anton Zaviriukhin
Anton Zaviriukhin

Reputation: 741

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.

Upvotes: 1

Related Questions