The moose likes JDBC and Relational Databases and the fly likes Error on trigger Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Error on trigger" Watch "Error on trigger" New topic

Error on trigger

sovan chatt
Ranch Hand

Joined: Aug 09, 2010
Posts: 43
I have a trigger which fires before the insert and delete operation on a table,and before insertion the no of employees column(per department) will be increased by 1,before deletion the no. of employees column is going to be decreased by 1;

but the problem is if I try to delete or update I get the error
ERROR at line 1:
ORA-04091: table SCOTT.EMPDEPT is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.T1", line 5
ORA-04088: error during execution of trigger 'SCOTT.T1'.What to do?
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

You're modifying the same table in the trigger on which the trigger is defined. This can cause consistency issues and therefore the "mutating table" exceptions are generated to prevent such inconsistencies from happening. You might search for "mutating table" on, it has been discussed there thoroughly and some workarounds have been offered there. I believe that Tom Kyte's position on this topic is that you should not use triggers at all, and if you do, you should not modify the table your trigger is defined on, exactly to avoid mutating table problem. It is very easy to make subtle and difficult-to-find bugs with triggers and you should know Oracle very well if you want to use them (at least so well to know about the mutating table problem before it jumps on you, that is).

Another consideration is that you're incrementing the noe column in the very row which is being deleted or inserted in your trigger, which can probably never work, and even if it did, it would not make any sense. Didn't you actually try to keep the count of related records in a parent table? Instead of triggers, I'd suggest to simply count the rows using the count function (it might be made faster by adding an index perhaps), or, if the table is really really huge, use materialized views (after learning about them).
I agree. Here's the link:
subject: Error on trigger
jQuery in Action, 3rd edition