Oracle triggers - problem with mutating tables

Question

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 :)

Answer 1

You should imho not use triggers for complicated business logic. Move it to a stored proc (PL/SQL package) or the client code. Apps with a lot of triggers become unmaintanable beause you will loose any feeling of "sequence of actions" very soon.

Using autonomous transactions is absolutely unsafe, use autonomous transaction only for logging, tracing, debugging and maybe auditing.

Read: http://www.oracle.com/technology/oramag/oracle/08-sep/o58asktom.html

Here you can read how you can solve the problem when you want to use triggers without using autonomous transactions: http://www.procaseconsulting.com/learning/papers/200004%20mutating%20table.pdf

Answer 2

You can use both triggers and integrity constraints to define and enforce any type of integrity rule. However, Oracle Corporation strongly recommends that you use triggers to constrain data input only in the following situations:

To enforce referential integrity when child and parent tables are on different nodes of a distributed database To enforce complex business rules not definable using integrity constraints When a required referential integrity rule cannot be enforced using the following integrity constraints:

• NOT NULL, UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DELETE CASCADE
• DELETE SET NULL

source: Oracle9i Database Concepts

Answer 3

Hi simon,

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.

Answer 4

Can you refactor the solution to include views to perform the calculation ?

CREATE VIEW a_view AS
SELECT a.Id, min(b.State) State FROM tableA,tableB
WHERE a.Id=b.tableAId
GROUP BY a.Id;

I agree that stored procs (as suggested here in other posts) are also a good candidate - but note that the view will automatically be kept up-to-date, whereas I believe you would have to schedule running stored-procs to keep the data 'in-sync': which may be fine - it depends on your requirements.

I guess another option is to create some functions to do the calculation, but personally I would opt for the view-approach (all things being equal).

Answer 5

Doing things like this is a great temptation, and if you follow the suggestions in the Tom Kyte article referenced by others it is possible. However, just because something can be done doesn't mean it should be done. I strongly recommend that you implement something like this as a stored procedure/function/package. Complex logic of this sort should not be performed using triggers, despite the obvious temptations, because it greatly raises the complexity of the system without a corresponding increase in utility. I have to work on code like this occasionally and it's no joy.

Good luck.

Answer 6

Don't use autonomous transactions, or you'll get very interesting results.

To avoid the mutating tables problem, you can do the following:

In a AFTER INSERT OR UPDATE OR DELETE FOR EACH ROW trigger, find out the parent ID and save it in a PL/SQL collection (inside a PACKAGE). Then, in a AFTER INSERT OR UPDATE OR DELETE TRIGGER (statement-level, without the "for each row" part), read the parent IDs from the PL/SQL collection and update the parent table accordingly.

Answer 7

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?

I don't know where you saw that, but I know have posted that opinion many times berfore.

Why do I think mutating tables are usually indicative of a flaw in the data model? Because the kind of "requirement" which drives code that hurls ORA-4091 is frequently associated with poor design, especially insufficient normalisation.

You scenario is a classic example of this. You get the ORA-04091 because you are selecting from TableC when your insert or update it. But why are you selecting from TableC? Because you "need" to update a column on its parent, TableB. But that column is redundant information. In a fully-normalised data model that column would not exist.

Denormalisation is often touted as a mechanism for improving the performance of queries. Unfortunately the proponents of denormalisation gloss over its cost, which is paid in the currency of excessive complexity when we insert, update and delete.

So, how can you change your logic? The simple answer is to drop the columns and don't bother storing the smallest state by parent ID. Instead, execute a MIN() query whenever you need that information. If you need it frequently and it would be expensive to execute the query then you build materialized views which store the data (be sure to use ENABLE QUERY REWRITE)

Answer 8

As an example of why your logic will fail, take a scenario where PARENT A has record 1 with CHILD records 1A and 1B. The STATE of 1A is 10 and 1B is 15, so you want your parent to be 10.

Now some-one updates the STATE of 1A to 20 while, at the same time, someone deletes 1B. Because the delete of 1B is uncommitted, the transaction updating 1A will still see 1B and will want to set the state of the parent to 15, while the transaction deleting 1B will see the old uncommitted value of 1A and will want the parent state to be 10.

If you do de-normalise this, you have to be very careful with locking so that, BEFORE inserting/updating/deleting any child records, the parent record is locked, execute your changes, select all the child records, update the parent, then commit to release the locks. While it can be done with triggers, you are best off with a stored procedure.