RE: Trigger mutating error

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Fri, 20 Oct 2000 14:35:47 -0400
Message-Id: <10655.119873@fatcity.com>

Cause I'm lazy, this is a copy of a message I sent to the list in September...the example is not particular to your situation, but the explanation and method applies:

You can't look at or touch records in the table that the trigger is on in a row trigger. This is because the rows are changing, and in an uncertain state. Oracle cannot guarantee that what you are seeing is consistent, hence the "mutating tables" error. Even if you call a procedure in that trigger, the procedure can't look at or touch the records. And there is no way that you can perform an insert on the mutating table from a "for each row" type of trigger.
When you do a "Select Into" statement, Oracle will complain if it didn't find something. The best way to avoid the complaint is use an explicit rather than implicit cursor. Then, you can check the cursor attribute %notfound.
You actually haven't encountered this problem yet, but you can't do "commit" or "rollback" statements from within a trigger either. That would subvert Oracle's transaction handling.
There is no way to "exit" from an insertion that is already in progress without throwing an error. So, you will have to handle the error somehow in your application code -- you could just ignore it. ;-) The other way would be to have a procedure called in the "after table" trigger that actually goes and deletes the record you just inserted.

That being said, you need to do something like this: (The examples given previously were very good, but let's just reiterate)

create or replace package dupCheckPkg is

	/*


	 * This structure is populated by the before insert or update

trigger,


	 * then is processed in the after insert or update trigger for the

entire


	 * table.  This avoids the mutating tables error.

	 */
	type statTabType is table of tfin.web_actu_stat.epoch%type index by
binary_integer;
	statTab	statTabType;

	/*


	 * checkDuplicates checks the web_actu_stat table for existing rows

with the same


	 * epoch as the rows awaiting insertion.  If an there is an existing

row, return 


	 * TRUE.

	 */
	function checkDuplicates return boolean;

end dupCheckPkg;
/

create or replace package body dupCheckPkg is

	function checkDuplicates return boolean is
		-- This is where you use an explicit cursor
		cursor do_check (p_epoch in varchar2) is	
			select 'X'
			from tfin.web_actu_stat
			where epoch = p_epoch;
		dummy	varchar2(1);
		i	binary_integer;
	begin
		for i in 1..nvl(statTab.LAST,0) loop
			open do_check(statTab(i));
			fetch do_check into dummy;
			close do_check;
		end loop;

		statTab.DELETE;		
		if (dummy = 'X') then
			return TRUE;
		else
			return FALSE;
		end if;
	end checkDuplicates;

end dupCheckPkg;
/

create or replace biur_web_actu_stat
before insert or update on tfin.web_actu_stat for each row
begin

Here, add the epoch to the list in the package. You probably only care
to do it in updates when the value has actually changed. -- if (INSERTING or :old.epoch != :new.epoch) then dupCheckPkg.statTab(nvl(dupCheckPkg.statTab.LAST,0) + 1) := :new.epoch; end if; end biur_web_actu_stat; /

create or replace trigger aiu_web_actu_stat after insert or update on tfin.web_actu_stat declare

duplicate boolean := FALSE;
begin

duplicate := dupCheckPkg.checkDuplicates;

	if duplicate then
		raise_application_error(-20102, 'This epoch already
exists.');
	end if;

end aiu_web_actu_stat;
/

As per usual, this is untested code (although I took it from a similar check I do, so it
should work barring any typing mistakes). I hope this has increased your understanding of triggers! ;-)

Regards,

Diana

-----Original Message-----
From: Jordi Sanmarti [mailto:JSanmarti_at_tss.com.pe] Sent: Friday, October 20, 2000 3:20 PM
To: Multiple recipients of list ORACLE-L Subject: Trigger mutating error

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,
	Jordi

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

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

Received on Fri Oct 20 2000 - 13:35:47 CDT