Oracle triggers – problem with mutating tables

mutating-tableora-04091oracletriggers

My tables:

TableA (id number, state number)
TableB (id number, tableAId number, state number)
TableC (id number, tableBId number, state number)

So items in TableC are TableB's children and items in TableB are TableA's children. Vice versa – items in TableA are TableB's parents and items in TableB are TableC's parents.

I'd like to control state of parent items… let's say for example, that we have this data:

TableA (id, state): 
1, 40

TableB (id, tableAId, state): 
1, 1, 40
2, 1, 60

TableC (id, tableBId, state): 
1, 1, 40
2, 1, 50
3, 2, 60
4, 2, 70

Parent state should always hvae the smallest state of his children. So if we now update TableC like this:

update TableC set state = 50 where Id = 1;

my trigger should automatically update TableB (set state = 50 where id = 1) and then update also TableA (set state = 50 where id = 1)

I'd like to do this with triggers (AFTER UPDATE, INSERT, DELETE, on TableA, TableB, TableC), so that after every action this steps would execute:

  1. get parent id
  2. find smallest state from all the children of current parent
  3. if smallest state of all children is greater than parent's state, then update parent

How can I avoid 'mutating table error'? Is it save to use autonomous transactions in this example? I saw some opinions, that mutating table error indicates flaws in logic of the application – is this true and how can I change my logic in order to prevent this error?

Thanks

EDIT:
Thanks for all the great answers!

In the end, I used triggers (thanks to Vincent Malgrat, who pointed out Tom Kyte's article).

EDIT:
In the REAL END, I used stored procedures and removed triggers 🙂

Best Solution

As you have noticed it will be difficult to answer your business requirements with triggers. The reason is that Oracle may update/insert the tables with more than one thread at the same time for a single query (parallel DML). This implies that your session can't query the table it updates while the update takes place.

If you really want to do this with triggers you will have to follow the kind of logic shown in this article by Tom Kyte. As you can see it is not something simple.

There is another, simpler, more elegant, easier to maintain method: use procedures. Revoke the right of update/insert to the user(s) of the application and write a set of procedures that allow the application to update the state columns.

These procedures would hold a lock on the parent row (to prevent multiple sessions to modify the same set of rows) and would apply your business logic in an efficient, readable and easily-maintainable way.

Related Question