ORA-4091: Mutating Tables...

In one of the applications I'm trying to maintain, we want to move some functionality from application software into database triggers.

There are two tables: call them master and detail. The detail table has a field called 'status', as does the master table.

I would like to add a trigger to the detail table so that when all of the rows in detail that are related to master have bee updated, such that all detail.status fields = 'DONE', the trigger updates the status field in the master table.

1. What this looks like:

  Using this snapshot as an example:

  master: detail:   

  id	status		id   id2	status
  -------------------	-------------------
  m1	'NOT DONE'	m1    1		'NOT DONE'
  			m1    2		'DONE'
  			m1    3		'NOT DONE' 
  

2. What should happen...

  action on detail			effect on master
  ------------------------------	--------------------
  update detail row m1.1 		none

  update detail row m1.3 		update master row m1
    so that detail.status='DONE'	  so that master.status='DONE'
  

Whenever I try this (creating a trigger that calls a procedure to check status in detail for all 'm1' row, the error (ORA-04091 : mutating table) says something to the effect that I can't access the same table that I've put a trigger on.

Any suggestions would be appreciated...

Thanks,

Ray Kruger
kruger_rc_at_corning.com Received on Tue Jun 14 1994 - 23:22:05 CEST