Oracle triggers - problem with changing tables

Question

My tables:

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

Thus, the elements in TableC are children of TableB, and the elements in TableB are children of TableA. Conversely, the elements in Table A are the parents of TableB, and the elements in TableB are the parents of TableC.

I would like to monitor the state of the parent elements ... 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 

Parental status should always have the smallest state of their children. Therefore, if we now update TableC as follows:

 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 would like to do this using triggers (AFTER UPDATE, INSERT, DELETE) in TableA, TableB, TableC), so that after each action these steps are performed:

• get parent id
• find the smallest state of all children of the current parent
• if the smallest state of all children is greater than the parent state, then update the parent

How can I avoid the "mutating table" error? Is autonomous transaction usage maintained in this example? I saw several opinions that a mutating table error indicates flaws in the application logic - this is true and how can I change my logic 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 an article by Tom Keith).

---

EDIT: In REAL END, I used stored procedures and remote triggers :)

Answer 1

As you noticed, it will be difficult to respond to your business requirements with triggers. The reason is that Oracle can update / insert tables with multiple threads at the same time for one query (parallel DML). This means that your session cannot query the table in which it is updating while the update is in progress .

If you really want to do this with triggers, you will need to follow the kind of logic shown in this article by Tom Keith . As you can see, this is not easy.

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

These procedures would lock the parent row (to prevent multiple sessions from modifying the same rowset) and apply your business logic in an efficient, understandable, and easily maintained manner.

Answer 2

You should not use triggers for complex business logic. Move it to a saved proc (PL / SQL package) or client code. Applications with many triggers become impregnable because you will soon lose the sense of "sequence of actions."

Using offline transactions is completely unsafe; use an offline transaction only for logging, tracking, debugging, and possibly auditing.

Read: http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html

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

Answer 3

Can you reorganize the solution to include views for 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 saved procs (as suggested here in other posts) are also a good candidate, but note that the view will be automatically updated, whereas I suppose you have to plan the launch of stored procedures to keep the data “in sync”: this may be good - it depends on your requirements.

I assume that another option is to create some functions for the calculation, but personally I would choose the approach to the representation (ceteris paribus).

Answer 4

I saw several opinions that a mutating table error indicates flaws in the application logic - this is true and how can I change my logic to prevent this error?

I don’t know where you saw it, but I know that I have expressed this opinion many times.

Why do I think mutating tables usually indicate a lack of data model? Because the “requirement” that drives the code that ORA-4091 throws is often due to poor design, especially with insufficient normalization.

This scenario is a classic example. You get ORA-04091 because you select TableC when you insert or update. But why do you choose from TableC ? Because you “need” to update the parent column, TableB . But this column is redundant information. In a fully normalized data model, this column will not exist.

Denormalization is often touted as a mechanism to improve query performance. Unfortunately, proponents of denormalization ignore its value, which is paid in a currency of excessive complexity when inserting, updating, and deleting.

So how can you change your logic? The simple answer is to delete the columns and do not bother saving the smallest state by parent ID. Instead, run the MIN() query whenever you need this information. If you need it often, and it would be expensive to complete the request, then you will create materialized views that store data (be sure to use ENABLE QUERY REWRITE )

Answer 5

You can use both triggers and integrity constraints to define and apply a rule of any type. However, Oracle strongly recommends using triggers to restrict data entry only in the following situations:

To ensure referential integrity when different nodes of a distributed database are included on the child and parent tables To ensure a comprehensive business, rules that are not defined by constraint integrity When a referential integrity rule cannot be observed with the following constraint integrity:

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

source: Oracle9i Database Concepts

Answer 6

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

Now, someone updates state 1A to 20, while at the same time, someone removes 1B. Since 1B deletion is not complete, transaction update 1A will still see 1B and will want to set the parent state to 15, while a transaction deleting 1B will see the old uncommitted value 1A and will want the parent state to be 10.

If you de-normalize this, you must be very careful about locking so that before inserting / updating / deleting any child records, the parent record is locked, make changes, select all child records, update the parent, then commit locks. Although this can be done using triggers, you are best served with a stored procedure.

Answer 7

Doing this kind of thing is a great temptation, and if you follow the recommendations in the article by Tom Keith that others refer to, it is possible. However, just because something can be done does not mean that he should . I highly recommend you implement something like this as a stored procedure / function / package. Complex logic of this kind should not be performed using triggers, despite the obvious temptations, as this greatly increases the complexity of the system without a corresponding increase in utility. I sometimes have to work on code, and this is not a joy.

Good luck.

Answer 8

Do not use offline transactions, or you will get very interesting results.

To avoid the problem with changing tables, you can do the following:

AFTER INSERTING OR UPDATING OR REMOVING FOR EACH ROW TRIGGER, find the identifier of the parent and save it in the PL / SQL collection (inside the PACKAGE). Then, AFTER INSERTING OR UPDATING OR DELETING A TRIGGER (instruction level, without the "for each row" part), read the parent identifiers from the PL / SQL collection and update the parent table accordingly.