• 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 all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Bear Bibeault
  • Knute Snortum
  • Liutauras Vilda
  • Tim Cooke
  • Devaka Cooray
  • Paul Clapham
Saloon Keepers:
  • Tim Moores
  • Frits Walraven
  • Ron McLeod
  • Ganesh Patekar
  • salvin francis
  • Tim Holloway
  • Carey Brown
  • Stephan van Hulst

Error on trigger  RSS feed

Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • 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?
Posts: 3752
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • 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).
You didn't tell me he was so big. Unlike this tiny ad:
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!