• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Error on trigger

 
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 http://asktom.oracle.com, 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).
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic