Oracle Trigger - On update I need to update other rows in same table - getting mutate error

Question

As part of a nasty hack I've been asked to do, I need to modify a trigger to update associated records in a table - but am getting the mutating table error.

Data looks like this:

roster table
Id   person_id  route_id  active_ind
1    1          1         Y
2    2          1         Y
3    3          2         Y
4    4          2         Y  

If the active_ind of person_id = 1 is set to N, I need to set the active_id of the other person associated to the same route (route_id = 1) also to N.

There is a current trigger (after update) that updates other route associated tables that works fine. When I add the following code, I get the mutating table error:

 update roster r
    set r.active_ind = 'N'
    where r.route_id = :new.route_id
      and r.id != :new.id
      and r.active_ind = 'Y';

This is due to the fact that I'm trying to update a table in the middle of being updated. Can anyone suggest a solution?

Answer 1

mutation table error is an error occurred when we are try to manipulate the rows on the a table when the trigger was fired due to DML statement on the same table. To avoid mutation table error then it is better to use COMPUND TRIGGERS