Re: Trigger mutating error

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Fri, 20 Oct 2000 14:48:46 -0400
Message-Id: <10655.119874@fatcity.com>

Your presumption is correct about Oracle handling the updates of a row it = is currently using. According to Mike Ault's literature "The usual fix = involves either use of views or temporary tables so the database is = selecting from one while updating the other".  I myself have not run into this problem so I can't verify the cure. Hope this helps.
ROR =AA=BF=AA
>>> JSanmarti_at_tss.com.pe 10/20/00 03:20PM >>>

        Hi to all,

        I've got the following error during the execution of a trigger = :=20

        "-- 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=20
	FOR EACH ROW=20
	DECLARE
	  countrec number;
	BEGIN
	   ...
	   select count(1) into countrec from scott.emp;
	   if countrec > 0 then
	     ....
	   end if;
	   ...   =20
	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,
	Jordi

--=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com=20 --=20
Author: Jordi Sanmarti
  INET: JSanmarti_at_tss.com.pe=20

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Oct 20 2000 - 13:48:46 CDT