RE: Trigger procedure problem

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Thu, 28 Sep 2000 17:59:40 -0400
Message-Id: <10633.118188@fatcity.com>

OK! Good to know. I know I was looking at the code and wondering "What the heck...?"

So, first let me explain what some of your problems stem from: 1. 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.
2. 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.
3. 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.
4. 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: Kader Ben [mailto:kaderb_at_yahoo.com] Sent: Thursday, September 28, 2000 11:31 AM To: Multiple recipients of list ORACLE-L Subject: RE: Trigger procedure problem

Thanks for your replies,

   I want just check if the new value for epoch column is already exists if so exit else I do the insertion. TIA Kader

• Gunawan Yuwono <gunawan.yuwono_at_webbox.com> wrote: > > Could you explain in more detail what you want to > accomplish > from this trigger? > > If the stored procedure is executed inside the > trigger, won't > it try to fire the trigger again since the stored > procedure itself > is trying to insert into the table? Or am I missing > something > here? > > > > Gunawan Yuwono > Oracle DBA > Kansas City, MO > > >--- Original Message --- > >From: Kader Ben <kaderb_at_yahoo.com> > >To: Multiple recipients of list ORACLE-L > <ORACLE-L_at_fatcity.com> > >Date: 9/28/00 4:00:43 AM > > > > >Hi gurus, > > Thanks for ypur replies. As suggested from most > of > >you. To workaroud my mutating table/trigger. I > create > >a trigger to execute a procedure that do an insert. > >But I run into an onther error (ORA-01403: no data > >found). > > > >Thank you for your help. > > > >-------------------------- > > > >SQL> create or replace procedure > PROC_WEB_ACTU_STAT( > > x WEBFIN_ACTU_STAT.id%TYPE, y > >WEB_ACTU_STAT.epoch%TYPE) > >AS > > > >BEGUIN > > > > insert into WEBFIN_ACTU_STAT(ID, EPOCH) VALUEs(x, > >y); > > commit; > >END; > >/ > >----------------------- > > > >SQL> CREATE OR REPLACE TRIGGER TRG_WEB_ACTU_STAT > > > > BEFORE INSERT OR UPDATE ON TFIN.WEB_ACTU_STAT > > FOR EACH ROW > > DECLARE > > epoch_stat tfin.webfin_actu_stat.epoch%TYPE; > > BEGIN > > SELECT epoch INTO epoch_stat FROM > TFIN.WEB_ACTU_STAT > > WHERE TFIN.WEB_ACTU_STAT.epoch = > :new.epoch; > > IF epoch_stat IS NULL > > THEN > > PROC_WEBFIN_ACTU_STAT(:new.id, :new.epoch); > > END IF; > > END; > > > > / > > > >---------------------- > > > >SQL> desc TFIN.WEB_ACTU_STAT > > > > Name Null? Type > > ------------------------------- -------- ---- > > ID NOT NULL > NUMBER(10) > > EPOCH NOT NULL > VARCHAR2(19) > > COMPTEUR NUMBER(7) > > > >---------------------------------------------------- > > > >SQL> insert into WEB_ACTU_STAT(ID, EPOCH) VALUES(1, > >'1452'); > > > >insert into WEBFIN_ACTU_STAT(ID, EPOCH) VALUES(1, > >'1452') > > * > >ERROR at line 1: > > > >ORA-01403: no data found > > > >------------------------------------------- > > > >__________________________________________________ > >Do You Yahoo!? > >Yahoo! Photos - 35mm Quality Prints, Now Get 15 > Free! > >http://photos.yahoo.com/ > >-- > >Please see the official ORACLE-L FAQ: > http://www.orafaq.com > >-- > >Author: Kader Ben > > INET: kaderb_at_yahoo.com > > > >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). > > > > > ---------------- > Sent from a WebBox - http://www.webbox.com > FREE Web based Email, Files, Bookmarks, Calendar, > People and > Great Ways to Share them with Others! > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Gunawan Yuwono > INET: gunawan.yuwono_at_webbox.com > > 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).

  ---

Do You Yahoo!?
Yahoo! Photos - 35mm Quality Prints, Now Get 15 Free! http://photos.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kader Ben
  INET: kaderb_at_yahoo.com

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

Received on Thu Sep 28 2000 - 16:59:40 CDT