Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Trigger procedure problem

RE: Trigger procedure problem

From: Thapliyal, Deepak <DThapliyal_at_ea.com>
Date: Thu, 28 Sep 2000 17:28:41 -0700
Message-Id: <10633.118194@fatcity.com> 





very good explanation diana



-----Original Message-----


From: Diana Duncan [mailto:Diana_at_fileFRENZY.com]
Sent: Thursday, September 28, 2000 4:06 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Trigger procedure problem




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





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


Sent: Thursday, September 28, 2000 11:31 AM
To: Multiple recipients of list ORACLE-L




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






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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Diana Duncan
  INET: Diana_at_fileFRENZY.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 - 19:28:41 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US