Trigger mutating error

From: Jordi Sanmarti <JSanmarti_at_tss.com.pe>
Date: Fri, 20 Oct 2000 13:20:46 -0500
Message-Id: <10655.119870@fatcity.com>

        Hi to all,

	I've got the following error during the execution of a trigger : 
	"-- ORA-20000: ORA-04091: table SCOTT.EMP is mutating,

trigger/function may not see it trg_emp_upd. "

        The trigger looks like this :

	CREATE TRIGGER scott.trg_emp_upd
	AFTER UPDATE ON scott.emp 
	FOR EACH ROW 
	DECLARE
	  countrec number;
	BEGIN
	   ...
	   select count(1) into countrec from scott.emp;
	   if countrec > 0 then
	     ....
	   end if;
	   ...    
	END;

	I presume the problem is that Oracle doesn't handle dataset records

of the same table which the trigger related is. The only solution I've found is to migrate the code into a procedure and be sure to run it through the application when an update occur.

        For example, that kind of dataset operation is feasible using sql-server but I'm not sure whether Oracle supports it.

        Does anybody know another solution as to how to get rid of this problem? Is there a special parameter to be used in the trigger syntax?

        Thanks in advance, Received on Fri Oct 20 2000 - 13:20:46 CDT