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:
- get parent id
- find smallest state from all the children of current parent
- 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 for all the great answers!
In the end, I used triggers (thanks to Vincent Malgrat, who pointed out Tom Kyte's article).
In the REAL END, I used stored procedures and removed triggers 🙂